lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE routes (
   rid integer PRIMARY KEY,
   dst_apid integer,              -- Id of destination airport
   dst_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the destination airport
   src_apid bigint,               -- Id of source airport
   src_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the source airport
   alid bigint,                   -- Id of airline
   airline varchar(4),            -- 2-letter (IATA) or 3-letter (ICAO) code of the airline
   codeshare text,                -- "Y" if this flight is a codeshare (that is, not operated by 
                                  -- Airline, but another carrier), empty otherwise
   FOREIGN KEY(dst_apid) REFERENCES airports(apid),
   FOREIGN KEY(src_apid) REFERENCES airports(apid),
   FOREIGN KEY(alid)     REFERENCES airlines(alid)
)

CREATE TABLE airports (
   apid integer PRIMARY KEY,      -- Id of the airport
   name text NOT NULL,            -- Name of airport
   city text,                     -- Main city served by airport
   country text,                  -- Country or territory where airport is located
   x real,                        -- Latitude of airport: Decimal degrees, usually to six
                                  -- significant digits. Negative is South, positive is North
   y real,                        -- Longitude of airport: Decimal degrees, usually to six 
                                  -- significant digits. Negative is West, positive is East
   elevation bigint,              -- Altitude of airport measured in feets
   iata character varchar(3),     -- 3-letter IATA code. empty or null if not assigned/unknown
   icao character varchar(4)      -- 4-letter ICAO code. empty or null if not assigned
   
)

CREATE TABLE airlines (
   alid integer PRIMARY KEY,      -- Id of the airline
   name text,                     -- Name of the airline
   iata varchar(2),               -- 2-letter IATA code. empty or null if not assigned/unknown 
   icao varchar(3),               -- 3-letter ICAO code. empty or null if not assigned
   callsign text,                 -- Airline callsign
   country text,                  -- Country or territory where airline is incorporated
   active varchar(2)              -- "Y" if the airline is or has until recently been operational,
)

### 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
### Database Schema
CREATE TABLE routes (
   rid integer PRIMARY KEY,
   dst_apid integer,              -- Id of destination airport
   dst_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the destination airport
   src_apid bigint,               -- Id of source airport
   src_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the source airport
   alid bigint,                   -- Id of airline
   airline varchar(4),            -- 2-letter (IATA) or 3-letter (ICAO) code of the airline
   codeshare text,                -- "Y" if this flight is a codeshare (that is, not operated by 
                                  -- Airline, but another carrier), empty otherwise
   FOREIGN KEY(dst_apid) REFERENCES airports(apid),
   FOREIGN KEY(src_apid) REFERENCES airports(apid),
   FOREIGN KEY(alid)     REFERENCES airlines(alid)
)

CREATE TABLE airports (
   apid integer PRIMARY KEY,      -- Id of the airport
   name text NOT NULL,            -- Name of airport
   city text,                     -- Main city served by airport
   country text,                  -- Country or territory where airport is located
   x real,                        -- Latitude of airport: Decimal degrees, usually to six
                                  -- significant digits. Negative is South, positive is North
   y real,                        -- Longitude of airport: Decimal degrees, usually to six 
                                  -- significant digits. Negative is West, positive is East
   elevation bigint,              -- Altitude of airport measured in feets
   iata character varchar(3),     -- 3-letter IATA code. empty or null if not assigned/unknown
   icao character varchar(4)      -- 4-letter ICAO code. empty or null if not assigned
   
)

CREATE TABLE airlines (
   alid integer PRIMARY KEY,      -- Id of the airline
   name text,                     -- Name of the airline
   iata varchar(2),               -- 2-letter IATA code. empty or null if not assigned/unknown 
   icao varchar(3),               -- 3-letter ICAO code. empty or null if not assigned
   callsign text,                 -- Airline callsign
   country text,                  -- Country or territory where airline is incorporated
   active varchar(2)              -- "Y" if the airline is or has until recently been operational,
)

### 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
### Database Schema
CREATE TABLE routes (
   rid integer PRIMARY KEY,
   dst_apid integer,              -- Id of destination airport
   dst_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the destination airport
   src_apid bigint,               -- Id of source airport
   src_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the source airport
   alid bigint,                   -- Id of airline
   airline varchar(4),            -- 2-letter (IATA) or 3-letter (ICAO) code of the airline
   codeshare text,                -- "Y" if this flight is a codeshare (that is, not operated by 
                                  -- Airline, but another carrier), empty otherwise
   FOREIGN KEY(dst_apid) REFERENCES airports(apid),
   FOREIGN KEY(src_apid) REFERENCES airports(apid),
   FOREIGN KEY(alid)     REFERENCES airlines(alid)
)

