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
Which room has the highest rate? List the room's full name, rate, check in and check out date.
assistant
SELECT T2.roomName , T1.Rate , T1.CheckIn , T1.CheckOut FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room = T2.RoomId GROUP BY T1.Room ORDER BY T1.Rate DESC LIMIT 1;
human
How many rooms are there?
assistant
SELECT count(*) FROM Rooms;
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 are the names of rooms whose reservation frequency exceeds 60 times?
assistant
SELECT T2.roomName FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room = T2.RoomId GROUP BY T1.Room HAVING COUNT( * ) > 60 ;