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;
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).
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
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
You can manage an AD LDS instance using the Active Directory Administrative Center
Indicate whether the statement is true or false