Data Definition Language (DDL) in sql

Create Table Construct
- An SQL relation is defined using the create table command:
create table r (A1 D1, A2 D2, ..., An Dn,
(integrity_constraint1),
...,
(integrity_constraintk))

• r is the name of the relation
• each Ai is an attribute name in the schema of relation r
• Di is the data type of values in the domain of attribute Ai
- Example:
create table branch
(branch-name char(15) not null,
branch-city char(30),
assets integer)

Integrity Constraints in Create Table
- not null
- primary key (A1, ..., An)
Example: Declare branch-name as the primary key for
branch and ensure that the values of assets are nonnegative.
create table branch
(branch-namechar(15),
branch-city char(30)
assets integer,
primary key (branch-name))
primary key declaration on an attribute automatically
ensures not null in SQL-92 onwards, needs to be
explicitly stated in SQL-89

Drop and Alter Table Constructs
- The drop table command deletes all information about the dropped relation from the database.
- The alter table command is used to add attributes to an existing relation.
alter table r add A D
where A is the name of the attribute to be added to relation r
and D is the domain of A.
* All tuples in the relation are assigned null as the value for the new attribute.
- The alter table command can also be used to drop attributes of a relation alter table r drop A
where A is the name of an attribute of relation r
* Dropping of attributes not supported by many databases