lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE "Air Carriers"
(
    Code        INTEGER
            primary key,
    Description TEXT
)

CREATE TABLE Airports
(
    Code        TEXT
            primary key,
    Description TEXT
)

CREATE TABLE Airlines
(
    FL_DATE               TEXT,
    OP_CARRIER_AIRLINE_ID INTEGER,
    TAIL_NUM              TEXT,
    OP_CARRIER_FL_NUM     INTEGER,
    ORIGIN_AIRPORT_ID     INTEGER,
    ORIGIN_AIRPORT_SEQ_ID INTEGER,
    ORIGIN_CITY_MARKET_ID INTEGER,
    ORIGIN                TEXT,
    DEST_AIRPORT_ID       INTEGER,
    DEST_AIRPORT_SEQ_ID   INTEGER,
    DEST_CITY_MARKET_ID   INTEGER,
    DEST                  TEXT,
    CRS_DEP_TIME          INTEGER,
    DEP_TIME              INTEGER,
    DEP_DELAY             INTEGER,
    DEP_DELAY_NEW         INTEGER,
    ARR_TIME              INTEGER,
    ARR_DELAY             INTEGER,
    ARR_DELAY_NEW         INTEGER,
    CANCELLED             INTEGER,
    CANCELLATION_CODE     TEXT,
    CRS_ELAPSED_TIME      INTEGER,
    ACTUAL_ELAPSED_TIME   INTEGER,
    CARRIER_DELAY         INTEGER,
    WEATHER_DELAY         INTEGER,
    NAS_DELAY             INTEGER,
    SECURITY_DELAY        INTEGER,
    LATE_AIRCRAFT_DELAY   INTEGER,
    FOREIGN KEY (ORIGIN) REFERENCES Airports(Code),
    FOREIGN KEY (DEST) REFERENCES Airports(Code),
    FOREIGN KEY (OP_CARRIER_AIRLINE_ID) REFERENCES "Air Carriers"(Code)
)

### Question
Among the flights on 2018/8/1, how many of them departed from an airport in New York?

### Hint
on 2018/8/1 refers to FL_DATE = '2018/8/1'; departed from an airport in New York refers to ORIGIN = 'JFK';

### SQL
SELECT COUNT(*) FROM Airlines WHERE FL_DATE = '2018/8/1' AND ORIGIN = 'JFK'
### Database Schema
CREATE TABLE "Air Carriers"
(
    Code        INTEGER
            primary key,
    Description TEXT
)

CREATE TABLE Airports
(
    Code        TEXT
            primary key,
    Description TEXT
)

CREATE TABLE Airlines
(
    FL_DATE               TEXT,
    OP_CARRIER_AIRLINE_ID INTEGER,
    TAIL_NUM              TEXT,
    OP_CARRIER_FL_NUM     INTEGER,
    ORIGIN_AIRPORT_ID     INTEGER,
    ORIGIN_AIRPORT_SEQ_ID INTEGER,
    ORIGIN_CITY_MARKET_ID INTEGER,
    ORIGIN                TEXT,
    DEST_AIRPORT_ID       INTEGER,
    DEST_AIRPORT_SEQ_ID   INTEGER,
    DEST_CITY_MARKET_ID   INTEGER,
    DEST                  TEXT,
    CRS_DEP_TIME          INTEGER,
    DEP_TIME              INTEGER,
    DEP_DELAY             INTEGER,
    DEP_DELAY_NEW         INTEGER,
    ARR_TIME              INTEGER,
    ARR_DELAY             INTEGER,
    ARR_DELAY_NEW         INTEGER,
    CANCELLED             INTEGER,
    CANCELLATION_CODE     TEXT,
    CRS_ELAPSED_TIME      INTEGER,
    ACTUAL_ELAPSED_TIME   INTEGER,
    CARRIER_DELAY         INTEGER,
    WEATHER_DELAY         INTEGER,
    NAS_DELAY             INTEGER,
    SECURITY_DELAY        INTEGER,
    LATE_AIRCRAFT_DELAY   INTEGER,
    FOREIGN KEY (ORIGIN) REFERENCES Airports(Code),
    FOREIGN KEY (DEST) REFERENCES Airports(Code),
    FOREIGN KEY (OP_CARRIER_AIRLINE_ID) REFERENCES "Air Carriers"(Code)
)

