Exercises

The following tables form part of a database held in a relational DBMS:-

Hotel (hotelNo, hotelName, city)

Room (roomNo, hotelNo, type, price)
Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)
Guest (guestNo, guestName, guestAddress)

where Hotel contains hotel details and hotelNo is the primary key;
Room contains room details for each hotel and (roomNo, hotelNo) forms the primary key;
Booking contains details of the bookings and (hotelNo, guestNo, dateFrom) forms the primary
key;
and Guest contains guest details and guestNo is the primary key.

Exercises

For the Exercises 6.7 6.28, use the Hotel schema defined at the start of the Exercises at the end of
Chapter 3.

Simple Queries

6.7 List full details of all hotels.

SELECT * FROM Hotel;

6.8 List full details of all hotels in London.

SELECT * FROM Hotel WHERE city = London;

6.9 List the names and addresses of all guests in London, alphabetically ordered by name.

SELECT guestName, guestAddress FROM Guest WHERE address LIKE %London%

ORDER BY guestName;

Strictly speaking, this would also find rows with an address like: 10 London Avenue, New York.

6.10 List all double or family rooms with a price below 40.00 per night, in ascending order of price.

SELECT * FROM Room WHERE price < 40 AND type IN (D, F)

ORDER BY price;

(Note, ASC is the default setting).

6.11 List the bookings for which no dateTo has been specified.
Database Systems: Instructors Guide - Part III

SELECT * FROM Booking WHERE dateTo IS NULL;

Aggregate Functions

6.12 How many hotels are there?

SELECT COUNT(*) FROM Hotel;

6.13 What is the average price of a room?

SELECT AVG(price) FROM Room;

6.14 What is the total revenue per night from all double rooms?

SELECT SUM(price) FROM Room WHERE type = D;

6.15 How many different guests have made bookings for August?

SELECT COUNT(DISTINCT guestNo) FROM Booking

WHERE (dateFrom <= DATE2004-08-01 AND dateTo >= DATE2004-08-01) OR
(dateFrom >= DATE2004-08-01 AND dateFrom <= DATE2004-08-31);

Subqueries and Joins

6.16 List the price and type of all rooms at the Grosvenor Hotel.

SELECT price, type FROM Room

WHERE hotelNo =
(SELECT hotelNo FROM Hotel
WHERE hotelName = Grosvenor Hotel);

6.17 List all guests currently staying at the Grosvenor Hotel.

SELECT * FROM Guest

WHERE guestNo =
(SELECT guestNo FROM Booking
WHERE dateFrom <= CURRENT_DATE AND
dateTo >= CURRENT_DATE AND
hotelNo =
(SELECT hotelNo FROM Hotel
WHERE hotelName = Grosvenor Hotel));

6.18 List the details of all rooms at the Grosvenor Hotel, including the name of the guest staying in the
room, if the room is occupied.

SELECT r.* FROM Room r LEFT JOIN

2
Database Systems: Instructors Guide - Part III

(SELECT g.guestName, h.hotelNo, b.roomNo FROM Guest g, Booking b, Hotel h

WHERE g.guestNo = b.guestNo AND b.hotelNo = h.hotelNo AND
hotelName= Grosvenor Hotel AND
dateFrom <= CURRENT_DATE AND
dateTo >= CURRENT_DATE) AS XXX
ON r.hotelNo = XXX.hotelNo AND r.roomNo = XXX.roomNo;

6.19 What is the total income from bookings for the Grosvenor Hotel today?

SELECT SUM(price) FROM Booking b, Room r, Hotel h

WHERE (dateFrom <= CURRENT_DATE AND
dateTo >= CURRENT_DATE) AND
r.hotelNo = h.hotelNo AND r.roomNo = b.roomNo AND
hotelName = Grosvenor Hotel;

6.20 List the rooms that are currently unoccupied at the Grosvenor Hotel.

SELECT * FROM Room r

WHERE roomNo NOT IN
(SELECT roomNo FROM Booking b, Hotel h
WHERE (dateFrom <= CURRENT_DATE AND
dateTo >= CURRENT_DATE) AND
b.hotelNo = h.hotelNo AND hotelName = Grosvenor Hotel);

