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 for each bed type.
assistant
SELECT bedType , count(*) FROM Rooms GROUP BY bedType;
human
Find the first and last names of people who payed more than the rooms' base prices.
assistant
SELECT T1.firstname , T1.lastname FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room = T2.RoomId WHERE T1.Rate - T2.basePrice > 0
human
Find the name of rooms booked by some customers whose first name contains ROY.
assistant
SELECT T2.roomName FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room = T2.RoomId WHERE firstname LIKE '%ROY%'
human
Find the names of rooms that have been reserved for more than 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
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
For each room, find its name and the number of times reservations were made for it.
assistant
SELECT T2.roomName , COUNT( * ) , T1.Room FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room = T2.RoomId GROUP BY T1.Room ;