80. USING DDL

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

    Ex:

        SQL> alter table student add sdob date;

b) REMOVING COLUMN

    Syntax:

        alter table <table_name> drop <col datatype>;

    Ex:

         SQL> alter table student drop column sdob;

c) INCREASING OR DECREASING PRECISION OF A COLUMN

    Syntax:

          alter table <table_name> modify <col datatype>;

    Ex:

          SQL> alter table student modify marks number(5);

          * To decrease precision the column should be empty.

d) MAKING COLUMN UNUSED

    Syntax:

         alter table <table_name> set unused column <col>;

    Ex:

         SQL> alter table student set unused column marks;

   

         Even though the column is unused still it will occupy memory.

d) DROPPING UNUSED COLUMNS

    Syntax:

        alter table <table_name> drop unused columns;

    Ex:

        SQL> alter table student drop unused columns; 

        * You can not drop individual unused columns of a table.

e) RENAMING COLUMN

    Syntax:

        alter table <table_name> rename column <old_col_name> to <new_col_name>;

    Ex:

        SQL> alter table student rename column marks to smarks; 

USING TRUNCATE

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

Syntax:

      truncate table <table_name>;

Ex:

     SQL> truncate table student;

USING DROP

This will be used to drop the database object;

Syntax:

     Drop table <table_name>;

Ex:

     SQL> drop table student;

USING RENAME

This will be used to rename the database object;

Syntax:

     rename table_name

> to <new_table_name>;

Ex:

     SQL> rename student to stud;