6.21 What is the lost income from unoccupied rooms at the Grosvenor Hotel?

SELECT SUM(price) FROM Room r

WHERE roomNo NOT IN
(SELECT roomNo FROM Booking b, Hotel h
WHERE (dateFrom <= CURRENT_DATE AND
dateTo >= CURRENT_DATE) AND
b.hotelNo = h.hotelNo AND hotelName = Grosvenor Hotel);

Grouping

6.22 List the number of rooms in each hotel.

SELECT hotelNo, COUNT(roomNo) AS count FROM Room

GROUP BY hotelNo;

6.23 List the number of rooms in each hotel in London.

SELECT hotelNo, COUNT(roomNo) AS count FROM Room r, Hotel h

WHERE r.hotelNo = h.hotelNo AND city = London
GROUP BY hotelNo;

3
Database Systems: Instructors Guide - Part III

6.24 What is the average number of bookings for each hotel in August?

SELECT AVG(X)
FROM ( SELECT hotelNo, COUNT(hotelNo) AS X
FROM Booking b
WHERE (dateFrom <= DATE2004-08-01 AND
dateTo >= DATE2004-08-01) OR
(dateFrom >= DATE2004-08-01 AND
dateFrom <= DATE2004-08-31)
GROUP BY hotelNo);

Yes - this is legal in SQL-92!

6.25 What is the most commonly booked room type for each hotel in London?

SELECT MAX(X)
FROM ( SELECT type, COUNT(type) AS X
FROM Booking b, Hotel h, Room r
WHERE r.roomNo = b.roomNo AND b.hotelNo = h.hotelNo AND
city = London
GROUP BY type);

6.26 What is the lost income from unoccupied rooms at each hotel today?

SELECT hotelNo, SUM(price) FROM Room r

WHERE roomNo NOT IN
(SELECT roomNo FROM Booking b, Hotel h
WHERE (dateFrom <= CURRENT_DATE AND
dateTo >= CURRENT_DATE) AND
b.hotelNo = h.hotelNo)
GROUP BY hotelNo;

Populating Tables

6.27 Insert records into each of these tables.

INSERT INTO Hotel

VALUES (H111, Grosvenor Hotel, London);

INSERT INTO Room

VALUES (1, H111, S, 72.00);
INSERT INTO Guest
VALUES (G111, John Smith, London);
INSERT INTO Booking
VALUES (H111, G111, DATE2005-01-01, DATE2005-01-02, 1);

4
Database Systems: Instructors Guide - Part III

6.28 Update the price of all rooms by 5%.

UPDATE Room SET price = price*1.05;

General

6.29 Investigate the SQL dialect on any DBMS that you are currently using. Determine the compliance
of the DBMS with the ISO standard. Investigate the functionality of any extensions the DBMS
supports. Are there any functions not supported?

This is a small student project, the result of which is dependent on the dialect of SQL being used.

6.30 Show that a query using the HAVING clause has an equivalent formulation without a HAVING
clause.

Hint: Allow the students to show that the restricted groups could have been restricted earlier with
a WHERE clause.

6.31 Show that SQL is relationally complete.

Hint: Allow the students to show that each of the relational algebra operations can be expressed in
SQL.

Case Study 2

For Exercises 6.326.40, use the Projects schema defined in the Exercises at the end of Chapter 5.

6.32 List all employees in alphabetical order of surname, and then first name.

SELECT * FROM Employee ORDER BY lName, fName;

6.33 List all the details of employees who are female.

SELECT * FROM Employee WHERE sex = female;

6.34 List the names and addresses of all employees who are Managers.

SELECT fName, lName, address FROM Employee WHERE position = Manager;

6.35 Produce a list of the names and addresses of all employees who work for the IT department.

SELECT fName, lName, address from Employee e, Department d

WHERE e.deptNo = d.deptNo
AND d.deptName = IT;

6.36 Produce a complete list of all managers who are due to retire this year, in alphabetical order
of surname.

6.37 Find out how many employees are managed by James Adams.

5
Database Systems: Instructors Guide - Part III

SELECT COUNT(empNo) FROM Employee

WHERE deptNo = (SELECT deptNo FROM Employee
WHERE fName = James AND lName = Adams;)
AND (fName <> James AND lName <> Adams);

