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.

Question

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;
(a) SELECT *
FROM HotelBookingCount;
(b) SELECT hotelNo
FROM HotelBookingCount
WHERE hotelNo = ‘H001’;
(c) SELECT MIN(bookingCount)
FROM HotelBookingCount;
(d) SELECT COUNT(*)
FROM HotelBookingCount;
(e) SELECT hotelNo
FROM HotelBookingCount
WHERE bookingCount > 1000;
(f) SELECT hotelNo
FROM HotelBookingCount
ORDER BY bookingCount;

Answer 1

(a) 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 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) Invalid – bookingCount is based on an aggregate function, so cannot be used within another aggregate function.
(d) Invalid for reason given above.
(e) Invalid – bookingCount is based on an aggregate function, so cannot be used in the WHERE clause.
(f) SELECT h.hotelNo, COUNT(*) AS bookingCount
FROM Hotel h, Room r, Booking b
WHERE h.hotelNo = r.hotelNo AND r.roomNo ? b.roomNo
GROUP BY h.hotelNo
ORDER BY bookingCount;