Topic 4

Database Systems

Unit 4 :
Structural Query
Language
(SQL) Part 2

IT1768 Database Systems

Unit 4 :
Structural Query Language (SQL)

Topic 4

Objective :
At the end of this unit, you should be able to:
Use SQL subquery for data retrieval
Data Manipulation Language (DML) and
Data Definition Language (DDL) in SQL
Referential Integrity in database tables

IT1768 Database Systems

Topic 4

Content
Subqueries in SQL
Data Definition Language Create
database, Create Table, Alter table, Drop
database, Drop table
Data Manipulation Language - INSERT /
UPDATE / DELETE

IT1768 Database Systems

Topic 4

Subqueries in SQL
Subqueries

(Nested Queries)

It is possible to place one query inside another. The

inner query is called a subquery or nested query .
During evaluation, the subquery is evaluated first,
followed by the outer query.
A subquery has a SELECT embedded in within them.
A subselect can be used in WHERE and HAVING clauses
of an outer SELECT.
Subselects may also appear in INSERT, UPDATE, and
DELETEs.
IT1768 Database Systems

Topic 4

Subqueries in SQL

( with = )

Subquery with Equality ( = ), used for returning one record.

List customers who reside in California
Select Fname, Lname
from Customer
CA
where StateCode =
(select StateCode
from State
where StateName = California);
State
StateCode

StateName

Population

Customer
CustId
Fname Lname

AK

Alaska

628,000

C01

John

Brown

AK

CA

California

34,000,000

C02

Ethan

Harris

CA

AL

Alabama

4,500,000

C03

Sophia

Cole

AL

FL

Florida

16,000,000

IT1768 Database Systems

StateCode

Fname

Lname

Ethan

Harris 5

Topic 4

Subqueries in SQL

( with alias names)

Note that the previous query can also be expressed as a

Join in SQL statement;
Select Fname, Lname
from customer AS c, state AS s
where c.StateCode = s.StateCode
and StateName = California;
Compare it with the original expression:
Select
Fname, Lname
from Customer
where StateCode =
(select StateCode
from State
where StateName = California);
IT1768 Database Systems

Topic 4

Subqueries in SQL

( with IN )

SELECT CName
FROM STUDENT
WHERE SID IN
(SELECT StudNo
FROM ENROLLMENT
WHERE ClassNo = BD445) ;

Result using Subquery

CName
Jones
Baker

ENROLLMENT

STUDENT
SID

CName Major

Age

100

Jones

Accounting

150

Parks

200

StudNo

ClassNo

21

100

BD445

Math

19

150

BA200

Baker

History

16

200

BD445

250

Glass

History

18

250

CS250

300

Andy

Accounting

17

300

CS250

IT1768 Database Systems

Position

Topic 4

Subqueries in SQL

Retrieval using two Subquery

CLASS

CName

Time

BA200

MWF9

SC110

BD445

MWF3

SC213

BF410

MWF8

SC213

CS150

MWF3

EA304

CS250

MWF12

EB210

Room

ENROLLMENT
StudNo

(dealing with 3 tables)

ClassNo

SELECT STUDENT.CName
FROM STUDENT
WHERE STUDENT.SID IN
(SELECT ENROLLMENT.StudNo
FROM ENROLLMENT
WHERE ENROLLMENT.ClassNo IN
(SELECT CLASS.CName
FROM CLASS
WHERE CLASS.Time = MWF3))
STUDENT

Position

SID

CName Major

Age

100

BD445

100

Jones

Accounting

21

150

BA200

150

Parks

Math

19

Result using subquery

200

BD445

200

Baker

History

16

CName

200

CS250

250

Glass

History

18

Jones

300

CS250

300

Andy

Accounting

17

Baker

IT1768 Database Systems

Topic 4

Alternate Solution to Subquery

To Join more than two tables

SELECT STUDENT.SID, CLASS.CName, ENROLLMENT.Position

FROM STUDENT, ENROLLMENT, CLASS
WHERE STUDENT.SID = ENROLLMENT.StudNo AND
STUDENT
ENROLLMENT.ClassNo = CLASS.CName AND
SID CName Major
Age
STUDENT.CName = BAKER
100 Jones
Accounting
21
150

Parks

Math

19

200

Baker

History

16

250

Glass

History

18

