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 names of all modern rooms with a base price below $160 and two beds.
SQL: SELECT roomName FROM Rooms WHERE basePrice  <  160 AND beds =  2 AND decor  =  'modern';

Question: What are the names of modern rooms that have a base price lower than $160 and two beds.
SQL: SELECT roomName FROM Rooms WHERE basePrice  <  160 AND beds =  2 AND decor  =  'modern';

Question: Find all the rooms that have a price higher than 160 and can accommodate more than 2 people. Report room names and ids.
SQL: SELECT roomName ,  RoomId FROM Rooms WHERE basePrice  >  160 AND maxOccupancy  >  2;

Question: What are the room names and ids of all the rooms that cost more than 160 and can accommodate more than two people.
SQL: SELECT roomName ,  RoomId FROM Rooms WHERE basePrice  >  160 AND maxOccupancy  >  2;

Question: Find the most popular room in the hotel. The most popular room is the room that had seen the largest number of reservations.
SQL: SELECT T2.roomName FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room  =  T2.RoomId GROUP BY T1.Room ORDER BY count(*) DESC LIMIT 1;

Question: Which room has the largest number of reservations?
SQL: SELECT T2.roomName FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room  =  T2.RoomId GROUP BY T1.Room ORDER BY count(*) DESC LIMIT 1;

Question: How many kids stay in the rooms reserved by ROY SWEAZY?
SQL: SELECT kids FROM Reservations WHERE FirstName = "ROY" AND LastName  =  "SWEAZY";

Question: Find the number of kids staying in the rooms reserved by a person called ROY SWEAZ.
SQL: SELECT kids FROM Reservations WHERE FirstName = "ROY" AND LastName  =  "SWEAZY";

Question: How many times does ROY SWEAZY has reserved a room.
SQL: SELECT count(*) FROM Reservations WHERE FirstName = "ROY" AND LastName  =  "SWEAZY";

Question: Find the number of times ROY SWEAZY has reserved a room.
SQL: SELECT count(*) FROM Reservations WHERE FirstName = "ROY" AND LastName  =  "SWEAZY";

Question: Which room has the highest rate? List the room's full name, rate, check in and check out date.
SQL: 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;

Question: Return the name, rate, check in and check out date for the room with the highest rate.
SQL: 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;

Question: How many adults stay in the room CONRAD SELBIG checked in on Oct 23, 2010?
SQL: SELECT Adults FROM Reservations WHERE CheckIn  =  "2010-10-23" AND FirstName  =  "CONRAD" AND LastName  =  "SELBIG";

Question: Find the number of adults for the room reserved and checked in by CONRAD SELBIG on Oct 23, 2010.
SQL: SELECT Adults FROM Reservations WHERE CheckIn  =  "2010-10-23" AND FirstName  =  "CONRAD" AND LastName  =  "SELBIG";

Question: How many kids stay in the room DAMIEN TRACHSEL checked in on Sep 21, 2010?
SQL: SELECT Kids FROM Reservations WHERE CheckIn  =  "2010-09-21" AND FirstName  =  "DAMIEN" AND LastName  =  "TRACHSEL";

Question: Return the number of kids for the room reserved and checked in by DAMIEN TRACHSEL on  Sep 21, 2010.
SQL: SELECT Kids FROM Reservations WHERE CheckIn  =  "2010-09-21" AND FirstName  =  "DAMIEN" AND LastName  =  "TRACHSEL";

Question: How many king beds are there?
SQL: SELECT sum(beds) FROM Rooms WHERE bedtype  =  'King';

Question: Find the total number of king beds available.
SQL: SELECT sum(beds) FROM Rooms WHERE bedtype  =  'King';

Question: List the names and decor of rooms that have a king bed. Sort the list by their price.
SQL: SELECT roomName ,  decor FROM Rooms WHERE bedtype  =  'King' ORDER BY basePrice;

Question: What are the names and decor of rooms with a king bed? Sort them by their price
SQL: SELECT roomName ,  decor FROM Rooms WHERE bedtype  =  'King' ORDER BY basePrice;

Question: Which room has cheapest base price? List the room's name and the base price.
SQL: SELECT roomName ,  basePrice FROM Rooms ORDER BY basePrice ASC LIMIT 1;

Question: What are the room name and base price of the room with the lowest base price?
SQL: SELECT roomName ,  basePrice FROM Rooms ORDER BY basePrice ASC LIMIT 1;

Question: What is the decor of room Recluse and defiance?
SQL: SELECT decor FROM Rooms WHERE roomName  =  "Recluse and defiance";

Question: Return the decor of the room named "Recluse and defiance".
SQL: SELECT decor FROM Rooms WHERE roomName  =  "Recluse and defiance";

Question: What is the average base price of different bed type? List bed type and average base price.
SQL: SELECT bedType ,  avg(basePrice) FROM Rooms GROUP BY bedType;

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

Question: What is the total number of people who could stay in the modern rooms in this inn?
SQL: SELECT sum(maxOccupancy) FROM Rooms WHERE decor  =  'modern';

Question: How many people in total can stay in the modern rooms of this inn?
SQL: SELECT sum(maxOccupancy) FROM Rooms WHERE decor  =  'modern';

Question: What kind of decor has the least number of reservations?
SQL: 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;

Question: What is the least popular kind of decor?
SQL: 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;

Question: List how many times the number of people in the room reached the maximum occupancy of the room. The number of people include adults and kids.
SQL: SELECT count(*) FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room  =  T2.RoomId WHERE T2.maxOccupancy  =  T1.Adults + T1.Kids;

Question: How many times the number of adults and kids staying in a room reached the maximum capacity of the room?
SQL: SELECT count(*) FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room  =  T2.RoomId WHERE T2.maxOccupancy  =  T1.Adults + T1.Kids;

Question: Find the first and last names of people who payed more than the rooms' base prices.
SQL: SELECT T1.firstname ,  T1.lastname FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room  =  T2.RoomId WHERE T1.Rate - T2.basePrice  >  0

Question: What are the first and last names of people who payed more than the rooms' base prices?
SQL: SELECT T1.firstname ,  T1.lastname FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room  =  T2.RoomId WHERE T1.Rate - T2.basePrice  >  0

Question: How many rooms are there?
SQL: SELECT count(*) FROM Rooms;

Question: What is the total number of rooms available in this inn?
SQL: SELECT count(*) FROM Rooms;

Question: Find the number of rooms with a king bed.
SQL: SELECT count(*) FROM Rooms WHERE bedType  =  "King";

Question: How many rooms have a king bed?
SQL: SELECT count(*) FROM Rooms WHERE bedType  =  "King";

Question: Find the number of rooms for each bed type.
SQL: SELECT bedType ,  count(*) FROM Rooms GROUP BY bedType;

Question: What are the number of rooms for each bed type?
SQL: SELECT bedType ,  count(*) FROM Rooms GROUP BY bedType;

Question: Find the name of the room with the maximum occupancy.
SQL: SELECT roomName FROM Rooms ORDER BY maxOccupancy DESC LIMIT 1;

Question: What is the name of the room that can accommodate the most people?
SQL: SELECT roomName FROM Rooms ORDER BY maxOccupancy DESC LIMIT 1;