CREATE TABLE airports (
   apid integer PRIMARY KEY,      -- Id of the airport
   name text NOT NULL,            -- Name of airport
   city text,                     -- Main city served by airport
   country text,                  -- Country or territory where airport is located
   x real,                        -- Latitude of airport: Decimal degrees, usually to six
                                  -- significant digits. Negative is South, positive is North
   y real,                        -- Longitude of airport: Decimal degrees, usually to six 
                                  -- significant digits. Negative is West, positive is East
   elevation bigint,              -- Altitude of airport measured in feets
   iata character varchar(3),     -- 3-letter IATA code. empty or null if not assigned/unknown
   icao character varchar(4)      -- 4-letter ICAO code. empty or null if not assigned
   
)

CREATE TABLE airlines (
   alid integer PRIMARY KEY,      -- Id of the airline
   name text,                     -- Name of the airline
   iata varchar(2),               -- 2-letter IATA code. empty or null if not assigned/unknown 
   icao varchar(3),               -- 3-letter ICAO code. empty or null if not assigned
   callsign text,                 -- Airline callsign
   country text,                  -- Country or territory where airline is incorporated
   active varchar(2)              -- "Y" if the airline is or has until recently been operational,
)

### 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
### Database Schema
CREATE TABLE routes (
   rid integer PRIMARY KEY,
   dst_apid integer,              -- Id of destination airport
   dst_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the destination airport
   src_apid bigint,               -- Id of source airport
   src_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the source airport
   alid bigint,                   -- Id of airline
   airline varchar(4),            -- 2-letter (IATA) or 3-letter (ICAO) code of the airline
   codeshare text,                -- "Y" if this flight is a codeshare (that is, not operated by 
                                  -- Airline, but another carrier), empty otherwise
   FOREIGN KEY(dst_apid) REFERENCES airports(apid),
   FOREIGN KEY(src_apid) REFERENCES airports(apid),
   FOREIGN KEY(alid)     REFERENCES airlines(alid)
)

CREATE TABLE airports (
   apid integer PRIMARY KEY,      -- Id of the airport
   name text NOT NULL,            -- Name of airport
   city text,                     -- Main city served by airport
   country text,                  -- Country or territory where airport is located
   x real,                        -- Latitude of airport: Decimal degrees, usually to six
                                  -- significant digits. Negative is South, positive is North
   y real,                        -- Longitude of airport: Decimal degrees, usually to six 
                                  -- significant digits. Negative is West, positive is East
   elevation bigint,              -- Altitude of airport measured in feets
   iata character varchar(3),     -- 3-letter IATA code. empty or null if not assigned/unknown
   icao character varchar(4)      -- 4-letter ICAO code. empty or null if not assigned
   
)

CREATE TABLE airlines (
   alid integer PRIMARY KEY,      -- Id of the airline
   name text,                     -- Name of the airline
   iata varchar(2),               -- 2-letter IATA code. empty or null if not assigned/unknown 
   icao varchar(3),               -- 3-letter ICAO code. empty or null if not assigned
   callsign text,                 -- Airline callsign
   country text,                  -- Country or territory where airline is incorporated
   active varchar(2)              -- "Y" if the airline is or has until recently been operational,
)

### Question
Find the average elevation of all airports for each country.

### SQL
SELECT avg(elevation) ,  country FROM airports GROUP BY country
### Database Schema
CREATE TABLE routes (
   rid integer PRIMARY KEY,
   dst_apid integer,              -- Id of destination airport
   dst_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the destination airport
   src_apid bigint,               -- Id of source airport
   src_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the source airport
   alid bigint,                   -- Id of airline
   airline varchar(4),            -- 2-letter (IATA) or 3-letter (ICAO) code of the airline
   codeshare text,                -- "Y" if this flight is a codeshare (that is, not operated by 
                                  -- Airline, but another carrier), empty otherwise
   FOREIGN KEY(dst_apid) REFERENCES airports(apid),
   FOREIGN KEY(src_apid) REFERENCES airports(apid),
   FOREIGN KEY(alid)     REFERENCES airlines(alid)
)

CREATE TABLE airports (
   apid integer PRIMARY KEY,      -- Id of the airport
   name text NOT NULL,            -- Name of airport
   city text,                     -- Main city served by airport
   country text,                  -- Country or territory where airport is located
   x real,                        -- Latitude of airport: Decimal degrees, usually to six
                                  -- significant digits. Negative is South, positive is North
   y real,                        -- Longitude of airport: Decimal degrees, usually to six 
                                  -- significant digits. Negative is West, positive is East
   elevation bigint,              -- Altitude of airport measured in feets
   iata character varchar(3),     -- 3-letter IATA code. empty or null if not assigned/unknown
   icao character varchar(4)      -- 4-letter ICAO code. empty or null if not assigned
   
)

