Question

HOW TO DO THIS DATABASE QUESTION USING ORACLE?

Hotel (hotelNo, hotelName, hotelAddress, country)

Room (roomNo, hotelNo, type, price)

Guest (guestNo, guestName, guestAddress, country)

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

1. (a).List all guests currently staying at the Grosvenor Hotel. Include the roomNo in the output. Use 2019-01-21 as the current date.

           (b) What is the total income from bookings for the Grosvenor Hotel today? Do this question for each of the hotels with 'Grosvenor' in name. Use 2019-01-21 as the current date.

Answer 1

(a)

select guestname from guest where guestNo in (select guestNo from booking where hotel=(select hotelNo from Hotel where hotelName='Grosvenor Hotel') and dateFrom<=today() and dateTo>=Today());

-- cannot list roomNo as A Guest does not have room no as per the detail above. Room no and guestno are not linked/referenced/related to each other.

(b)

select sum(price) from room where hotelNo IN (select hotelNo from booking where dateFrom<=today() or dateTo>=Today() and hotelNo= (select hotelNo from Hotel where hotelName='Grosvenor Hotel') );

--today() is a date function which returns today's date. You can replace it by date of your choice like '2019-01-21'