
CREATE TABLE "Other_Available_Features" (
	feature_id INTEGER, 
	feature_type_code VARCHAR(20) NOT NULL, 
	feature_name VARCHAR(80), 
	feature_description VARCHAR(80), 
	PRIMARY KEY (feature_id), 
	FOREIGN KEY(feature_type_code) REFERENCES "Ref_Feature_Types" (feature_type_code)
)


CREATE TABLE "Other_Property_Features" (
	property_id INTEGER NOT NULL, 
	feature_id INTEGER NOT NULL, 
	property_feature_description VARCHAR(80), 
	FOREIGN KEY(property_id) REFERENCES "Properties" (property_id), 
	FOREIGN KEY(feature_id) REFERENCES "Other_Available_Features" (feature_id)
)


CREATE TABLE "Properties" (
	property_id INTEGER, 
	property_type_code VARCHAR(20) NOT NULL, 
	date_on_market DATETIME, 
	date_sold DATETIME, 
	property_name VARCHAR(80), 
	property_address VARCHAR(255), 
	room_count INTEGER, 
	vendor_requested_price DECIMAL(19, 4), 
	buyer_offered_price DECIMAL(19, 4), 
	agreed_selling_price DECIMAL(19, 4), 
	apt_feature_1 VARCHAR(255), 
	apt_feature_2 VARCHAR(255), 
	apt_feature_3 VARCHAR(255), 
	fld_feature_1 VARCHAR(255), 
	fld_feature_2 VARCHAR(255), 
	fld_feature_3 VARCHAR(255), 
	hse_feature_1 VARCHAR(255), 
	hse_feature_2 VARCHAR(255), 
	hse_feature_3 VARCHAR(255), 
	oth_feature_1 VARCHAR(255), 
	oth_feature_2 VARCHAR(255), 
	oth_feature_3 VARCHAR(255), 
	shp_feature_1 VARCHAR(255), 
	shp_feature_2 VARCHAR(255), 
	shp_feature_3 VARCHAR(255), 
	other_property_details VARCHAR(255), 
	PRIMARY KEY (property_id), 
	FOREIGN KEY(property_type_code) REFERENCES "Ref_Property_Types" (property_type_code)
)


CREATE TABLE "Ref_Feature_Types" (
	feature_type_code VARCHAR(20), 
	feature_type_name VARCHAR(80), 
	PRIMARY KEY (feature_type_code)
)


CREATE TABLE "Ref_Property_Types" (
	property_type_code VARCHAR(20), 
	property_type_description VARCHAR(80), 
	PRIMARY KEY (property_type_code)
)

CREATE TABLE "Other_Available_Features" (
	feature_id INTEGER, 
	feature_type_code VARCHAR(20) NOT NULL, 
	feature_name VARCHAR(80), 
	feature_description VARCHAR(80), 
	PRIMARY KEY (feature_id), 
	FOREIGN KEY(feature_type_code) REFERENCES "Ref_Feature_Types" (feature_type_code)
)

/*
2 rows from Other_Available_Features table:
feature_id	feature_type_code	feature_name	feature_description
2	Amenity	AirCon	Air Conditioning.
3	Amenity	Pool	Swimming Pool.
*/


CREATE TABLE "Other_Property_Features" (
	property_id INTEGER NOT NULL, 
	feature_id INTEGER NOT NULL, 
	property_feature_description VARCHAR(80), 
	FOREIGN KEY(property_id) REFERENCES "Properties" (property_id), 
	FOREIGN KEY(feature_id) REFERENCES "Other_Available_Features" (feature_id)
)

/*
2 rows from Other_Property_Features table:
property_id	feature_id	property_feature_description
15	3	dolorem
12	4	earum
*/


CREATE TABLE "Properties" (
	property_id INTEGER, 
	property_type_code VARCHAR(20) NOT NULL, 
	date_on_market DATETIME, 
	date_sold DATETIME, 
	property_name VARCHAR(80), 
	property_address VARCHAR(255), 
	room_count INTEGER, 
	vendor_requested_price DECIMAL(19, 4), 
	buyer_offered_price DECIMAL(19, 4), 
	agreed_selling_price DECIMAL(19, 4), 
	apt_feature_1 VARCHAR(255), 
	apt_feature_2 VARCHAR(255), 
	apt_feature_3 VARCHAR(255), 
	fld_feature_1 VARCHAR(255), 
	fld_feature_2 VARCHAR(255), 
	fld_feature_3 VARCHAR(255), 
	hse_feature_1 VARCHAR(255), 
	hse_feature_2 VARCHAR(255), 
	hse_feature_3 VARCHAR(255), 
	oth_feature_1 VARCHAR(255), 
	oth_feature_2 VARCHAR(255), 
	oth_feature_3 VARCHAR(255), 
	shp_feature_1 VARCHAR(255), 
	shp_feature_2 VARCHAR(255), 
	shp_feature_3 VARCHAR(255), 
	other_property_details VARCHAR(255), 
	PRIMARY KEY (property_id), 
	FOREIGN KEY(property_type_code) REFERENCES "Ref_Property_Types" (property_type_code)
)

/*
2 rows from Properties table:
property_id	property_type_code	date_on_market	date_sold	property_name	property_address	room_count	vendor_requested_price	buyer_offered_price	agreed_selling_price	apt_feature_1	apt_feature_2	apt_feature_3	fld_feature_1	fld_feature_2	fld_feature_3	hse_feature_1	hse_feature_2	hse_feature_3	oth_feature_1	oth_feature_2	oth_feature_3	shp_feature_1	shp_feature_2	shp_feature_3	other_property_details
1	House	1991-06-21 23:52:10	1979-05-13 16:58:06	park	4745 Emerson Stravenue Suite 829
South Garret, IN 16772-5682	7	372652.2909	1.6800	4201.8000	aut	suscipit	None	None	None	None	None	None	None	None	None	None	None	None	None	None
2	House	1990-05-25 23:01:51	1990-11-14 19:16:38	the cole	098 Tremaine Highway Suite 569
South Wilford, NJ 46587-3537	1	661536468.4429	8.7122	21769471.8328	est	est	None	None	None	None	None	None	None	None	None	None	None	None	None	None
*/


CREATE TABLE "Ref_Feature_Types" (
	feature_type_code VARCHAR(20), 
	feature_type_name VARCHAR(80), 
	PRIMARY KEY (feature_type_code)
)

/*
2 rows from Ref_Feature_Types table:
feature_type_code	feature_type_name
Amenity	Amenity, eg Pool.
Security	Securiyt, eg Burglar Alarm.
*/


CREATE TABLE "Ref_Property_Types" (
	property_type_code VARCHAR(20), 
	property_type_description VARCHAR(80), 
	PRIMARY KEY (property_type_code)
)

/*
2 rows from Ref_Property_Types table:
property_type_code	property_type_description
House	House, Bungalow, etc.
Apartment	Apartment, Flat, Condo, etc.
*/
What is the feature type name of feature AirCon?
SELECT T2.feature_type_name FROM Other_Available_Features AS T1 JOIN Ref_Feature_Types AS T2 ON T1.feature_type_code  =  T2.feature_type_code WHERE T1.feature_name  =  "AirCon"
[{'feature_type_name': 'Amenity, eg Pool.'}]