CREATE TABLE airlines (
   alid integer PRIMARY KEY,      -- Id of the airline
   name text,                     -- Name of the airline
   iata varchar(2),               -- 2-letter IATA code. empty or null if not assigned/unknown 
   icao varchar(3),               -- 3-letter ICAO code. empty or null if not assigned
   callsign text,                 -- Airline callsign
   country text,                  -- Country or territory where airline is incorporated
   active varchar(2)              -- "Y" if the airline is or has until recently been operational,
)

### Question
For each country, what is the average elevation of that country's airports?

### SQL
SELECT avg(elevation) ,  country FROM airports GROUP BY country
### Database Schema
CREATE TABLE routes (
   rid integer PRIMARY KEY,
   dst_apid integer,              -- Id of destination airport
   dst_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the destination airport
   src_apid bigint,               -- Id of source airport
   src_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the source airport
   alid bigint,                   -- Id of airline
   airline varchar(4),            -- 2-letter (IATA) or 3-letter (ICAO) code of the airline
   codeshare text,                -- "Y" if this flight is a codeshare (that is, not operated by 
                                  -- Airline, but another carrier), empty otherwise
   FOREIGN KEY(dst_apid) REFERENCES airports(apid),
   FOREIGN KEY(src_apid) REFERENCES airports(apid),
   FOREIGN KEY(alid)     REFERENCES airlines(alid)
)

CREATE TABLE airports (
   apid integer PRIMARY KEY,      -- Id of the airport
   name text NOT NULL,            -- Name of airport
   city text,                     -- Main city served by airport
   country text,                  -- Country or territory where airport is located
   x real,                        -- Latitude of airport: Decimal degrees, usually to six
                                  -- significant digits. Negative is South, positive is North
   y real,                        -- Longitude of airport: Decimal degrees, usually to six 
                                  -- significant digits. Negative is West, positive is East
   elevation bigint,              -- Altitude of airport measured in feets
   iata character varchar(3),     -- 3-letter IATA code. empty or null if not assigned/unknown
   icao character varchar(4)      -- 4-letter ICAO code. empty or null if not assigned
   
)

CREATE TABLE airlines (
   alid integer PRIMARY KEY,      -- Id of the airline
   name text,                     -- Name of the airline
   iata varchar(2),               -- 2-letter IATA code. empty or null if not assigned/unknown 
   icao varchar(3),               -- 3-letter ICAO code. empty or null if not assigned
   callsign text,                 -- Airline callsign
   country text,                  -- Country or territory where airline is incorporated
   active varchar(2)              -- "Y" if the airline is or has until recently been operational,
)

### Question
Find the cities which have exactly two airports.

### SQL
SELECT city FROM airports GROUP BY city HAVING count(*)  =  2
### Database Schema
CREATE TABLE routes (
   rid integer PRIMARY KEY,
   dst_apid integer,              -- Id of destination airport
   dst_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the destination airport
   src_apid bigint,               -- Id of source airport
   src_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the source airport
   alid bigint,                   -- Id of airline
   airline varchar(4),            -- 2-letter (IATA) or 3-letter (ICAO) code of the airline
   codeshare text,                -- "Y" if this flight is a codeshare (that is, not operated by 
                                  -- Airline, but another carrier), empty otherwise
   FOREIGN KEY(dst_apid) REFERENCES airports(apid),
   FOREIGN KEY(src_apid) REFERENCES airports(apid),
   FOREIGN KEY(alid)     REFERENCES airlines(alid)
)

CREATE TABLE airports (
   apid integer PRIMARY KEY,      -- Id of the airport
   name text NOT NULL,            -- Name of airport
   city text,                     -- Main city served by airport
   country text,                  -- Country or territory where airport is located
   x real,                        -- Latitude of airport: Decimal degrees, usually to six
                                  -- significant digits. Negative is South, positive is North
   y real,                        -- Longitude of airport: Decimal degrees, usually to six 
                                  -- significant digits. Negative is West, positive is East
   elevation bigint,              -- Altitude of airport measured in feets
   iata character varchar(3),     -- 3-letter IATA code. empty or null if not assigned/unknown
   icao character varchar(4)      -- 4-letter ICAO code. empty or null if not assigned
   
)

CREATE TABLE airlines (
   alid integer PRIMARY KEY,      -- Id of the airline
   name text,                     -- Name of the airline
   iata varchar(2),               -- 2-letter IATA code. empty or null if not assigned/unknown 
   icao varchar(3),               -- 3-letter ICAO code. empty or null if not assigned
   callsign text,                 -- Airline callsign
   country text,                  -- Country or territory where airline is incorporated
   active varchar(2)              -- "Y" if the airline is or has until recently been operational,
)

### Question
What are the cities with exactly two airports?