### Question
Please list the destination cities of all the flights that were cancelled on 2018/8/1.

### Hint
destination cities refers to DEST; cancelled refers to CANCELLED = 1; on 2018/8/1 refers to FL_DATE = '2018/8/1';

### SQL
SELECT DEST FROM Airlines WHERE FL_DATE = '2018/8/1' AND CANCELLED = 1 GROUP BY DEST
### Database Schema
CREATE TABLE "Air Carriers"
(
    Code        INTEGER
            primary key,
    Description TEXT
)

CREATE TABLE Airports
(
    Code        TEXT
            primary key,
    Description TEXT
)

CREATE TABLE Airlines
(
    FL_DATE               TEXT,
    OP_CARRIER_AIRLINE_ID INTEGER,
    TAIL_NUM              TEXT,
    OP_CARRIER_FL_NUM     INTEGER,
    ORIGIN_AIRPORT_ID     INTEGER,
    ORIGIN_AIRPORT_SEQ_ID INTEGER,
    ORIGIN_CITY_MARKET_ID INTEGER,
    ORIGIN                TEXT,
    DEST_AIRPORT_ID       INTEGER,
    DEST_AIRPORT_SEQ_ID   INTEGER,
    DEST_CITY_MARKET_ID   INTEGER,
    DEST                  TEXT,
    CRS_DEP_TIME          INTEGER,
    DEP_TIME              INTEGER,
    DEP_DELAY             INTEGER,
    DEP_DELAY_NEW         INTEGER,
    ARR_TIME              INTEGER,
    ARR_DELAY             INTEGER,
    ARR_DELAY_NEW         INTEGER,
    CANCELLED             INTEGER,
    CANCELLATION_CODE     TEXT,
    CRS_ELAPSED_TIME      INTEGER,
    ACTUAL_ELAPSED_TIME   INTEGER,
    CARRIER_DELAY         INTEGER,
    WEATHER_DELAY         INTEGER,
    NAS_DELAY             INTEGER,
    SECURITY_DELAY        INTEGER,
    LATE_AIRCRAFT_DELAY   INTEGER,
    FOREIGN KEY (ORIGIN) REFERENCES Airports(Code),
    FOREIGN KEY (DEST) REFERENCES Airports(Code),
    FOREIGN KEY (OP_CARRIER_AIRLINE_ID) REFERENCES "Air Carriers"(Code)
)

### Question
Please list the dates of the flights that were cancelled due to the most serious reason.

### Hint
dates of the flights refers to FL_DATE; cancelled refers to CANCELLED = 1; most serious reason refers to CANCELLATION_CODE = 'A';

### SQL
SELECT FL_DATE FROM Airlines WHERE CANCELLATION_CODE = 'A' GROUP BY FL_DATE
### Database Schema
CREATE TABLE "Air Carriers"
(
    Code        INTEGER
            primary key,
    Description TEXT
)

CREATE TABLE Airports
(
    Code        TEXT
            primary key,
    Description TEXT
)

CREATE TABLE Airlines
(
    FL_DATE               TEXT,
    OP_CARRIER_AIRLINE_ID INTEGER,
    TAIL_NUM              TEXT,
    OP_CARRIER_FL_NUM     INTEGER,
    ORIGIN_AIRPORT_ID     INTEGER,
    ORIGIN_AIRPORT_SEQ_ID INTEGER,
    ORIGIN_CITY_MARKET_ID INTEGER,
    ORIGIN                TEXT,
    DEST_AIRPORT_ID       INTEGER,
    DEST_AIRPORT_SEQ_ID   INTEGER,
    DEST_CITY_MARKET_ID   INTEGER,
    DEST                  TEXT,
    CRS_DEP_TIME          INTEGER,
    DEP_TIME              INTEGER,
    DEP_DELAY             INTEGER,
    DEP_DELAY_NEW         INTEGER,
    ARR_TIME              INTEGER,
    ARR_DELAY             INTEGER,
    ARR_DELAY_NEW         INTEGER,
    CANCELLED             INTEGER,
    CANCELLATION_CODE     TEXT,
    CRS_ELAPSED_TIME      INTEGER,
    ACTUAL_ELAPSED_TIME   INTEGER,
    CARRIER_DELAY         INTEGER,
    WEATHER_DELAY         INTEGER,
    NAS_DELAY             INTEGER,
    SECURITY_DELAY        INTEGER,
    LATE_AIRCRAFT_DELAY   INTEGER,
    FOREIGN KEY (ORIGIN) REFERENCES Airports(Code),
    FOREIGN KEY (DEST) REFERENCES Airports(Code),
    FOREIGN KEY (OP_CARRIER_AIRLINE_ID) REFERENCES "Air Carriers"(Code)
)

