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
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 number of rooms with a king bed.
assistant
SELECT count(*) FROM Rooms WHERE bedType = 'King';
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
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
Find the number of adults for the room reserved and checked in by CONRAD SELBIG on Oct 23, 2010.
assistant
SELECT Adults FROM Reservations WHERE CheckIn = "2010-10-23" AND FirstName = "CONRAD" AND LastName = "SELBIG" ;