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 that do not have any reservation.
assistant
SELECT count(*) FROM rooms WHERE roomid NOT IN (SELECT DISTINCT room FROM reservations)
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
How many kids stay in the room DAMIEN TRACHSEL checked in on Sep 21, 2010?
assistant
SELECT Kids FROM Reservations WHERE CheckIn = '2010-09-21' AND FirstName = 'DAMIEN' AND LastName = 'TRACHSEL';
human
How many times does ROY SWEAZY has reserved a room.
assistant
SELECT count(*) FROM Reservations WHERE FirstName = 'ROY' AND LastName = 'SWEAZY';
human
Find the name of rooms booked by some customers whose first name contains ROY.
assistant
SELECT T2.roomName FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room = T2.RoomId WHERE firstname LIKE '%ROY%'
human
Find the number of kids staying in the rooms reserved by a person called ROY SWEAZ.
assistant
SELECT kids FROM Reservations WHERE FirstName = "ROY" AND LastName = "SWEAZY" ;