Database: flight_2
Schema: CREATE TABLE 'airlines' ( 	uid INTEGER PRIMARY KEY,  	Airline TEXT,  	Abbreviation TEXT,  	Country TEXT ); CREATE TABLE 'airports' ( 	City TEXT,  	AirportCode TEXT PRIMARY KEY,  	AirportName TEXT,  	Country TEXT,  	CountryAbbrev TEXT ); 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) );

Question (easy): Which country does Airline "JetBlue Airways" belong to?
SQL: SELECT Country FROM AIRLINES WHERE Airline  =  "JetBlue Airways"

Question (easy): What country is Jetblue Airways affiliated with?
SQL: SELECT Country FROM AIRLINES WHERE Airline  =  "JetBlue Airways"

Question (easy): What is the abbreviation of Airline "JetBlue Airways"?
SQL: SELECT Abbreviation FROM AIRLINES WHERE Airline  =  "JetBlue Airways"

Question (easy): Which abbreviation corresponds to Jetblue Airways?
SQL: SELECT Abbreviation FROM AIRLINES WHERE Airline  =  "JetBlue Airways"

Question (medium): List all airline names and their abbreviations in "USA".
SQL: SELECT Airline ,  Abbreviation FROM AIRLINES WHERE Country  =  "USA"

Question (medium): What are the airline names and abbreviations for airlines in the USA?
SQL: SELECT Airline ,  Abbreviation FROM AIRLINES WHERE Country  =  "USA"

Question (medium): List the airport code and name in the city of Anthony.
SQL: SELECT AirportCode ,  AirportName FROM AIRPORTS WHERE city  =  "Anthony"

Question (medium): Give the airport code and airport name corresonding to the city Anthony.
SQL: SELECT AirportCode ,  AirportName FROM AIRPORTS WHERE city  =  "Anthony"

Question (easy): How many airlines do we have?
SQL: SELECT count(*) FROM AIRLINES

Question (easy): What is the total number of airlines?
SQL: SELECT count(*) FROM AIRLINES

Question (easy): How many airports do we have?
SQL: SELECT count(*) FROM AIRPORTS

Question (easy): Return the number of  airports.
SQL: SELECT count(*) FROM AIRPORTS

Question (easy): How many flights do we have?
SQL: SELECT count(*) FROM FLIGHTS

Question (easy): Return the number of flights.
SQL: SELECT count(*) FROM FLIGHTS

Question (easy): Which airline has abbreviation 'UAL'?
SQL: SELECT Airline FROM AIRLINES WHERE Abbreviation  =  "UAL"

Question (easy): Give the airline with abbreviation 'UAL'.
SQL: SELECT Airline FROM AIRLINES WHERE Abbreviation  =  "UAL"

Question (easy): How many airlines are from USA?
SQL: SELECT count(*) FROM AIRLINES WHERE Country  =  "USA"

Question (easy): Return the number of airlines in the USA.
SQL: SELECT count(*) FROM AIRLINES WHERE Country  =  "USA"

Question (medium): Which city and country is the Alton airport at?
SQL: SELECT City ,  Country FROM AIRPORTS WHERE AirportName  =  "Alton"

Question (medium): Give the city and country for the Alton airport.
SQL: SELECT City ,  Country FROM AIRPORTS WHERE AirportName  =  "Alton"

Question (easy): What is the airport name for airport 'AKO'?
SQL: SELECT AirportName FROM AIRPORTS WHERE AirportCode  =  "AKO"

Question (easy): Return the name of the airport with code 'AKO'.
SQL: SELECT AirportName FROM AIRPORTS WHERE AirportCode  =  "AKO"

Question (easy): What are airport names at City 'Aberdeen'?
SQL: SELECT AirportName FROM AIRPORTS WHERE City = "Aberdeen"

Question (easy): What are the names of airports in Aberdeen?
SQL: SELECT AirportName FROM AIRPORTS WHERE City = "Aberdeen"

Question (easy): How many flights depart from 'APG'?
SQL: SELECT count(*) FROM FLIGHTS WHERE SourceAirport  =  "APG"

