Selection Queries: Hotel Rom Booking (hotelNo, hotelName, city) (roomNo, hotelNo

Question

Selection Queries: Hotel Rom Booking (hotelNo, hotelName, city) (roomNo, hotelNo, type, price) (hotelNo, questNo, dateFrom, date To, roomNo) (guestNo, guestName, guestAddress) Guest Room contains room details for each hotel and (roomNo, hotelNo) forms the primary key Guest contains guest details and guestNo is the primary key. 1. Sub queries and Joins: a ist the price and type of all rooms at the Grosvenor Hotel. List the details of all rooms at the Grosvenor Hotel, including the name of the guest staying in the room, if the room is occupied. b) HINT: Use LEFT JOIN for this statement

Explanation / Answer

a)

SELECT DISTINCT type, price

FROM Hotel H, Room R

WHERE H.hotelName = ‘Grosvenor’

AND H.hotelNo = R.hotelNo

ORDER By type

b)

SELECT R.*

FROM Room R

LEFT JOIN

(SELECT G.guestName, H.hotelNo, B.roomNo

FROM Guest G, Booking B, Hotel H

WHERE G.guestNo = B.guestNo AND B.hotelNo = H.hotelNo AND

H.hotelname= 'Grosvenor' AND

B.datefrom <= CURRENT_DATE AND B.dateTo >= CURRENT_DATE

) AS temp

ON R.hotelNo = temp.hotelNo

AND R.roomNo = temp.roomNo;

c)

SELECT *

FROM Room R

WHERE roomNo NOT IN

(SELECT roomNo FROM Booking B, Hotel H

WHERE (datefrom <= CURRENT_DATE AND

dateto >= CURRENT_DATE) AND

B.hotelNo = H.hotelNo AND hotelName = 'Grosvenor'

);

d)

SELECT hotelName, Count(*) as RoomCount

FROM Hotel H, Room R

WHERE H.HotelNo = R.HotelNo

AND H.city = 'London'

GROUP By HotelName

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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