6.38 Produce a report of the total hours worked by each employee, arranged in order of
department number and within department, alphabetically by employee surname.

SELECT e.lName, e.fName, hoursWorked

FROM WorksOn w, Employee e, Department d
WHERE e.deptNo = d.deptNo
AND e.empNo = w.empNo
ORDER by d.deptNo, e.lName;

6.39 For each project on which more than two employees worked, list the project
number, project name, and the number of employees who work on that project.

SELECT p.projNo, projName, count(empNo)

FROM Project p, WorksOn w
WHERE p.projNo = w.projNo
GROUP BY p.projNo
HAVING COUNT (empNo) > 2;

6.40 List the total number of employees in each department for those departments with more than
10 employees. Create an appropriate heading for the columns of the results table.

SELECT COUNT(empNo) as empCount, deptNo

FROM Employee
GROUP BY deptNo
HAVING COUNT(empNo) > 10;

6
Database Systems: Instructors Guide - Part III

Chapter 7 SQL: Data Definition

Review Questions

7.1 Describe the eight base data types in SQL.

The eight base types are: Boolean, character, bit (removed from SQL:2003), exact numeric,
approximate numeric, datetime, interval, large object. See Section 7.1.2.

7.2 Discuss the functionality and importance of the Integrity Enhancement Feature (IEF).

Required data: NOT NULL of CREATE/ALTER TABLE.

Domain constraint: CHECK clause of CREATE/ALTER TABLE and CREATE
DOMAIN.
Entity integrity: PRIMARY KEY (and UNIQUE) clause of CREATE/ALTER
TABLE.
Referential integrity: FOREIGN KEY clause of CREATE/ALTER TABLE.
General constraints: CHECK and UNIQUE clauses of CREATE/ALTER TABLE and
(CREATE) ASSERTION.

See Section 7.2.

7.3 Discuss each of the clauses of the CREATE TABLE statement.

The clauses are (see Section 7.3.2):

column definition;
PRIMARY KEY
FOREIGN KEY
CHECK constraints

7.4 Discuss the advantages and disadvantages of views.

See Section 7.4.7.

7.5 Describe how the process of view resolution works.

Described in Section 7.4.3.

7.6 What restrictions are necessary to ensure that a view is updatable?

ISO standard specifies the views that must be updatable in a system that conforms to the standard.
Definition given in SQL standard is that a view is updatable if and only if:

DISTINCT is not specified; that is, duplicate rows must not be eliminated from the query
results.

7
Database Systems: Instructors Guide - Part III

Every element in the SELECT list of the defining query is a column name (rather than a
constant, expression, or aggregate function) and no column appears more than once.
The FROM clause specifies only one table; that is, the view must have a single source table
for which the user has the required privileges. If the source table is itself a view, then that
view must satisfy these conditions. This, therefore, excludes any views based on a join, union
(UNION), intersection (INTERSECT), or difference (EXCEPT).
The WHERE clause does not include any nested SELECTs that reference the table in the
FROM clause.
There is no GROUP BY or HAVING clause in the defining query.

In addition, every row that is added through the view must not violate the integrity constraints of
the base table (Section 7.4.5).

7.7 What is a materialized view and what are the advantages of a maintaining a materialized view
rather than using the view resolution process?

Materialized view is a temporary table that is stored in the database to represent a view, which
is maintained as the base table(s) are updated.

Advantages - may be faster than trying to perform view resolution.

- may also be useful for integrity checking and query optimisation.

See Section 7.4.8.

7.8 Describe the difference between discretionary and mandatory access control. What type of
control mechanism does SQL support.

Discretionary each user is given appropriate access rights (or privileges) on specific database
objects.
Mandatory each database object is assigned a certain classification level (e.g. Top Secret, Secret,
Confidential, Unclassified) and each subject (e.g. user, application) is given a designated
clearance level (Top Secret > Secret > Confidential > Unclassified).

SQL security mechanism is based on discretionary access control.

7.9 Discuss how the Access Control mechanism of SQL works.

Each user has an authorization identifier (allocated by DBA).

