DATABASE SYSTEMS
Dr. Noha Nagy
Lecture 4 SQL: DDL
SQL
2
Structured Query language SQL is pronounced as “S-Q-L” or
sometimes as “See-Quel” which is the standard language for
dealing with Relational Databases.
It is effectively used to create, insert, search, update, delete,
modify database records.
DDL: Data Definition Language
3
It is used to create and modify the structure of database objects in
database. Includes statements like:
▪ CREATE: Creates a new table or any other database object
▪ ALTER: Modifies an existing database object, such as a table
▪ DROP: Removes an entire table or any other object in database
CREATE Statement
4
Creating a new database:
CREATE DATABASE databasename;
Example:
create database customers;
Major CREATE statements:
CREATE TABLE–defines a new table and its columns
CREATE VIEW–defines a logical table from one or more
tables or views (Not part of the course)
Steps in Table Creation
5
1. Identify data types for attributes
2. Identify columns that can and cannot be null
3. Identify columns that must be unique (candidate keys)
4. Identify primary key–foreign key mates
5. Determine default values
6. Identify constraints on columns (domain specifications)
7. Create the table
Data Types for Attributes
6
Common Data Types for table attributes are:
1- Numeric: integer number (INTEGER, INT, AND SMALLINT), and
floating number (FLOAT, REAL, and DOUBLE).
2- Character: data types are either fixed length (CHAR (n), where n is
the number of character) or variable length (VARCHAR(n)).
3- Boolean: TRUE or FALSE.
4- Timestamp
Create Table Syntax
7
CREATE TABLE <Table Name> (
<column1 datatype constraint_1 constraint_2 >,
<column2 datatype>,
…..
constraint_3,
Constraint_4);
Create Table: Constraints
8
• NOT NULL: A Constraint that ensures that a column cannot have NULL value.
• DEFAULT: A Constraint that provides a default value for a column when none is
specified.
• UNIQUE : A Constraint that ensures that all values in a column are different.
• PRIMARY Key: A Constraint that uniquely identify each row/record in a database
table (NOT NULL + UNIQUE)
• FOREIGN KEY (FK): A Constraint that ensures referential integrity. A foreign key
from 1 table to another is used link a tuple in the 1st table to a unique tuple in the
2nd table.
• CHECK: A constraint that ensures that all values in a column satisfy a certain
condition.
Create Table: Example
9
10
SQL AUTO
INCREMENT Field
Note:
Identity(seed,increment)
Using Check
11
Create Table:
Check
Constraint
https://www.w3schools.com/sql/sql_check.asp
Using Default Value
12
Create Table:
Default
Constraint
‘Cairo’
Foreign Key Constraint
13
Order ID is the
primary key
Foreign Key Example
14
Foreign Key
Constraint:
Example
15
Foreign Key
Constraint
You can also add how the Foreign key updates will be
handled:
REFERENCES Persons(ID)
ON DELETE CASCADE
ON UPDATE CASCADE
ON DELETE SET NULL);
SQL ALTER TABLE: Add Column
16
Changing Tables
17
17
Student ID Student Name Faculty Department Age
Numeric(5) Varchar(100) Varchar(30) Varchar(30) Integer
ALTER TABLE changes column specifications:
ALTER TABLE Student ADD (Type Varchar(2))
Student ID Student Name Faculty Department Age Type
Numeric(5) Varchar(100) Varchar(30) Varchar(30) Integer Varchar(2)
ALTER TABLE Student DROP Age;
OR
ALTER TABLE Student DROP COLUMN Age;
Student ID Student Name Faculty Department Type
Numeric(5) Varchar(100) Varchar(30) Varchar(30) Varchar(2)
ALTER TABLE: Constraints
18
Adding and dropping Constraints
ALTER TABLE<table name>
ADD CONSTRAINT < CONSTRAINT Syntax>
ALTER TABLE<table name>
ADD CONSTRAINT < CONSTRAINT Name > < CONSTRAINT Syntax>
ALTER TABLE<table name>
Drop CONSTRAINT < CONSTRAINT Name >
ALTER TABLE: Constraints Example
19
ALTER TABLE Customers
ADD PRIMARY KEY (SSN, Telephone);
Or
ALTER TABLE Registered
ADD CONSTRAINT PK1 PRIMARY KEY (SSN, Telephone );
-- constraint name is unique across database
Alter Table : Examples
20
ALTER TABLE STUDENT
ADD CONSTRAINT UQ_Std_Name unique (Name);
ALTER TABLE STUDENT
DROP CONSTRAINT UQ_Std_Name;
ALTER TABLE DEPARTMENT
ADD CONSTRAINT CK_DEPTCODE CHECK (DEPTCODE IN (‘IS’,’CS’,’IT’,
‘DS’))
Source: Dr. Ayman Kilany’s Database Course Slides
Alter Table : Examples
21
ALTER TABLE DEPARTMENT
DROP CONSTRAINT CK_DEPTCODE;
ALTER TABLE Student
ALTER COLUMN City SET DEFAULT 'Cairo’;
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;
Source: Dr. Ayman Kilany’s Database Course Slides
SQL DROP : For deleting Tables
22
DROP TABLE statement allows you to
remove tables from your schema:
DROP TABLE TableName
SQL DROP : For deleting databases
23
DROP Database statement allows you
to delete all the database:
DROP database <database name>;
Example: DROP database mydatabase;
Questions
24
1. Create a table for employee with the following columns:
Employee_id (int)
Employee_name (varchar(50))
Employee_address (varchar(250))
Employee_age (int)
2. Drop the table
Neamat El Tazi
Answer
25
CREATE TABLE employees (
Employee_id INT NOT NULL,
Employee_name VARCHAR(50) NOT NULL,
Employee_address VARCHAR(250) NOT NULL,
Employee_age INT NOT NULL,
PRIMARY KEY (Employee_id));
DROP Table employees;
Neamat El Tazi
