Question

2. The following tables form part of a database hield in a relatioual DBMS: (10 Points) Hotel Room Booking Guest hotelNo, hotelName, city {room No. hotelNo. Type.price) booking No, hotelNo, guestNo, date from date To roomNo) (guestNo. guestName. guestAddress) A. List the price and type of all rooms at the Shangri-La Hotel B. Listall guests staying at Shangri-La Hotel berween Jimmy I and January 31, 2020 Liste details of all rooms at the Shangri-La llore, including the name of the guest styme bombetween Sommary I atd Jamar al fomnis cupied

Answer 1

Please rate the answer. Feel free to contact me, in case of any more clarifications required

Question_01.LIST the Price and type of all rooms at Shangri-La HOTEL
Answer => 
                select  a.price,
                                a.type                          
                From Room a,
                         HOTEL b
                Where a.HotelNo = b.HotelNo
                And HotelName = 'Shangri-La';

Explanation:
Pick Data [Price and type] from ROOM table and join with HOTEL table to filter for 'Shangri-La' Hotel

Output Question_01:

select * from hotel; select * from Room; select a price, a. type From Room a, HOTEL b Where a.HotelNo = b. HotelNo And HotelName = 'Shangri-La'; select * from hotel; select * from Room; select a.price, a. type From Room a, HOTEL b Where a.HotelNo = b. Hotelno And HotelName = 'Shangri-La'; select * from hotel; select * from Room; select a.price, a. type From Room a HOTEL D where a.Hotelno = b. Hotelno And HotelName = 'Shangri-La': Script Output x Query Result x I c ER SQL | All Rows Fetched: 1 in 0.001 seconds HOTELNO HOTELNAME CITY 1 1121 Shangri-La HK CITY Script Output x Query Result x 9 4 ER SQL | All Rows Fetched: 2 in 0.001 seconds ROOMNO HOTELNO TYPE PRICE 111 1121 EXECUITVE 2 112 1121 FAMILY Script Output x Query Result x I ER SQL | All Rows Fetched: 2 in 0.005 seconds PRICE TYPE 5000 EXECUITVE 1 5000 1 5500 2 5500 FAMILY

Question_02. LIST all guests staying in Shangri-La HOTEL between January 1 and January 31, 2020.
Answer => 
                Select a.GuestName
                From Guest a,
                     Booking b
                where a.GuestNo = b.GuestNo
                And b.dateFrom >= '01-JAN-2020' 
                AND b.dateto <= '31-JAN-2020';

Explanation:
Fetch the Guest Name from GUEST Table and join with BOOKING Table using the GuestNo field.

Filter the data using DATEFROM and DATETO field in BOOKING Table.
Filter Condition => Guest Staying in the hotel between ist January 2020 and 31st January 2020.

Output Question_02:

select * from Booking; select * from Booking; select * from Booking; select * from guest; select * from guest; select * from quest; Select a.Guest Name From Guest a, Booking b where a.GuestNo = b. GuestNo And b.date From >= '01-JAN-2020 AND b.dateto <= '31-JAN-2020'; Select a.GuestName From Guest a, Booking b where a. GuestNo = b. GuestNo And b.date From >= '01-JAN-2020 AND b.dateto <= '31-JAN-2020'; Select a.GuestName From Guest a, Booking b where a.GuestNo = b. Guesto And b.dateFrom >= '01-JAN-2020' AND b.dateto <= '31-JAN-2020'; insert into Room values (113, 1121, 'EXECUTIVE', 6000); Script Output x Query Result x I W SQL | All Rows Fetched: 3 in 0.002 seconds BOOKINGNO HOTELNO GUESTNO DATEFROM DATETO ROOMNO 1 300021 1121 2001 01-01-20 21-01-20 111 2 3001121 2002 21-01-20 31-01-20 112 3 3001321 1121 2003 24-01-20 03-02-20 113 Script Output x Query Result x 9 4 ER SQL | All Rows Fetched: 3 in 0.001 seconds GUESTNO GUESTNAME GUESTADDRESS 2001 DAVID J ABC CITY HK 2 2002 JACK DAVIS XYZ CITY HK 3 2003 MOHAN KUMARS ZGC CITY HK Script Output x Query Result x x G2 E3 SQL SQL | All Rows Fetched: 2 in 0.002 seconds GUESTNAME 1 DAVID J 2 JACK DAVIS MOHAN KUMARS is omitted since he stayed till 03 rd feb 2020 1 1121

Question_03. List the details of all rooms at 'Shangri-La'Hotel including the name of the guest staying in the room between January 1 and January 31, 2020, 
if the room is occupied.

Answer =>
Select c.*,
       a.GuestName
From Guest a,
     Booking b,
     room c
where a.GuestNo = b.GuestNo
And b.RoomNo = c.RoomNo
And b.dateFrom >= '01-JAN-2020' 
AND b.dateto <= '31-JAN-2020';

Explanation: 
Add Room details to the query given for the Question_02.
The join condition field will be RoomNo, which is the primary key Room Table and Foreign Key in Booking Table. 
The Query will omit the records where hotel stay is extended beyond '31-JAN-2020'.
So only two records will be displayed, The thrid record will be omitted since the guest is staying till 3rd Feb 2020.

Output Question_03:

G Select c.*, a. Guest Name From Guest a, Booking b, room c where a. GuestNo = b. GuestNo And b. RoomNo = c.RoomNo And b.date From >= '01-JAN-2020 AND b.dateto <= '31-JAN-2020'; Script Output x Query Result x SQL | All Rows Fetched: 2 in 0.006 seconds ROOMNO HOTELNO TYPE PRICE GUESTNAME 111 1121 EXECUITVE 5000 DAVID J 2 112 1121 FAMILY 5500 JACK DAVIS 1