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: 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