
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 airports do not have departing or arriving flights?
SELECT AirportName FROM Airports WHERE AirportCode NOT IN (SELECT SourceAirport FROM Flights UNION SELECT DestAirport FROM Flights)
[{'AirportName': 'Phillips AAF '}, {'AirportName': 'Municipal '}, {'AirportName': 'Dyess AFB '}, {'AirportName': 'Municipal '}, {'AirportName': 'Virginia Highlands '}, {'AirportName': 'Ada '}, {'AirportName': 'Adak Island Ns '}, {'AirportName': 'Lenawee County '}, {'AirportName': 'Municipal '}, {'AirportName': 'Municipal '}, {'AirportName': 'Ainsworth '}, {'AirportName': 'Akhiok SPB '}, {'AirportName': 'Spb '}, {'AirportName': 'Akiak '}, {'AirportName': 'Colorado Plains Regional Airport '}, {'AirportName': 'Akron/canton Regional '}, {'AirportName': 'Fulton International '}, {'AirportName': 'Akutan '}, {'AirportName': 'Alakanuk '}, {'AirportName': 'NAS '}, {'AirportName': 'Holloman AFB '}, {'AirportName': 'Municipal '}, {'AirportName': 'Municipal '}, {'AirportName': 'Albany NAS '}, {'AirportName': 'Dougherty County '}, {'AirportName': 'Albany International '}, {'AirportName': 'Albany '}, {'AirportName': 'Albert Lea '}, {'AirportName': 'Albuquerque International '}, {'AirportName': 'Aleknagik '}, {'AirportName': 'Aleneva '}, {'AirportName': 'Thomas C Russell Fld '}, {'AirportName': 'Alexandria International '}, {'AirportName': 'Esler Field '}, {'AirportName': 'Alexandria '}, {'AirportName': 'Alexandria Bay '}, {'AirportName': 'Algona '}, {'AirportName': 'International '}, {'AirportName': 'George Downer '}, {'AirportName': 'Alitak SPB '}, {'AirportName': 'Allakaket '}, {'AirportName': 'Alliance '}, {'AirportName': 'Gratiot Community '}, {'AirportName': 'Alpena County Regional '}, {'AirportName': 'Alpine '}, {'AirportName': 'Alton '}, {'AirportName': 'Altus AFB '}, {'AirportName': 'Municipal '}, {'AirportName': 'Alyeska '}, {'AirportName': 'Rick Husband Amarillo International '}, {'AirportName': 'Tradewind '}, {'AirportName': 'Ambler '}, {'AirportName': 'Amchitka '}, {'AirportName': 'Municipal '}, {'AirportName': 'Ames '}, {'AirportName': 'Zahns '}, {'AirportName': 'Amook '}, {'AirportName': 'Anacortes '}, {'AirportName': 'USN Heliport '}, {'AirportName': 'Orange County Steel Salvage Heliport '}, {'AirportName': 'Anaktuvuk '}, {'AirportName': 'Elmendorf Afb '}, {'AirportName': 'Ted Stevens Anchorage International Airport '}, {'AirportName': 'Merrill Field '}, {'AirportName': 'Municipal '}, {'AirportName': 'Anderson '}, {'AirportName': 'Andrews '}, {'AirportName': 'Angel Fire '}, {'AirportName': 'Tri-State Steuben Cty '}, {'AirportName': 'Angoon '}, {'AirportName': 'Rollang Field '}, {'AirportName': 'Aniak '}, {'AirportName': 'Anita Bay '}, {'AirportName': 'Municipal '}, {'AirportName': 'Lee '}, {'AirportName': 'Annette Island '}, {'AirportName': 'Anniston Metropolitan '}, {'AirportName': 'Ft Mcclellan Bus Trml '}, {'AirportName': 'Reilly AHP '}, {'AirportName': 'Anthony '}, {'AirportName': 'Antlers '}, {'AirportName': 'Anvik '}, {'AirportName': 'Municipal '}, {'AirportName': 'Apple Valley '}, {'AirportName': 'Outagamie County '}, {'AirportName': 'Municipal '}, {'AirportName': 'Arcata '}, {'AirportName': 'Arctic Village '}, {'AirportName': 'Downtown '}, {'AirportName': 'Ardmore Municipal Arpt '}, {'AirportName': 'US Army Heliport '}, {'AirportName': 'Artesia '}, {'AirportName': 'Asbury Park '}, {'AirportName': 'Ashland '}, {'AirportName': 'Ashley '}, {'AirportName': 'Aspen '}, {'AirportName': 'Astoria '}, {'AirportName': 'Athens '}, {'AirportName': 'Ohio University '}, {'AirportName': 'McMinn County '}]