300

Andy

Accounting

17

ENROLLMENT

CName

200

BD445

200

CS250

CLASS
CName

Time

Position

Room

StudNo

ClassNo

100

BD445

BA200

M-F9

SC110

150

BA200

BD445

MWF3

SC213

200

BD445

BF410

MWF8

SC213

200

CS250

CS150

MWF3

EA304

300

CS250

CS250

MWF12

EB210

IT1768 Database Systems

Position

SID

Topic 4

Subqueries in SQL

( with EXISTS )

Logical operators whose value is either TRUE or FALSE

depending on the presence/absence of rows that fits the
qualifying conditions. EXISTS is one of the logical operators.
Example:
SELECT
DISTINCT StudNo
FROM
ENROLLMENT As A
WHERE EXISTS
(SELECT *
FROM ENROLLMENT As B
If exists, return true
WHERE A.StudNo= B.StudNo AND
A.ClassNo <> B.ClassNo) ;

IT1768 Database Systems

10

Topic 4

Subqueries in SQL

( with EXISTS )

ENROLLMENT_A

ENROLLMENT_B

StudNo

ClassNo

Position

StudNo

ClassNo

Position

100

BD445

100

BD445

150

BA200

150

BA200

200

BD445

200

BD445

200

CS250

200

CS250

300

CS250

300

CS250

SELECT DISTINCT StudNo

FROM
ENROLLMENT_A As A
WHERE EXISTS
(SELECT *
FROM ENROLLMENT_B As B
WHERE A.StudNo= B.StudNo AND
A.ClassNo <> B.ClassNo) ;

The result is :
StudNo
200

Compare row 1 in A with row 1 in B. If StudNo and ClassNo are identical, non-exist, no display.
Compare row 3 in A with row 4 in B. Same StudNo but different ClassNo, exist, display StudNo from A
IT1768 Database Systems

11

Topic 4

Subqueries with Aggregate Functions

Subqueries with Aggregate Functions : e.g.
- Count( )
- Avg( )
- Sum( )
- Max( )
- Min( )

Emp_Num

Employee
Emp_Name Salary

E-01

James

2,500

E-02

Jennifer

3,600

E-03

Gilbert

3,200

Ex 1: List the employee(s) with the highest salary.

3,600
SELECT Max(salary)
FROM Employee
Ex 2: List the Average salary of the employee.
3,100
SELECT Avg(salary)
FROM Employee

IT1768 Database Systems

12

Topic 4

Subqueries with Aggregate Functions

Ex 3: List those employees with salary higher than the average
Employee
salary of all employees.
Emp_Num

Emp_Name Salary

E-01
James
SELECT Salary
E-02
Jennifer
FROM Employee
E-03
Gilbert
WHERE Salary >
(SELECT AVG(salary)
FROM employee) ;

2,500
3,600
3,200

Note : The average salary is $3,100

Emp_Num

Emp_Name Salary

E-02

Jennifer

3,600

E-03

Gilbert

3,200

SELECT Salary
FROM Employee
WHERE Salary > AVG(Salary)

Cannot write WHERE Salary > AVG(Salary).

(Recall : cannot use aggregate functions in WHERE clause)
IT1768 Database Systems

13

Topic 4

Data Definition Language (DDL)

DDL SQL statements define the structure of a database, including

rows, columns, tables, indexes, and database specifics such as file
locations. DDL SQL statements are more part of the DBMS and have
large differences between the SQL variations. DML SQL commands.
These include:
CREATE/ALTER to make a new/modify database, table, index, or stored
query.

Using SQL to create a database named BOOKING

CREATE database BOOKING;
Using SQL to display all database
DESCRIBE database ;
Using SQL to modify a database named BOOKING
ALTER database BOOKING;
Using SQL to drop a database named BOOKING
DROP database BOOKING;

IT1768 Database Systems

14

Topic 4

Data Definition Language (DDL)

SQL DDL allows database objects such as tables,

views, and indexes to be created and destroyed.
Main SQL - DDL statements are:
CREATE/ALTER TABLE
CREATE INDEX
CREATE VIEW

IT1768 Database Systems

DROP TABLE
DROP INDEX
DROP VIEW

15

Topic 4

Data Definition Language (DDL)

CREATE TABLE [TableName]

