DDL Alter, Drop, Rename, Truncate

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 <old_table_name> to <new_table_name>;
Ex:
SQL> rename student to stud;

 

Related posts:

Leave a Reply