lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE Episode
(
    episode_id       TEXT
            primary key,
    series           TEXT,
    season           INTEGER,
    episode          INTEGER,
    number_in_series INTEGER,
    title            TEXT,
    summary          TEXT,
    air_date         DATE,
    episode_image    TEXT,
    rating           REAL,
    votes            INTEGER
)

CREATE TABLE Keyword
(
    episode_id TEXT,
    keyword    TEXT,
    primary key (episode_id, keyword),
    foreign key (episode_id) references Episode(episode_id)
)

CREATE TABLE Person
(
    person_id     TEXT
            primary key,
    name          TEXT,
    birthdate     DATE,
    birth_name    TEXT,
    birth_place   TEXT,
    birth_region  TEXT,
    birth_country TEXT,
    height_meters REAL,
    nickname      TEXT
)

CREATE TABLE Award
(
    award_id       INTEGER
            primary key,
    organization   TEXT,
    year           INTEGER,
    award_category TEXT,
    award          TEXT,
    series         TEXT,
    episode_id     TEXT,
    person_id      TEXT,
    role           TEXT,
    result         TEXT,
    foreign key (episode_id) references Episode(episode_id),
    foreign key (person_id) references Person(person_id)
)

CREATE TABLE Credit
(
    episode_id TEXT,
    person_id  TEXT,
    category   TEXT,
    role       TEXT,
    credited   TEXT,
    primary key (episode_id, person_id),
    foreign key (episode_id) references Episode(episode_id),
    foreign key (person_id) references Person(person_id)
)

CREATE TABLE Vote
(
    episode_id TEXT,
    stars      INTEGER,
    votes      INTEGER,
    percent    REAL,
    foreign key (episode_id) references Episode(episode_id)
)

### Question
Which episode was nominated for the award for "Outstanding Costume Design for a Series"?

### Hint
episode refers to title; "Outstanding Costume Design for a Series" refers to award = 'Outstanding Costume Design for a Series'

### SQL
SELECT T2.title FROM Award AS T1 INNER JOIN Episode AS T2 ON T1.episode_id = T2.episode_id WHERE T1.award = 'Outstanding Costume Design for a Series'
### Database Schema
CREATE TABLE Episode
(
    episode_id       TEXT
            primary key,
    series           TEXT,
    season           INTEGER,
    episode          INTEGER,
    number_in_series INTEGER,
    title            TEXT,
    summary          TEXT,
    air_date         DATE,
    episode_image    TEXT,
    rating           REAL,
    votes            INTEGER
)

CREATE TABLE Keyword
(
    episode_id TEXT,
    keyword    TEXT,
    primary key (episode_id, keyword),
    foreign key (episode_id) references Episode(episode_id)
)

CREATE TABLE Person
(
    person_id     TEXT
            primary key,
    name          TEXT,
    birthdate     DATE,
    birth_name    TEXT,
    birth_place   TEXT,
    birth_region  TEXT,
    birth_country TEXT,
    height_meters REAL,
    nickname      TEXT
)

CREATE TABLE Award
(
    award_id       INTEGER
            primary key,
    organization   TEXT,
    year           INTEGER,
    award_category TEXT,
    award          TEXT,
    series         TEXT,
    episode_id     TEXT,
    person_id      TEXT,
    role           TEXT,
    result         TEXT,
    foreign key (episode_id) references Episode(episode_id),
    foreign key (person_id) references Person(person_id)
)

CREATE TABLE Credit
(
    episode_id TEXT,
    person_id  TEXT,
    category   TEXT,
    role       TEXT,
    credited   TEXT,
    primary key (episode_id, person_id),
    foreign key (episode_id) references Episode(episode_id),
    foreign key (person_id) references Person(person_id)
)

CREATE TABLE Vote
(
    episode_id TEXT,
    stars      INTEGER,
    votes      INTEGER,
    percent    REAL,
    foreign key (episode_id) references Episode(episode_id)
)

### Question
Which episode has the highest total number of viewer votes?

### Hint
episode refers to title; the highest total number of viewer votes refers to max(sum(votes))

