sert121/SpiderSQL huggingface dataset:
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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)
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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)
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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(*)
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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(*)
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Find the average elevation of all airports for each country.
SQL: SELECT avg(elevation) ,  country FROM airports GROUP BY country
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: For each country, what is the average elevation of that country's airports?
SQL: SELECT avg(elevation) ,  country FROM airports GROUP BY country
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Find the cities which have exactly two airports.
SQL: SELECT city FROM airports GROUP BY city HAVING count(*)  =  2
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are the cities with exactly two airports?
SQL: SELECT city FROM airports GROUP BY city HAVING count(*)  =  2
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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'
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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'
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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'
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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'
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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'
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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'
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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')
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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')
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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')
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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')
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What is the name of the airline with the most 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
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Find the busiest source airport that runs most number of routes in China.
SQL: SELECT T1.name FROM airports AS T1 JOIN routes AS T2 ON T1.apid  =  T2.src_apid WHERE T1.country  =  'China' GROUP BY T1.name ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What is the name of the airport with the most number of routes that start in China?
SQL: SELECT T1.name FROM airports AS T1 JOIN routes AS T2 ON T1.apid  =  T2.src_apid WHERE T1.country  =  'China' GROUP BY T1.name ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Find the busiest destination airport that runs most number of routes in China.
SQL: SELECT T1.name FROM airports AS T1 JOIN routes AS T2 ON T1.apid  =  T2.dst_apid WHERE T1.country  =  'China' GROUP BY T1.name ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: routes 
columns : [['route id', 'number'], ['destination airport id', 'number'], ['destination airport', 'text'], ['source airport id', 'number'], ['source airport', 'text'], ['airline id', 'number'], ['airline', 'text'], ['code share', 'text']]

 -- Table: airports 
columns : [['airport id', 'number'], ['name', 'text'], ['city', 'text'], ['country', 'text'], ['x', 'number'], ['y', 'number'], ['elevation', 'number'], ['iata', 'text'], ['icao', 'text']]

 -- Table: airlines 
columns : [['airline id', 'number'], ['name', 'text'], ['iata', 'text'], ['icao', 'text'], ['call sign', 'text'], ['country', 'text'], ['active', 'text']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What is the name of the airport that is the destination of the most number of routes that start in China?
SQL: SELECT T1.name FROM airports AS T1 JOIN routes AS T2 ON T1.apid  =  T2.dst_apid WHERE T1.country  =  'China' GROUP BY T1.name ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: customers 
columns : [['customer id', 'number'], ['customer name', 'text'], ['customer details', 'text']]

 -- Table: invoices 
columns : [['invoice number', 'number'], ['invoice date', 'time'], ['invoice details', 'text']]

 -- Table: orders 
columns : [['order id', 'number'], ['customer id', 'number'], ['order status', 'text'], ['date order placed', 'time'], ['order details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['product name', 'text'], ['product details', 'text']]

 -- Table: order items 
columns : [['order item id', 'number'], ['product id', 'number'], ['order id', 'number'], ['order item status', 'text'], ['order item details', 'text']]

 -- Table: shipments 
columns : [['shipment id', 'number'], ['order id', 'number'], ['invoice number', 'number'], ['shipment tracking number', 'text'], ['shipment date', 'time'], ['other shipment details', 'text']]

 -- Table: shipment items 
columns : [['shipment id', 'number'], ['order item id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What is the id of the most recent order?
SQL: SELECT order_id FROM orders ORDER BY date_order_placed DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: customers 
columns : [['customer id', 'number'], ['customer name', 'text'], ['customer details', 'text']]

 -- Table: invoices 
columns : [['invoice number', 'number'], ['invoice date', 'time'], ['invoice details', 'text']]

 -- Table: orders 
columns : [['order id', 'number'], ['customer id', 'number'], ['order status', 'text'], ['date order placed', 'time'], ['order details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['product name', 'text'], ['product details', 'text']]

 -- Table: order items 
columns : [['order item id', 'number'], ['product id', 'number'], ['order id', 'number'], ['order item status', 'text'], ['order item details', 'text']]

 -- Table: shipments 
columns : [['shipment id', 'number'], ['order id', 'number'], ['invoice number', 'number'], ['shipment tracking number', 'text'], ['shipment date', 'time'], ['other shipment details', 'text']]

 -- Table: shipment items 
columns : [['shipment id', 'number'], ['order item id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Find the id of the order made most recently.
SQL: SELECT order_id FROM orders ORDER BY date_order_placed DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: customers 
columns : [['customer id', 'number'], ['customer name', 'text'], ['customer details', 'text']]

 -- Table: invoices 
columns : [['invoice number', 'number'], ['invoice date', 'time'], ['invoice details', 'text']]

 -- Table: orders 
columns : [['order id', 'number'], ['customer id', 'number'], ['order status', 'text'], ['date order placed', 'time'], ['order details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['product name', 'text'], ['product details', 'text']]

 -- Table: order items 
columns : [['order item id', 'number'], ['product id', 'number'], ['order id', 'number'], ['order item status', 'text'], ['order item details', 'text']]

 -- Table: shipments 
columns : [['shipment id', 'number'], ['order id', 'number'], ['invoice number', 'number'], ['shipment tracking number', 'text'], ['shipment date', 'time'], ['other shipment details', 'text']]

 -- Table: shipment items 
columns : [['shipment id', 'number'], ['order item id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: what are the order id and customer id of the oldest order?
SQL: SELECT order_id ,  customer_id FROM orders ORDER BY date_order_placed LIMIT 1