### Question
Please list the departure airports of the flights on 2018/8/1 that were delayed.

### Hint
departure airports refers ORIGIN; on 2018/8/1 refers to FL_DATE = '2018/8/1'; delayed refers to DEP_DELAY > 0;

### SQL
SELECT T1.Description FROM Airports AS T1 INNER JOIN Airlines AS T2 ON T1.Code = T2.ORIGIN WHERE T2.FL_DATE = '2018/8/1' AND T2.DEP_DELAY > 0 GROUP BY T1.Description
### Database Schema
CREATE TABLE "Air Carriers"
(
    Code        INTEGER
            primary key,
    Description TEXT
)

CREATE TABLE Airports
(
    Code        TEXT
            primary key,
    Description TEXT
)

CREATE TABLE Airlines
(
    FL_DATE               TEXT,
    OP_CARRIER_AIRLINE_ID INTEGER,
    TAIL_NUM              TEXT,
    OP_CARRIER_FL_NUM     INTEGER,
    ORIGIN_AIRPORT_ID     INTEGER,
    ORIGIN_AIRPORT_SEQ_ID INTEGER,
    ORIGIN_CITY_MARKET_ID INTEGER,
    ORIGIN                TEXT,
    DEST_AIRPORT_ID       INTEGER,
    DEST_AIRPORT_SEQ_ID   INTEGER,
    DEST_CITY_MARKET_ID   INTEGER,
    DEST                  TEXT,
    CRS_DEP_TIME          INTEGER,
    DEP_TIME              INTEGER,
    DEP_DELAY             INTEGER,
    DEP_DELAY_NEW         INTEGER,
    ARR_TIME              INTEGER,
    ARR_DELAY             INTEGER,
    ARR_DELAY_NEW         INTEGER,
    CANCELLED             INTEGER,
    CANCELLATION_CODE     TEXT,
    CRS_ELAPSED_TIME      INTEGER,
    ACTUAL_ELAPSED_TIME   INTEGER,
    CARRIER_DELAY         INTEGER,
    WEATHER_DELAY         INTEGER,
    NAS_DELAY             INTEGER,
    SECURITY_DELAY        INTEGER,
    LATE_AIRCRAFT_DELAY   INTEGER,
    FOREIGN KEY (ORIGIN) REFERENCES Airports(Code),
    FOREIGN KEY (DEST) REFERENCES Airports(Code),
    FOREIGN KEY (OP_CARRIER_AIRLINE_ID) REFERENCES "Air Carriers"(Code)
)

### Question
Among the flights on 2018/8/1, how many of them were scheduled to depart from John F. Kennedy International in New York?

### Hint
on 2018/8/1 refers to FL_DATE = '2018/8/1'; depart from refers to ORIGIN; John F. Kennedy International in New York refers to Description = 'New York, NY: John F. Kennedy International';

### SQL
SELECT COUNT(T1.Code) FROM Airports AS T1 INNER JOIN Airlines AS T2 ON T1.Code = T2.ORIGIN WHERE T2.FL_DATE = '2018/8/1' AND T1.Description = 'New York, NY: John F. Kennedy International'
### Database Schema
CREATE TABLE "Air Carriers"
(
    Code        INTEGER
            primary key,
    Description TEXT
)

CREATE TABLE Airports
(
    Code        TEXT
            primary key,
    Description TEXT
)