### SQL
SELECT T1.title FROM Episode AS T1 INNER JOIN Vote AS T2 ON T1.episode_id = T2.episode_id GROUP BY T1.title ORDER BY SUM(T1.votes) DESC LIMIT 1
### Database Schema
CREATE TABLE Episode
(
    episode_id       TEXT
            primary key,
    series           TEXT,
    season           INTEGER,
    episode          INTEGER,
    number_in_series INTEGER,
    title            TEXT,
    summary          TEXT,
    air_date         DATE,
    episode_image    TEXT,
    rating           REAL,
    votes            INTEGER
)

CREATE TABLE Keyword
(
    episode_id TEXT,
    keyword    TEXT,
    primary key (episode_id, keyword),
    foreign key (episode_id) references Episode(episode_id)
)

CREATE TABLE Person
(
    person_id     TEXT
            primary key,
    name          TEXT,
    birthdate     DATE,
    birth_name    TEXT,
    birth_place   TEXT,
    birth_region  TEXT,
    birth_country TEXT,
    height_meters REAL,
    nickname      TEXT
)

CREATE TABLE Award
(
    award_id       INTEGER
            primary key,
    organization   TEXT,
    year           INTEGER,
    award_category TEXT,
    award          TEXT,
    series         TEXT,
    episode_id     TEXT,
    person_id      TEXT,
    role           TEXT,
    result         TEXT,
    foreign key (episode_id) references Episode(episode_id),
    foreign key (person_id) references Person(person_id)
)

CREATE TABLE Credit
(
    episode_id TEXT,
    person_id  TEXT,
    category   TEXT,
    role       TEXT,
    credited   TEXT,
    primary key (episode_id, person_id),
    foreign key (episode_id) references Episode(episode_id),
    foreign key (person_id) references Person(person_id)
)

CREATE TABLE Vote
(
    episode_id TEXT,
    stars      INTEGER,
    votes      INTEGER,
    percent    REAL,
    foreign key (episode_id) references Episode(episode_id)
)

### Question
Who was the actor who was portraying "Alex Brown" and has been credited?

### Hint
who refers to name; portraying "Alex Brown" refers to role = 'Alex Brown'; has been credited refers to credited = 'true'

### SQL
SELECT T1.name FROM Person AS T1 INNER JOIN Credit AS T2 ON T1.person_id = T2.person_id WHERE T2.role = 'Alex Brown' AND T2.credited = 'true'
### Database Schema
CREATE TABLE Episode
(
    episode_id       TEXT
            primary key,
    series           TEXT,
    season           INTEGER,
    episode          INTEGER,
    number_in_series INTEGER,
    title            TEXT,
    summary          TEXT,
    air_date         DATE,
    episode_image    TEXT,
    rating           REAL,
    votes            INTEGER
)

CREATE TABLE Keyword
(
    episode_id TEXT,
    keyword    TEXT,
    primary key (episode_id, keyword),
    foreign key (episode_id) references Episode(episode_id)
)

CREATE TABLE Person
(
    person_id     TEXT
            primary key,
    name          TEXT,
    birthdate     DATE,
    birth_name    TEXT,
    birth_place   TEXT,
    birth_region  TEXT,
    birth_country TEXT,
    height_meters REAL,
    nickname      TEXT
)

CREATE TABLE Award
(
    award_id       INTEGER
            primary key,
    organization   TEXT,
    year           INTEGER,
    award_category TEXT,
    award          TEXT,
    series         TEXT,
    episode_id     TEXT,
    person_id      TEXT,
    role           TEXT,
    result         TEXT,
    foreign key (episode_id) references Episode(episode_id),
    foreign key (person_id) references Person(person_id)
)

CREATE TABLE Credit
(
    episode_id TEXT,
    person_id  TEXT,
    category   TEXT,
    role       TEXT,
    credited   TEXT,
    primary key (episode_id, person_id),
    foreign key (episode_id) references Episode(episode_id),
    foreign key (person_id) references Person(person_id)
)

CREATE TABLE Vote
(
    episode_id TEXT,
    stars      INTEGER,
    votes      INTEGER,
    percent    REAL,
    foreign key (episode_id) references Episode(episode_id)
)

