Question

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

Hotel (hotelNo, hotelName, hotelAddress, country)

Room (roomNo, hotelNo, type, price)

Guest (guestNo, guestName, guestAddress, country)

Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)

Write the SQL statements for the following questions:

1. List the rooms that are currently unoccupied at the Grosvenor Hotel, for:

(a) Use 2019-10-01 as today's date. Include all 'Grosvenor' hotels. List in hotelNo, roomNo order. Use NOT IN to perform the difference operation

(b) Use 2019-10-01 as today's date. Include all 'Grosvenor' hotels. List in hotelNo, roomNo order. Use NOT EXISTS to perform the difference operation.

(c) Use 2019-10-01 as today's date. Include all 'Grosvenor' hotels. List in hotelNo, roomNo order. Use LEFT JOIN to perform the difference operation.

(d) Use 2019-10-01 as today's date. Include all 'Grosvenor' hotels. List in hotelNo, roomNo order. Use MINUS to perform the difference operation.

Answer 1

Explanation

we can write query like that

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’);