CREATE TABLE Airlines
(
    FL_DATE               TEXT,
    OP_CARRIER_AIRLINE_ID INTEGER,
    TAIL_NUM              TEXT,
    OP_CARRIER_FL_NUM     INTEGER,
    ORIGIN_AIRPORT_ID     INTEGER,
    ORIGIN_AIRPORT_SEQ_ID INTEGER,
    ORIGIN_CITY_MARKET_ID INTEGER,
    ORIGIN                TEXT,
    DEST_AIRPORT_ID       INTEGER,
    DEST_AIRPORT_SEQ_ID   INTEGER,
    DEST_CITY_MARKET_ID   INTEGER,
    DEST                  TEXT,
    CRS_DEP_TIME          INTEGER,
    DEP_TIME              INTEGER,
    DEP_DELAY             INTEGER,
    DEP_DELAY_NEW         INTEGER,
    ARR_TIME              INTEGER,
    ARR_DELAY             INTEGER,
    ARR_DELAY_NEW         INTEGER,
    CANCELLED             INTEGER,
    CANCELLATION_CODE     TEXT,
    CRS_ELAPSED_TIME      INTEGER,
    ACTUAL_ELAPSED_TIME   INTEGER,
    CARRIER_DELAY         INTEGER,
    WEATHER_DELAY         INTEGER,
    NAS_DELAY             INTEGER,
    SECURITY_DELAY        INTEGER,
    LATE_AIRCRAFT_DELAY   INTEGER,
    FOREIGN KEY (ORIGIN) REFERENCES Airports(Code),
    FOREIGN KEY (DEST) REFERENCES Airports(Code),
    FOREIGN KEY (OP_CARRIER_AIRLINE_ID) REFERENCES "Air Carriers"(Code)
)

### Question
For the flight on 2018/8/1 that was delayed for the longest time, which was the destination airport of this flight?

### Hint
on 2018/8/1 refers to FL_DATE = '2018/8/1';  delayed for the longest time refers to MAX(DEP_DELAY); destination airport refers to DEST;

### SQL
SELECT T1.Description FROM Airports AS T1 INNER JOIN Airlines AS T2 ON T1.Code = T2.DEST WHERE T2.FL_DATE = '2018/8/1' ORDER BY T2.DEP_DELAY DESC LIMIT 1
### Database Schema
CREATE TABLE "Air Carriers"
(
    Code        INTEGER
            primary key,
    Description TEXT
)

CREATE TABLE Airports
(
    Code        TEXT
            primary key,
    Description TEXT
)

CREATE TABLE Airlines
(
    FL_DATE               TEXT,
    OP_CARRIER_AIRLINE_ID INTEGER,
    TAIL_NUM              TEXT,
    OP_CARRIER_FL_NUM     INTEGER,
    ORIGIN_AIRPORT_ID     INTEGER,
    ORIGIN_AIRPORT_SEQ_ID INTEGER,
    ORIGIN_CITY_MARKET_ID INTEGER,
    ORIGIN                TEXT,
    DEST_AIRPORT_ID       INTEGER,
    DEST_AIRPORT_SEQ_ID   INTEGER,
    DEST_CITY_MARKET_ID   INTEGER,
    DEST                  TEXT,
    CRS_DEP_TIME          INTEGER,
    DEP_TIME              INTEGER,
    DEP_DELAY             INTEGER,
    DEP_DELAY_NEW         INTEGER,
    ARR_TIME              INTEGER,
    ARR_DELAY             INTEGER,
    ARR_DELAY_NEW         INTEGER,
    CANCELLED             INTEGER,
    CANCELLATION_CODE     TEXT,
    CRS_ELAPSED_TIME      INTEGER,
    ACTUAL_ELAPSED_TIME   INTEGER,
    CARRIER_DELAY         INTEGER,
    WEATHER_DELAY         INTEGER,
    NAS_DELAY             INTEGER,
    SECURITY_DELAY        INTEGER,
    LATE_AIRCRAFT_DELAY   INTEGER,
    FOREIGN KEY (ORIGIN) REFERENCES Airports(Code),
    FOREIGN KEY (DEST) REFERENCES Airports(Code),
    FOREIGN KEY (OP_CARRIER_AIRLINE_ID) REFERENCES "Air Carriers"(Code)
)

### Question
Among the flights departing from John F. Kennedy International, how many of them arrived earlier than scheduled?

### Hint
departing from refers to ORIGIN; John F. Kennedy International refers to Description = 'New York, NY: John F. Kennedy International'; arrived earlier than scheduled refers to ARR_DELAY < 0;

