DDL statement

DDL (Data definition language) is used to create a schema ,table and contains ,

• create
• alter
• drop
• truncate
• rename

Create : To create a table in database.
SQL> create table emp(id number(3), name varchar2(15), salary number(5));

Table created.

SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                      NUMBER(3)
 NAME                                               VARCHAR2(15)
 SALARY                                            NUMBER(5)

Alter: To make changes to existing table structure we use alter at following levels

• add
• modify
• rename
• drop

ADD
SQL> alter table emp add did number(3);

Table altered.

SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                      NUMBER(3)
 NAME                                               VARCHAR2(15)
 SALARY                                            NUMBER(5)
 DID                                                    NUMBER(3)

DROP
SQL> alter table emp drop column did;

Table altered.

SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                      NUMBER(3)
 NAME                                               VARCHAR2(15)
 SALARY                                            NUMBER(5)

SQL> alter table emp add did number(3);

Table altered.

SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                      NUMBER(3)
 NAME                                               VARCHAR2(15)
 SALARY                                            NUMBER(5)
 DID                                                    NUMBER(3)

RENAME

SQL> alter table emp rename column did to dno;

Table altered.

SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                       NUMBER(3)
 NAME                                               VARCHAR2(15)
 SALARY                                            NUMBER(5)
 DNO                                                  NUMBER(3)

MODIFY

SQL> alter table emp modify dno number(10);

Table altered.

SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                      NUMBER(3)
 NAME                                               VARCHAR2(15)
 SALARY                                            NUMBER(5)
 DNO                                                   NUMBER(10)

Truncate : To delete a specific row of data we use truncate statement.

SQL> truncate table emp;

Table truncated.

SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(3)
 NAME                                               VARCHAR2(15)
 SALARY                                             NUMBER(5)
 DNO                                                NUMBER(10)

Rename : To rename a column,table we use rename statement

SQL> rename  emp to employee;

Table renamed.

SQL> desc emp;
ERROR:
ORA-04043: object emp does not exist


SQL> desc employee;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(3)
 NAME                                               VARCHAR2(15)
 SALARY                                             NUMBER(5)
 DNO                                                NUMBER(10)

Drop : To delete a table or schema from a database we use drop statement.

SQL> drop table employee;

Table dropped.

SQL> desc employee;
ERROR:
ORA-04043: object employee does not exist

Take the test now