Data definition language (DDL) in SQL

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.

Syntaxto 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;