Database: apartment_rentals

CREATE TABLE Apartment_Buildings (
building_id INTEGER NOT NULL,
building_short_name CHAR(15),
building_full_name VARCHAR(80),
building_description VARCHAR(255),
building_address VARCHAR(255),
building_manager VARCHAR(50),
building_phone VARCHAR(80),
PRIMARY KEY (building_id),
UNIQUE (building_id)
);
CREATE TABLE Apartments (
apt_id INTEGER NOT NULL ,
building_id INTEGER NOT NULL,
apt_type_code CHAR(15),
apt_number CHAR(10),
bathroom_count INTEGER,
bedroom_count INTEGER,
room_count CHAR(5),
PRIMARY KEY (apt_id),
UNIQUE (apt_id),
FOREIGN KEY (building_id) REFERENCES Apartment_Buildings (building_id)
);
CREATE TABLE Apartment_Facilities (
apt_id INTEGER NOT NULL,
facility_code CHAR(15) NOT NULL,
PRIMARY KEY (apt_id, facility_code),
FOREIGN KEY (apt_id) REFERENCES Apartments (apt_id)
);
CREATE TABLE Guests (
guest_id INTEGER NOT NULL ,
gender_code CHAR(1),
guest_first_name VARCHAR(80),
guest_last_name VARCHAR(80),
date_of_birth DATETIME,
PRIMARY KEY (guest_id),
UNIQUE (guest_id)
);
CREATE TABLE Apartment_Bookings (
apt_booking_id INTEGER NOT NULL,
apt_id INTEGER,
guest_id INTEGER NOT NULL,
booking_status_code CHAR(15) NOT NULL,
booking_start_date DATETIME,
booking_end_date DATETIME,
PRIMARY KEY (apt_booking_id),
UNIQUE (apt_booking_id),
FOREIGN KEY (apt_id) REFERENCES Apartments (apt_id),
FOREIGN KEY (guest_id) REFERENCES Guests (guest_id)
);
CREATE TABLE View_Unit_Status (
apt_id INTEGER,
apt_booking_id INTEGER,
status_date DATETIME NOT NULL,
available_yn BIT,
PRIMARY KEY (status_date),
FOREIGN KEY (apt_id) REFERENCES Apartments (apt_id),
FOREIGN KEY (apt_booking_id) REFERENCES Apartment_Bookings (apt_booking_id)
);

Question: How many apartment bookings are there in total?
SQL: SELECT count(*) FROM Apartment_Bookings

Question: Count the total number of apartment bookings.
SQL: SELECT count(*) FROM Apartment_Bookings

Question: Show the start dates and end dates of all the apartment bookings.
SQL: SELECT booking_start_date ,  booking_end_date FROM Apartment_Bookings

Question: What are the start date and end date of each apartment booking?
SQL: SELECT booking_start_date ,  booking_end_date FROM Apartment_Bookings

Question: Show all distinct building descriptions.
SQL: SELECT DISTINCT building_description FROM Apartment_Buildings

Question: Give me a list of all the distinct building descriptions.
SQL: SELECT DISTINCT building_description FROM Apartment_Buildings

Question: Show the short names of the buildings managed by "Emma".
SQL: SELECT building_short_name FROM Apartment_Buildings WHERE building_manager	 =  "Emma"

Question: Which buildings does "Emma" manage? Give me the short names of the buildings.
SQL: SELECT building_short_name FROM Apartment_Buildings WHERE building_manager	 =  "Emma"

Question: Show the addresses and phones of all the buildings managed by "Brenden".
SQL: SELECT building_address ,  building_phone FROM Apartment_Buildings WHERE building_manager	 =  "Brenden"

Question: What are the address and phone number of the buildings managed by "Brenden"?
SQL: SELECT building_address ,  building_phone FROM Apartment_Buildings WHERE building_manager	 =  "Brenden"

Question: What are the building full names that contain the word "court"?
SQL: SELECT building_full_name FROM Apartment_Buildings WHERE building_full_name LIKE "%court%"

Question: Find all the building full names containing the word "court".
SQL: SELECT building_full_name FROM Apartment_Buildings WHERE building_full_name LIKE "%court%"

Question: What is the minimum and maximum number of bathrooms of all the apartments?
SQL: SELECT min(bathroom_count) ,  max(bathroom_count) FROM Apartments

