CREATE TABLE Apartment_Buildings (
    building_id NUMBER PRIMARY KEY,
    building_short_name TEXT,
    building_full_name TEXT,
    building_description TEXT,
    building_address TEXT,
    building_manager TEXT,
    building_phone TEXT
);
CREATE TABLE Apartments (
    apt_id NUMBER PRIMARY KEY,
    building_id NUMBER,
    apt_type_code TEXT,
    apt_number TEXT,
    bathroom_count NUMBER,
    bedroom_count NUMBER,
    room_count TEXT,
    FOREIGN KEY ("building_id") REFERENCES Apartment_Buildings("building_id")
);
CREATE TABLE Apartment_Facilities (
    apt_id NUMBER PRIMARY KEY,
    facility_code TEXT,
    FOREIGN KEY ("apt_id") REFERENCES Apartments("apt_id")
);
CREATE TABLE Guests (
    guest_id NUMBER PRIMARY KEY,
    gender_code TEXT,
    guest_first_name TEXT,
    guest_last_name TEXT,
    date_of_birth TIME
);
CREATE TABLE Apartment_Bookings (
    apt_booking_id NUMBER PRIMARY KEY,
    apt_id NUMBER,
    guest_id NUMBER,
    booking_status_code TEXT,
    booking_start_date TIME,
    booking_end_date TIME,
    FOREIGN KEY ("guest_id") REFERENCES Guests("guest_id"),
    FOREIGN KEY ("apt_id") REFERENCES Apartments("apt_id")
);
CREATE TABLE View_Unit_Status (
    apt_id NUMBER,
    apt_booking_id NUMBER,
    status_date TIME PRIMARY KEY,
    available_yn OTHERS,
    FOREIGN KEY ("apt_booking_id") REFERENCES Apartment_Bookings("apt_booking_id"),
    FOREIGN KEY ("apt_id") REFERENCES Apartments("apt_id")
);

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