CREATE TABLE routes (
    rid NUMBER PRIMARY KEY,
    dst_apid NUMBER,
    dst_ap TEXT,
    src_apid NUMBER,
    src_ap TEXT,
    alid NUMBER,
    airline TEXT,
    codeshare TEXT,
    FOREIGN KEY ("alid") REFERENCES airlines("alid"),
    FOREIGN KEY ("src_apid") REFERENCES airports("apid"),
    FOREIGN KEY ("dst_apid") REFERENCES airports("apid")
);
CREATE TABLE airports (
    apid NUMBER PRIMARY KEY,
    name TEXT,
    city TEXT,
    country TEXT,
    x NUMBER,
    y NUMBER,
    elevation NUMBER,
    iata TEXT,
    icao TEXT
);
CREATE TABLE airlines (
    alid NUMBER PRIMARY KEY,
    name TEXT,
    iata TEXT,
    icao TEXT,
    callsign TEXT,
    country TEXT,
    active TEXT
);

Question: List all countries and their number of airlines in the descending order of number of airlines.
SQL: SELECT country ,  count(*) FROM airlines GROUP BY country ORDER BY count(*) DESC

Question: How many airlines operate out of each country in descending order?
SQL: SELECT country ,  count(*) FROM airlines GROUP BY country ORDER BY count(*) DESC

Question: How many airports are there per country? Order the countries by decreasing number of airports.
SQL: SELECT count(*) ,  country FROM airports GROUP BY country ORDER BY count(*) DESC

Question: What is the number of airports per country, ordered from most to least?
SQL: SELECT count(*) ,  country FROM airports GROUP BY country ORDER BY count(*) DESC

Question: How many airports are there per city in the United States? Order the cities by decreasing number of airports.
SQL: SELECT count(*) ,  city FROM airports WHERE country  =  'United States' GROUP BY city ORDER BY count(*) DESC

Question: How many airports are there per city in the US ordered from most to least?
SQL: SELECT count(*) ,  city FROM airports WHERE country  =  'United States' GROUP BY city ORDER BY count(*) DESC

Question: Return the cities with more than 3 airports in the United States.
SQL: SELECT city FROM airports WHERE country  =  'United States' GROUP BY city HAVING count(*)  >  3

Question: What is the number of cities in the United States with more than 3 airports?
SQL: SELECT city FROM airports WHERE country  =  'United States' GROUP BY city HAVING count(*)  >  3

Question: How many cities are there that have more than 3 airports?
SQL: SELECT count(*) FROM (SELECT city FROM airports GROUP BY city HAVING count(*)  >  3)

Question: What is the count of cities with more than 3 airports?
SQL: SELECT count(*) FROM (SELECT city FROM airports GROUP BY city HAVING count(*)  >  3)

Question: List the cities which have more than one airport and number of airports.
SQL: SELECT city ,  count(*) FROM airports GROUP BY city HAVING count(*)  >  1

Question: What are the names of all cities with more than one airport and how many airports do they have?
SQL: SELECT city ,  count(*) FROM airports GROUP BY city HAVING count(*)  >  1

Question: List the cities which have more than 2 airports sorted by the number of airports.
SQL: SELECT city FROM airports GROUP BY city HAVING count(*)  >  2 ORDER BY count(*)

Question: What are the cities that have more than 2 airports sorted by number of airports?
SQL: SELECT city FROM airports GROUP BY city HAVING count(*)  >  2 ORDER BY count(*)

Question: Find the number of routes for each source airport and the airport name.
SQL: SELECT count(*) ,  T1.name FROM airports AS T1 JOIN routes AS T2 ON T1.apid  =  T2.src_apid GROUP BY T1.name

Question: For each airport name, how many routes start at that airport?
SQL: SELECT count(*) ,  T1.name FROM airports AS T1 JOIN routes AS T2 ON T1.apid  =  T2.src_apid GROUP BY T1.name

Question: Find the number of routes and airport name for each source airport, order the results by decreasing number of routes.
SQL: SELECT count(*) ,  T1.name FROM airports AS T1 JOIN routes AS T2 ON T1.apid  =  T2.src_apid GROUP BY T1.name ORDER BY count(*) DESC