### SQL
SELECT city FROM airports GROUP BY city HAVING count(*)  =  2
### Database Schema
CREATE TABLE routes (
   rid integer PRIMARY KEY,
   dst_apid integer,              -- Id of destination airport
   dst_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the destination airport
   src_apid bigint,               -- Id of source airport
   src_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the source airport
   alid bigint,                   -- Id of airline
   airline varchar(4),            -- 2-letter (IATA) or 3-letter (ICAO) code of the airline
   codeshare text,                -- "Y" if this flight is a codeshare (that is, not operated by 
                                  -- Airline, but another carrier), empty otherwise
   FOREIGN KEY(dst_apid) REFERENCES airports(apid),
   FOREIGN KEY(src_apid) REFERENCES airports(apid),
   FOREIGN KEY(alid)     REFERENCES airlines(alid)
)

CREATE TABLE airports (
   apid integer PRIMARY KEY,      -- Id of the airport
   name text NOT NULL,            -- Name of airport
   city text,                     -- Main city served by airport
   country text,                  -- Country or territory where airport is located
   x real,                        -- Latitude of airport: Decimal degrees, usually to six
                                  -- significant digits. Negative is South, positive is North
   y real,                        -- Longitude of airport: Decimal degrees, usually to six 
                                  -- significant digits. Negative is West, positive is East
   elevation bigint,              -- Altitude of airport measured in feets
   iata character varchar(3),     -- 3-letter IATA code. empty or null if not assigned/unknown
   icao character varchar(4)      -- 4-letter ICAO code. empty or null if not assigned
   
)

CREATE TABLE airlines (
   alid integer PRIMARY KEY,      -- Id of the airline
   name text,                     -- Name of the airline
   iata varchar(2),               -- 2-letter IATA code. empty or null if not assigned/unknown 
   icao varchar(3),               -- 3-letter ICAO code. empty or null if not assigned
   callsign text,                 -- Airline callsign
   country text,                  -- Country or territory where airline is incorporated
   active varchar(2)              -- "Y" if the airline is or has until recently been operational,
)

### 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
### Database Schema
CREATE TABLE routes (
   rid integer PRIMARY KEY,
   dst_apid integer,              -- Id of destination airport
   dst_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the destination airport
   src_apid bigint,               -- Id of source airport
   src_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the source airport
   alid bigint,                   -- Id of airline
   airline varchar(4),            -- 2-letter (IATA) or 3-letter (ICAO) code of the airline
   codeshare text,                -- "Y" if this flight is a codeshare (that is, not operated by 
                                  -- Airline, but another carrier), empty otherwise
   FOREIGN KEY(dst_apid) REFERENCES airports(apid),
   FOREIGN KEY(src_apid) REFERENCES airports(apid),
   FOREIGN KEY(alid)     REFERENCES airlines(alid)
)

CREATE TABLE airports (
   apid integer PRIMARY KEY,      -- Id of the airport
   name text NOT NULL,            -- Name of airport
   city text,                     -- Main city served by airport
   country text,                  -- Country or territory where airport is located
   x real,                        -- Latitude of airport: Decimal degrees, usually to six
                                  -- significant digits. Negative is South, positive is North
   y real,                        -- Longitude of airport: Decimal degrees, usually to six 
                                  -- significant digits. Negative is West, positive is East
   elevation bigint,              -- Altitude of airport measured in feets
   iata character varchar(3),     -- 3-letter IATA code. empty or null if not assigned/unknown
   icao character varchar(4)      -- 4-letter ICAO code. empty or null if not assigned
   
)

CREATE TABLE airlines (
   alid integer PRIMARY KEY,      -- Id of the airline
   name text,                     -- Name of the airline
   iata varchar(2),               -- 2-letter IATA code. empty or null if not assigned/unknown 
   icao varchar(3),               -- 3-letter ICAO code. empty or null if not assigned
   callsign text,                 -- Airline callsign
   country text,                  -- Country or territory where airline is incorporated
   active varchar(2)              -- "Y" if the airline is or has until recently been operational,
)

### 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
### Database Schema
CREATE TABLE routes (
   rid integer PRIMARY KEY,
   dst_apid integer,              -- Id of destination airport
   dst_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the destination airport
   src_apid bigint,               -- Id of source airport
   src_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the source airport
   alid bigint,                   -- Id of airline
   airline varchar(4),            -- 2-letter (IATA) or 3-letter (ICAO) code of the airline
   codeshare text,                -- "Y" if this flight is a codeshare (that is, not operated by 
                                  -- Airline, but another carrier), empty otherwise
   FOREIGN KEY(dst_apid) REFERENCES airports(apid),
   FOREIGN KEY(src_apid) REFERENCES airports(apid),
   FOREIGN KEY(alid)     REFERENCES airlines(alid)
)

