1/28/16

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;



0 comments:

Post a Comment