
CREATE TABLE airlines (
	uid INTEGER, 
	"Airline" TEXT, 
	"Abbreviation" TEXT, 
	"Country" TEXT, 
	PRIMARY KEY (uid)
)


CREATE TABLE airports (
	"City" TEXT, 
	"AirportCode" TEXT, 
	"AirportName" TEXT, 
	"Country" TEXT, 
	"CountryAbbrev" TEXT, 
	PRIMARY KEY ("AirportCode")
)


CREATE TABLE flights (
	"Airline" INTEGER, 
	"FlightNo" INTEGER, 
	"SourceAirport" TEXT, 
	"DestAirport" TEXT, 
	PRIMARY KEY ("Airline", "FlightNo"), 
	FOREIGN KEY("SourceAirport") REFERENCES airports ("AirportCode"), 
	FOREIGN KEY("DestAirport") REFERENCES airports ("AirportCode")
)

CREATE TABLE airlines (
	uid INTEGER, 
	"Airline" TEXT, 
	"Abbreviation" TEXT, 
	"Country" TEXT, 
	PRIMARY KEY (uid)
)

/*
2 rows from airlines table:
uid	Airline	Abbreviation	Country
1	United Airlines	UAL	USA
2	US Airways	USAir	USA
*/


CREATE TABLE airports (
	"City" TEXT, 
	"AirportCode" TEXT, 
	"AirportName" TEXT, 
	"Country" TEXT, 
	"CountryAbbrev" TEXT, 
	PRIMARY KEY ("AirportCode")
)

/*
2 rows from airports table:
City	AirportCode	AirportName	Country	CountryAbbrev
Aberdeen 	APG	Phillips AAF 	United States 	US 
Aberdeen 	ABR	Municipal 	United States 	US
*/


CREATE TABLE flights (
	"Airline" INTEGER, 
	"FlightNo" INTEGER, 
	"SourceAirport" TEXT, 
	"DestAirport" TEXT, 
	PRIMARY KEY ("Airline", "FlightNo"), 
	FOREIGN KEY("SourceAirport") REFERENCES airports ("AirportCode"), 
	FOREIGN KEY("DestAirport") REFERENCES airports ("AirportCode")
)

/*
2 rows from flights table:
Airline	FlightNo	SourceAirport	DestAirport
1	28	 APG	 ASY
1	29	 ASY	 APG
*/
Which airline has most number of flights?
SELECT T1.Airline FROM AIRLINES AS T1 JOIN FLIGHTS AS T2 ON T1.uid  =  T2.Airline GROUP BY T1.Airline ORDER BY count(*) DESC LIMIT 1
[{'Airline': 'Virgin America'}]