### SQL
SELECT SUM(CASE WHEN T2.ARR_DELAY < 0 THEN 1 ELSE 0 END) AS count FROM Airports AS T1 INNER JOIN Airlines AS T2 ON T1.Code = T2.ORIGIN WHERE T1.Description = 'New York, NY: John F. Kennedy International'
### Database Schema
CREATE TABLE "Air Carriers"
(
    Code        INTEGER
            primary key,
    Description TEXT
)

CREATE TABLE Airports
(
    Code        TEXT
            primary key,
    Description TEXT
)

CREATE TABLE Airlines
(
    FL_DATE               TEXT,
    OP_CARRIER_AIRLINE_ID INTEGER,
    TAIL_NUM              TEXT,
    OP_CARRIER_FL_NUM     INTEGER,
    ORIGIN_AIRPORT_ID     INTEGER,
    ORIGIN_AIRPORT_SEQ_ID INTEGER,
    ORIGIN_CITY_MARKET_ID INTEGER,
    ORIGIN                TEXT,
    DEST_AIRPORT_ID       INTEGER,
    DEST_AIRPORT_SEQ_ID   INTEGER,
    DEST_CITY_MARKET_ID   INTEGER,
    DEST                  TEXT,
    CRS_DEP_TIME          INTEGER,
    DEP_TIME              INTEGER,
    DEP_DELAY             INTEGER,
    DEP_DELAY_NEW         INTEGER,
    ARR_TIME              INTEGER,
    ARR_DELAY             INTEGER,
    ARR_DELAY_NEW         INTEGER,
    CANCELLED             INTEGER,
    CANCELLATION_CODE     TEXT,
    CRS_ELAPSED_TIME      INTEGER,
    ACTUAL_ELAPSED_TIME   INTEGER,
    CARRIER_DELAY         INTEGER,
    WEATHER_DELAY         INTEGER,
    NAS_DELAY             INTEGER,
    SECURITY_DELAY        INTEGER,
    LATE_AIRCRAFT_DELAY   INTEGER,
    FOREIGN KEY (ORIGIN) REFERENCES Airports(Code),
    FOREIGN KEY (DEST) REFERENCES Airports(Code),
    FOREIGN KEY (OP_CARRIER_AIRLINE_ID) REFERENCES "Air Carriers"(Code)
)

### Question
Among all the flights scheduled to depart from John F. Kennedy International on 2018/8/1, when was the earliest one scheduled to depart?

### Hint
depart from refers to ORIGIN; John F. Kennedy International refers to Description = 'New York, NY: John F. Kennedy International'; on 2018/8/1 refers to FL_DATE = '2018/8/1'; earliest one scheduled to depart refers to MIN(DEP_TIME);

### SQL
SELECT T2.DEP_TIME FROM Airports AS T1 INNER JOIN Airlines AS T2 ON T1.Code = T2.ORIGIN WHERE T2.FL_DATE = '2018/8/1' AND T1.Description = 'New York, NY: John F. Kennedy International' AND T2.DEP_TIME IS NOT NULL ORDER BY T2.DEP_TIME ASC LIMIT 1
### Database Schema
CREATE TABLE "Air Carriers"
(
    Code        INTEGER
            primary key,
    Description TEXT
)

CREATE TABLE Airports
(
    Code        TEXT
            primary key,
    Description TEXT
)

