
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
*/
Find the name of airports which do not have any flight in and out.
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 '}]