### Question
Where is the place of birth of the actor with the number nm0007064 who has not been credited for playing the role of a "Narrator"?

### Hint
place of birth refers to birth_place; actor with the number nm0007064 refers to person_id = 'nm007064'; has not been credited refers to credited = ''; the role of a "Narrator" refers to role = 'narrator'

### SQL
SELECT DISTINCT T1.birth_place FROM Person AS T1 INNER JOIN Credit AS T2 ON T1.person_id = T2.person_id WHERE T1.person_id = 'nm0007064' AND T2.role = 'Narrator' AND T2.credited = 'false'
### Database Schema
CREATE TABLE Episode
(
    episode_id       TEXT
            primary key,
    series           TEXT,
    season           INTEGER,
    episode          INTEGER,
    number_in_series INTEGER,
    title            TEXT,
    summary          TEXT,
    air_date         DATE,
    episode_image    TEXT,
    rating           REAL,
    votes            INTEGER
)

CREATE TABLE Keyword
(
    episode_id TEXT,
    keyword    TEXT,
    primary key (episode_id, keyword),
    foreign key (episode_id) references Episode(episode_id)
)

CREATE TABLE Person
(
    person_id     TEXT
            primary key,
    name          TEXT,
    birthdate     DATE,
    birth_name    TEXT,
    birth_place   TEXT,
    birth_region  TEXT,
    birth_country TEXT,
    height_meters REAL,
    nickname      TEXT
)

CREATE TABLE Award
(
    award_id       INTEGER
            primary key,
    organization   TEXT,
    year           INTEGER,
    award_category TEXT,
    award          TEXT,
    series         TEXT,
    episode_id     TEXT,
    person_id      TEXT,
    role           TEXT,
    result         TEXT,
    foreign key (episode_id) references Episode(episode_id),
    foreign key (person_id) references Person(person_id)
)

CREATE TABLE Credit
(
    episode_id TEXT,
    person_id  TEXT,
    category   TEXT,
    role       TEXT,
    credited   TEXT,
    primary key (episode_id, person_id),
    foreign key (episode_id) references Episode(episode_id),
    foreign key (person_id) references Person(person_id)
)

CREATE TABLE Vote
(
    episode_id TEXT,
    stars      INTEGER,
    votes      INTEGER,
    percent    REAL,
    foreign key (episode_id) references Episode(episode_id)
)

### Question
What are the keywords of the episode "Shield"?

### Hint
the episode "Shield" refers to title = 'Shield'

### SQL
SELECT T2.keyword FROM Episode AS T1 INNER JOIN Keyword AS T2 ON T1.episode_id = T2.episode_id WHERE T1.title = 'Shield'
### Database Schema
CREATE TABLE Episode
(
    episode_id       TEXT
            primary key,
    series           TEXT,
    season           INTEGER,
    episode          INTEGER,
    number_in_series INTEGER,
    title            TEXT,
    summary          TEXT,
    air_date         DATE,
    episode_image    TEXT,
    rating           REAL,
    votes            INTEGER
)

CREATE TABLE Keyword
(
    episode_id TEXT,
    keyword    TEXT,
    primary key (episode_id, keyword),
    foreign key (episode_id) references Episode(episode_id)
)

CREATE TABLE Person
(
    person_id     TEXT
            primary key,
    name          TEXT,
    birthdate     DATE,
    birth_name    TEXT,
    birth_place   TEXT,
    birth_region  TEXT,
    birth_country TEXT,
    height_meters REAL,
    nickname      TEXT
)

CREATE TABLE Award
(
    award_id       INTEGER
            primary key,
    organization   TEXT,
    year           INTEGER,
    award_category TEXT,
    award          TEXT,
    series         TEXT,
    episode_id     TEXT,
    person_id      TEXT,
    role           TEXT,
    result         TEXT,
    foreign key (episode_id) references Episode(episode_id),
    foreign key (person_id) references Person(person_id)
)

CREATE TABLE Credit
(
    episode_id TEXT,
    person_id  TEXT,
    category   TEXT,
    role       TEXT,
    credited   TEXT,
    primary key (episode_id, person_id),
    foreign key (episode_id) references Episode(episode_id),
    foreign key (person_id) references Person(person_id)
)