Question (easy): Count the number of flights departing from 'APG'.
SQL: SELECT count(*) FROM FLIGHTS WHERE SourceAirport  =  "APG"

Question (easy): How many flights have destination ATO?
SQL: SELECT count(*) FROM FLIGHTS WHERE DestAirport  =  "ATO"

Question (easy): Count the number of flights into ATO.
SQL: SELECT count(*) FROM FLIGHTS WHERE DestAirport  =  "ATO"

Question (medium): How many flights depart from City Aberdeen?
SQL: SELECT count(*) FROM FLIGHTS AS T1 JOIN AIRPORTS AS T2 ON T1.SourceAirport  =  T2.AirportCode WHERE T2.City  =  "Aberdeen"

Question (medium): Return the number of flights departing from Aberdeen.
SQL: SELECT count(*) FROM FLIGHTS AS T1 JOIN AIRPORTS AS T2 ON T1.SourceAirport  =  T2.AirportCode WHERE T2.City  =  "Aberdeen"

Question (medium): How many flights arriving in Aberdeen city?
SQL: SELECT count(*) FROM FLIGHTS AS T1 JOIN AIRPORTS AS T2 ON T1.DestAirport  =  T2.AirportCode WHERE T2.City  =  "Aberdeen"

Question (medium): Return the number of flights arriving in Aberdeen.
SQL: SELECT count(*) FROM FLIGHTS AS T1 JOIN AIRPORTS AS T2 ON T1.DestAirport  =  T2.AirportCode WHERE T2.City  =  "Aberdeen"

Question (hard): How many flights depart from City 'Aberdeen' and have destination City 'Ashley'?
SQL: SELECT count(*) FROM FLIGHTS AS T1 JOIN AIRPORTS AS T2 ON T1.DestAirport  =  T2.AirportCode JOIN AIRPORTS AS T3 ON T1.SourceAirport  =  T3.AirportCode WHERE T2.City  =  "Ashley" AND T3.City  =  "Aberdeen"

Question (hard): How many flights fly from Aberdeen to Ashley?
SQL: SELECT count(*) FROM FLIGHTS AS T1 JOIN AIRPORTS AS T2 ON T1.DestAirport  =  T2.AirportCode JOIN AIRPORTS AS T3 ON T1.SourceAirport  =  T3.AirportCode WHERE T2.City  =  "Ashley" AND T3.City  =  "Aberdeen"

Question (medium): How many flights does airline 'JetBlue Airways' have?
SQL: SELECT count(*) FROM FLIGHTS AS T1 JOIN AIRLINES AS T2 ON T1.Airline  =  T2.uid WHERE T2.Airline = "JetBlue Airways"

Question (medium): Give the number of Jetblue Airways flights.
SQL: SELECT count(*) FROM FLIGHTS AS T1 JOIN AIRLINES AS T2 ON T1.Airline  =  T2.uid WHERE T2.Airline = "JetBlue Airways"

Question (medium): How many 'United Airlines' flights go to Airport 'ASY'?
SQL: SELECT count(*) FROM AIRLINES AS T1 JOIN FLIGHTS AS T2 ON T2.Airline  =  T1.uid WHERE T1.Airline  =  "United Airlines" AND T2.DestAirport  =  "ASY"

Question (medium): Count the number of United Airlines flights arriving in ASY Airport.
SQL: SELECT count(*) FROM AIRLINES AS T1 JOIN FLIGHTS AS T2 ON T2.Airline  =  T1.uid WHERE T1.Airline  =  "United Airlines" AND T2.DestAirport  =  "ASY"

Question (medium): How many 'United Airlines' flights depart from Airport 'AHD'?
SQL: SELECT count(*) FROM AIRLINES AS T1 JOIN FLIGHTS AS T2 ON T2.Airline  =  T1.uid WHERE T1.Airline  =  "United Airlines" AND T2.SourceAirport  =  "AHD"

