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
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 name and id of the top 3 expensive rooms.
assistant
SELECT RoomId , roomName FROM Rooms ORDER BY basePrice DESC LIMIT 3;
human
Which room has the highest rate? List the room's full name, rate, check in and check out date.
assistant
SELECT T2.roomName , T1.Rate , T1.CheckIn , T1.CheckOut FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room = T2.RoomId GROUP BY T1.Room ORDER BY T1.Rate DESC LIMIT 1;
human
List how many times the number of people in the room reached the maximum occupancy of the room. The number of people include adults and kids.
assistant
SELECT count(*) FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room = T2.RoomId WHERE T2.maxOccupancy = T1.Adults + T1.Kids;
human
Find the most popular room in the hotel. The most popular room is the room that had seen the largest number of reservations.
assistant
SELECT T2.roomName FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room = T2.RoomId GROUP BY T1.Room ORDER BY COUNT( * ) DESC LIMIT 1 ;