CREATE TABLE airports (
   apid integer PRIMARY KEY,      -- Id of the airport
   name text NOT NULL,            -- Name of airport
   city text,                     -- Main city served by airport
   country text,                  -- Country or territory where airport is located
   x real,                        -- Latitude of airport: Decimal degrees, usually to six
                                  -- significant digits. Negative is South, positive is North
   y real,                        -- Longitude of airport: Decimal degrees, usually to six 
                                  -- significant digits. Negative is West, positive is East
   elevation bigint,              -- Altitude of airport measured in feets
   iata character varchar(3),     -- 3-letter IATA code. empty or null if not assigned/unknown
   icao character varchar(4)      -- 4-letter ICAO code. empty or null if not assigned
   
)

CREATE TABLE airlines (
   alid integer PRIMARY KEY,      -- Id of the airline
   name text,                     -- Name of the airline
   iata varchar(2),               -- 2-letter IATA code. empty or null if not assigned/unknown 
   icao varchar(3),               -- 3-letter ICAO code. empty or null if not assigned
   callsign text,                 -- Airline callsign
   country text,                  -- Country or territory where airline is incorporated
   active varchar(2)              -- "Y" if the airline is or has until recently been operational,
)

### 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'
### Database Schema
CREATE TABLE routes (
   rid integer PRIMARY KEY,
   dst_apid integer,              -- Id of destination airport
   dst_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the destination airport
   src_apid bigint,               -- Id of source airport
   src_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the source airport
   alid bigint,                   -- Id of airline
   airline varchar(4),            -- 2-letter (IATA) or 3-letter (ICAO) code of the airline
   codeshare text,                -- "Y" if this flight is a codeshare (that is, not operated by 
                                  -- Airline, but another carrier), empty otherwise
   FOREIGN KEY(dst_apid) REFERENCES airports(apid),
   FOREIGN KEY(src_apid) REFERENCES airports(apid),
   FOREIGN KEY(alid)     REFERENCES airlines(alid)
)

CREATE TABLE airports (
   apid integer PRIMARY KEY,      -- Id of the airport
   name text NOT NULL,            -- Name of airport
   city text,                     -- Main city served by airport
   country text,                  -- Country or territory where airport is located
   x real,                        -- Latitude of airport: Decimal degrees, usually to six
                                  -- significant digits. Negative is South, positive is North
   y real,                        -- Longitude of airport: Decimal degrees, usually to six 
                                  -- significant digits. Negative is West, positive is East
   elevation bigint,              -- Altitude of airport measured in feets
   iata character varchar(3),     -- 3-letter IATA code. empty or null if not assigned/unknown
   icao character varchar(4)      -- 4-letter ICAO code. empty or null if not assigned
   
)

CREATE TABLE airlines (
   alid integer PRIMARY KEY,      -- Id of the airline
   name text,                     -- Name of the airline
   iata varchar(2),               -- 2-letter IATA code. empty or null if not assigned/unknown 
   icao varchar(3),               -- 3-letter ICAO code. empty or null if not assigned
   callsign text,                 -- Airline callsign
   country text,                  -- Country or territory where airline is incorporated
   active varchar(2)              -- "Y" if the airline is or has until recently been operational,
)

### 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'
### Database Schema
CREATE TABLE routes (
   rid integer PRIMARY KEY,
   dst_apid integer,              -- Id of destination airport
   dst_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the destination airport
   src_apid bigint,               -- Id of source airport
   src_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the source airport
   alid bigint,                   -- Id of airline
   airline varchar(4),            -- 2-letter (IATA) or 3-letter (ICAO) code of the airline
   codeshare text,                -- "Y" if this flight is a codeshare (that is, not operated by 
                                  -- Airline, but another carrier), empty otherwise
   FOREIGN KEY(dst_apid) REFERENCES airports(apid),
   FOREIGN KEY(src_apid) REFERENCES airports(apid),
   FOREIGN KEY(alid)     REFERENCES airlines(alid)
)

CREATE TABLE airports (
   apid integer PRIMARY KEY,      -- Id of the airport
   name text NOT NULL,            -- Name of airport
   city text,                     -- Main city served by airport
   country text,                  -- Country or territory where airport is located
   x real,                        -- Latitude of airport: Decimal degrees, usually to six
                                  -- significant digits. Negative is South, positive is North
   y real,                        -- Longitude of airport: Decimal degrees, usually to six 
                                  -- significant digits. Negative is West, positive is East
   elevation bigint,              -- Altitude of airport measured in feets
   iata character varchar(3),     -- 3-letter IATA code. empty or null if not assigned/unknown
   icao character varchar(4)      -- 4-letter ICAO code. empty or null if not assigned
   
)

