PL/SQL>SQL>Data Definition Language

Data Definition Language(create,alter,drop,truncate,rename)

1) CREATE

      Syntax:  Create table table_name (col1 datatype1, col2 datatype2 …coln datatypen);

      Example:  SQL> create table Graduates (no number (2), name varchar (10), marks number (3));

2) ALTER

This can be used to add or remove columns and to modify the precision of the datatype.

a) Adding Column:

      Syntax:  alter table table_name add col datatype;

      Example:  SQL> alter table Graduates add sdob date;

b) Removing Column:

      Syntax:  alter table table_name drop >col datatype;

      Example:  SQL> alter table Graduates drop column sdob;

c) Increasing or decreasing precision of a Column:

      Syntax:  alter table table_name modify >col datatypep>

      Example:  SQL> alter table Graduates modify marks number(5);(* To decrease precision the column should be empty.)

d) Making Column Unused:

      Syntax:  alter table table_name set unused column ;

      Example:  SQL> alter table Graduates set unused column marks;(* Even though the column is unused still it will occupy memory.)

e) Dropping Unused Columns:

      Syntax:  alter table table_name drop unused columns;

      Example:  SQL> alter table Graduates drop unused columns;(* You cannot drop individual unused columns of a table.)

f) Renaming Column:

      Syntax:  alter table table_name rename column old_col_name to new_col_name;

      Example:  SQL> alter table Graduates rename column marks to smarks;

3) TRUNCATE

This can be used to delete the entire table data permanently.

      Syntax:  truncate table table_name;

      Example:  SQL> truncate table Graduates;

4) DROP

This will be used to drop the database object.

      Syntax:  Drop table table_name;

      Example:  SQL> drop table Graduates;

5) RENAME

This will be used to rename the database object.

      Syntax:  rename old_table_name to new_table_name;

      Example:  SQL> rename Graduates to Graduate;

Go Back