SQL Question

The following relations (based on chapter 4, DS textbook) form part of a relational database schema:

Hotel (hotelNo, hotelName, hotel_address, city, province, country)

Room (roomID, roomNo, hotelNo, type, price)

Booking (BookingID, guestNo, dateFrom, dateTo, roomID)

Guest (guestNo, guestName, guestAddress, comments)

where Hotel contains hotel details and hotelNo is the primary key; Room contains room details for each hotel and roomID is the primary key; Room type specifies single, double or family. Booking contains details of the bookings and BookingID is the primary key; and the Guest table contains guest details and guestNo is the primary key. Comments are a string of characters and digits stored a variable character type with maximum 128 characters.

Preliminary task:

Create required tables (use prefix A2_ for their names) and add data. You can use CASE tool or SQLDeveloper.

Our reservation center gets a lot of last minute requests for a one night reservation starting the same day. The staff would like to have a quick method of finding an available room (any type any hotel) in a specific city. For example: a guest arrives to Kamloops in the late afternoon and needs to stay for one night in Kamloops. The system should find all hotels in Kamloops with at least one available room for tonight.

Hints for solution:

Create a stored procedure LIST_AVAILABLE with one input p_city. This procedure should list all hotels in the specified city with at least one room available for todays (SYSDATE) night. The room is available if there are no reservations for todays night. List the title: Hotels available in city .for 2016-10-26 and a line for each hotel name and address.