Question

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

Hotel (hotelNo, hotelName, city)

Room (roomNo, hotelNo, type, price)

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

Guest (guestNo, guestName, guestAddress)

where Hotel contains hotel details and hotelNo is the primary key;

Room contains room details for each hotel and (roomNo, hoteINo) forms the primary key;

Booking contains details of bookings and (hoteINo, guestNo, dateFrom) forms the primary key;

Guest contains guest details and guestNo is the primary key. Note: Hotel, Room, and Guest are relatively self explanatory.

Each row in the Booking tabledescribes a single booking for a guest at a hotel (past, present, and future).Assumptions:price is some type of a number field.type is a VARCHAR field with one of the following values: {“double”,”king”,”suite”}dateTo and dateFrom are both DATE fieldsAdditionally, in SQL Server the ​getDate()​ function will return the current date/time. You canuse this within a query, even something as simple as ​SELECT getDate()You can also use this in comparison to dateFields using normal mathematical comparisons:...WHERE dateFrom <= getDate()

Question1​. Write a single SQL query that calculates the average price of a room across all hotels.

Question2​. Write a single SQL query that determines the list of all guests staying at the HyattRegency today.

Question3​. Write a single SQL query that, with the current bookings, shows how much moneyis each hotel making today

Question4​. Write one insert statement for each table. Thus, there should be four separatestatements. You may make up data as you wish but it should adhere to the schema and ourassumptions so far.

Question5​. Write a single SQL query that will increase the price of all rooms at the HyattRegency by 6%.

Question6​. Write a single SQL query that lists all rooms that are currently unoccupied.

Question7​. Write a single SQL query that, for the Hyatt Regency hotel, list all rooms and ifthere is a person occupying the room also list the guest’s name.

Answer 1

Question1​. Write a single SQL query that calculates the average price of a room across all hotels.

Answer) SELECT AVG(R.PRICE),H.HOTELNAME FROM ROOM R, HOTEL H WHERE R.HOTELNO=H.HOTELNO ;

Question2​. Write a single SQL query that determines the list of all guests staying at the HyattRegency today.

ANSWER) SELECT G.GUESTNAME FROM GUESTS G,BOOKING B WHERE B.GUESTNO=G.GUESTNO AND B.DATEFROM= GETDATE() AND B.DATETO=GETDATE();

Question3​. Write a single SQL query that, with the current bookings, shows how much moneyis each hotel making today

ANSWER)SELECT H.HOTELNAME,TOTAL(R.PRICE) FROM ROOM R, HOTEL H, BOOKING B WHERE B.DATEFROM= GETDATE() AND B.DATETO=GETDATE() AND H.HOTELNO=R.HOTELNO AND H.HOTELNO=B.HOTELNO;

Question4​. Write one insert statement for each table. Thus, there should be four separatestatements. You may make up data as you wish but it should adhere to the schema and ourassumptions so far.

ANSWER) INSERT STATEMENT FOR THE HOTELTABLE:

INSERT INTO HOTEL VALUES('OP512', 'TAJ', 'KOLKATA')

INSERT STATEMENT FOR ROOM TABLE

INSERT INTO ROOM VALUES('A1','OP512','DOUBLE BED',4652.15)

INSERT STATEMENT FOR BOOKING TABLE

INSERT STATEMENT BOOKING VALUES( 'OP512', '45', '2008-11-11', '2008-11-14, 'A1')

INSERT STATEMENT FOR GUEST TABLE

INSERT INTO GUEST VALUES('45', 'JOHN', 'HYDERABAD')

Question5​. Write a single SQL query that will increase the price of all rooms at the HyattRegency by 6%

ANSWER ) UPDATE ROOMS SET PRICE= PRICE+(6/100)*PRICE FROM HOTEL H, ROOM R WHERE H.HOTELID=R.HOTELID AND H.HOTELNAME='HYATT REGACY';

Question6​. Write a single SQL query that lists all rooms that are currently unoccupied.

ANSWER) SELECT R.ROOMNO AS ROOM FROM ROOM R, BOOKING B WHERE R.HOTELNO=B.HOTELNO AND R.ROOMNO<>B.ROOMNO;

Question7​. Write a single SQL query that, for the Hyatt Regency hotel, list all rooms and ifthere is a person occupying the room also list the guest’s name.

ANSWER)SELECT B.ROOMNO AS ROOMS FROM HOTEL H,BOOKING B, GUEST G WHERE G.GUESTNO=B.GUESTNO AND H.ROOMNO=B.ROOMNO AND H.HOTELNAME='HYATT REGACY'