Question: Give me the minimum and maximum bathroom count among all the apartments.
SQL: SELECT min(bathroom_count) ,  max(bathroom_count) FROM Apartments

Question: What is the average number of bedrooms of all apartments?
SQL: SELECT avg(bedroom_count) FROM Apartments

Question: Find the average number of bedrooms of all the apartments.
SQL: SELECT avg(bedroom_count) FROM Apartments

Question: Return the apartment number and the number of rooms for each apartment.
SQL: SELECT apt_number ,  room_count FROM Apartments

Question: What are the apartment number and the room count of each apartment?
SQL: SELECT apt_number ,  room_count FROM Apartments

Question: What is the average number of rooms of apartments with type code "Studio"?
SQL: SELECT avg(room_count) FROM Apartments WHERE apt_type_code  =  "Studio"

Question: Find the average room count of the apartments that have the "Studio" type code.
SQL: SELECT avg(room_count) FROM Apartments WHERE apt_type_code  =  "Studio"

Question: Return the apartment numbers of the apartments with type code "Flat".
SQL: SELECT apt_number FROM Apartments WHERE apt_type_code  =  "Flat"

Question: Which apartments have type code "Flat"? Give me their apartment numbers.
SQL: SELECT apt_number FROM Apartments WHERE apt_type_code  =  "Flat"

Question: Return the first names and last names of all guests
SQL: SELECT guest_first_name ,  guest_last_name FROM Guests

Question: What are the first names and last names of all the guests?
SQL: SELECT guest_first_name ,  guest_last_name FROM Guests

Question: Return the date of birth for all the guests with gender code "Male".
SQL: SELECT date_of_birth FROM Guests WHERE gender_code  =  "Male"

Question: What are dates of birth of all the guests whose gender is "Male"?
SQL: SELECT date_of_birth FROM Guests WHERE gender_code  =  "Male"

Question: Show the apartment numbers, start dates, and end dates of all the apartment bookings.
SQL: SELECT T2.apt_number ,  T1.booking_start_date ,  T1.booking_start_date FROM Apartment_Bookings AS T1 JOIN Apartments AS T2 ON T1.apt_id  =  T2.apt_id

Question: What are the apartment number, start date, and end date of each apartment booking?
SQL: SELECT T2.apt_number ,  T1.booking_start_date ,  T1.booking_start_date FROM Apartment_Bookings AS T1 JOIN Apartments AS T2 ON T1.apt_id  =  T2.apt_id

Question: What are the booking start and end dates of the apartments with type code "Duplex"?
SQL: SELECT T1.booking_start_date ,  T1.booking_start_date FROM Apartment_Bookings AS T1 JOIN Apartments AS T2 ON T1.apt_id  =  T2.apt_id WHERE T2.apt_type_code  =  "Duplex"

Question: Return the booking start date and end date for the apartments that have type code "Duplex".
SQL: SELECT T1.booking_start_date ,  T1.booking_start_date FROM Apartment_Bookings AS T1 JOIN Apartments AS T2 ON T1.apt_id  =  T2.apt_id WHERE T2.apt_type_code  =  "Duplex"

Question: What are the booking start and end dates of the apartments with more than 2 bedrooms?
SQL: SELECT T1.booking_start_date ,  T1.booking_start_date FROM Apartment_Bookings AS T1 JOIN Apartments AS T2 ON T1.apt_id  =  T2.apt_id WHERE T2.bedroom_count  >  2

Question: Find the booking start date and end date for the apartments that have more than two bedrooms.
SQL: SELECT T1.booking_start_date ,  T1.booking_start_date FROM Apartment_Bookings AS T1 JOIN Apartments AS T2 ON T1.apt_id  =  T2.apt_id WHERE T2.bedroom_count  >  2

Question: What is the booking status code of the apartment with apartment number "Suite 634"?
SQL: SELECT T1.booking_status_code FROM Apartment_Bookings AS T1 JOIN Apartments AS T2 ON T1.apt_id  =  T2.apt_id WHERE T2.apt_number  =  "Suite 634"

Question: Tell me the booking status code for the apartment with number "Suite 634".
SQL: SELECT T1.booking_status_code FROM Apartment_Bookings AS T1 JOIN Apartments AS T2 ON T1.apt_id  =  T2.apt_id WHERE T2.apt_number  =  "Suite 634"

