CODEWITHSUNDEEP
mysqlsqldatabasedate
sangeen
sangeen

Reputation: 304

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

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

Upvotes: 0

Views: 491

Answers (2)

ffflabs
ffflabs

Reputation: 17481

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

Upvotes: 2

Nadeem_MK
Nadeem_MK

Reputation: 7689

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

Upvotes: 1

Related Questions