{ (colName dataType [NOT NULL] [UNIQUE] [DEFAULT defaultOption]
[CHECK searchCondition] [,...] }
[CONSTRAINT constraint_name
PRIMARY KEY (listOfColumns),]
{[UNIQUE (listOfColumns),] [,]}
{[CONSTRAINT constraint_name
FOREIGN KEY (listOfFKColumns)
REFERENCES ParentTableName [(listOfCKColumns)],
[ON UPDATE referentialAction]
[ON DELETE referentialAction ]] [,]}

{[CHECK searchCondition)] [,] } );

IT1768 Database Systems

16

Topic 4

Data Definition Language (DDL)

A Table definition
Consists of a list of definitions of columns that make up a row in
the table.

CREATE TABLE [table-name]

( column1 data-type [NULL | NOT NULL] ,
...,
,
columnN data-type [NULL | NOT NULL] ) ;
Creates a table with one or more columns of the specified data
type.
NULL (default) indicates whether column can contain nulls.
With NOT NULL, system rejects any attempt to insert a null in the
column.
Primary keys should always be specified as NOT NULL.
IT1768 Database Systems

17

Topic 4

Data Definition Language (DDL)

Example ( Create a simple table )

CREATE TABLE
(Cust_Num
Last_Name
First_Name
State

CUSTOMER
Cust_Num

Last_Name

Ngiam Seow Ping

CUSTOMER C-001
NUMBER(5)
NOT NULL,
VARCHAR(15) NULL,
VARCHAR(8)
NULL,
VARCHAR(2)
NULL );

First_Name

State

Jennifer

CA

What is a constraint
A constraint implements a business rule that restricts
values stored in a table.
Constraints are implemented as either:
Table constraints that apply to the entire table or
Column constraints that apply to a single column.
IT1768 Database Systems

18

Topic 4

Data Definition Language (DDL)

If applicable, create the following constraints

Primary Key constraint
identifies the primary key of a table.

Foreign Key constraint

if a value exists, it must be a primary key in the referenced
table.

Not Null constraint

specifies that null values are not allowed.

Unique constraint
prevents duplicate values for a column or group of columns.

Default constraint
if value for a column is not specify during an insert, it will take
the default given.

Domain constraint
to specify the set of allowable values that a column can have.
IT1768 Database Systems

19

Topic 4

Data Definition Language (DDL)

Example 1 ( create table with Primary Key & Foreign Key constraint )
CREATE TABLE CUSTOMER

Create Table with

( CUST_NUM
NUMBER(5) NOT NULL,
Constraints
LAST_NAME
VARCHAR(15),
FIRST_NAME
VARCHAR(8),
STATE
VARCHAR(2),
CONSTRAINT cust_num_pk
PRIMARY KEY ( CUST_NUM ),
CONSTRAINT state_fk
FOREIGN KEY( STATE ) REFERENCES STATE(STATE_CODE ) );

Example 2 ( create table with NOT NULL constraint )

CREATE TABLE NEWITEMS
( ITEM_NUM
SUPPL_CODE
DESCR
IT1768 Database Systems

NUMBER(5),
VARCHAR(3) NOT NULL,
VARCHAR(30) );
20

Topic 4

Data Definition Language (DDL)

Example 3 ( create a table with Default constraint )

CREATE TABLE ACCOUNTS

( ACC_NUM NUMBER(12) DEFAULT 0001,
ACC_TYPE VARCHAR(1) DEFAULT A,
ACC_DESCR VARCHAR(30),
PRIMARY KEY ( ACC_NUM ) );
Example 4 ( create table with domain constraints )

CREATE TABLE CUST_TEST

( CUST_NUM NUMBER(5) NOT NULL,
LAST_NAME VARCHAR(15),
FIRST_NAME VARCHAR(8),
SEX
VARCHAR(1) CHECK ( SEX IN (M,F) ),
BAL
NUMBER(3) CHECK ( BAL BETWEEN 0 AND 999 ),
STATE
VARCHAR(2),
PRIMARY KEY ( CUST_NUM ),
FOREIGN KEY( STATE ) REFERENCES STATE( STATE_CODE )
);

IT1768 Database Systems

21

Topic 4

ALTER TABLE

Changing Table Definitions

