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
How many different types of beds are there?
assistant
SELECT count(DISTINCT bedType) FROM Rooms;
human
What is the decor of room Recluse and defiance?
assistant
SELECT decor FROM Rooms WHERE roomName = 'Recluse AND defiance';
human
Return the name and number of reservations made for each of the rooms.
assistant
SELECT T2.roomName , count(*) , T1.Room FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room = T2.RoomId GROUP BY T1.Room
human
Find the number of rooms that do not have any reservation.
assistant
SELECT count(*) FROM rooms WHERE roomid NOT IN (SELECT DISTINCT room FROM reservations)
human
What kind of decor has the least number of reservations?
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 ;