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 number of rooms with price higher than 120 for different decor.
assistant
SELECT decor , count(*) FROM Rooms WHERE basePrice > 120 GROUP BY decor;
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
How many rooms are there?
assistant
SELECT count(*) FROM Rooms;
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
What are the room names and ids of all the rooms that cost more than 160 and can accommodate more than two people.
assistant
SELECT roomName , RoomId FROM Rooms WHERE basePrice > 160 AND maxOccupancy > 2 ;