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 for each bed type.
assistant
SELECT bedType , count(*) FROM Rooms GROUP BY bedType;
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
How many rooms are there?
assistant
SELECT count(*) FROM Rooms;
human
Find the name of the room with the maximum occupancy.
assistant
SELECT roomName FROM Rooms ORDER BY maxOccupancy DESC LIMIT 1;
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 ;