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 names and decor of rooms that have a king bed. Sort the list by their price.
assistant
SELECT roomName , decor FROM Rooms WHERE bedtype = 'King' ORDER BY basePrice;
human
List the type of bed and name of all traditional rooms.
assistant
SELECT roomName , bedType FROM Rooms WHERE decor = 'traditional';
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 the average and minimum price of the rooms in different decor.
assistant
SELECT decor , avg(basePrice) , min(basePrice) FROM Rooms GROUP BY decor;
human
What is the decor of room Recluse and defiance?
assistant
SELECT decor FROM Rooms WHERE roomName = 'Recluse AND defiance';
human
What is the least popular kind of decor?
assistant
SELECT T2.decor FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room = T2.RoomId GROUP BY T2.decor ORDER BY COUNT( T2.decor ) ASC LIMIT 1 ;