Write SQL queries for the following: The hotel database has the following schema

Question

Write SQL queries for the following:

The hotel database has the following schema:

HOTEL(hnumber, hname, address)

Primary key: {hnumber}

ROOM(rnumber, hnumber, type, price_night)

Primary key: {rnumber, hnumber}

Foreign key: {hnumber} refernces primary key of HOTEL

GUEST(gnumber, gname, address)

Primary key: {gnumber}

BOOKING(hnumber, gnumber, arrive_date, left_date, rnumber)

Primary key: {hnumber, gnumber, rnumber}

Foreign key 1: {gnumber} references primary key of GUEST

Foreign key 2: {rnumber, hnumber} references primary key of ROOM

Write the following queries:

1) Write the room numbers (along with the hotel numbers) of all rooms of type Executive Suite at the BIG HOTEL that are today unoccupied (today is Aug 7, 2017, BIG HOTEL is a hotel name.

2) How many quests have booked a room in the NEW HOTEL (NEW HOTEL is a hotel name)?

3) What is the price difference between the cheapest and most expensive room in the BIG HOTEL (BIG HOTEL is a hotel name)?

4) Find names of all guests that have never stayed in the NEW HOTEL (NEW HOTEL is a hotel name)?

Explanation / Answer

1)Write the room numbers along with the hotel numbers of all rooms of type Executive Suite at the BIG HOTEL that are today unoccupied?

SELECT R.rnumber,H.hnumber from ROOM R,HOTEL H, BOOKING B where H.hnumber=R.hnumber and R.rnumber=B.rnumber and B. left_date= 'Aug 7, 2017' group by R.rnumber;

2)How many quests have booked a room in the NEW HOTEL (NEW HOTEL is a hotel name)?

SELECT COUNT(*) AS total FROM ( SELECT G.gnumber from GUEST G,,HOTEL H,BOOKING B where G.gnumber=B.gnumber and B.hnumber=H.hnumber and H.hname='NEW HOTEL';

4) (SELECT G.gname from GUEST G) MINUS (SELECT G.gname from GUEST G,BOOKING B,HOTEL H where G.gnumber=B.gnumber and B.hnumber=H.hnumber and H.hname='NEW HOTEL');

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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