Question (medium): Return the number of United Airlines flights leaving from AHD Airport.
SQL: SELECT count(*) FROM AIRLINES AS T1 JOIN FLIGHTS AS T2 ON T2.Airline  =  T1.uid WHERE T1.Airline  =  "United Airlines" AND T2.SourceAirport  =  "AHD"

Question (hard): How many United Airlines flights go to City 'Aberdeen'?
SQL: SELECT count(*) FROM FLIGHTS AS T1 JOIN AIRPORTS AS T2 ON T1.DestAirport  =  T2.AirportCode JOIN AIRLINES AS T3 ON T3.uid  =  T1.Airline WHERE T2.City  =  "Aberdeen" AND T3.Airline  =  "United Airlines"

Question (hard): Count the number of United Airlines flights that arrive in Aberdeen.
SQL: SELECT count(*) FROM FLIGHTS AS T1 JOIN AIRPORTS AS T2 ON T1.DestAirport  =  T2.AirportCode JOIN AIRLINES AS T3 ON T3.uid  =  T1.Airline WHERE T2.City  =  "Aberdeen" AND T3.Airline  =  "United Airlines"

Question (extra): Which city has most number of arriving flights?
SQL: SELECT T1.City FROM AIRPORTS AS T1 JOIN FLIGHTS AS T2 ON T1.AirportCode  =  T2.DestAirport GROUP BY T1.City ORDER BY count(*) DESC LIMIT 1

Question (extra): Which city has the most frequent destination airport?
SQL: SELECT T1.City FROM AIRPORTS AS T1 JOIN FLIGHTS AS T2 ON T1.AirportCode  =  T2.DestAirport GROUP BY T1.City ORDER BY count(*) DESC LIMIT 1

Question (extra): Which city has most number of departing flights?
SQL: SELECT T1.City FROM AIRPORTS AS T1 JOIN FLIGHTS AS T2 ON T1.AirportCode  =  T2.SourceAirport GROUP BY T1.City ORDER BY count(*) DESC LIMIT 1

Question (extra): Which city is the most frequent source airport?
SQL: SELECT T1.City FROM AIRPORTS AS T1 JOIN FLIGHTS AS T2 ON T1.AirportCode  =  T2.SourceAirport GROUP BY T1.City ORDER BY count(*) DESC LIMIT 1

Question (extra): What is the code of airport that has the highest number of flights?
SQL: SELECT T1.AirportCode FROM AIRPORTS AS T1 JOIN FLIGHTS AS T2 ON T1.AirportCode  =  T2.DestAirport OR T1.AirportCode  =  T2.SourceAirport GROUP BY T1.AirportCode ORDER BY count(*) DESC LIMIT 1

Question (extra): What is the airport code of the airport with the most flights?
SQL: SELECT T1.AirportCode FROM AIRPORTS AS T1 JOIN FLIGHTS AS T2 ON T1.AirportCode  =  T2.DestAirport OR T1.AirportCode  =  T2.SourceAirport GROUP BY T1.AirportCode ORDER BY count(*) DESC LIMIT 1

Question (extra): What is the code of airport that has fewest number of flights?
SQL: SELECT T1.AirportCode FROM AIRPORTS AS T1 JOIN FLIGHTS AS T2 ON T1.AirportCode  =  T2.DestAirport OR T1.AirportCode  =  T2.SourceAirport GROUP BY T1.AirportCode ORDER BY count(*) LIMIT 1

Question (extra): Give the code of the airport with the least flights.
SQL: SELECT T1.AirportCode FROM AIRPORTS AS T1 JOIN FLIGHTS AS T2 ON T1.AirportCode  =  T2.DestAirport OR T1.AirportCode  =  T2.SourceAirport GROUP BY T1.AirportCode ORDER BY count(*) LIMIT 1

Question (extra): Which airline has most number of flights?
SQL: 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

Question (extra): What airline serves the most flights?
SQL: 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

Question (extra): Find the abbreviation and country of the airline that has fewest number of flights?
SQL: SELECT T1.Abbreviation ,  T1.Country FROM AIRLINES AS T1 JOIN FLIGHTS AS T2 ON T1.uid  =  T2.Airline GROUP BY T1.Airline ORDER BY count(*) LIMIT 1