The definition of a base table can be changed by using the ALTER
TABLE command.
The alter table action includes:
Add a new column to a table.
Drop a column from a table.
Add a new table constraint.
Drop a table constraint.
Set a default for a column.
ITEMS
Item-No Description ITEM_WEIGHT
Drop a default for a column.

Example 1 ( add a new column )

ALTER TABLE ITEMS
ADD ITEM_WEIGHT NUMBER (6,2)

IT1768 Database Systems

T-01

Sugar

T-02

Salt

T-03

Powder

NOT NULL ;

22

Topic 4

ALTER TABLE

Example 2 ( drop constraint, add a new constraint )

ALTER TABLE order_detail
DROP CONSTRAINT suppl_code_fk;
ALTER TABLE order_detail
Alter Table by
ADD
adding constraints
CONSTRAINT suppl_code_fk
FOREIGN KEY (suppl_code)
REFERENCES SUPPLIER (suppl_code)
;
Example 3 ( change column definition )
ALTER TABLE staff
ALTER position DROP DEFAULT;
ALTER TABLE staff
ALTER sex SET DEFAULT F ;
IT1768 Database Systems

Alter Table by
altering attribute
properties
23

Topic 4

DROP Table

Removing any elements in Database

The DROP command can be used to destroy any existing database,
table, index, or view.

Format/Syntax

DROP DATABASE Booking_System

DROP TABLE table-name;
DROP VIEW view-name;
DROP INDEX index-name;

Drop == Destroy

DROP TABLE command deletes a table. By default, DROP TABLE also deletes
the tables data.
DROP TABLE CUSTOMER CASCADE automatically drops objects that depend
on the table
DROP TABLE CUSTOMER RESTRICT refuses to drop the table if there are any
dependent objects.
DROP TABLE CUSTOMER %NODELDATA, this option can prevent the
automatic deletion of the table's data.
IT1768 Database Systems

24

Topic 4

Data Manipulation Language (DML)

Data Manipulation Language (DML)

- DML refers to SELECT, INSERT, UPDATE, and DELETE of tuples

INSERT statement ;
Add a new row into a table.
Copy data from one table to another.

UPDATE statement ;
Modify attribute values of existing rows.

DELETE statement ;
Drop any number of rows from a table.

COMMIT ;
To commit the changes, i.e. make the change permanent

ROLLBACK ;
To rollback (discard) the changes
IT1768 Database Systems

25

Topic 4

INSERT Statement
Create a single row
INSERT INTO [table-name] [(column1, ... , columnN)]
VALUES
(value1, .., valueN) ;
column1, column2, is optional.
If omitted, SQL assumes a list of all columns in the table.

value list must match column list as follows:

Number of items in each list must be the same.
Must be direct correspondence in position of items in two
lists.
Data type of each item in value list must be compatible with
data type of corresponding column.

IT1768 Database Systems

26

Topic 4

INSERT Statement

(Single row insertion)

INSERT INTO Enrollment

VALUES ( 400, BD445, 44 )
Same as:
INSERT INTO Enrollment (StudNo, ClassNo, Position )
VALUES (400, BD445 , 44)
ENROLLMENT
StudNo

ClassNo

100

BD445

150

BA200

200

BD445

200

CS250

300

CS250

400

BD445

44

IT1768 Database Systems

Position

The newly inserted row

27

Topic 4

INSERT Statement

(Insert null value into a row)

Example : Student pending for registration:

INSERT INTO
REGISTER ( StudNum, Course, Semester, Date_Reg )
VALUES ( 072416P, ITDF04, One, NULL );
Note:
It is mandatory to supply values for all columns; besides those columns
defined as NOT NULL, those with no default value should be assigned
with NULL or DEFAULT).
StudNum

Course

Semester

Date_Reg

074592G

ITDF02

One

16-Mar-2008

072789K

ITDF03

One

16-Mar-2008

072416P

ITDF04

One

NULL
The newly inserted row

IT1768 Database Systems

28

Topic 4

INSERT Statement

(Multiple rows insertion)

Example : Multiple row insertion

Note : This syntax may not work with MS Access
INSERT INTO EMPLOYEE
VALUES
(Prod-01, Jacob, Lee, 12-Mar-1976, 3,000) ,
(Engg-12, Sunny, Chin, 03-May-1968, 3,500),
(QA-08, Susan, Kwek, 23-Dec-1980, 2,600) ;

