human
Schema:
Rooms (RoomId, roomName, beds, bedType, maxOccupancy, basePrice, decor)
Reservations (Code, Room, CheckIn, CheckOut, Rate, LastName, FirstName, Adults, Kids)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the names of all modern rooms with a base price below $160 and two beds.
assistant
SELECT roomName FROM Rooms WHERE basePrice < 160 AND beds = 2 AND decor = 'modern';
human
Find the name of rooms whose base price is between 120 and 150.
assistant
SELECT roomname FROM rooms WHERE baseprice BETWEEN 120 AND 150
human
Find the name and id of the top 3 expensive rooms.
assistant
SELECT RoomId , roomName FROM Rooms ORDER BY basePrice DESC LIMIT 3;
human
Which room has the highest rate? List the room's full name, rate, check in and check out date.
assistant
SELECT T2.roomName , T1.Rate , T1.CheckIn , T1.CheckOut FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room = T2.RoomId GROUP BY T1.Room ORDER BY T1.Rate DESC LIMIT 1;
human
Which room has cheapest base price? List the room's name and the base price.
assistant
SELECT roomName , basePrice FROM Rooms ORDER BY basePrice ASC LIMIT 1;
human
Which room has the highest base price?
assistant
SELECT RoomId , roomName FROM Rooms ORDER BY basePrice DESC LIMIT 1 ;