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