Question: Show the distinct apartment numbers of the apartments that have bookings with status code "Confirmed".
SQL: SELECT DISTINCT T2.apt_number FROM Apartment_Bookings AS T1 JOIN Apartments AS T2 ON T1.apt_id  =  T2.apt_id WHERE T1.booking_status_code  =  "Confirmed"

Question: Which apartments have bookings with status code "Confirmed"? Return their apartment numbers.
SQL: SELECT DISTINCT T2.apt_number FROM Apartment_Bookings AS T1 JOIN Apartments AS T2 ON T1.apt_id  =  T2.apt_id WHERE T1.booking_status_code  =  "Confirmed"

Question: Show the average room count of the apartments that have booking status code "Provisional".
SQL: SELECT avg(room_count) FROM Apartment_Bookings AS T1 JOIN Apartments AS T2 ON T1.apt_id  =  T2.apt_id WHERE T1.booking_status_code  =  "Provisional"

Question: What is the average room count of the apartments whose booking status code is "Provisional"?
SQL: SELECT avg(room_count) FROM Apartment_Bookings AS T1 JOIN Apartments AS T2 ON T1.apt_id  =  T2.apt_id WHERE T1.booking_status_code  =  "Provisional"

Question: Show the guest first names, start dates, and end dates of all the apartment bookings.
SQL: SELECT T2.guest_first_name ,  T1.booking_start_date ,  T1.booking_start_date FROM Apartment_Bookings AS T1 JOIN Guests AS T2 ON T1.guest_id  =  T2.guest_id

Question: What are the guest first name, start date, and end date of each apartment booking?
SQL: SELECT T2.guest_first_name ,  T1.booking_start_date ,  T1.booking_start_date FROM Apartment_Bookings AS T1 JOIN Guests AS T2 ON T1.guest_id  =  T2.guest_id

Question: Show the start dates and end dates of all the apartment bookings made by guests with gender code "Female".
SQL: SELECT T1.booking_start_date ,  T1.booking_start_date FROM Apartment_Bookings AS T1 JOIN Guests AS T2 ON T1.guest_id  =  T2.guest_id WHERE T2.gender_code  =  "Female"

Question: What are the start date and end date of the apartment bookings made by female guests (gender code "Female")?
SQL: SELECT T1.booking_start_date ,  T1.booking_start_date FROM Apartment_Bookings AS T1 JOIN Guests AS T2 ON T1.guest_id  =  T2.guest_id WHERE T2.gender_code  =  "Female"

Question: Show the first names and last names of all the guests that have apartment bookings with status code "Confirmed".
SQL: SELECT T2.guest_first_name ,  T2.guest_last_name FROM Apartment_Bookings AS T1 JOIN Guests AS T2 ON T1.guest_id  =  T2.guest_id WHERE T1.booking_status_code  =  "Confirmed"

Question: Which guests have apartment bookings with status code "Confirmed"? Return their first names and last names.
SQL: SELECT T2.guest_first_name ,  T2.guest_last_name FROM Apartment_Bookings AS T1 JOIN Guests AS T2 ON T1.guest_id  =  T2.guest_id WHERE T1.booking_status_code  =  "Confirmed"

Question: Show the facility codes of apartments with more than 4 bedrooms.
SQL: SELECT T1.facility_code FROM Apartment_Facilities AS T1 JOIN Apartments AS T2 ON T1.apt_id  =  T2.apt_id WHERE T2.bedroom_count  >  4

Question: What are the facility codes of the apartments with more than four bedrooms?
SQL: SELECT T1.facility_code FROM Apartment_Facilities AS T1 JOIN Apartments AS T2 ON T1.apt_id  =  T2.apt_id WHERE T2.bedroom_count  >  4

Question: Show the total number of rooms of all apartments with facility code "Gym".
SQL: SELECT sum(T2.room_count) FROM Apartment_Facilities AS T1 JOIN Apartments AS T2 ON T1.apt_id  =  T2.apt_id WHERE T1.facility_code  =  "Gym"

Question: Find the total number of rooms in the apartments that have facility code "Gym".
SQL: SELECT sum(T2.room_count) FROM Apartment_Facilities AS T1 JOIN Apartments AS T2 ON T1.apt_id  =  T2.apt_id WHERE T1.facility_code  =  "Gym"