The newly
inserted rows

IT1768 Database Systems

Emp_Num

Fname

Lname

DOB

Salary

Purc-06

Jennifer

Yee

16-Mar-1965

2,800

Wh-05

Peter

Ng

26-Feb-1986

2,950

Prod-01

Jacob

Lee

12-Mar-1976

3,000

Engg-12

Sunny

Chin

03-May-1968

3,500

QA-08

Susan

Kwek

23-Dec-1980

2,600
29

Topic 4

INSERT Statement

(Advanced statement)

Create a group of new rows in mass using data

selected from one table to other tables.
INSERT INTO [table-name] [(column1, ..., columnN)]
SELECT expression ;

Example 1:
INSERT INTO STUD_DETAIL
SELECT StudNo, Name, Major
FROM STUDENT
WHERE StudNo = 072318P ;
Note : This syntax may not work with MS Access

IT1768 Database Systems

30

Topic 4

INSERT Statement

(Advanced statement)

Example 2:
Suppose a follow-up call is required for every order that has
been paid but not shipped.
Create those rows in the CUST_CALLS table.

INSERT INTO CUST_CALLS ( CUSTOMER_NUM, CALL-DESCR )

SELECT CUSTOMER_NUM, ORDER_NUM
FROM ORDERS
WHERE PAID_DATE IS NOT NULL AND SHIP_DATE IS NULL ;
Restrictions on the INSERT - SELECT
cannot contain an ORDER BY clause.
cannot refer to the table into which rows are inserted.

IT1768 Database Systems

31

Topic 4

INSERT Statement

(Advanced statement)

Example :
Suppose retrieving sales records in year 2008 and insert new
rows in the STORE_INFOR table.

INSERT INTO STORE_INFOR ( Store_name, Sales, Date )

SELECT Store_name, Sales, Date
FROM SALERECORD
WHERE Year(Date) = 2008 ;
Note : This syntax may not work with MS Access

IT1768 Database Systems

32

Topic 4

UPDATE Statement
Format/Syntax
UPDATE
SET
WHERE

[table-name]
column-name1 = data-value1
[,column-name = data-value2...]
[condition] ;

SET clause specifies column(s) that are to be updated, and the

corresponding new value(s) to be set for the column(s).
WHERE clause is optional:
If omitted, named columns are updated for all rows in table.
If specified, only those rows that satisfy the condition are
updated.
New data-value(s) must be compatible with data type for
corresponding column.
IT1768 Database Systems

33

Topic 4

UPDATE Statement

(Update to new values)

Example 1
UPDATE
CUSTOMER
SET
FNAME = Barnaby,
LNAME = Dorfler
WHERE CUSTOMER_NUM = 113;

Example 2
Write an update statement to show that the supplier HRO
has raised all prices by 5%.
UPDATE PRODUCT
SET
UNIT_PRICE = UNIT_PRICE * 1.05
WHERE SUPPL_CODE = HRO;
IT1768 Database Systems

34

Topic 4

UPDATE Statement
Example 3 :
UPDATE
SET
WHERE

ENROLLMENT
Position = 44
StudNo = 400

ENROLLMENT
StudNo

ClassNo

100

BD445

150

BA200

200

BD445

200

CS250

400

CS250

IT1768 Database Systems

Position
ENROLLMENT
StudNo

ClassNo

Position

100

BD445

150

BA200

200

BD445

200

CS250

400

CS250

44
35

Topic 4

UPDATE Statement
Example 4 :
UPDATE
SET
WHERE

ENROLLMENT
Position = MAX(Position) + 1
StudNo = 400

ENROLLMENT
StudNo

ClassNo

100

BD445

150

BA200

200

BD445

StudNo

ClassNo

200

CS250

100

BD445

400

CS250

150

BA200

200

BD445

200

CS250

400

CS250

IT1768 Database Systems

Position
ENROLLMENT
Position

Note : MAX(Position) is 3

36

Topic 4

UPDATE Statement
Example 5 :
UPDATE ENROLLMENT
SET
Position = 6
WHERE StudNo = 200 AND ClassNo=CS250
ENROLLMENT
StudNo

ClassNo

100

BD445

ENROLLMENT

150

BA200

StudNo

ClassNo

200

BD445

100

BD445

