
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), 
	FOREIGN KEY(apt_id) REFERENCES "Apartments" (apt_id), 
	FOREIGN KEY(guest_id) REFERENCES "Guests" (guest_id), 
	UNIQUE (apt_booking_id)
)


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 "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 "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), 
	FOREIGN KEY(building_id) REFERENCES "Apartment_Buildings" (building_id), 
	UNIQUE (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 "View_Unit_Status" (
	apt_id INTEGER, 
	apt_booking_id INTEGER, 
	status_date DATETIME NOT NULL, 
	available_yn NUMERIC, 
	PRIMARY KEY (status_date), 
	FOREIGN KEY(apt_id) REFERENCES "Apartments" (apt_id), 
	FOREIGN KEY(apt_booking_id) REFERENCES "Apartment_Bookings" (apt_booking_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), 
	FOREIGN KEY(apt_id) REFERENCES "Apartments" (apt_id), 
	FOREIGN KEY(guest_id) REFERENCES "Guests" (guest_id), 
	UNIQUE (apt_booking_id)
)

/*
2 rows from Apartment_Bookings table:
apt_booking_id	apt_id	guest_id	booking_status_code	booking_start_date	booking_end_date
258	10	2	Provisional	2016-09-26 17:13:49	2017-10-07 11:38:48
279	15	15	Provisional	2016-04-01 06:28:08	2017-10-25 11:08:42
*/


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

/*
2 rows from Apartment_Buildings table:
building_id	building_short_name	building_full_name	building_description	building_address	building_manager	building_phone
133	Normandie Court	Normandie Court	Studio	7950 Casper Vista Apt. 176
Marquiseberg, CA 70496	Emma	(948)040-1064x387
153	Mercedes House	Mercedes House	Studio	354 Otto Villages
Charliefort, VT 71664	Brenden	915-617-2408x832
*/


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

/*
2 rows from Apartment_Facilities table:
apt_id	facility_code
1	Boardband
2	Boardband
*/


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), 
	FOREIGN KEY(building_id) REFERENCES "Apartment_Buildings" (building_id), 
	UNIQUE (apt_id)
)

/*
2 rows from Apartments table:
apt_id	building_id	apt_type_code	apt_number	bathroom_count	bedroom_count	room_count
1	808	Flat	Suite 645	1	3	7
2	624	Flat	Apt. 585	2	4	5
*/


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

/*
2 rows from Guests table:
guest_id	gender_code	guest_first_name	guest_last_name	date_of_birth
1	Male	Kip	DuBuque	1995-11-04 07:09:57
2	Unknown	Rebeca	Runolfsdottir	1974-05-12 21:53:58
*/


CREATE TABLE "View_Unit_Status" (
	apt_id INTEGER, 
	apt_booking_id INTEGER, 
	status_date DATETIME NOT NULL, 
	available_yn NUMERIC, 
	PRIMARY KEY (status_date), 
	FOREIGN KEY(apt_id) REFERENCES "Apartments" (apt_id), 
	FOREIGN KEY(apt_booking_id) REFERENCES "Apartment_Bookings" (apt_booking_id)
)

/*
2 rows from View_Unit_Status table:
apt_id	apt_booking_id	status_date	available_yn
11	920	1970-09-28 10:24:29	1.0000000000
15	575	1972-03-23 22:55:53	1.0000000000
*/
Show the total number of rooms of all apartments with facility code "Gym".
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"
[{'sum(T2.room_count)': 15}]