CREATE TABLE Airlines
(
    FL_DATE               TEXT,
    OP_CARRIER_AIRLINE_ID INTEGER,
    TAIL_NUM              TEXT,
    OP_CARRIER_FL_NUM     INTEGER,
    ORIGIN_AIRPORT_ID     INTEGER,
    ORIGIN_AIRPORT_SEQ_ID INTEGER,
    ORIGIN_CITY_MARKET_ID INTEGER,
    ORIGIN                TEXT,
    DEST_AIRPORT_ID       INTEGER,
    DEST_AIRPORT_SEQ_ID   INTEGER,
    DEST_CITY_MARKET_ID   INTEGER,
    DEST                  TEXT,
    CRS_DEP_TIME          INTEGER,
    DEP_TIME              INTEGER,
    DEP_DELAY             INTEGER,
    DEP_DELAY_NEW         INTEGER,
    ARR_TIME              INTEGER,
    ARR_DELAY             INTEGER,
    ARR_DELAY_NEW         INTEGER,
    CANCELLED             INTEGER,
    CANCELLATION_CODE     TEXT,
    CRS_ELAPSED_TIME      INTEGER,
    ACTUAL_ELAPSED_TIME   INTEGER,
    CARRIER_DELAY         INTEGER,
    WEATHER_DELAY         INTEGER,
    NAS_DELAY             INTEGER,
    SECURITY_DELAY        INTEGER,
    LATE_AIRCRAFT_DELAY   INTEGER,
    FOREIGN KEY (ORIGIN) REFERENCES Airports(Code),
    FOREIGN KEY (DEST) REFERENCES Airports(Code),
    FOREIGN KEY (OP_CARRIER_AIRLINE_ID) REFERENCES "Air Carriers"(Code)
)

### Question
How many flights on 2018/8/1 were operated by American Airlines Inc.?

### Hint
on 2018/8/1 refers to FL_DATE = '2018/8/1'; American Airlines Inc. refers to Description = 'American Airlines Inc.: AA';

### SQL
SELECT COUNT(*) FROM Airports AS T1 INNER JOIN Airlines AS T2 ON T1.Code = T2.ORIGIN INNER JOIN `Air Carriers` AS T3 ON T2.OP_CARRIER_AIRLINE_ID = T3.Code WHERE T2.FL_DATE = '2018/8/1' AND T3.Description = 'American Airlines Inc.: AA'
### Database Schema
CREATE TABLE "Air Carriers"
(
    Code        INTEGER
            primary key,
    Description TEXT
)

CREATE TABLE Airports
(
    Code        TEXT
            primary key,
    Description TEXT
)

CREATE TABLE Airlines
(
    FL_DATE               TEXT,
    OP_CARRIER_AIRLINE_ID INTEGER,
    TAIL_NUM              TEXT,
    OP_CARRIER_FL_NUM     INTEGER,
    ORIGIN_AIRPORT_ID     INTEGER,
    ORIGIN_AIRPORT_SEQ_ID INTEGER,
    ORIGIN_CITY_MARKET_ID INTEGER,
    ORIGIN                TEXT,
    DEST_AIRPORT_ID       INTEGER,
    DEST_AIRPORT_SEQ_ID   INTEGER,
    DEST_CITY_MARKET_ID   INTEGER,
    DEST                  TEXT,
    CRS_DEP_TIME          INTEGER,
    DEP_TIME              INTEGER,
    DEP_DELAY             INTEGER,
    DEP_DELAY_NEW         INTEGER,
    ARR_TIME              INTEGER,
    ARR_DELAY             INTEGER,
    ARR_DELAY_NEW         INTEGER,
    CANCELLED             INTEGER,
    CANCELLATION_CODE     TEXT,
    CRS_ELAPSED_TIME      INTEGER,
    ACTUAL_ELAPSED_TIME   INTEGER,
    CARRIER_DELAY         INTEGER,
    WEATHER_DELAY         INTEGER,
    NAS_DELAY             INTEGER,
    SECURITY_DELAY        INTEGER,
    LATE_AIRCRAFT_DELAY   INTEGER,
    FOREIGN KEY (ORIGIN) REFERENCES Airports(Code),
    FOREIGN KEY (DEST) REFERENCES Airports(Code),
    FOREIGN KEY (OP_CARRIER_AIRLINE_ID) REFERENCES "Air Carriers"(Code)
)

### Question
Please list the flight numbers of all the flights operated by American Airlines Inc. that were scheduled to depart from John F. Kennedy International.

### Hint
flight numbers refers to OP_CARRIER_FL_NUM; American Airlines Inc. refers to Description = 'American Airlines Inc.: AA'; depart from refers to ORIGIN; John F. Kennedy International refers to Description = 'New York, NY: John F. Kennedy International';

