Data definition language (DDL) in SQL

Neil Bhanja
Data definition language is a sub language of sql respond able to create and manipulate table structure in oracle to do so DDL provide following statement
1.      Create
2.      Alter
3.      Drop
4.      Truncate
5.      Rename

 Syntax of create a table

Create table <t_name>
( col_name datatype(size),
Col_name datatype(size),
Col_name datatype(size)
................ n );

 Ex
Create table sysemp
(empno number(4), name varchar2(10), sal number(7,2));
The above ex create a table structure physical in oracle data base

 NOTE:
       Once table structure is created to display the structure of table user can use describe, desc command
SQL> desc sysemp
Modification using Alter:
Alter of table means
è  Add after new column after table design
è  Deleting of exiting column
è  Changing a data type of column
è  Changing the size of table

Add and Modifies:

Alter table <t_name>
Add/Modify (c_name datatype(size), col_name datatype(size)..................N);

NOTE :
       To add a new column, to delete a exiting column or change size of column is possible weather table not contain the data but change the data type of column a column must be empty.

 Syntax to delete and exiting column:

 Alter table<t_name>
Drop <col_name>
Above command is required to delete single column from exiting column
Alter table<t_name>
Drop (col1, col2, col3.....N);
Above column required to delete multiple columns.
Ex
1)      Alter table sysemp
Drop job;
2)      Alter table sysemp
Drop job,name;

 NOTE:
“User can not be delete all column of table using alter.”

 Deleting table structure using drop
Drop table <t_name>;
Ex
Drop table sysemp;
Above example will delete structure as well as data of a table.
Deleting all records of table physical remaining the structure using truncate.

 TRUCATE TABLE <T_NAME>;
Ex
Truncate table sysemp;

 Rename a table using rename
RENAME <T1_NAME> TO <T2_NAME>;
Ex
Rename sysemp to sysemp2;