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).
CREATE TABLE Dept
(DID Number(4),
DName Varchar2(20),
Loc Varchar2(20),
MID Number(4));
- Column Constraints : Constraints are rules which are forced on database to follow them for consistency purpose
*Attribute or Column are synonyms.
- 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
UNIQUE
Loc Varchar2(20),
MID Number(4));
OR
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.
Syntax:
(i) To add a constraint:
ALTER Table <table name>
ADD CONSTRAINT <condition>;
(ii) To drop a constraint:
ALTER Table <table name>
DROP CONSTRAINT <constraint name> CASCADE CONSTRAINTS;
(iii) To enable a constraint:
ALTER Table <table name>
ENABLE CONSTRAINT <constraint name>;
(iv) To disable a constraint:
ALTER Table <table name>
DISABLE CONSTRAINT <constraint name> CASCADE;
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.
DESCRIBE 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),
ADDRESS VARCHAR2(25));
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.
DESCRIBE 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.
DROP TABLE Student;
Source: Gupta Satinder Bal, Mittal Aditya (2017), Introduction to Basic Database Management System, 2nd Edition-University Science Press (2017)