Structured Query Language: Data definition language (DDL)

SQL DDL commands are used to create, modify or remove database structures including tables. These commands have an immediate effect on the database, and also record information in the data dictionary. The following examples show working of some of the DDL commands.

1. Create Table

Create table statement is used  to create new tables  in database.

Syntax :     CREATE TABLE <table name>

(<column name <data type (size)>,


Example 51 : Create a table Dept with attributes DID (Department ID), DName (Department Name), Loc (Location), MID (Manager ID).


(DID                Number(4),

DName          Varchar2(20),

Loc                   Varchar2(20),

MID                  Number(4));

  1. Column Constraints : Constraints are rules which are forced on database to follow them for consistency purpose


*Attribute or Column are synonyms.

  1. DEFAULT Value : It assigns a default value to an attribute if at the time of insertion of new row, no  value is  given to  that attribute.

Example 52 : Create a table student with attributes SID (student ID), Name, Fee and default value  of  fee  is  zero.

CREATE        TABLE                   Student

(      SID         Number(4),

    Name        Varchar2(20),

       Fee          Number(4)           DEFAULT 0,);

It is better to give name to constraints so that later you can drop them easily.

Example 53  : Repeat ex-2 with one more constraint, that the name of student must not be null.

Example 54  : Create a table, Dept, with attributes DID, DName, Loc and MID. DName must be unique.

CREATE         TABLE                 Dept

(                         DID                   Number(4),

DName                    Varchar2(20),

Constraint      dname-unique


    Loc                       Varchar2(20),

    MID                      Number(4));


CREATE         TABLE                 Dept

(                         DID                    Number(4),

DName                    Varchar2(20),

Loc                          Varchar2(20),

MID                         Number(4),

CONSTRAINT          dname_unique UNIQUE (DName));

Example 55 : Repeat Ex-3 with one more constraint that DID acts as primary key.

CREATE         TABLE                 Dept

(                         DID                   Number(4),

DName                   Varchar2(20),

Loc                         Varchar2(20),

MID                        Number(4),

CONSTRAINT did_pkey                         PRIMARY KEY(DID),

CONSTRAINT dname_unique              UNIQUE(DName));

Example 56 : Create a table student with attributes SID, Name, Fee and it is ensured that at the time of insertion of values in table, value of Fee must be more than 0.

CREATE           TABLE               Student

(        SID                                   Number(4),

     Name                                   Varchar2(20),

        Fee                                    Number(4),

CONSTRAINT fee_get_zero

CHECK(Fee > 0));

Example 57 : Create a table Emp with attributes EID (Employee ID), Name, Salary, Hire-date, Job, DID (department ID), MID (manager ID).

Constraints – EID acts as primary key.

– Hire-date must not be

– DID acts as  foreign key  that  acts as  primary  key in  Dept

When Emp table is created, at that time Dept is not created. So, it is not possible to apply foreign key constraint here. So, after creating table Dept, add this constraint. So, that it will be dealt later on.

CREATE       TABLE                           Emp

(                         EID                           Number(4),

Name                             Varchar2(30),

Salary                             Number(6),

Hire-date                        Date, NOT NULL,

Job                                  Varchar2(20),

DID                                 Number(4),

MID                                 Number(4),

CONSTRAINT               eid_pkey              PRIMARY KEY (EID));

Example 58 : Create a table Dept with attributes DID (department ID), DName (department name), Loc (location), MID (manager ID)

Constraints – DID acts as primary key.

– DName must be unique.

CREATE      TABLE            Dept
(                        DID          Number(4),
DName         Varchar2(20),
Loc             Varchar2(20),
MID             Number(4),
CONSTRAINT            did_pkey Primary Key(DID),
CONSTRAINT            dname_unique UNIQUE(DName));

2. Alter Table Statement

Alter table statement is used to add or drop a constraint. It can also be used to disable or enable any constraint.


(i)     To add a constraint:

ALTER Table <table name>

ADD CONSTRAINT <condition>;

(ii)    To drop a constraint:

ALTER Table <table name>


(iii)    To enable a constraint:

ALTER Table <table name>

ENABLE CONSTRAINT <constraint name>;

(iv)     To disable a constraint:

ALTER Table <table name>


Note: Cascade is  used  to disable  or  drop dependent  integrity  constraints,  otherwise it  is  not needed.

Example 59 : Consider ex-6 and ex-7. After creating table Dept, it is possible to add foreign key  constraint  in  Emp  table.

ALTER      TABLE                                Emp

ADD         CONSTRAINT                    emp_did_fk

      FOREIGN Key   (DID)

      REFERENCES Dept (DID);

Example 60 : Consider ex-5. To drop constraint on fee later on.

ALTER                TABLE                    Student

DROP                 CONSTRAINT      fee_gt_zero;

3. Describe Statement

It describes the structure of table.

Syntax : DESCRIBE <table name>;

Example 61 : Describe the  structure of table  Emp.


In future, if you want to further modify the table by adding some more columns or by droping any column, you can do by using ALTER TABLE statement.

4. Alter Table Statement (Continue)

Alter table statement is also used to add or drop columns of tables and to modify name and attributes of an existing column.

(v)     To add new column:

ALTER TABLE <table name>

ADD (<column name> <data type(size)>);

(vi)     To drop a column:

ALTER TABLE <table name>

DROP COLUMN <column name>;

(vii)     To modify a column:

ALTER TABLE <table name>

MODIFY (<column name> <new data type |new size| new default value>);

Example 62 :

62 (a)        Create  a table student with attributes SID,  Name and Address.

CREATE     TABLE             Student


SID                  NUMBER(4),

NAME             VARCHAR2(20),


62 (b)        Add a new column fee in table student.

ALTER      TABLE          Student

ADD            (FEE            NUMBER(4));

62 (c)         Drop column address from table student

ALTER              TABLE             Student

DROP              COLUMN          ADDRESS;

62 (d)        Modify column NAME (increase size to 30)

ALTER         TABLE               Student

MODIFY      (Name              VARCHAR2(30));

62 (e)         Describe the structure of table student.


5. Drop Statement

Drop table statement is used to remove table from database.

Syntax :       DROP       TABLE <table name>;

Example 63  : Remove table  student (Example-62)  from database.


Source: Gupta Satinder Bal, Mittal Aditya (2017), Introduction to Basic Database Management System, 2nd Edition-University Science Press (2017)

Leave a Reply

Your email address will not be published. Required fields are marked *