Division of Oracle SQL PL/SQL

There are three major categories: 

1. Data definition language (DDL): This is used a set of commands that defines data base objects. (Create, alter, drop and Rename). 

(i) Create table Command : The Create Table Command defines each column of the table uniquely. Each column has a minimum of three attributes, a name, data type and size (i.e. column width). 

Syntax : 

Create table <table name> (<column1> <Data Type> [Size], 

<column2> <Data Type> [Size],------------------------------<column N> <Data Type> [Size]); 

Eg., Create Table Sample ( sno number(3), sname varchar2(10)); 

(ii) Alter Table : 

It is used to change the structure of the Table i.e. adding new column, changing the data type and size. The alter command can have 3 types of sub commands. They are:

2)ADD: By using this command we can add new columns to the existing table. 

Syntax:

Alter Table <Table_Name> add (Column1 <data_type>[<size>],

Column2 <data_type>[<size>],- - - - -,Column(n) <data_type>[<size>])

Modify: It is used to change the Data Type and size of the existing columns. If you can change the Data Type and Size you must satisfy the following rules.

By using Modify command in alter we cannot change the column name.

We can not Change the Positions of the existing or new columns

We cannot decrease the length (Size) of an existing column, if that column is having values. But we can increase the size of the existing column even if the data is present

Syntax:

Alter Table <Table_Name> Modify (Column1 <data_type>[<size>],Column2 <data_type>[<size>],- - - - -,Column(n) <data_type>[<size>])

3. Drop This command is introduced in Oracle 8i. It is used to remove the column of a table.

Syntax:

Alter Table <table_name> Drop column <column_name>

Adding single column :

Alter table students Add (total number(4));

Adding Multiple column :

Alter table students Add (average number(6,2),result char(4));

3. Drop Command

This command is used to drop or delete any table from the database.

Syntax: Drop table <table_name>

Example: Drop table students;

2.Data Manipulating Language (DML): These are used to view, update, adding record, and removing records (select, update, insert, delete).

(i) Select Statement :

The select statement is used to display the details of a table

Syntax :

Select [ * | Distinct | <column list>] from <table name>[ Where <condition > ][ Group by <column name(s) > ][ Having <Condition> ][ Order by <Expression > ]

Examples :

1. To display all Tables List

select * from tab;

2. To display a particular table details

select * from emp;

3. to display particular columns in a table

select empno, ename, job from emp;

(ii) Update Command :

This command is used to update or modify all or specified column values with new values.

Syntax:

Update < Table Name > set <column1> = <value1>, <column2>=<value2>, -------<column N>=<value N> where <Condition>

Examples:

(i)To update only one column value

Update sample set sno=500 where sno=100;

(ii)To update Multiple column values

Update sample set sno=111,sname=’ Nandhini’ where sno=500;

(iii)To update all values with same value

Update sample set sno=200;

(iii) Insert Command :

Syntax :

Insert into <table name>(columns list) values(sequencename.nextval,……);

eg : Insert into Student Values (100,’Naveen’) ;

(iv) Delete Command :

This Command Is Used To Delete All Or Specified Rows In A Table

Syntax : Delete From <table name> where <condition>

Eg: (i) To Delete A Single Row

Delete from sample where sno=109;

Eg : (2)To Delete Multiple Rows (More Than One Row)

Delete from sample where sno=106 or sno=108;

3.Data Control Language (DCL) or Transaction Control Language (TCL) : It supports grant, revoke , commit and role back commands.

(i) A commit statement guarantees all of the transactions, modifications are made permanent part of the data base. By default, all your transactions are temporarily stored in the database.

Syntax : commit;

(ii) This Rollback command is used to undo work done in the current transaction. Ie the user can continue with any number of inserts, updates and / or deletion, and still undo the work, issuing the Rollback Command.

Syntax : Rollback To [Savepoint] Savepoint_Name

optional

(iii) Save point command Sets a save point within a transaction or to identify a point in a transaction to which you can later roll back.

Syntax :

Savepoint savepoint_name;