### SQL
SELECT T2.OP_CARRIER_FL_NUM FROM Airports AS T1 INNER JOIN Airlines AS T2 ON T1.Code = T2.ORIGIN INNER JOIN `Air Carriers` AS T3 ON T2.OP_CARRIER_AIRLINE_ID = T3.Code WHERE T3.Description = 'American Airlines Inc.: AA' AND T1.Description = 'New York, NY: John F. Kennedy International' AND T2.FL_DATE = '2018/8/1'
### Database Schema
CREATE TABLE "Air Carriers"
(
    Code        INTEGER
            primary key,
    Description TEXT
)

CREATE TABLE Airports
(
    Code        TEXT
            primary key,
    Description TEXT
)

CREATE TABLE Airlines
(
    FL_DATE               TEXT,
    OP_CARRIER_AIRLINE_ID INTEGER,
    TAIL_NUM              TEXT,
    OP_CARRIER_FL_NUM     INTEGER,
    ORIGIN_AIRPORT_ID     INTEGER,
    ORIGIN_AIRPORT_SEQ_ID INTEGER,
    ORIGIN_CITY_MARKET_ID INTEGER,
    ORIGIN                TEXT,
    DEST_AIRPORT_ID       INTEGER,
    DEST_AIRPORT_SEQ_ID   INTEGER,
    DEST_CITY_MARKET_ID   INTEGER,
    DEST                  TEXT,
    CRS_DEP_TIME          INTEGER,
    DEP_TIME              INTEGER,
    DEP_DELAY             INTEGER,
    DEP_DELAY_NEW         INTEGER,
    ARR_TIME              INTEGER,
    ARR_DELAY             INTEGER,
    ARR_DELAY_NEW         INTEGER,
    CANCELLED             INTEGER,
    CANCELLATION_CODE     TEXT,
    CRS_ELAPSED_TIME      INTEGER,
    ACTUAL_ELAPSED_TIME   INTEGER,
    CARRIER_DELAY         INTEGER,
    WEATHER_DELAY         INTEGER,
    NAS_DELAY             INTEGER,
    SECURITY_DELAY        INTEGER,
    LATE_AIRCRAFT_DELAY   INTEGER,
    FOREIGN KEY (ORIGIN) REFERENCES Airports(Code),
    FOREIGN KEY (DEST) REFERENCES Airports(Code),
    FOREIGN KEY (OP_CARRIER_AIRLINE_ID) REFERENCES "Air Carriers"(Code)
)

### Question
How many flights operated by American Airlines Inc. on 2018/8/1 were faster than scheduled?

### Hint
American Airlines Inc. refers to Description = 'American Airlines Inc.: AA'; on 2018/8/1 refers to FL_DATE = '2018/8/1'; faster than scheduled refers to ACTUAL_ELAPSED_TIME < CRS_ELAPSED_TIME;

### SQL
SELECT SUM(CASE WHEN T2.ACTUAL_ELAPSED_TIME < CRS_ELAPSED_TIME THEN 1 ELSE 0 END) AS count FROM Airports AS T1 INNER JOIN Airlines AS T2 ON T1.Code = T2.ORIGIN INNER JOIN `Air Carriers` AS T3 ON T2.OP_CARRIER_AIRLINE_ID = T3.Code WHERE T2.FL_DATE = '2018/8/1' AND T3.Description = 'American Airlines Inc.: AA'
### Database Schema
CREATE TABLE "Air Carriers"
(
    Code        INTEGER
            primary key,
    Description TEXT
)

CREATE TABLE Airports
(
    Code        TEXT
            primary key,
    Description TEXT
)

CREATE TABLE Airlines
(
    FL_DATE               TEXT,
    OP_CARRIER_AIRLINE_ID INTEGER,
    TAIL_NUM              TEXT,
    OP_CARRIER_FL_NUM     INTEGER,
    ORIGIN_AIRPORT_ID     INTEGER,
    ORIGIN_AIRPORT_SEQ_ID INTEGER,
    ORIGIN_CITY_MARKET_ID INTEGER,
    ORIGIN                TEXT,
    DEST_AIRPORT_ID       INTEGER,
    DEST_AIRPORT_SEQ_ID   INTEGER,
    DEST_CITY_MARKET_ID   INTEGER,
    DEST                  TEXT,
    CRS_DEP_TIME          INTEGER,
    DEP_TIME              INTEGER,
    DEP_DELAY             INTEGER,
    DEP_DELAY_NEW         INTEGER,
    ARR_TIME              INTEGER,
    ARR_DELAY             INTEGER,
    ARR_DELAY_NEW         INTEGER,
    CANCELLED             INTEGER,
    CANCELLATION_CODE     TEXT,
    CRS_ELAPSED_TIME      INTEGER,
    ACTUAL_ELAPSED_TIME   INTEGER,
    CARRIER_DELAY         INTEGER,
    WEATHER_DELAY         INTEGER,
    NAS_DELAY             INTEGER,
    SECURITY_DELAY        INTEGER,
    LATE_AIRCRAFT_DELAY   INTEGER,
    FOREIGN KEY (ORIGIN) REFERENCES Airports(Code),
    FOREIGN KEY (DEST) REFERENCES Airports(Code),
    FOREIGN KEY (OP_CARRIER_AIRLINE_ID) REFERENCES "Air Carriers"(Code)
)