200

CS250

150

BA200

300

CS250

200

BD445

200

CS250

300

CS250

IT1768 Database Systems

Position
Position

37

Topic 4

DELETE Statement
Format/Syntax
DELETE FROM [table-name]
WHERE [condition] ;
WHERE clause is optional:
If omitted, all rows are deleted from table. This does
not delete the table.
If condition is specified, only those rows that satisfy
the condition are deleted.

IT1768 Database Systems

38

Topic 4

DELETE Statement

(Delete all rows from a table)

Example 1 ( to delete all rows from CUSTIMER table)

DELETE FROM CUSTOMER;
CUSTOMER
Cust_Num Cust_Name

Address

Phone

C-01

ABC Supplies Pte Ltd

5 AMK Ave 1

61111111

C-02

XYZ Computers Pte Ltd #01-99 Sim Lim Square

62222222

C-03

MNO Electric Pte Ltd

Blk 3 Bedok North

63333333

Cust_Num

Cust_Name

Address

Phone

IT1768 Database Systems

39

Topic 4

DELETE Statement

(Delete a specific row from a table)

Example 2 ( to delete a specific row from CUSTOMER table)

DELETE FROM CUSTOMER
WHERE
Cust_Num = C-02
CUSTOMER
Cust_Num Cust_Name

Address

Phone

C-01

ABC Supplies Pte Ltd

5 AMK Ave 1

61111111

C-02

XYZ Computers Pte Ltd #01-99 Sim Lim Square

62222222

C-03

MNO Electric Pte Ltd

Blk 3 Bedok North

63333333

Cust_Num

Cust_Name

Address

Phone

C-01

ABC Supplies Pte Ltd

5 AMK Ave 1

61111111

C-03

MNO Electric Pte Ltd

Blk 3 Bedok North

63333333

IT1768 Database Systems

40

Topic 4

DELETE Statement

(Delete a specific row from a table)

Example 3 ( to delete specific rows from CUSTOMER table)

DELETE FROM CUSTOMER
WHERE Cust_Name = MNO Electric Pte Ltd;
CUSTOMER
Cust_Num Cust_Name

Address

Phone

C-01

ABC Supplies Pte Ltd

5 AMK Ave 1

61111111

C-02

XYZ Computers Pte Ltd #01-99 Sim Lim Square

62222222

C-03

MNO Electrics Pte Ltd

Blk 3 Bedok North

63333333

Cust_Num

Cust_Name

Address

Phone

C-01

ABC Supplies Pte Ltd

5 AMK Ave 1

61111111

C-02

XYZ Computers Pte Ltd #01-99 Sim Lim Square

IT1768 Database Systems

62222222
41

Topic 4

DELETE Statement
Example 4 (Use Specific tables name in WHERE command):
DELETE FROM STUDENT
WHERE STUDENT.Major = Accounting)

STUDENT

ENROLLMENT

SID

CName Major

Age

100

Jones

Accounting

150

Parks

200

StudNo

ClassNo

21

150

BA200

Math

19

200

BD445

Baker

History

16

200

CS250

250

Glass

History

18

300

Andy

Accounting

17

IT1768 Database Systems

Position

42

Topic 4

DELETE Statement

(Delete dealing with 2 tables)

ENROLLMENT

Example 5 :

StudNo
DELETE FROM ENROLNMENT
150
WHERE ENROLLMENT.StudNo IN
200
(SELECT STUDENT.SID
FROM STUDENT
200
WHERE STUDENT.Major = Accounting)
STUDENT

ClassNo

Position

BA200

BD445

CS250

ENROLLMENT

SID

CName Major

Age

100

Jones

Accounting

150

Parks

200

StudNo

ClassNo

21

100

BD445

Math

19

150

BA200

Baker

History

16

200

BD445

250

Glass

History

18

200

CS250

300

Andy

Accounting

17

300

CS250

IT1768 Database Systems

Position

43

Topic 4

EXERCISE REVIEW 1
Guest

CheckInDate

Hotel

Diana Jones

12-Jul-2004

Costa Bonaventure

John Smith

18-Aug-2004

Sun Palm

Justin Black

12-Aug-2004

Costa Bonaventure

Booking

HotelName

WebAddress

Costa Bonaventure

www.costabonaventure.com.la

Sand Coasts