Question: Show the total number of rooms of the apartments in the building with short name "Columbus Square".
SQL: SELECT sum(T2.room_count) FROM Apartment_Buildings AS T1 JOIN Apartments AS T2 ON T1.building_id  =  T2.building_id WHERE T1.building_short_name  =  "Columbus Square"

Question: How many rooms in total are there in the apartments in the building with short name "Columbus Square"?
SQL: SELECT sum(T2.room_count) FROM Apartment_Buildings AS T1 JOIN Apartments AS T2 ON T1.building_id  =  T2.building_id WHERE T1.building_short_name  =  "Columbus Square"

Question: Show the addresses of the buildings that have apartments with more than 2 bathrooms.
SQL: SELECT T1.building_address FROM Apartment_Buildings AS T1 JOIN Apartments AS T2 ON T1.building_id  =  T2.building_id WHERE T2.bathroom_count  >  2

Question: Which buildings have apartments that have more than two bathrooms? Give me the addresses of the buildings.
SQL: SELECT T1.building_address FROM Apartment_Buildings AS T1 JOIN Apartments AS T2 ON T1.building_id  =  T2.building_id WHERE T2.bathroom_count  >  2

Question: Show the apartment type codes and apartment numbers in the buildings managed by "Kyle".
SQL: SELECT T2.apt_type_code ,  T2.apt_number FROM Apartment_Buildings AS T1 JOIN Apartments AS T2 ON T1.building_id  =  T2.building_id WHERE T1.building_manager  =  "Kyle"

Question: What apartment type codes and apartment numbers do the buildings managed by "Kyle" have?
SQL: SELECT T2.apt_type_code ,  T2.apt_number FROM Apartment_Buildings AS T1 JOIN Apartments AS T2 ON T1.building_id  =  T2.building_id WHERE T1.building_manager  =  "Kyle"

Question: Show the booking status code and the corresponding number of bookings.
SQL: SELECT 	booking_status_code ,  COUNT(*) FROM Apartment_Bookings GROUP BY booking_status_code

Question: How many bookings does each booking status have? List the booking status code and the number of corresponding bookings.
SQL: SELECT 	booking_status_code ,  COUNT(*) FROM Apartment_Bookings GROUP BY booking_status_code

Question: Return all the apartment numbers sorted by the room count in ascending order.
SQL: SELECT apt_number FROM Apartments ORDER BY room_count ASC

Question: Sort the apartment numbers in ascending order of room count.
SQL: SELECT apt_number FROM Apartments ORDER BY room_count ASC

Question: Return the apartment number with the largest number of bedrooms.
SQL: SELECT apt_number FROM Apartments ORDER BY bedroom_count DESC LIMIT 1

Question: What is the apartment number of the apartment with the most beds?
SQL: SELECT apt_number FROM Apartments ORDER BY bedroom_count DESC LIMIT 1

Question: Show the apartment type codes and the corresponding number of apartments sorted by the number of apartments in ascending order.
SQL: SELECT apt_type_code ,  COUNT(*) FROM Apartments GROUP BY apt_type_code ORDER BY COUNT(*) ASC

Question: Return each apartment type code with the number of apartments having that apartment type, in ascending order of the number of apartments.
SQL: SELECT apt_type_code ,  COUNT(*) FROM Apartments GROUP BY apt_type_code ORDER BY COUNT(*) ASC

Question: Show the top 3 apartment type codes sorted by the average number of rooms in descending order.
SQL: SELECT apt_type_code FROM Apartments GROUP BY apt_type_code ORDER BY avg(room_count) DESC LIMIT 3

Question: What are the top three apartment types in terms of the average room count? Give me the
SQL: SELECT apt_type_code FROM Apartments GROUP BY apt_type_code ORDER BY avg(room_count) DESC LIMIT 3

Question: Show the apartment type code that has the largest number of total rooms, together with the number of bathrooms and number of bedrooms.
SQL: SELECT apt_type_code ,  bathroom_count ,  bedroom_count FROM Apartments GROUP BY apt_type_code ORDER BY sum(room_count) DESC LIMIT 1

Question: Which apartment type has the largest number of total rooms? Return the apartment type code, its number of bathrooms and number of bedrooms.
SQL: SELECT apt_type_code ,  bathroom_count ,  bedroom_count FROM Apartments GROUP BY apt_type_code ORDER BY sum(room_count) DESC LIMIT 1