### Question
What is the flight number of the flight operated by American Airlines Inc. that had the longest delay in departure?

### Hint
flight numbers refers to OP_CARRIER_FL_NUM; American Airlines Inc. refers to Description = 'American Airlines Inc.: AA'; longest delay in departure refers to MAX(DEP_DELAY);

### SQL
SELECT T1.OP_CARRIER_FL_NUM FROM Airlines AS T1 INNER JOIN Airports AS T2 ON T2.Code = T1.ORIGIN INNER JOIN `Air Carriers` AS T3 ON T1.OP_CARRIER_AIRLINE_ID = T3.Code WHERE T3.Description = 'American Airlines Inc.: AA' ORDER BY T1.DEP_TIME DESC LIMIT 1
### Database Schema
CREATE TABLE "Air Carriers"
(
    Code        INTEGER
            primary key,
    Description TEXT
)

CREATE TABLE Airports
(
    Code        TEXT
            primary key,
    Description TEXT
)

CREATE TABLE Airlines
(
    FL_DATE               TEXT,
    OP_CARRIER_AIRLINE_ID INTEGER,
    TAIL_NUM              TEXT,
    OP_CARRIER_FL_NUM     INTEGER,
    ORIGIN_AIRPORT_ID     INTEGER,
    ORIGIN_AIRPORT_SEQ_ID INTEGER,
    ORIGIN_CITY_MARKET_ID INTEGER,
    ORIGIN                TEXT,
    DEST_AIRPORT_ID       INTEGER,
    DEST_AIRPORT_SEQ_ID   INTEGER,
    DEST_CITY_MARKET_ID   INTEGER,
    DEST                  TEXT,
    CRS_DEP_TIME          INTEGER,
    DEP_TIME              INTEGER,
    DEP_DELAY             INTEGER,
    DEP_DELAY_NEW         INTEGER,
    ARR_TIME              INTEGER,
    ARR_DELAY             INTEGER,
    ARR_DELAY_NEW         INTEGER,
    CANCELLED             INTEGER,
    CANCELLATION_CODE     TEXT,
    CRS_ELAPSED_TIME      INTEGER,
    ACTUAL_ELAPSED_TIME   INTEGER,
    CARRIER_DELAY         INTEGER,
    WEATHER_DELAY         INTEGER,
    NAS_DELAY             INTEGER,
    SECURITY_DELAY        INTEGER,
    LATE_AIRCRAFT_DELAY   INTEGER,
    FOREIGN KEY (ORIGIN) REFERENCES Airports(Code),
    FOREIGN KEY (DEST) REFERENCES Airports(Code),
    FOREIGN KEY (OP_CARRIER_AIRLINE_ID) REFERENCES "Air Carriers"(Code)
)

### Question
Among the flights operated by American Airlines Inc., how many of them were scheduled to land in New York?

### Hint
American Airlines Inc. refers to Description = 'American Airlines Inc.: AA'; land in New York refers to DEST = 'JFK';

### SQL
SELECT SUM(CASE WHEN T2.DEST = 'JFK' THEN 1 ELSE 0 END) AS count FROM Airports AS T1 INNER JOIN Airlines AS T2 ON T1.Code = T2.DEST INNER JOIN `Air Carriers` AS T3 ON T2.OP_CARRIER_AIRLINE_ID = T3.Code WHERE T3.Description = 'American Airlines Inc.: AA'