www.sancoasts.com.la

Sun Palm

www.sunpalm.com.sf

Star_Rating

Min_price

Hotel

Max_price

$300

$499

$200

$299

$100

$199

$50

$99

IT1768 Database Systems

Rating

Category

44

Topic 4

EXERCISE REVIEW 1

..continue

(a) Display the names of hotels with 5-star rating.

(b) Display the names of hotels that have more than one
booking.
(c)

Display the names and web addresses of hotels that

charge $100 and above but less than $300.

(d) Add a new booking for guest, Brian Williams who wants
to check-in on the 5th August 2004 to Sun Palm.

IT1768 Database Systems

45

Topic 4

Referential Integrity

A Foreign Key is a column, or set of columns, that links each

row in the child table containing the foreign key to the row
of the parent table containing the matching primary key
value.
Referential integrity means that, if the foreign key contains a
value, that value must refer to an existing row in the parent
table. PK in parent branchNo
Zone_ID
table

Name

Title

Address

Sector

Size

branch_ID

FK in child table

ISO standard supports the definition of foreign keys with the FOREIGN
KEY clause in the CREATE and ALTER TABLE statements, for example :
FOREIGN KEY (branch_ID) REFERENCES Branch (branchNo)
IT1768 Database Systems

46

Topic 4

Referential Integrity

Example 5 ( indirect violation of a constraint )

CREATE TABLE TESTA
(B_ID
NUMBER(5)
PRIMARY KEY ,
Name
VARCHAR(30) );

B_ID

Name

Bravo

Alpha

CREATE TABLE TESTB

Z_ID Title
B_No
(Z_ID
VARCHAR(2)
PRIMARY KEY,
A2 Zone2
2
Title
VARCHAR(10),
A1 Zone1
1
B_No
NUMBER(5),
FOREIGN KEY( B_No ) REFERENCES TESTA ( B_ID ) );
INSERT INTO TESTA VALUES (1, Alpha);
INSERT INTO TESTB VALUES (A1, Zone1, 1);

TESTA
B_ID

TESTB
B_No fk

DELETE FROM TESTA WHERE B_ID = 1; What happens?

Default action for ON DELETE is No Action. This SQL
will fail as there is a child record.
IT1768 Database Systems

47

Topic 4

Referential Integrity

Example 6 ( use the ON DELETE CASCADE option )

CREATE TABLE TESTA
(B_ID
NUMBER(5)
PRIMARY KEY ,
Name
VARCHAR(30) );

B_ID

Name

Bravo

Alpha

CREATE TABLE TESTB

Z_ID Title
B_No
(Z_ID
VARCHAR(2)
PRIMARY KEY,
A2 Zone2
2
Title
VARCHAR(10),
A1 Zone1
1
B_No
NUMBER(5),
FOREIGN KEY( B_No ) REFERENCES TESTA ( B_ID )
ON DELETE CASCADE );
INSERT INTO TESTA VALUES (1, Alpha);
INSERT INTO TESTB VALUES (A1, Zone1, 1);

DELETE FROM TESTA WHERE B_ID = 1; What happens?

The matching child record will also be deleted.
IT1768 Database Systems

48

Topic 4

Summary
SQL statements for multiple tables :
- Sub-queries
- Joins

SQL Data Definition Languages (DDL)

- Create database, Create table
- Alter table, Drop Database, Drop table
SQL statements for data modifications :
INSERT
DELETE
UPDATE
IT1768 Database Systems

49

Topic 4

EXERCISE REVIEW 1

(Solution)

(a) Display the names of hotels with 5-star rating.

SELECT HotelName from Hotel where rating =5

(b)Display the names of hotels that have more than one

booking.
SELECT Hotel from BOOKING group by Hotel Having count(*) > 1

(c) Display the names and web addresses of hotels that charge
$100 and above but less than $300.
SELECT HotelName, WebAddress from HOTEL where
Rating in (SELECT STAR_rating from CATEGORY where MinPrice
>=$100 and MaxPrice <$300)

(d) Add a new booking for guest, Brian Williams who wants to
check-in on the 5th August 2004 to Sun Palm.
INSERT into BOOKING (Guest, CheckInDate,Hotel) values (Brian
Williams,05-Aug-2004,Sun Palm)
IT1768 Database Systems

50