Give a list of booked rooms for months of May, Jun And July 2009, having price greater than 8000 per day

Question

Tables

Hotel    (hotelNo, HotelName, city)

Room     (roomNo, hotelNo, type, price)

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

Guest    (guestNo, guestName, guestAddress)

Give the Structured Query Language syntax for the following query based upon the above tables:

Give a list of booked rooms for months of May, Jun And July 2009, having price greater than 8000 per day.

I tried the following, but it's not working:

SELECT *
FROM Booking 
WHERE HotleNo IN (SELECT HotelNo FROM Room WHERE Price>8000)
AND (DateFrom>= '2009-05-01' AND dateTo<= '2009-07-31');

Answer 1

The price is a Room attribute not an Hotel attribute. But I guess you might be handling several hotels which overlapping room numbers, so you have to match roomNo and hotelNo

SELECT *
FROM Booking 
JOIN Room on Booking.roomNo=Room.roomNo and Booking.hotleNo=Room.hotleNo
WHERE Room.Price>8000
AND Booking.DateFrom>= '2009-05-01' 
AND Booking.dateTo<= '2009-07-31'

Edit: in your question the field is hotleNo so I'll stick to that field name

Answer 2

You can try the below. Since all your information needed can be retrieve from table Booking and Room, you only need to join the 2 and get the data.

select B.* from Booking B
left outer join Room  R   
on    R.roomNo   = B.roomNo
where B.dateFrom >= '2009-05-01'
and   B.dateTo   <=   '2009-07-31'
and   R.price    > 8000