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
For each reservation, find the base price and the actual rate payed.
assistant
SELECT T1.Rate , T2.basePrice FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room = T2.RoomId;
human
How many times did the actual price exceeded the base price?
assistant
SELECT COUNT( * ) FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room = T2.RoomId WHERE T1.Rate - T2.basePrice > 0;
human
What are 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 ;