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;