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 first and last names of people who payed more than the rooms' base prices.
assistant
SELECT T1.firstname , T1.lastname FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room = T2.RoomId WHERE T1.Rate - T2.basePrice > 0
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 all the rooms that have a price higher than 160 and can accommodate more than 2 people. Report room names and ids.
assistant
SELECT roomName , RoomId FROM Rooms WHERE basePrice > 160 AND maxOccupancy > 2;
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;
human
Find the name of the room with the maximum occupancy.
assistant
SELECT roomName FROM Rooms ORDER BY maxOccupancy 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 ;