Write SQL queries for the following problem: The hotel database has the followin

Question

Write SQL queries for the following problem:

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)?

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';

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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