Each object has an owner. Initially, only owner has access to an object but the owner can pass
privileges to carry out certain actions on to other users via the GRANT statement and take away
given privileges using REVOKE.

Exercises

Answer the following questions using the relational schema from the Exercises at the end of Chapter 4.

8
Database Systems: Instructors Guide - Part III

7.10 Create the Hotel table using the integrity enhancement features of SQL.

CREATE DOMAIN HotelNumber AS CHAR(4);

CREATE TABLE Hotel(

hotelNo HotelNumber NOT NULL,
hotelName VARCHAR(20) NOT NULL,
city VARCHAR(50) NOT NULL,
PRIMARY KEY (hotelNo));

7.11 Now create the Room, Booking, and Guest tables using the integrity enhancement features of SQL
with the following constraints:

(a) Type must be one of Single, Double, or Family.

(b) Price must be between 10 and 100.
(c) roomNo must be between 1 and 100.
(d) dateFrom and dateTo must be greater than todays date.
(e) The same room cannot be double booked.
(f) The same guest cannot have overlapping bookings.

CREATE DOMAIN RoomType AS CHAR(1)

CHECK(VALUE IN (S, F, D));
CREATE DOMAIN HotelNumbers AS HotelNumber
CHECK(VALUE IN (SELECT hotelNo FROM Hotel));
CREATE DOMAIN RoomPrice AS DECIMAL(5, 2)
CHECK(VALUE BETWEEN 10 AND 100);
CREATE DOMAIN RoomNumber AS VARCHAR(4)
CHECK(VALUE BETWEEN 1 AND 100);

CREATE TABLE Room(

roomNo RoomNumber NOT NULL,
hotelNo HotelNumbers NOT NULL,
type RoomType NOT NULL DEFAULT S
price RoomPrice NOT NULL,
PRIMARY KEY (roomNo, hotelNo),
FOREIGN KEY (hotelNo) REFERENCES Hotel
ON DELETE CASCADE ON UPDATE CASCADE);

CREATE DOMAIN GuestNumber AS CHAR(4);

CREATE TABLE Guest(

guestNo GuestNumber NOT NULL,
guestName VARCHAR(20) NOT NULL,
guestAddress VARCHAR(50) NOT NULL);

CREATE DOMAIN GuestNumbers AS GuestNumber

9
Database Systems: Instructors Guide - Part III

CHECK(VALUE IN (SELECT guestNo FROM Guest));

CREATE DOMAIN BookingDate AS DATETIME
CHECK(VALUE > CURRENT_DATE);

CREATE TABLE Booking(

hotelNo HotelNumbers NOT NULL,
guestNo GuestNumbers NOT NULL,
dateFrom BookingDate NOT NULL,
dateTo BookingDate NULL,
roomNo RoomNumber NOT NULL,
PRIMARY KEY (hotelNo, guestNo, dateFrom),
FOREIGN KEY (hotelNo) REFERENCES Hotel
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (guestNo) REFERENCES Guest
ON DELETE NO ACTION ON UPDATE CASCADE,
FOREIGN KEY (hotelNo, roomNo) REFERENCES Room
ON DELETE NO ACTION ON UPDATE CASCADE,
CONSTRAINT RoomBooked
CHECK (NOT EXISTS ( SELECT *
FROM Booking b
WHERE b.dateTo > Booking.dateFrom AND
b.dateFrom < Booking.dateTo AND
b.roomNo = Booking.roomNo AND
b.hotelNo = Booking.hotelNo)),
CONSTRAINT GuestBooked
CHECK (NOT EXISTS ( SELECT *
FROM Booking b
WHERE b.dateTo > Booking.dateFrom AND
b.dateFrom < Booking.dateTo AND
b.guestNo = Booking.guestNo)));

7.12 Create a separate table with the same structure as the Booking table to hold archive records.
Using the INSERT statement, copy the records from the Booking table to the archive table relating
to bookings before 1st January 2003. Delete all bookings before 1st January 2003 from the
Booking table.

CREATE TABLE BookingOld( hotelNo CHAR(4) NOT NULL,

guestNo CHAR(4) NOT NULL,
dateFrom DATETIME NOT NULL,
dateTo DATETIME NULL,
roomNo VARCHAR(4) NOT NULL);

