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.

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;


(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
GROUP BY h.hotelNo
ORDER BY bookingCount;

Computer Science & Information Technology

You might also like to view...

____________________ happens when input applied to a variable is larger than the memory allotted to that variable.

Fill in the blank(s) with the appropriate word(s).

Computer Science & Information Technology

The present_value argument of the Pmt Function is the amount owed after the final payment has been made

Indicate whether the statement is true or false

Computer Science & Information Technology

Files with the ________ extension are formatted as the creator intended and read only and readable by a free downloadable program

Fill in the blank(s) with correct word

Computer Science & Information Technology

You can manage an AD LDS instance using the Active Directory Administrative Center

Indicate whether the statement is true or false

Computer Science & Information Technology