
CREATE TABLE aircraft (
	"Aircraft_ID" INTEGER, 
	"Order_Year" INTEGER, 
	"Manufacturer" TEXT, 
	"Model" TEXT, 
	"Fleet_Series" TEXT, 
	"Powertrain" TEXT, 
	"Fuel_Propulsion" TEXT, 
	PRIMARY KEY ("Aircraft_ID")
)


CREATE TABLE pilot (
	"Pilot_ID" INTEGER, 
	"Pilot_name" TEXT, 
	"Rank" INTEGER, 
	"Age" INTEGER, 
	"Nationality" TEXT, 
	"Position" TEXT, 
	"Join_Year" INTEGER, 
	"Team" TEXT, 
	PRIMARY KEY ("Pilot_ID")
)


CREATE TABLE pilot_record (
	"Record_ID" INTEGER, 
	"Pilot_ID" INTEGER, 
	"Aircraft_ID" INTEGER, 
	"Date" TEXT, 
	PRIMARY KEY ("Pilot_ID", "Aircraft_ID", "Date"), 
	FOREIGN KEY("Aircraft_ID") REFERENCES aircraft ("Aircraft_ID"), 
	FOREIGN KEY("Pilot_ID") REFERENCES pilot ("Pilot_ID")
)

CREATE TABLE aircraft (
	"Aircraft_ID" INTEGER, 
	"Order_Year" INTEGER, 
	"Manufacturer" TEXT, 
	"Model" TEXT, 
	"Fleet_Series" TEXT, 
	"Powertrain" TEXT, 
	"Fuel_Propulsion" TEXT, 
	PRIMARY KEY ("Aircraft_ID")
)

/*
2 rows from aircraft table:
Aircraft_ID	Order_Year	Manufacturer	Model	Fleet_Series	Powertrain	Fuel_Propulsion
1	1992	Gillig	Phantom (High Floor)	444-464 (21)	DD S50EGR Allison WB-400R	Diesel
2	1996	Gillig	Phantom (High Floor)	465-467 (3)	DD S50 Allison WB-400R	Diesel
*/


CREATE TABLE pilot (
	"Pilot_ID" INTEGER, 
	"Pilot_name" TEXT, 
	"Rank" INTEGER, 
	"Age" INTEGER, 
	"Nationality" TEXT, 
	"Position" TEXT, 
	"Join_Year" INTEGER, 
	"Team" TEXT, 
	PRIMARY KEY ("Pilot_ID")
)

/*
2 rows from pilot table:
Pilot_ID	Pilot_name	Rank	Age	Nationality	Position	Join_Year	Team
1	Patrick O'Bryant	13	33	United States	Center Team	2009	Bradley
2	Jermaine O'Neal	6	40	United States	Forward-Center Team	2008	Eau Claire High School
*/


CREATE TABLE pilot_record (
	"Record_ID" INTEGER, 
	"Pilot_ID" INTEGER, 
	"Aircraft_ID" INTEGER, 
	"Date" TEXT, 
	PRIMARY KEY ("Pilot_ID", "Aircraft_ID", "Date"), 
	FOREIGN KEY("Aircraft_ID") REFERENCES aircraft ("Aircraft_ID"), 
	FOREIGN KEY("Pilot_ID") REFERENCES pilot ("Pilot_ID")
)

/*
2 rows from pilot_record table:
Record_ID	Pilot_ID	Aircraft_ID	Date
1	1	1	2003/01/04
2	2	1	2004/01/04
*/
How many pilots are there?
SELECT count(*) FROM pilot
[{'count(*)': 5}]