CREATE TABLE airlines (
   alid integer PRIMARY KEY,      -- Id of the airline
   name text,                     -- Name of the airline
   iata varchar(2),               -- 2-letter IATA code. empty or null if not assigned/unknown 
   icao varchar(3),               -- 3-letter ICAO code. empty or null if not assigned
   callsign text,                 -- Airline callsign
   country text,                  -- Country or territory where airline is incorporated
   active varchar(2)              -- "Y" if the airline is or has until recently been operational,
)

### 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'
### Database Schema
CREATE TABLE routes (
   rid integer PRIMARY KEY,
   dst_apid integer,              -- Id of destination airport
   dst_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the destination airport
   src_apid bigint,               -- Id of source airport
   src_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the source airport
   alid bigint,                   -- Id of airline
   airline varchar(4),            -- 2-letter (IATA) or 3-letter (ICAO) code of the airline
   codeshare text,                -- "Y" if this flight is a codeshare (that is, not operated by 
                                  -- Airline, but another carrier), empty otherwise
   FOREIGN KEY(dst_apid) REFERENCES airports(apid),
   FOREIGN KEY(src_apid) REFERENCES airports(apid),
   FOREIGN KEY(alid)     REFERENCES airlines(alid)
)

CREATE TABLE airports (
   apid integer PRIMARY KEY,      -- Id of the airport
   name text NOT NULL,            -- Name of airport
   city text,                     -- Main city served by airport
   country text,                  -- Country or territory where airport is located
   x real,                        -- Latitude of airport: Decimal degrees, usually to six
                                  -- significant digits. Negative is South, positive is North
   y real,                        -- Longitude of airport: Decimal degrees, usually to six 
                                  -- significant digits. Negative is West, positive is East
   elevation bigint,              -- Altitude of airport measured in feets
   iata character varchar(3),     -- 3-letter IATA code. empty or null if not assigned/unknown
   icao character varchar(4)      -- 4-letter ICAO code. empty or null if not assigned
   
)

CREATE TABLE airlines (
   alid integer PRIMARY KEY,      -- Id of the airline
   name text,                     -- Name of the airline
   iata varchar(2),               -- 2-letter IATA code. empty or null if not assigned/unknown 
   icao varchar(3),               -- 3-letter ICAO code. empty or null if not assigned
   callsign text,                 -- Airline callsign
   country text,                  -- Country or territory where airline is incorporated
   active varchar(2)              -- "Y" if the airline is or has until recently been operational,
)

### 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'
### Database Schema
CREATE TABLE routes (
   rid integer PRIMARY KEY,
   dst_apid integer,              -- Id of destination airport
   dst_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the destination airport
   src_apid bigint,               -- Id of source airport
   src_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the source airport
   alid bigint,                   -- Id of airline
   airline varchar(4),            -- 2-letter (IATA) or 3-letter (ICAO) code of the airline
   codeshare text,                -- "Y" if this flight is a codeshare (that is, not operated by 
                                  -- Airline, but another carrier), empty otherwise
   FOREIGN KEY(dst_apid) REFERENCES airports(apid),
   FOREIGN KEY(src_apid) REFERENCES airports(apid),
   FOREIGN KEY(alid)     REFERENCES airlines(alid)
)

CREATE TABLE airports (
   apid integer PRIMARY KEY,      -- Id of the airport
   name text NOT NULL,            -- Name of airport
   city text,                     -- Main city served by airport
   country text,                  -- Country or territory where airport is located
   x real,                        -- Latitude of airport: Decimal degrees, usually to six
                                  -- significant digits. Negative is South, positive is North
   y real,                        -- Longitude of airport: Decimal degrees, usually to six 
                                  -- significant digits. Negative is West, positive is East
   elevation bigint,              -- Altitude of airport measured in feets
   iata character varchar(3),     -- 3-letter IATA code. empty or null if not assigned/unknown
   icao character varchar(4)      -- 4-letter ICAO code. empty or null if not assigned
   
)

CREATE TABLE airlines (
   alid integer PRIMARY KEY,      -- Id of the airline
   name text,                     -- Name of the airline
   iata varchar(2),               -- 2-letter IATA code. empty or null if not assigned/unknown 
   icao varchar(3),               -- 3-letter ICAO code. empty or null if not assigned
   callsign text,                 -- Airline callsign
   country text,                  -- Country or territory where airline is incorporated
   active varchar(2)              -- "Y" if the airline is or has until recently been operational,
)

### 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'
### Database Schema
CREATE TABLE routes (
   rid integer PRIMARY KEY,
   dst_apid integer,              -- Id of destination airport
   dst_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the destination airport
   src_apid bigint,               -- Id of source airport
   src_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the source airport
   alid bigint,                   -- Id of airline
   airline varchar(4),            -- 2-letter (IATA) or 3-letter (ICAO) code of the airline
   codeshare text,                -- "Y" if this flight is a codeshare (that is, not operated by 
                                  -- Airline, but another carrier), empty otherwise
   FOREIGN KEY(dst_apid) REFERENCES airports(apid),
   FOREIGN KEY(src_apid) REFERENCES airports(apid),
   FOREIGN KEY(alid)     REFERENCES airlines(alid)
)

