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
Find the name of rooms whose base price is between 120 and 150.
assistant
SELECT roomname FROM rooms WHERE baseprice BETWEEN 120 AND 150
human
List the name of all rooms sorted by their prices.
assistant
SELECT roomName FROM Rooms ORDER BY basePrice;
human
Find the number of rooms with price higher than 120 for different decor.
assistant
SELECT decor , count(*) FROM Rooms WHERE basePrice > 120 GROUP BY decor;
human
Find the name of rooms whose price is higher than the average price.
assistant
SELECT roomName FROM Rooms WHERE basePrice > ( SELECT avg(basePrice) FROM Rooms );
human
Find the average and minimum price of the rooms in different decor.
assistant
SELECT decor , avg ( basePrice ) , MIN( basePrice ) FROM Rooms GROUP BY decor ;