
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
*/
What are the airline names and abbreviations for airlines in the USA?
SELECT Airline ,  Abbreviation FROM AIRLINES WHERE Country  =  "USA"
[{'Airline': 'United Airlines', 'Abbreviation': 'UAL'}, {'Airline': 'US Airways', 'Abbreviation': 'USAir'}, {'Airline': 'Delta Airlines', 'Abbreviation': 'Delta'}, {'Airline': 'Southwest Airlines', 'Abbreviation': 'Southwest'}, {'Airline': 'American Airlines', 'Abbreviation': 'American'}, {'Airline': 'Northwest Airlines', 'Abbreviation': 'Northwest'}, {'Airline': 'Continental Airlines', 'Abbreviation': 'Continental'}, {'Airline': 'JetBlue Airways', 'Abbreviation': 'JetBlue'}, {'Airline': 'Frontier Airlines', 'Abbreviation': 'Frontier'}, {'Airline': 'AirTran Airways', 'Abbreviation': 'AirTran'}, {'Airline': 'Allegiant Air', 'Abbreviation': 'Allegiant'}, {'Airline': 'Virgin America', 'Abbreviation': 'Virgin'}]