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
List the name of rooms with king or queen bed.
assistant
SELECT roomName FROM Rooms WHERE bedType = 'King' OR bedType = 'Queen';
human
How many rooms are there?
assistant
SELECT count(*) FROM Rooms;
human
Find the number of rooms with king bed for each decor type.
assistant
SELECT decor , count(*) FROM Rooms WHERE bedType = 'King' GROUP BY decor;
human
Return the name and number of reservations made for each of the rooms.
assistant
SELECT T2.roomName , count(*) , T1.Room FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room = T2.RoomId GROUP BY T1.Room
human
Find the names of rooms that have been reserved for more than 60 times.
assistant
SELECT T2.roomName FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room = T2.RoomId GROUP BY T1.Room HAVING count(*) > 60
human
Return the decor of the room named 'Recluse and defiance'.
assistant
SELECT decor FROM Rooms WHERE roomName = "Recluse AND defiance" ;