SQL - Data Definition Language Commands

·         Data Definition Language Commands:-

1.Create: By using this command we can create any data base objects like tables, procedures, functions, view, sequence , triggers etc….

Syntax:  create table Student(Student_id number(10),Student_name varchar2(10));

2.Alter: By using this command we can change the structure of the existing data base object. Here Structure means the layout(ex: removing columns, adding columns, renaming columns etc..) but not the data inside the data base object.

Syntax: alter table student add student_marks number(10);

             alter table student add (student_marks number(10), student_address                           varchar2(10));

Alter+modify:  By using this command we change the datatypes of columns and also we can increase or decrease the size of the data base table columns

Syntax: alter table student modify (student_id varchar2(10), student_name varchar2(20));

Alter+drop: By using this command we can remove the columns from the existing data base object  like table.

Syntax: alter table student drop (student_id,student_name);

Alter+rename: By using this command we can rename the existing columns ,but it is not possible to rename more than one column at a time.

Syntax: alter table student rename student_id to student_no;

We can drop maximum 999 columns from a table because every table should contain atleast one column

3.Drop: By using this command we drop the data base object from the database

Syntax: drop table student;

From Oracle 10g onwards if we drop any table, that table is going to be stored into the recycle bin memory area

Syntax: show recyclebin;

In order to get back those tables we can use a command called flashback which was introduced in oracle 10g

Syntax: flashback table student to before drop;

Purge: By using this command ,we can drop the database objects from the database without storing in to the recyclebin memory area of the database.

Syntax: drop table student purge;

We can also use purge Query to clear the recycle bin

Syntax: purge recyclebin;

4.Truncate: By using this command we can remove the entire records from the data base object but we cannot remove the structure.

 It is not possible to get back the truncated data.

Syntax: truncate table student;

5.Rename: By using this command we can change the names of the existing database objects.

Syntax: rename student to student_info;