CREATE TABLE Vote
(
    episode_id TEXT,
    stars      INTEGER,
    votes      INTEGER,
    percent    REAL,
    foreign key (episode_id) references Episode(episode_id)
)

### Question
Who are the actors with a height of over 1.80m in an episode that won an award?

### Hint
who refers to name; a height of over 1.80m refers to height_meters > 1.80; won an award refers to result = 'Winner'

### SQL
SELECT T2.name FROM Award AS T1 INNER JOIN Person AS T2 ON T1.person_id = T2.person_id WHERE T1.result = 'Winner' AND T2.height_meters > 1.80
### Database Schema
CREATE TABLE Episode
(
    episode_id       TEXT
            primary key,
    series           TEXT,
    season           INTEGER,
    episode          INTEGER,
    number_in_series INTEGER,
    title            TEXT,
    summary          TEXT,
    air_date         DATE,
    episode_image    TEXT,
    rating           REAL,
    votes            INTEGER
)

CREATE TABLE Keyword
(
    episode_id TEXT,
    keyword    TEXT,
    primary key (episode_id, keyword),
    foreign key (episode_id) references Episode(episode_id)
)

CREATE TABLE Person
(
    person_id     TEXT
            primary key,
    name          TEXT,
    birthdate     DATE,
    birth_name    TEXT,
    birth_place   TEXT,
    birth_region  TEXT,
    birth_country TEXT,
    height_meters REAL,
    nickname      TEXT
)

CREATE TABLE Award
(
    award_id       INTEGER
            primary key,
    organization   TEXT,
    year           INTEGER,
    award_category TEXT,
    award          TEXT,
    series         TEXT,
    episode_id     TEXT,
    person_id      TEXT,
    role           TEXT,
    result         TEXT,
    foreign key (episode_id) references Episode(episode_id),
    foreign key (person_id) references Person(person_id)
)

CREATE TABLE Credit
(
    episode_id TEXT,
    person_id  TEXT,
    category   TEXT,
    role       TEXT,
    credited   TEXT,
    primary key (episode_id, person_id),
    foreign key (episode_id) references Episode(episode_id),
    foreign key (person_id) references Person(person_id)
)

CREATE TABLE Vote
(
    episode_id TEXT,
    stars      INTEGER,
    votes      INTEGER,
    percent    REAL,
    foreign key (episode_id) references Episode(episode_id)
)

### Question
Which episode has the two keywords "nun" and "priest"?

### Hint
episode refers to title; the two keywords "nun" and "priest" refers to keyword = 'nun' or keyword = 'priest';

### SQL
SELECT T1.title FROM Episode AS T1 INNER JOIN Keyword AS T2 ON T1.episode_id = T2.episode_id WHERE T2.keyword IN ('nun', 'priest')
### Database Schema
CREATE TABLE Episode
(
    episode_id       TEXT
            primary key,
    series           TEXT,
    season           INTEGER,
    episode          INTEGER,
    number_in_series INTEGER,
    title            TEXT,
    summary          TEXT,
    air_date         DATE,
    episode_image    TEXT,
    rating           REAL,
    votes            INTEGER
)

CREATE TABLE Keyword
(
    episode_id TEXT,
    keyword    TEXT,
    primary key (episode_id, keyword),
    foreign key (episode_id) references Episode(episode_id)
)

CREATE TABLE Person
(
    person_id     TEXT
            primary key,
    name          TEXT,
    birthdate     DATE,
    birth_name    TEXT,
    birth_place   TEXT,
    birth_region  TEXT,
    birth_country TEXT,
    height_meters REAL,
    nickname      TEXT
)

CREATE TABLE Award
(
    award_id       INTEGER
            primary key,
    organization   TEXT,
    year           INTEGER,
    award_category TEXT,
    award          TEXT,
    series         TEXT,
    episode_id     TEXT,
    person_id      TEXT,
    role           TEXT,
    result         TEXT,
    foreign key (episode_id) references Episode(episode_id),
    foreign key (person_id) references Person(person_id)
)

