Ok we\'ve been given the following tables Hotel (hno,name,address) Room (rno,hno

Question

Ok we've been given the following tables

Hotel (hno,name,address)
Room (rno,hno,type,price)
Booking (hno,gno,dateFrom,dateTo,rno)
Guest (gno,name,address)

and a few SQL queries to write,(stuck on these for a while now, please help out)

For the date 26 March 1997, perform the following queries

(a)calculate the total income for the Grosvenor hotel

(b)List the unoccupied rooms in the Grosvenor Hotel

(c)Calculate the lost income from unoccupied rooms at the Grosvenor Hotel

(d)List the details of all rooms in all hotels, including the name of any guests who were staying in the room

Explanation / Answer

a)

SELECT SUM(price) FROM Booking b, Room r, Hotel h
WHERE r.hno = h.hno AND r.rno = b.rno;

b)

SELECT * FROM room r
WHERE rno NOT IN
(SELECT rno FROM Booking b, Hotel h
WHERE b.hno = h.hno AND h.name = 'Grosvenor');

c)

SELECT SUM(price) FROM Room r
WHERE rno NOT IN
(SELECT rno FROM booking b, hotel h
WHERE b.hno = h.hno AND h.name = 'Grosvenor');

d)

SELECT * FROM Guest
WHERE gno=
(SELECT gno FROM Booking
WHERE hno = (SELECT hno FROM Hotel));

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

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