INSERT INTO BookingOld

(SELECT * FROM Booking
WHERE dateTo < DATE2003-01-01);
DELETE FROM Booking

10
Database Systems: Instructors Guide - Part III

WHERE dateTo < DATE2003-01-01;

7.13 Create a view containing the hotel name and the names of the guests staying at the hotel.

CREATE VIEW HotelData(hotelName, guestName)

AS SELECT h.hotelName, g.guestName
FROM Hotel h, Guest g, Booking b
WHERE h.hotelNo = b.hotelNo AND g.guestNo = b.guestNo AND
b.dateFrom <= CURRENT_DATE AND
b.dateTo >= CURRENT_DATE;

7.14 Create a view containing the account for each guest at the Grosvenor Hotel.

CREATE VIEW BookingOutToday

AS SELECT g.guestNo,g.guestName,g.guestAddress,r.price*(b.dateTo-b.dateFrom)
FROM Guest g, Booking b, Hotel h, Room r
WHERE g.guestNo = b.guestNo AND r.roomNo = b.roomNo AND
b.hotelNo = h.hotelNo AND h.hotelName = Grosvenor Hotel AND
b.dateTo = CURRENT_DATE;

7.15 Give the users Manager and Deputy full access to these views, with the privilege to pass the
access on to other users.

GRANT ALL PRIVILEGES ON HotelData

TO Manager, Director WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON BookingOutToday

TO Manager, Director WITH GRANT OPTION;

7.16 Give the user Accounts SELECT access to these views. Now revoke the access from this user.

GRANT SELECT ON HotelData TO Accounts;

GRANT SELECT ON BookingOutToday TO Accounts;

REVOKE SELECT ON HotelData FROM Accounts;

REVOKE SELECT ON BookingOutToday FROM Accounts;

7.17 Consider the following view defined on the Hotel schema:

CREATE VIEW HotelBookingCount (hotelNo, bookingCount)

AS SELECT h.hotelNo, COUNT(*)
FROM Hotel h, Room r, Booking b
WHERE h.hotelNo = r.hotelNo AND r.roomNo b.roomNo
GROUP BY h.hotelNo;

11
Database Systems: Instructors Guide - Part III

For each of the following queries, state whether the query is valid and for the valid ones
should how each of the queries would be mapped onto a query on the underling base tables.

(a) SELECT *
FROM HotelBookingCount;

SELECT h.hotelNo, COUNT(*)

FROM Hotel h, Room r, Booking b
WHERE h.hotelNo = r.hotelNo AND r.roomNo b.roomNo
GROUP BY h.hotelNo;

(b) SELECT hotelNo

FROM HotelBookingCount
WHERE hotelNo = H001;

SELECT h.hotelNo
FROM Hotel h, Room r, Booking b
WHERE h.hotelNo = r.hotelNo AND r.roomNo b.roomNo AND
h.hotelNo = H001
GROUP BY h.hotelNo;

(c) SELECT MIN(bookingCount)

FROM HotelBookingCount;

Invalid bookingCount is based on an aggregate function, so cannot be used within

another aggregate function.

(d) SELECT COUNT(*)

FROM HotelBookingCount;

Invalid for reason given above.

(e) SELECT hotelNo

FROM HotelBookingCount
WHERE bookingCount > 1000;

Invalid bookingCount is based on an aggregate function, so cannot be used in the

WHERE clause.

(f) SELECT hotelNo

FROM HotelBookingCount
ORDER BY bookingCount;

SELECT h.hotelNo, COUNT(*) AS bookingCount

FROM Hotel h, Room r, Booking b
WHERE h.hotelNo = r.hotelNo AND r.roomNo b.roomNo

12
Database Systems: Instructors Guide - Part III

GROUP BY h.hotelNo
ORDER BY bookingCount;

7.19 Assume that we also have a table for suppliers:

Supplier (supplierNo, partNo, price)

and a view SupplierParts, which contains the distinct part numbers that are supplied by at
least one supplier:

CREATE VIEW SupplierParts (partNo)

AS SELECT DISTINCT partNo
FROM Supplier s, Part p
WHERE s.partNo = p.partNo;

13