CREATE TABLE airports (
   apid integer PRIMARY KEY,      -- Id of the airport
   name text NOT NULL,            -- Name of airport
   city text,                     -- Main city served by airport
   country text,                  -- Country or territory where airport is located
   x real,                        -- Latitude of airport: Decimal degrees, usually to six
                                  -- significant digits. Negative is South, positive is North
   y real,                        -- Longitude of airport: Decimal degrees, usually to six 
                                  -- significant digits. Negative is West, positive is East
   elevation bigint,              -- Altitude of airport measured in feets
   iata character varchar(3),     -- 3-letter IATA code. empty or null if not assigned/unknown
   icao character varchar(4)      -- 4-letter ICAO code. empty or null if not assigned
   
)

CREATE TABLE airlines (
   alid integer PRIMARY KEY,      -- Id of the airline
   name text,                     -- Name of the airline
   iata varchar(2),               -- 2-letter IATA code. empty or null if not assigned/unknown 
   icao varchar(3),               -- 3-letter ICAO code. empty or null if not assigned
   callsign text,                 -- Airline callsign
   country text,                  -- Country or territory where airline is incorporated
   active varchar(2)              -- "Y" if the airline is or has until recently been operational,
)

### 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'
### Database Schema
CREATE TABLE routes (
   rid integer PRIMARY KEY,
   dst_apid integer,              -- Id of destination airport
   dst_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the destination airport
   src_apid bigint,               -- Id of source airport
   src_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the source airport
   alid bigint,                   -- Id of airline
   airline varchar(4),            -- 2-letter (IATA) or 3-letter (ICAO) code of the airline
   codeshare text,                -- "Y" if this flight is a codeshare (that is, not operated by 
                                  -- Airline, but another carrier), empty otherwise
   FOREIGN KEY(dst_apid) REFERENCES airports(apid),
   FOREIGN KEY(src_apid) REFERENCES airports(apid),
   FOREIGN KEY(alid)     REFERENCES airlines(alid)
)

CREATE TABLE airports (
   apid integer PRIMARY KEY,      -- Id of the airport
   name text NOT NULL,            -- Name of airport
   city text,                     -- Main city served by airport
   country text,                  -- Country or territory where airport is located
   x real,                        -- Latitude of airport: Decimal degrees, usually to six
                                  -- significant digits. Negative is South, positive is North
   y real,                        -- Longitude of airport: Decimal degrees, usually to six 
                                  -- significant digits. Negative is West, positive is East
   elevation bigint,              -- Altitude of airport measured in feets
   iata character varchar(3),     -- 3-letter IATA code. empty or null if not assigned/unknown
   icao character varchar(4)      -- 4-letter ICAO code. empty or null if not assigned
   
)

CREATE TABLE airlines (
   alid integer PRIMARY KEY,      -- Id of the airline
   name text,                     -- Name of the airline
   iata varchar(2),               -- 2-letter IATA code. empty or null if not assigned/unknown 
   icao varchar(3),               -- 3-letter ICAO code. empty or null if not assigned
   callsign text,                 -- Airline callsign
   country text,                  -- Country or territory where airline is incorporated
   active varchar(2)              -- "Y" if the airline is or has until recently been operational,
)

### 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')
### Database Schema
CREATE TABLE routes (
   rid integer PRIMARY KEY,
   dst_apid integer,              -- Id of destination airport
   dst_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the destination airport
   src_apid bigint,               -- Id of source airport
   src_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the source airport
   alid bigint,                   -- Id of airline
   airline varchar(4),            -- 2-letter (IATA) or 3-letter (ICAO) code of the airline
   codeshare text,                -- "Y" if this flight is a codeshare (that is, not operated by 
                                  -- Airline, but another carrier), empty otherwise
   FOREIGN KEY(dst_apid) REFERENCES airports(apid),
   FOREIGN KEY(src_apid) REFERENCES airports(apid),
   FOREIGN KEY(alid)     REFERENCES airlines(alid)
)

CREATE TABLE airports (
   apid integer PRIMARY KEY,      -- Id of the airport
   name text NOT NULL,            -- Name of airport
   city text,                     -- Main city served by airport
   country text,                  -- Country or territory where airport is located
   x real,                        -- Latitude of airport: Decimal degrees, usually to six
                                  -- significant digits. Negative is South, positive is North
   y real,                        -- Longitude of airport: Decimal degrees, usually to six 
                                  -- significant digits. Negative is West, positive is East
   elevation bigint,              -- Altitude of airport measured in feets
   iata character varchar(3),     -- 3-letter IATA code. empty or null if not assigned/unknown
   icao character varchar(4)      -- 4-letter ICAO code. empty or null if not assigned
   
)

