
CREATE TABLE "match" (
	"Round" REAL, 
	"Location" TEXT, 
	"Country" TEXT, 
	"Date" TEXT, 
	"Fastest_Qualifying" TEXT, 
	"Winning_Pilot" TEXT, 
	"Winning_Aircraft" TEXT, 
	PRIMARY KEY ("Round"), 
	FOREIGN KEY("Winning_Pilot") REFERENCES pilot ("Pilot_Id"), 
	FOREIGN KEY("Winning_Aircraft") REFERENCES aircraft ("Aircraft_ID")
)


CREATE TABLE aircraft (
	"Aircraft_ID" INTEGER NOT NULL, 
	"Aircraft" VARCHAR(50) NOT NULL, 
	"Description" VARCHAR(50) NOT NULL, 
	"Max_Gross_Weight" VARCHAR(50) NOT NULL, 
	"Total_disk_area" VARCHAR(50) NOT NULL, 
	"Max_disk_Loading" VARCHAR(50) NOT NULL, 
	PRIMARY KEY ("Aircraft_ID")
)


CREATE TABLE airport (
	"Airport_ID" INTEGER, 
	"Airport_Name" TEXT, 
	"Total_Passengers" REAL, 
	"%_Change_2007" TEXT, 
	"International_Passengers" REAL, 
	"Domestic_Passengers" REAL, 
	"Transit_Passengers" REAL, 
	"Aircraft_Movements" REAL, 
	"Freight_Metric_Tonnes" REAL, 
	PRIMARY KEY ("Airport_ID")
)


CREATE TABLE airport_aircraft (
	"ID" INTEGER, 
	"Airport_ID" INTEGER, 
	"Aircraft_ID" INTEGER, 
	PRIMARY KEY ("Airport_ID", "Aircraft_ID"), 
	FOREIGN KEY("Aircraft_ID") REFERENCES aircraft ("Aircraft_ID"), 
	FOREIGN KEY("Airport_ID") REFERENCES airport ("Airport_ID")
)


CREATE TABLE pilot (
	"Pilot_Id" INTEGER NOT NULL, 
	"Name" VARCHAR(50) NOT NULL, 
	"Age" INTEGER NOT NULL, 
	PRIMARY KEY ("Pilot_Id")
)

CREATE TABLE "match" (
	"Round" REAL, 
	"Location" TEXT, 
	"Country" TEXT, 
	"Date" TEXT, 
	"Fastest_Qualifying" TEXT, 
	"Winning_Pilot" TEXT, 
	"Winning_Aircraft" TEXT, 
	PRIMARY KEY ("Round"), 
	FOREIGN KEY("Winning_Pilot") REFERENCES pilot ("Pilot_Id"), 
	FOREIGN KEY("Winning_Aircraft") REFERENCES aircraft ("Aircraft_ID")
)

/*
2 rows from match table:
Round	Location	Country	Date	Fastest_Qualifying	Winning_Pilot	Winning_Aircraft
1.0	Mina' Zayid , Abu Dhabi	United Arab Emirates	March 26–27	Hannes Arch	1	1
2.0	Swan River , Perth	Australia	April 17–18	Paul Bonhomme	4	1
*/


CREATE TABLE aircraft (
	"Aircraft_ID" INTEGER NOT NULL, 
	"Aircraft" VARCHAR(50) NOT NULL, 
	"Description" VARCHAR(50) NOT NULL, 
	"Max_Gross_Weight" VARCHAR(50) NOT NULL, 
	"Total_disk_area" VARCHAR(50) NOT NULL, 
	"Max_disk_Loading" VARCHAR(50) NOT NULL, 
	PRIMARY KEY ("Aircraft_ID")
)

/*
2 rows from aircraft table:
Aircraft_ID	Aircraft	Description	Max_Gross_Weight	Total_disk_area	Max_disk_Loading
1	Robinson R-22	Light utility helicopter	1,370 lb (635 kg)	497 ft² (46.2 m²)	2.6 lb/ft² (14 kg/m²)
2	Bell 206B3 JetRanger	Turboshaft utility helicopter	3,200 lb (1,451 kg)	872 ft² (81.1 m²)	3.7 lb/ft² (18 kg/m²)
*/


CREATE TABLE airport (
	"Airport_ID" INTEGER, 
	"Airport_Name" TEXT, 
	"Total_Passengers" REAL, 
	"%_Change_2007" TEXT, 
	"International_Passengers" REAL, 
	"Domestic_Passengers" REAL, 
	"Transit_Passengers" REAL, 
	"Aircraft_Movements" REAL, 
	"Freight_Metric_Tonnes" REAL, 
	PRIMARY KEY ("Airport_ID")
)

/*
2 rows from airport table:
Airport_ID	Airport_Name	Total_Passengers	%_Change_2007	International_Passengers	Domestic_Passengers	Transit_Passengers	Aircraft_Movements	Freight_Metric_Tonnes
1	London Heathrow	67054745.0	1.5%	61344438.0	5562516.0	147791.0	478693.0	1397054.0
2	London Gatwick	34205887.0	2.9%	30431051.0	3730963.0	43873.0	263653.0	107702.0
*/


CREATE TABLE airport_aircraft (
	"ID" INTEGER, 
	"Airport_ID" INTEGER, 
	"Aircraft_ID" INTEGER, 
	PRIMARY KEY ("Airport_ID", "Aircraft_ID"), 
	FOREIGN KEY("Aircraft_ID") REFERENCES aircraft ("Aircraft_ID"), 
	FOREIGN KEY("Airport_ID") REFERENCES airport ("Airport_ID")
)

/*
2 rows from airport_aircraft table:
ID	Airport_ID	Aircraft_ID
1	6	5
2	2	1
*/


CREATE TABLE pilot (
	"Pilot_Id" INTEGER NOT NULL, 
	"Name" VARCHAR(50) NOT NULL, 
	"Age" INTEGER NOT NULL, 
	PRIMARY KEY ("Pilot_Id")
)

/*
2 rows from pilot table:
Pilot_Id	Name	Age
1	Prof. Zackery Collins	23
2	Katheryn Gorczany IV	20
*/
List the names of aircrafts and that won matches at least twice.
SELECT T1.Aircraft FROM aircraft AS T1 JOIN MATCH AS T2 ON T1.Aircraft_ID  =  T2.Winning_Aircraft GROUP BY T2.Winning_Aircraft HAVING COUNT(*)  >=  2
[{'Aircraft': 'Robinson R-22'}, {'Aircraft': 'Mil Mi-26'}]