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
Can you find the first and last names of all people who paid 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
Could you add in the rates they paid to the table please? | Sorry, did you mean the base price of the room? | The reservation rate, please
assistant
SELECT T1.firstname , T1.lastname , T1.Rate FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room = T2.RoomId WHERE T1.Rate - T2.basePrice > 0;
human
Can you find the first and last names of all people who paid less 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
Could you add in the reservation rates they paid to the table?
assistant
SELECT T1.firstname , T1.lastname , T1.Rate FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room = T2.RoomId WHERE T1.Rate - T2.basePrice < 0;
human
What is the name of the room that was booked most often?
assistant
SELECT Room FROM Reservations GROUP BY Room ORDER BY COUNT( * ) DESC LIMIT 1 ;