9. Write a query which displays the list of rooms (room number) at the Bar Harbo

Question

9. Write a query which displays the list of rooms (room number) at the Bar Harbor Hotel which are available during the period Sept 9, 2017 through Sept 25, 2017. To write this query, note that a room is available during this period if, for all of the room's bookings ( sdi,,edi), either sdi> 09/25/2017 or edi09/09/2017 or the room has no booking at all. To catch all of these possibilites, of writing a query that finds rooms that are not available during the period and returns the rooms that are left. Your query should produce a table with the following columns and headers: Room number available at Bar Harbor Hotel during 09/09/2017 through 09/25/2017

Explanation / Answer

Once you have all the above five tables created in your database, you can write the below approach to get the list of rooms available at the Bar Harbor Hotel during the specified period as below.

The following query gives you HID of 'The Bar Harbor' hotel.

Query: select HID from Hotel where hotelname = 'Bar Harbor Hotel'

Now we will get all rooms in 'The Bar Harbor' hotel using below query. Observe that we use above query as a "Subquery" in this query.

Query: select roomNo from Rooms where HID in ( select HID from Hotel where hotelname = 'Bar Harbor Hotel' )

Now we will write the query which gives you all not available rooms in all hotels during the given period using Booking table.

Query:

select roomNo from Bookings where (startdate >= '09-09-2017' AND startdate <='25-09-2017') OR (enddate>= '09-09-2017' AND enddate<='25-09-2017')

Note: if date representation is not supported in your database, use e.g., to_date('25-09-2017','DD-MM-YYYY') function.

Now we will write the final query that gives you all the available rooms in 'Bar Harbor Hotel' during given period.

Final Query:

SELECT roomNo as "Room Number available at Bar Harbor Hotel during 09/09/2017 through 09/25/2017"
FROM Bookings
WHERE roomNo NOT IN
( SELECT roomNo FROM Bookings WHERE (startdate >= '09-09-2017' AND startdate <='25-09-2017') OR (enddate>= '09-09-2017' AND enddate<='25-09-2017') )
AND
roomNo IN ( SELECT roomNo FROM Rooms WHERE HID IN ( SELECT HID FROM Hotel WHERE hotelname = 'Bar Harbor Hotel' ) )

The above query will give you required output.

Related Questions

Navigate

• Browse (All)
• Browse 9
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.