CREATE TABLE airlines (
   alid integer PRIMARY KEY,      -- Id of the airline
   name text,                     -- Name of the airline
   iata varchar(2),               -- 2-letter IATA code. empty or null if not assigned/unknown 
   icao varchar(3),               -- 3-letter ICAO code. empty or null if not assigned
   callsign text,                 -- Airline callsign
   country text,                  -- Country or territory where airline is incorporated
   active varchar(2)              -- "Y" if the airline is or has until recently been operational,
)

### 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')
### Database Schema
CREATE TABLE routes (
   rid integer PRIMARY KEY,
   dst_apid integer,              -- Id of destination airport
   dst_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the destination airport
   src_apid bigint,               -- Id of source airport
   src_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the source airport
   alid bigint,                   -- Id of airline
   airline varchar(4),            -- 2-letter (IATA) or 3-letter (ICAO) code of the airline
   codeshare text,                -- "Y" if this flight is a codeshare (that is, not operated by 
                                  -- Airline, but another carrier), empty otherwise
   FOREIGN KEY(dst_apid) REFERENCES airports(apid),
   FOREIGN KEY(src_apid) REFERENCES airports(apid),
   FOREIGN KEY(alid)     REFERENCES airlines(alid)
)

CREATE TABLE airports (
   apid integer PRIMARY KEY,      -- Id of the airport
   name text NOT NULL,            -- Name of airport
   city text,                     -- Main city served by airport
   country text,                  -- Country or territory where airport is located
   x real,                        -- Latitude of airport: Decimal degrees, usually to six
                                  -- significant digits. Negative is South, positive is North
   y real,                        -- Longitude of airport: Decimal degrees, usually to six 
                                  -- significant digits. Negative is West, positive is East
   elevation bigint,              -- Altitude of airport measured in feets
   iata character varchar(3),     -- 3-letter IATA code. empty or null if not assigned/unknown
   icao character varchar(4)      -- 4-letter ICAO code. empty or null if not assigned
   
)

CREATE TABLE airlines (
   alid integer PRIMARY KEY,      -- Id of the airline
   name text,                     -- Name of the airline
   iata varchar(2),               -- 2-letter IATA code. empty or null if not assigned/unknown 
   icao varchar(3),               -- 3-letter ICAO code. empty or null if not assigned
   callsign text,                 -- Airline callsign
   country text,                  -- Country or territory where airline is incorporated
   active varchar(2)              -- "Y" if the airline is or has until recently been operational,
)

### 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')
### Database Schema
CREATE TABLE routes (
   rid integer PRIMARY KEY,
   dst_apid integer,              -- Id of destination airport
   dst_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the destination airport
   src_apid bigint,               -- Id of source airport
   src_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the source airport
   alid bigint,                   -- Id of airline
   airline varchar(4),            -- 2-letter (IATA) or 3-letter (ICAO) code of the airline
   codeshare text,                -- "Y" if this flight is a codeshare (that is, not operated by 
                                  -- Airline, but another carrier), empty otherwise
   FOREIGN KEY(dst_apid) REFERENCES airports(apid),
   FOREIGN KEY(src_apid) REFERENCES airports(apid),
   FOREIGN KEY(alid)     REFERENCES airlines(alid)
)

CREATE TABLE airports (
   apid integer PRIMARY KEY,      -- Id of the airport
   name text NOT NULL,            -- Name of airport
   city text,                     -- Main city served by airport
   country text,                  -- Country or territory where airport is located
   x real,                        -- Latitude of airport: Decimal degrees, usually to six
                                  -- significant digits. Negative is South, positive is North
   y real,                        -- Longitude of airport: Decimal degrees, usually to six 
                                  -- significant digits. Negative is West, positive is East
   elevation bigint,              -- Altitude of airport measured in feets
   iata character varchar(3),     -- 3-letter IATA code. empty or null if not assigned/unknown
   icao character varchar(4)      -- 4-letter ICAO code. empty or null if not assigned
   
)

CREATE TABLE airlines (
   alid integer PRIMARY KEY,      -- Id of the airline
   name text,                     -- Name of the airline
   iata varchar(2),               -- 2-letter IATA code. empty or null if not assigned/unknown 
   icao varchar(3),               -- 3-letter ICAO code. empty or null if not assigned
   callsign text,                 -- Airline callsign
   country text,                  -- Country or territory where airline is incorporated
   active varchar(2)              -- "Y" if the airline is or has until recently been operational,
)

### 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')