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 operated by American Airlines Inc. on 2018/8/1, how many of them were cancelled?

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

### SQL
SELECT SUM(CASE WHEN T2.CANCELLED = 1 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
Which airline operated more flights on 2018/8/1, American Airlines Inc. or Endeavor Air Inc.?

### Hint
SUM(Description = 'American Airlines Inc.: AA') > SUM(Description = 'Endeavor Air Inc.: 9E') means American Airlines Inc. operated more flights than  Endeavor Air Inc; SUM(Description = 'American Airlines Inc.: AA') < SUM(Description = 'Endeavor Air Inc.: 9E') means Endeavor Air Inc. operated more flights than American Airlines Inc.; on 2018/8/1 refers to FL_DATE = '2018/8/1';

### SQL
SELECT CASE WHEN COUNT(CASE WHEN T3.Description = 'American Airlines Inc.: AA' THEN 1 ELSE NULL END) > COUNT(CASE WHEN T3.Description = 'Endeavor Air Inc.: 9E' THEN 1 ELSE NULL END) THEN 'American Airlines Inc.: AA' ELSE 'Endeavor Air Inc.: 9E' END AS RESULT 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'
### 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 average departure delay time of flights operated by American Airlines Inc.?

### Hint
average departure delay time = DIVIDE(SUM(DEP_DELAY), COUNT(Code)); American Airlines Inc. refers to Description = 'American Airlines Inc.: AA';

### SQL
SELECT AVG(T1.DEP_DELAY) 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'
### 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 average does American Airlines Inc. operate every day in August, 2018?

### Hint
flights on average = DIVIDE(COUNT(Code), 31); American Airlines Inc. refers to Description = 'American Airlines Inc.: AA'; every day in August, 2018 refers to FL_DATE like '2018/8%';

### SQL
SELECT CAST( SUM(CASE WHEN T2.FL_DATE LIKE '2018/8%' THEN 1 ELSE 0 END) AS REAL) / 31 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'
### 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 number of air carriers in the database?

### SQL
SELECT COUNT(Code) FROM `Air Carriers`
### 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
Give the number of planes that took off from Los Angeles International airport on 2018/8/27.

### Hint
took off from refers to ORIGIN; Los Angeles International airport refers to Description = 'Los Angeles, CA: Los Angeles International'; on 2018/8/27 refers to FL_DATE = '2018/8/27';

### SQL
SELECT SUM(CASE WHEN T2.FL_DATE = '2018/8/27' THEN 1 ELSE 0 END) AS count FROM Airports AS T1 INNER JOIN Airlines AS T2 ON T1.Code = T2.ORIGIN WHERE T1.Description = 'Los Angeles, CA: Los Angeles 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
Provide the number of airplanes that landed on Oakland Airport on 2018/8/7.

### Hint
landed on refers to DEST; Oakland Airport refers to Description which contains 'Oakland'; on 2018/8/7 refers to FL_DATE = '2018/8/7';

### SQL
SELECT SUM(CASE WHEN T1.Description LIKE '%Oakland%' THEN 1 ELSE 0 END) AS count FROM Airports AS T1 INNER JOIN Airlines AS T2 ON T1.Code = T2.DEST WHERE T2.FL_DATE = '2018/8/7'
### 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 of Alaska Airlines were delayed on 2018/8/2?

### Hint
Alaska Airlines refers to Description = 'Alaska Airlines Inc.: AS'; delayed refers to DEP_DELAY > 0; on 2018/8/2 refers to FL_DATE = '2018/8/2';

### SQL
SELECT COUNT(*) FROM Airlines AS T1 INNER JOIN `Air Carriers` AS T2 ON T1.OP_CARRIER_AIRLINE_ID = T2.Code WHERE T1.FL_DATE = '2018/8/2' AND T2.Description = 'Alaska Airlines Inc.: AS' AND T1.DEP_DELAY > 0
### 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
Tell the number of fights landed earlier on Miami Airport on 2018/8/12.

### Hint
landed on refers to DEST; landed earlier refers to ARR_DELAY < 0; Miami Airport refers to DEST = 'MIA'; on 2018/8/12 refers to FL_DATE = '2018/8/12';

### SQL
SELECT COUNT(*) FROM Airports AS T1 INNER JOIN Airlines AS T2 ON T1.Code = T2.DEST WHERE T2.FL_DATE = '2018/8/12' AND T2.DEST = 'MIA' AND T2.ARR_DELAY < 0
### 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 from American Airlines were cancelled due to a type A cancellation code?

### Hint
American Airlines refers to Description = 'American Airlines Inc.: AA'; cancelled refers to Cancelled = 1; cancelled due to type A cancellation code refers to CANCELLATION_CODE = 'A';

### SQL
SELECT COUNT(*) FROM Airlines AS T1 INNER JOIN `Air Carriers` AS T2 ON T1.OP_CARRIER_AIRLINE_ID = T2.Code WHERE T1.CANCELLATION_CODE = 'A' AND T2.Description = 'American Airlines Inc.: AA' AND T1.CANCELLED = 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 of Endeavor Air Inc. were faster than scheduled on 2018/8/31?

### Hint
Endeavor Air Inc. refers to Description = 'Endeavor Air Inc.: 9E'; faster than scheduled refers to ACTUAL_ELAPSED_TIME < CRS_ELAPSED_TIME; on 2018/8/31 refers to FL_DATE = '2018/8/31';

### SQL
SELECT SUM(CASE WHEN T1.ACTUAL_ELAPSED_TIME < CRS_ELAPSED_TIME THEN 1 ELSE 0 END) AS count FROM Airlines AS T1 INNER JOIN `Air Carriers` AS T2 ON T1.OP_CARRIER_AIRLINE_ID = T2.Code WHERE T1.FL_DATE = '2018/8/31' AND T2.Description = 'Endeavor Air Inc.: 9E'
### 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 planes of Spirit Air Lines took off on 2018/8/7?

### Hint
Spirit Air Lines refers to Description = 'Spirit Air Lines: NK'; on 2018/8/7 refers to FL_DATE = '2018/8/7';

### SQL
SELECT COUNT(T2.Code) FROM Airlines AS T1 INNER JOIN `Air Carriers` AS T2 ON T1.OP_CARRIER_AIRLINE_ID = T2.Code WHERE T1.FL_DATE = '2018/8/7' AND T2.Description = 'Spirit Air Lines: NK'
### 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 with the tail number 'N702SK', which air carrier does it belong to?

### Hint
tail number refers to TAIL_NUM; TAIL_NUM = 'N702SK';

### SQL
SELECT T2.Description FROM Airlines AS T1 INNER JOIN `Air Carriers` AS T2 ON T1.OP_CARRIER_AIRLINE_ID = T2.Code WHERE T1.TAIL_NUM = 'N702SK' GROUP BY T2.Description