CREATE TABLE Credit
(
    episode_id TEXT,
    person_id  TEXT,
    category   TEXT,
    role       TEXT,
    credited   TEXT,
    primary key (episode_id, person_id),
    foreign key (episode_id) references Episode(episode_id),
    foreign key (person_id) references Person(person_id)
)

CREATE TABLE Vote
(
    episode_id TEXT,
    stars      INTEGER,
    votes      INTEGER,
    percent    REAL,
    foreign key (episode_id) references Episode(episode_id)
)

### Question
Which episode number has the second highest positive viewer comments and has been awarded "Best Television Episode"?

### Hint
episode number refers to episode_id; awarded "Best Television Episode" refers to award = 'Best Television Episode' and result = 'Winner'; the second highest positive viewer comments refers to rating = 8.5

### SQL
SELECT T2.episode_id FROM Award AS T1 INNER JOIN Episode AS T2 ON T1.episode_id = T2.episode_id WHERE T1.award = 'Best Television Episode' AND T1.result = 'Winner' ORDER BY T2.rating DESC LIMIT 2
### Database Schema
CREATE TABLE Episode
(
    episode_id       TEXT
            primary key,
    series           TEXT,
    season           INTEGER,
    episode          INTEGER,
    number_in_series INTEGER,
    title            TEXT,
    summary          TEXT,
    air_date         DATE,
    episode_image    TEXT,
    rating           REAL,
    votes            INTEGER
)

CREATE TABLE Keyword
(
    episode_id TEXT,
    keyword    TEXT,
    primary key (episode_id, keyword),
    foreign key (episode_id) references Episode(episode_id)
)

CREATE TABLE Person
(
    person_id     TEXT
            primary key,
    name          TEXT,
    birthdate     DATE,
    birth_name    TEXT,
    birth_place   TEXT,
    birth_region  TEXT,
    birth_country TEXT,
    height_meters REAL,
    nickname      TEXT
)

CREATE TABLE Award
(
    award_id       INTEGER
            primary key,
    organization   TEXT,
    year           INTEGER,
    award_category TEXT,
    award          TEXT,
    series         TEXT,
    episode_id     TEXT,
    person_id      TEXT,
    role           TEXT,
    result         TEXT,
    foreign key (episode_id) references Episode(episode_id),
    foreign key (person_id) references Person(person_id)
)

CREATE TABLE Credit
(
    episode_id TEXT,
    person_id  TEXT,
    category   TEXT,
    role       TEXT,
    credited   TEXT,
    primary key (episode_id, person_id),
    foreign key (episode_id) references Episode(episode_id),
    foreign key (person_id) references Person(person_id)
)

CREATE TABLE Vote
(
    episode_id TEXT,
    stars      INTEGER,
    votes      INTEGER,
    percent    REAL,
    foreign key (episode_id) references Episode(episode_id)
)

### Question
Please list any three episodes that were most enjoyed by the viewers.

### Hint
episode refers to title; most enjoyed by the viewers refers to stars = 10

### SQL
SELECT T1.title FROM Episode AS T1 INNER JOIN Vote AS T2 ON T1.episode_id = T2.episode_id WHERE T2.stars = 10 LIMIT 3
### Database Schema
CREATE TABLE offices
(
    officeCode   TEXT not null
        primary key,
    city         TEXT not null,
    phone        TEXT not null,
    addressLine1 TEXT not null,
    addressLine2 TEXT,
    state        TEXT,
    country      TEXT not null,
    postalCode  TEXT not null,
    territory    TEXT not null
)

CREATE TABLE employees
(
    employeeNumber INTEGER      not null
        primary key,
    lastName       TEXT  not null,
    firstName      TEXT  not null,
    extension      TEXT  not null,
    email          TEXT not null,
    officeCode     TEXT  not null,
    reportsTo      INTEGER,
    jobTitle       TEXT  not null,
    foreign key (officeCode) references offices(officeCode),
    foreign key (reportsTo) references employees(employeeNumber)
)