Question: For each  airport name, how many routes start at that airport, ordered from most to least?
SQL: SELECT count(*) ,  T1.name FROM airports AS T1 JOIN routes AS T2 ON T1.apid  =  T2.src_apid GROUP BY T1.name ORDER BY count(*) DESC

Question: Find the average elevation of all airports for each country.
SQL: SELECT avg(elevation) ,  country FROM airports GROUP BY country

Question: For each country, what is the average elevation of that country's airports?
SQL: SELECT avg(elevation) ,  country FROM airports GROUP BY country

Question: Find the cities which have exactly two airports.
SQL: SELECT city FROM airports GROUP BY city HAVING count(*)  =  2

Question: What are the cities with exactly two airports?
SQL: SELECT city FROM airports GROUP BY city HAVING count(*)  =  2

Question: For each country and airline name, how many routes are there?
SQL: SELECT T1.country ,  T1.name ,  count(*) FROM airlines AS T1 JOIN routes AS T2 ON T1.alid  =  T2.alid GROUP BY T1.country ,  T1.name

Question: What is the total number of routes for each country and airline in that country?
SQL: SELECT T1.country ,  T1.name ,  count(*) FROM airlines AS T1 JOIN routes AS T2 ON T1.alid  =  T2.alid GROUP BY T1.country ,  T1.name

Question: Find the number of routes with destination airports in Italy.
SQL: SELECT count(*) FROM routes AS T1 JOIN airports AS T2 ON T1.dst_apid  =  T2.apid WHERE T2.country  =  'Italy'

Question: What is the number of routes whose destinations are Italian airports?
SQL: SELECT count(*) FROM routes AS T1 JOIN airports AS T2 ON T1.dst_apid  =  T2.apid WHERE T2.country  =  'Italy'

Question: Return the number of routes with destination airport in Italy operated by the airline with name 'American Airlines'.
SQL: SELECT count(*) FROM routes AS T1 JOIN airports AS T2 ON T1.dst_apid  =  T2.apid JOIN airlines AS T3 ON T1.alid  =  T3.alid WHERE T2.country  =  'Italy' AND T3.name  =  'American Airlines'

Question: What is the number of routes operated by the airline American Airlines whose destinations are in Italy?
SQL: SELECT count(*) FROM routes AS T1 JOIN airports AS T2 ON T1.dst_apid  =  T2.apid JOIN airlines AS T3 ON T1.alid  =  T3.alid WHERE T2.country  =  'Italy' AND T3.name  =  'American Airlines'

Question: Find the number of routes that have destination John F Kennedy International Airport.
SQL: SELECT count(*) FROM airports AS T1 JOIN routes AS T2 ON T1.apid  =  T2.dst_apid WHERE T1.name  =  'John F Kennedy International Airport'

Question: What is the number of routes that end at John F Kennedy International Airport?
SQL: SELECT count(*) FROM airports AS T1 JOIN routes AS T2 ON T1.apid  =  T2.dst_apid WHERE T1.name  =  'John F Kennedy International Airport'

Question: Find the number of routes from the United States to Canada.
SQL: SELECT count(*) FROM routes WHERE dst_apid IN (SELECT apid FROM airports WHERE country  =  'Canada') AND src_apid IN (SELECT apid FROM airports WHERE country  =  'United States')

Question: How many routes go from the United States to Canada?
SQL: SELECT count(*) FROM routes WHERE dst_apid IN (SELECT apid FROM airports WHERE country  =  'Canada') AND src_apid IN (SELECT apid FROM airports WHERE country  =  'United States')

Question: Find the id of routes whose source and destination airports are in the United States.
SQL: SELECT rid FROM routes WHERE dst_apid IN (SELECT apid FROM airports WHERE country  =  'United States') AND src_apid IN (SELECT apid FROM airports WHERE country  =  'United States')

Question: What is the id of the routes whose source and destination airports are in the United States?
SQL: SELECT rid FROM routes WHERE dst_apid IN (SELECT apid FROM airports WHERE country  =  'United States') AND src_apid IN (SELECT apid FROM airports WHERE country  =  'United States')

Question: Find the name of airline which runs the most number of routes.
SQL: SELECT T1.name FROM airlines AS T1 JOIN routes AS T2 ON T1.alid  =  T2.alid GROUP BY T1.name ORDER BY count(*) DESC LIMIT 1