Scribd
Upload
5 views9 pages

Week 3 - SQL - DDL

The document provides an introduction to SQL, focusing on Data Definition Language (DDL) and its commands such as CREATE, ALTER, and DROP for managing database tables. It also covers Data Manipulation Language (DML) commands like INSERT, UPDATE, and DELETE, along with the use of SELECT statements. Additionally, it discusses referential integrity options for foreign keys in table creation.

Uploaded by

Abdulrahman Alkhayat
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
5 views9 pages

Week 3 - SQL - DDL

The document provides an introduction to SQL, focusing on Data Definition Language (DDL) and its commands such as CREATE, ALTER, and DROP for managing database tables. It also covers Data Manipulation Language (DML) commands like INSERT, UPDATE, and DELETE, along with the use of SELECT statements. Additionally, it discusses referential integrity options for foreign keys in table creation.

Uploaded by

Abdulrahman Alkhayat
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Introduction to

Database Systems
Week 3
SQL - DDL

Dr. Ruba Skaik


© 2025 CMPS 360 - All rights reserved
Content
❑ Introduction to DDL
◦ Create
◦ Alter
◦ Drop
❑ Introduction to DML
◦ Insert
◦ Update
◦ Delete
❑ Select Statements
Fall - 2024 CMPS - 360 2
Structured Query Language (SQL)
SQL is a language used for accessing and manipulating databases.
ANSI (American National Standards Institute)
Example usage:
◦ Inserting
◦ Updating
◦ deleting records

Fall - 2024 CMPS - 360 3


Data Definition
◼ Used to
◼ CREATE
◼ DROP
◼ ALTER the descriptions of the tables (relations) of a database

Fall - 2024 CMPS - 360 4


Create Table
◼ Specifies a new base relation by giving it a name, and specifying
each of its attributes and their data types (INTEGER, FLOAT,
DECIMAL(i,j), CHAR(n), VARCHAR(n))
◼ A constraint NOT NULL may be specified on an attribute
CREATE TABLE DEPARTMENT (
DNAME VARCHAR(10) NOT NULL,
DNUMBER INTEGER NOT NULL,
MGRSSN CHAR(9),
MGRSTARTDATE CHAR(9)
);

Fall - 2024 CMPS - 360 5


Drop Table
◼ Used to remove a relation (base table) and its definition
◼ The relation can no longer be used in queries, updates, or any other commands since its
description no longer exists

◼ Example:
DROP TABLE DEPENDENT;

Fall - 2024 CMPS - 360 6


Alter Table
◼ Used to add an attribute to one of the base relations
◼ The new attribute will have NULLs in all the tuples of the relation right
after the command is executed; hence, the NOT NULL constraint is not
allowed for such an attribute
◼ Example:
ALTER TABLE EMPLOYEE ADD JOB VARCHAR(12);

◼ The database users must still enter a value for the new attribute JOB for each EMPLOYEE
tuple.
◼ This can be done using the UPDATE command.

Fall - 2024 CMPS - 360 7


Referential Integrity Options
◼ We can specify RESTRICT, CASCADE, SET NULL or SET DEFAULT on referential
integrity constraints (foreign keys)
CREATE TABLE DEPT (
DNAME VARCHAR(10) NOT NULL,
DNUMBER INTEGER NOT NULL,
MGRSSN CHAR(9),
MGR_SDATE CHAR(9),
PRIMARY KEY (DNUMBER),
UNIQUE (DNAME),
FOREIGN KEY (MGRSSN) REFERENCES EMP
ON DELETE SET DEFAULT ON UPDATE CASCADE);

Fall - 2024 CMPS - 360 8


Referential Integrity Options
CREATE TABLE EMP(
ENAME VARCHAR(30) NOT NULL,
ESSN CHAR(9),
BDATE DATE,
INTEGER DEFAULT 1,
SUPDNOERSSN CHAR(9),
PRIMARY KEY (ESSN),
FOREIGN KEY (DNO) REFERENCES DEPT
ON DELETE SET DEFAULT ON UPDATE CASCADE,
FOREIGN KEY (SUPERSSN) REFERENCES EMP
ON DELETE SET NULL ON UPDATE CASCADE);

Fall - 2024 CMPS - 360 9

You might also like