Question: Show the most common apartment type code.
SQL: SELECT apt_type_code FROM Apartments GROUP BY apt_type_code ORDER BY count(*) DESC LIMIT 1

Question: Which apartment type code appears the most often?
SQL: SELECT apt_type_code FROM Apartments GROUP BY apt_type_code ORDER BY count(*) DESC LIMIT 1

Question: Show the most common apartment type code among apartments with more than 1 bathroom.
SQL: SELECT apt_type_code FROM Apartments WHERE bathroom_count  >  1 GROUP BY apt_type_code ORDER BY count(*) DESC LIMIT 1

Question: Which apartment type code is the most common among apartments with more than one bathroom?
SQL: SELECT apt_type_code FROM Apartments WHERE bathroom_count  >  1 GROUP BY apt_type_code ORDER BY count(*) DESC LIMIT 1

Question: Show each apartment type code, and the maximum and minimum number of rooms for each type.
SQL: SELECT apt_type_code ,  max(room_count) ,  min(room_count) FROM Apartments GROUP BY apt_type_code

Question: Return each apartment type code along with the maximum and minimum number of rooms among each type.
SQL: SELECT apt_type_code ,  max(room_count) ,  min(room_count) FROM Apartments GROUP BY apt_type_code

Question: Show each gender code and the corresponding count of guests sorted by the count in descending order.
SQL: SELECT gender_code ,  COUNT(*) FROM Guests GROUP BY gender_code ORDER BY COUNT(*) DESC

Question: Sort the gender codes in descending order of their corresponding number of guests. Return both the gender codes and counts.
SQL: SELECT gender_code ,  COUNT(*) FROM Guests GROUP BY gender_code ORDER BY COUNT(*) DESC

Question: How many apartments do not have any facility?
SQL: SELECT count(*) FROM Apartments WHERE apt_id NOT IN (SELECT apt_id FROM Apartment_Facilities)

Question: Find the number of apartments that have no facility.
SQL: SELECT count(*) FROM Apartments WHERE apt_id NOT IN (SELECT apt_id FROM Apartment_Facilities)

Question: Show the apartment numbers of apartments with bookings that have status code both "Provisional" and "Confirmed"
SQL: SELECT T2.apt_number FROM Apartment_Bookings AS T1 JOIN Apartments AS T2 ON T1.apt_id  =  T2.apt_id WHERE T1.booking_status_code  =  "Confirmed" INTERSECT SELECT T2.apt_number FROM Apartment_Bookings AS T1 JOIN Apartments AS T2 ON T1.apt_id  =  T2.apt_id WHERE T1.booking_status_code  =  "Provisional"

Question: Which apartments have bookings with both status codes "Provisional" and "Confirmed"? Give me the apartment numbers.
SQL: SELECT T2.apt_number FROM Apartment_Bookings AS T1 JOIN Apartments AS T2 ON T1.apt_id  =  T2.apt_id WHERE T1.booking_status_code  =  "Confirmed" INTERSECT SELECT T2.apt_number FROM Apartment_Bookings AS T1 JOIN Apartments AS T2 ON T1.apt_id  =  T2.apt_id WHERE T1.booking_status_code  =  "Provisional"

Question: Show the apartment numbers of apartments with unit status availability of both 0 and 1.
SQL: SELECT T1.apt_number FROM Apartments AS T1 JOIN View_Unit_Status AS T2 ON T1.apt_id  =  T2.apt_id WHERE T2.available_yn  =  0 INTERSECT SELECT T1.apt_number FROM Apartments AS T1 JOIN View_Unit_Status AS T2 ON T1.apt_id  =  T2.apt_id WHERE T2.available_yn  =  1

Question: Which apartments have unit status availability of both 0 and 1? Return their apartment numbers.
SQL: SELECT T1.apt_number FROM Apartments AS T1 JOIN View_Unit_Status AS T2 ON T1.apt_id  =  T2.apt_id WHERE T2.available_yn  =  0 INTERSECT SELECT T1.apt_number FROM Apartments AS T1 JOIN View_Unit_Status AS T2 ON T1.apt_id  =  T2.apt_id WHERE T2.available_yn  =  1