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 number of rooms with king bed for each decor type.
assistant
SELECT decor , count(*) FROM Rooms WHERE bedType = 'King' GROUP BY decor;
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 average and minimum price of the rooms in different decor.
assistant
SELECT decor , avg(basePrice) , min(basePrice) FROM Rooms GROUP BY decor;
human
How many rooms are there?
assistant
SELECT count(*) FROM Rooms;
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
How many rooms cost more than 120, for each different decor?
assistant
SELECT decor , COUNT( * ) FROM Rooms WHERE basePrice > 120 GROUP BY decor ;