Question (extra): What is the abbreviation of the airilne has the fewest flights and what country is it in?
SQL: SELECT T1.Abbreviation ,  T1.Country FROM AIRLINES AS T1 JOIN FLIGHTS AS T2 ON T1.uid  =  T2.Airline GROUP BY T1.Airline ORDER BY count(*) LIMIT 1

Question (medium): What are airlines that have some flight departing from airport 'AHD'?
SQL: SELECT T1.Airline FROM AIRLINES AS T1 JOIN FLIGHTS AS T2 ON T1.uid  =  T2.Airline WHERE T2.SourceAirport  =  "AHD"

Question (medium): Which airlines have a flight with source airport AHD?
SQL: SELECT T1.Airline FROM AIRLINES AS T1 JOIN FLIGHTS AS T2 ON T1.uid  =  T2.Airline WHERE T2.SourceAirport  =  "AHD"

Question (medium): What are airlines that have flights arriving at airport 'AHD'?
SQL: SELECT T1.Airline FROM AIRLINES AS T1 JOIN FLIGHTS AS T2 ON T1.uid  =  T2.Airline WHERE T2.DestAirport  =  "AHD"

Question (medium): Which airlines have a flight with destination airport AHD?
SQL: SELECT T1.Airline FROM AIRLINES AS T1 JOIN FLIGHTS AS T2 ON T1.uid  =  T2.Airline WHERE T2.DestAirport  =  "AHD"

Question (extra): Find all airlines that have flights from both airports 'APG' and 'CVO'.
SQL: SELECT T1.Airline FROM AIRLINES AS T1 JOIN FLIGHTS AS T2 ON T1.uid  =  T2.Airline WHERE T2.SourceAirport  =  "APG" INTERSECT SELECT T1.Airline FROM AIRLINES AS T1 JOIN FLIGHTS AS T2 ON T1.uid  =  T2.Airline WHERE T2.SourceAirport  =  "CVO"

Question (extra): Which airlines have departing flights from both APG and CVO airports?
SQL: SELECT T1.Airline FROM AIRLINES AS T1 JOIN FLIGHTS AS T2 ON T1.uid  =  T2.Airline WHERE T2.SourceAirport  =  "APG" INTERSECT SELECT T1.Airline FROM AIRLINES AS T1 JOIN FLIGHTS AS T2 ON T1.uid  =  T2.Airline WHERE T2.SourceAirport  =  "CVO"

Question (extra): Find all airlines that have flights from airport 'CVO' but not from 'APG'.
SQL: SELECT T1.Airline FROM AIRLINES AS T1 JOIN FLIGHTS AS T2 ON T1.uid  =  T2.Airline WHERE T2.SourceAirport  =  "CVO" EXCEPT SELECT T1.Airline FROM AIRLINES AS T1 JOIN FLIGHTS AS T2 ON T1.uid  =  T2.Airline WHERE T2.SourceAirport  =  "APG"

Question (extra): Which airlines have departures from CVO but not from APG airports?
SQL: SELECT T1.Airline FROM AIRLINES AS T1 JOIN FLIGHTS AS T2 ON T1.uid  =  T2.Airline WHERE T2.SourceAirport  =  "CVO" EXCEPT SELECT T1.Airline FROM AIRLINES AS T1 JOIN FLIGHTS AS T2 ON T1.uid  =  T2.Airline WHERE T2.SourceAirport  =  "APG"

Question (medium): Find all airlines that have at least 10 flights.
SQL: SELECT T1.Airline FROM AIRLINES AS T1 JOIN FLIGHTS AS T2 ON T1.uid  =  T2.Airline GROUP BY T1.Airline HAVING count(*)  >  10

Question (medium): Which airlines have at least 10 flights?
SQL: SELECT T1.Airline FROM AIRLINES AS T1 JOIN FLIGHTS AS T2 ON T1.uid  =  T2.Airline GROUP BY T1.Airline HAVING count(*)  >  10

