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: Find the name of the airport in the city of Goroka.
SQL: SELECT name FROM airports WHERE city  =  'Goroka'

Question: What are the names of the airports in the city of Goroka?
SQL: SELECT name FROM airports WHERE city  =  'Goroka'

Question: Find the name, city, country, and altitude (or elevation) of the airports in the city of New York.
SQL: SELECT name ,  city ,  country ,  elevation FROM airports WHERE city  =  'New York'

Question: What is the name, city, country, and elevation for every airport in the city of New York?
SQL: SELECT name ,  city ,  country ,  elevation FROM airports WHERE city  =  'New York'

Question: How many airlines are there?
SQL: SELECT count(*) FROM airlines

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

Question: How many airlines does Russia has?
SQL: SELECT count(*) FROM airlines WHERE country  =  'Russia'

Question: What is the number of airlines based in Russia?
SQL: SELECT count(*) FROM airlines WHERE country  =  'Russia'

Question: What is the maximum elevation of all airports in the country of Iceland?
SQL: SELECT max(elevation) FROM airports WHERE country  =  'Iceland'

Question: What is the highest elevation of an airport in the country of Iceland?
SQL: SELECT max(elevation) FROM airports WHERE country  =  'Iceland'

Question: Find the name of the airports located in Cuba or Argentina.
SQL: SELECT name FROM airports WHERE country  =  'Cuba' OR country  =  'Argentina'

Question: What are the names of all airports in Cuba or Argentina?
SQL: SELECT name FROM airports WHERE country  =  'Cuba' OR country  =  'Argentina'

Question: Find the country of the airlines whose name starts with 'Orbit'.
SQL: SELECT country FROM airlines WHERE name LIKE 'Orbit%'

Question: What are the countries of all airlines whose names start with Orbit?
SQL: SELECT country FROM airlines WHERE name LIKE 'Orbit%'

Question: Find the name of airports whose altitude is between -50 and 50.
SQL: SELECT name FROM airports WHERE elevation BETWEEN -50 AND 50

Question: What are the names of all airports whose elevation is between -50 and 50?
SQL: SELECT name FROM airports WHERE elevation BETWEEN -50 AND 50

Question: Which country is the airport that has the highest altitude located in?
SQL: SELECT country FROM airports ORDER BY elevation DESC LIMIT 1

Question: What is the country of the airport with the highest elevation?
SQL: SELECT country FROM airports ORDER BY elevation DESC LIMIT 1

Question: Find the number of airports whose name contain the word 'International'.
SQL: SELECT count(*) FROM airports WHERE name LIKE '%International%'

Question: How many airports' names have the word Interanation in them?
SQL: SELECT count(*) FROM airports WHERE name LIKE '%International%'

Question: How many different cities do have some airport in the country of Greenland?
SQL: SELECT count(DISTINCT city) FROM airports WHERE country  =  'Greenland'

Question: In how many cities are there airports in the country of Greenland?
SQL: SELECT count(DISTINCT city) FROM airports WHERE country  =  'Greenland'

Question: Find the number of routes operated by American Airlines.
SQL: SELECT count(*) FROM airlines AS T1 JOIN routes AS T2 ON T1.alid  =  T2.alid WHERE T1.name  =  'American Airlines'

Question: How many routes does American Airlines operate?
SQL: SELECT count(*) FROM airlines AS T1 JOIN routes AS T2 ON T1.alid  =  T2.alid WHERE T1.name  =  'American Airlines'

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

Question: How many routes end in a Canadian airport?
SQL: SELECT count(*) FROM airports AS T1 JOIN routes AS T2 ON T1.apid  =  T2.dst_apid WHERE country  =  'Canada'

Question: Find the name, city, and country of the airport that has the lowest altitude.
SQL: SELECT name ,  city ,  country FROM airports ORDER BY elevation LIMIT 1

Question: What is the name, city, and country of the airport with the lowest altitude?
SQL: SELECT name ,  city ,  country FROM airports ORDER BY elevation LIMIT 1

Question: Find the name, city, and country of the airport that has the highest latitude.
SQL: SELECT name ,  city ,  country FROM airports ORDER BY elevation DESC LIMIT 1

Question: What is the name, city, and country of the airport with the highest elevation?
SQL: SELECT name ,  city ,  country FROM airports ORDER BY elevation DESC LIMIT 1

Question: Find the name and city of the airport which is the destination of the most number of routes.
SQL: SELECT T1.name ,  T1.city ,  T2.dst_apid FROM airports AS T1 JOIN routes AS T2 ON T1.apid  =  T2.dst_apid GROUP BY T2.dst_apid ORDER BY count(*) DESC LIMIT 1

Question: What is the name and city of the airport that the most routes end at?
SQL: SELECT T1.name ,  T1.city ,  T2.dst_apid FROM airports AS T1 JOIN routes AS T2 ON T1.apid  =  T2.dst_apid GROUP BY T2.dst_apid ORDER BY count(*) DESC LIMIT 1

Question: Find the names of the top 10 airlines that operate the most number of routes.
SQL: SELECT T1.name ,  T2.alid FROM airlines AS T1 JOIN routes AS T2 ON T1.alid  =  T2.alid GROUP BY T2.alid ORDER BY count(*) DESC LIMIT 10

Question: For the airline ids with the top 10 most routes operated, what are their names?
SQL: SELECT T1.name ,  T2.alid FROM airlines AS T1 JOIN routes AS T2 ON T1.alid  =  T2.alid GROUP BY T2.alid ORDER BY count(*) DESC LIMIT 10

Question: Find the name and city of the airport which is the source for the most number of flight routes.
SQL: SELECT T1.name ,  T1.city ,  T2.src_apid FROM airports AS T1 JOIN routes AS T2 ON T1.apid  =  T2.src_apid GROUP BY T2.src_apid ORDER BY count(*) DESC LIMIT 1

Question: What is the name and city of the airport from most of the routes start?
SQL: SELECT T1.name ,  T1.city ,  T2.src_apid FROM airports AS T1 JOIN routes AS T2 ON T1.apid  =  T2.src_apid GROUP BY T2.src_apid ORDER BY count(*) DESC LIMIT 1

Question: Find the number of different airports which are the destinations of the American Airlines.
SQL: SELECT count(DISTINCT dst_apid) FROM airlines AS T1 JOIN routes AS T2 ON T1.alid  =  T2.alid WHERE T1.name  =  'American Airlines'

Question: What is the number of different different airports that are destinations for American Airlines?
SQL: SELECT count(DISTINCT dst_apid) FROM airlines AS T1 JOIN routes AS T2 ON T1.alid  =  T2.alid WHERE T1.name  =  'American Airlines'

Question: Which countries has the most number of airlines?
SQL: SELECT country FROM airlines GROUP BY country ORDER BY count(*) DESC LIMIT 1

Question: What is the name of the country with the most number of home airlines?
SQL: SELECT country FROM airlines GROUP BY country ORDER BY count(*) DESC LIMIT 1

Question: Which countries has the most number of airlines whose active status is 'Y'?
SQL: SELECT country FROM airlines WHERE active  =  'Y' GROUP BY country ORDER BY count(*) DESC LIMIT 1

Question: What are the countries with the most airlines whose active status is Y?
SQL: SELECT country FROM airlines WHERE active  =  'Y' GROUP BY country ORDER BY count(*) DESC LIMIT 1