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
List the name of all rooms sorted by their prices.
assistant
SELECT roomName FROM Rooms ORDER BY basePrice;
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 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 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 names of all modern rooms with a base price below $160 and two beds.
assistant
SELECT roomName FROM Rooms WHERE basePrice < 160 AND beds = 2 AND decor = 'modern';
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 ;