CREATE TABLE customers
(
    customerNumber         INTEGER     not null
        primary key,
    customerName           TEXT not null,
    contactLastName        TEXT not null,
    contactFirstName       TEXT not null,
    phone                  TEXT not null,
    addressLine1           TEXT not null,
    addressLine2           TEXT,
    city                   TEXT not null,
    state                  TEXT,
    postalCode             TEXT,
    country                TEXT not null,
    salesRepEmployeeNumber INTEGER,
    creditLimit            REAL,
    foreign key (salesRepEmployeeNumber) references employees(employeeNumber)
)

CREATE TABLE orders
(
    orderNumber    INTEGER     not null
        primary key,
    orderDate      DATE        not null,
    requiredDate   DATE        not null,
    shippedDate    DATE,
    status         TEXT not null,
    comments       TEXT,
    customerNumber INTEGER     not null,
    foreign key (customerNumber) references customers(customerNumber)
)

CREATE TABLE payments
(
    customerNumber INTEGER     not null,
    checkNumber    TEXT not null,
    paymentDate    DATE        not null,
    amount         REAL        not null,
    primary key (customerNumber, checkNumber),
    foreign key (customerNumber) references customers(customerNumber)
)

CREATE TABLE productlines
(
    productLine     TEXT not null
        primary key,
    textDescription TEXT,
    htmlDescription TEXT,
    image           BLOB
)

CREATE TABLE products
(
    productCode        TEXT not null
        primary key,
    productName        TEXT not null,
    productLine        TEXT not null,
    productScale      TEXT not null,
    productVendor      TEXT not null,
    productDescription TEXT        not null,
    quantityInStock    INTEGER     not null,
    buyPrice           REAL        not null,
    MSRP               REAL        not null,
    foreign key (productLine) references productlines(productLine)
)

CREATE TABLE "orderdetails"
(
    orderNumber     INTEGER not null
        references orders,
    productCode     TEXT    not null
        references products,
    quantityOrdered INTEGER not null,
    priceEach       REAL    not null,
    orderLineNumber INTEGER not null,
    primary key (orderNumber, productCode)
)

### Question
List the country and how many customers are there.

### SQL
SELECT country, COUNT(customerNumber) FROM customers GROUP BY country
### Database Schema
CREATE TABLE geographic
(
    city   TEXT not null
        primary key,
    county TEXT null,
    region TEXT null
)

CREATE TABLE generalinfo
(
    id_restaurant INTEGER         not null
        primary key,
    label         TEXT  null,
    food_type     TEXT  null,
    city          TEXT  null,
    review        REAL null,
    foreign key (city) references geographic(city)
            on update cascade on delete cascade
)

CREATE TABLE location
(
    id_restaurant INTEGER        not null
        primary key,
    street_num    INTEGER          null,
    street_name   TEXT null,
    city          TEXT null,
    foreign key (city) references geographic (city)
            on update cascade on delete cascade,
    foreign key (id_restaurant) references generalinfo (id_restaurant)
            on update cascade on delete cascade
)

### Question
How many restaurants have not obtained a minimum of 3 in their reviews?

### Hint
have not obtained a minimum of 3 in review refers to review < 3

### SQL
SELECT COUNT(id_restaurant) FROM generalinfo WHERE review < 3
### Database Schema
CREATE TABLE geographic
(
    city   TEXT not null
        primary key,
    county TEXT null,
    region TEXT null
)

CREATE TABLE generalinfo
(
    id_restaurant INTEGER         not null
        primary key,
    label         TEXT  null,
    food_type     TEXT  null,
    city          TEXT  null,
    review        REAL null,
    foreign key (city) references geographic(city)
            on update cascade on delete cascade
)

CREATE TABLE location
(
    id_restaurant INTEGER        not null
        primary key,
    street_num    INTEGER          null,
    street_name   TEXT null,
    city          TEXT null,
    foreign key (city) references geographic (city)
            on update cascade on delete cascade,
    foreign key (id_restaurant) references generalinfo (id_restaurant)
            on update cascade on delete cascade
)

### Question
What types of food are served at the 4 top-reviewed restaurants?

### Hint
top-reviewed refers to review = 4; type of food refers to food_type



### SQL
SELECT food_type FROM generalinfo WHERE review = ( SELECT MAX(review) FROM generalinfo ) LIMIT 4