Question (medium): Find all airlines that have fewer than 200 flights.
SQL: SELECT T1.Airline FROM AIRLINES AS T1 JOIN FLIGHTS AS T2 ON T1.uid  =  T2.Airline GROUP BY T1.Airline HAVING count(*)  <  200

Question (medium): Which airlines have less than 200 flights?
SQL: SELECT T1.Airline FROM AIRLINES AS T1 JOIN FLIGHTS AS T2 ON T1.uid  =  T2.Airline GROUP BY T1.Airline HAVING count(*)  <  200

Question (medium): What are flight numbers of Airline "United Airlines"?
SQL: SELECT T1.FlightNo FROM FLIGHTS AS T1 JOIN AIRLINES AS T2 ON T2.uid  =  T1.Airline WHERE T2.Airline  =  "United Airlines"

Question (medium): Which flight numbers correspond to United Airlines flights?
SQL: SELECT T1.FlightNo FROM FLIGHTS AS T1 JOIN AIRLINES AS T2 ON T2.uid  =  T1.Airline WHERE T2.Airline  =  "United Airlines"

Question (easy): What are flight numbers of flights departing from Airport "APG"?
SQL: SELECT FlightNo FROM FLIGHTS WHERE SourceAirport  =  "APG"

Question (easy): Give the flight numbers of flights leaving from APG.
SQL: SELECT FlightNo FROM FLIGHTS WHERE SourceAirport  =  "APG"

Question (easy): What are flight numbers of flights arriving at Airport "APG"?
SQL: SELECT FlightNo FROM FLIGHTS WHERE DestAirport  =  "APG"

Question (easy): Give the flight numbers of flights landing at APG.
SQL: SELECT FlightNo FROM FLIGHTS WHERE DestAirport  =  "APG"

Question (medium): What are flight numbers of flights departing from City "Aberdeen "?
SQL: SELECT T1.FlightNo FROM FLIGHTS AS T1 JOIN AIRPORTS AS T2 ON T1.SourceAirport   =  T2.AirportCode WHERE T2.City  =  "Aberdeen"

Question (medium): Give the flight numbers of flights leaving from Aberdeen.
SQL: SELECT T1.FlightNo FROM FLIGHTS AS T1 JOIN AIRPORTS AS T2 ON T1.SourceAirport   =  T2.AirportCode WHERE T2.City  =  "Aberdeen"

Question (medium): What are flight numbers of flights arriving at City "Aberdeen"?
SQL: SELECT T1.FlightNo FROM FLIGHTS AS T1 JOIN AIRPORTS AS T2 ON T1.DestAirport   =  T2.AirportCode WHERE T2.City  =  "Aberdeen"

Question (medium): Give the flight numbers of flights arriving in Aberdeen.
SQL: SELECT T1.FlightNo FROM FLIGHTS AS T1 JOIN AIRPORTS AS T2 ON T1.DestAirport   =  T2.AirportCode WHERE T2.City  =  "Aberdeen"

Question (hard): Find the number of flights landing in the city of Aberdeen or Abilene.
SQL: SELECT count(*) FROM Flights AS T1 JOIN Airports AS T2 ON T1.DestAirport  =  T2.AirportCode WHERE T2.city  =  "Aberdeen" OR T2.city  =  "Abilene"

Question (hard): How many flights land in Aberdeen or Abilene?
SQL: SELECT count(*) FROM Flights AS T1 JOIN Airports AS T2 ON T1.DestAirport  =  T2.AirportCode WHERE T2.city  =  "Aberdeen" OR T2.city  =  "Abilene"

Question (hard): Find the name of airports which do not have any flight in and out.
SQL: SELECT AirportName FROM Airports WHERE AirportCode NOT IN (SELECT SourceAirport FROM Flights UNION SELECT DestAirport FROM Flights)

Question (hard): Which airports do not have departing or arriving flights?
SQL: SELECT AirportName FROM Airports WHERE AirportCode NOT IN (SELECT SourceAirport FROM Flights UNION SELECT DestAirport FROM Flights)