CREATE TABLE Rooms (
    RoomId TEXT PRIMARY KEY,
    roomName TEXT,
    beds NUMBER,
    bedType TEXT,
    maxOccupancy NUMBER,
    basePrice NUMBER,
    decor TEXT
);
CREATE TABLE Reservations (
    Code NUMBER PRIMARY KEY,
    Room TEXT,
    CheckIn TEXT,
    CheckOut TEXT,
    Rate NUMBER,
    LastName TEXT,
    FirstName TEXT,
    Adults NUMBER,
    Kids NUMBER,
    FOREIGN KEY ("Room") REFERENCES Rooms("RoomId")
);

Question: Find the id and name of the most expensive base price room.
SQL: SELECT RoomId ,  roomName FROM Rooms ORDER BY basePrice DESC LIMIT 1;

Question: Which room has the highest base price?
SQL: SELECT RoomId ,  roomName FROM Rooms ORDER BY basePrice DESC LIMIT 1;

Question: List the type of bed and name of all traditional rooms.
SQL: SELECT roomName ,  bedType FROM Rooms WHERE decor = "traditional";

Question: What are the bed type and name of all the rooms with traditional decor?
SQL: SELECT roomName ,  bedType FROM Rooms WHERE decor = "traditional";

Question: Find the number of rooms with king bed for each decor type.
SQL: SELECT decor ,  count(*) FROM Rooms WHERE bedType = "King" GROUP BY decor;

Question: How many rooms have king beds? Report the number for each decor type.
SQL: SELECT decor ,  count(*) FROM Rooms WHERE bedType = "King" GROUP BY decor;

Question: Find the average and minimum price of the rooms in different decor.
SQL: SELECT decor , avg(basePrice) ,  min(basePrice) FROM Rooms GROUP BY decor;

Question: What is the average minimum and price of the rooms for each different decor.
SQL: SELECT decor , avg(basePrice) ,  min(basePrice) FROM Rooms GROUP BY decor;

Question: List the name of all rooms sorted by their prices.
SQL: SELECT roomName FROM Rooms ORDER BY basePrice;

Question: Sort all the rooms according to the price. Just report the room names.
SQL: SELECT roomName FROM Rooms ORDER BY basePrice;

Question: Find the number of rooms with price higher than 120 for different decor.
SQL: SELECT decor , count(*) FROM Rooms WHERE basePrice  >  120 GROUP BY decor;

Question: How many rooms cost more than 120, for each different decor?
SQL: SELECT decor , count(*) FROM Rooms WHERE basePrice  >  120 GROUP BY decor;

Question: For each bed type, find the average room price.
SQL: SELECT bedType ,  avg(basePrice) FROM Rooms GROUP BY bedType;

Question: What is the average base price of rooms, for each bed type?
SQL: SELECT bedType ,  avg(basePrice) FROM Rooms GROUP BY bedType;

Question: List the name of rooms with king or queen bed.
SQL: SELECT roomName FROM Rooms WHERE bedType  =  "King" OR bedType  =  "Queen";

Question: What are the names of rooms that have either king or queen bed?
SQL: SELECT roomName FROM Rooms WHERE bedType  =  "King" OR bedType  =  "Queen";

Question: How many different types of beds are there?
SQL: SELECT count(DISTINCT bedType) FROM Rooms;

Question: Find the number of distinct bed types available in this inn.
SQL: SELECT count(DISTINCT bedType) FROM Rooms;

Question: Find the name and id of the top 3 expensive rooms.
SQL: SELECT RoomId ,  roomName FROM Rooms ORDER BY basePrice DESC LIMIT 3;

Question: What are the name and id of the three highest priced rooms?
SQL: SELECT RoomId ,  roomName FROM Rooms ORDER BY basePrice DESC LIMIT 3;

Question: Find the name of rooms whose price is higher than the average price.
SQL: SELECT roomName FROM Rooms WHERE basePrice  >  ( SELECT avg(basePrice) FROM Rooms );

Question: What are the name of rooms that cost more than the average.
SQL: SELECT roomName FROM Rooms WHERE basePrice  >  ( SELECT avg(basePrice) FROM Rooms );

Question: Find the number of rooms that do not have any reservation.
SQL: SELECT count(*) FROM rooms WHERE roomid NOT IN (SELECT DISTINCT room FROM reservations)

Question: How many rooms have not had any reservation yet?
SQL: SELECT count(*) FROM rooms WHERE roomid NOT IN (SELECT DISTINCT room FROM reservations)

Question: Return the name and number of reservations made for each of the rooms.
SQL: SELECT T2.roomName ,  count(*) ,  T1.Room FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room  =  T2.RoomId GROUP BY T1.Room

Question: For each room, find its name and the number of times reservations were made for it.
SQL: SELECT T2.roomName ,  count(*) ,  T1.Room FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room  =  T2.RoomId GROUP BY T1.Room

Question: Find the names of rooms that have been reserved for more than 60 times.
SQL: SELECT T2.roomName FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room  =  T2.RoomId GROUP BY T1.Room HAVING count(*)  >  60

Question: What are the names of rooms whose reservation frequency exceeds 60 times?
SQL: SELECT T2.roomName FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room  =  T2.RoomId GROUP BY T1.Room HAVING count(*)  >  60

Question: Find the name of rooms whose base price is between 120 and 150.
SQL: SELECT roomname FROM rooms WHERE baseprice BETWEEN 120 AND 150

Question: Which rooms cost between 120 and 150? Give me the room names.
SQL: SELECT roomname FROM rooms WHERE baseprice BETWEEN 120 AND 150

Question: Find the name of rooms booked by some customers whose first name contains ROY.
SQL: SELECT T2.roomName FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room  =  T2.RoomId WHERE firstname LIKE '%ROY%'

Question: What are the name of rooms booked by customers whose first name has "ROY" in part?
SQL: SELECT T2.roomName FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room  =  T2.RoomId WHERE firstname LIKE '%ROY%'