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

CREATE TABLE Person
(
    name          TEXT
        constraint Person_pk
            primary key,
    birthdate     TEXT,
    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,
    person         TEXT,
    role           TEXT,
    episode_id     TEXT,
    season         TEXT,
    song           TEXT,
    result         TEXT,
    foreign key (person) references Person(name),
    foreign key (episode_id) references Episode(episode_id)
)

CREATE TABLE Character_Award
(
    award_id  INTEGER,
    character TEXT,
    foreign key (award_id) references Award(award_id)
)

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

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

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

### Question
How many 10 star votes did the top 4 episodes with the highest rating received?

### Hint
10 stars votes refers to stars = 10; highest rating refers to Max(rating); number of votes refers to SUM(votes)

### SQL
SELECT SUM(T1.votes) FROM Episode AS T1 INNER JOIN Vote AS T2 ON T2.episode_id = T1.episode_id WHERE T2.stars = 10 ORDER BY T1.rating DESC LIMIT 4;
### Database Schema
CREATE TABLE "Episode"
(
    episode_id       TEXT
        constraint Episode_pk
            primary key,
    season           INTEGER,
    episode          INTEGER,
    number_in_series INTEGER,
    title            TEXT,
    summary          TEXT,
    air_date         TEXT,
    episode_image    TEXT,
    rating           REAL,
    votes            INTEGER
)

CREATE TABLE Person
(
    name          TEXT
        constraint Person_pk
            primary key,
    birthdate     TEXT,
    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,
    person         TEXT,
    role           TEXT,
    episode_id     TEXT,
    season         TEXT,
    song           TEXT,
    result         TEXT,
    foreign key (person) references Person(name),
    foreign key (episode_id) references Episode(episode_id)
)

CREATE TABLE Character_Award
(
    award_id  INTEGER,
    character TEXT,
    foreign key (award_id) references Award(award_id)
)

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

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

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

### Question
In "No Loan Again, Naturally", how many stars received votes of no more than 50?

### Hint
"No Loan Again, Naturally" is the title of episode; votes of no more than 50 refers to votes < 50; number of stars refers to SUM(stars)

### SQL
SELECT COUNT(*) FROM Episode AS T1 INNER JOIN Vote AS T2 ON T2.episode_id = T1.episode_id WHERE T1.title = 'No Loan Again, Naturally' AND T2.votes < 50;
### Database Schema
CREATE TABLE "Episode"
(
    episode_id       TEXT
        constraint Episode_pk
            primary key,
    season           INTEGER,
    episode          INTEGER,
    number_in_series INTEGER,
    title            TEXT,
    summary          TEXT,
    air_date         TEXT,
    episode_image    TEXT,
    rating           REAL,
    votes            INTEGER
)

CREATE TABLE Person
(
    name          TEXT
        constraint Person_pk
            primary key,
    birthdate     TEXT,
    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,
    person         TEXT,
    role           TEXT,
    episode_id     TEXT,
    season         TEXT,
    song           TEXT,
    result         TEXT,
    foreign key (person) references Person(name),
    foreign key (episode_id) references Episode(episode_id)
)

CREATE TABLE Character_Award
(
    award_id  INTEGER,
    character TEXT,
    foreign key (award_id) references Award(award_id)
)

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

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

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

### Question
How many award winners are from the USA?

### Hint
award winner refers to result = 'Winner'; 'USA' is the birth_country

### SQL
SELECT COUNT(*) FROM Person AS T1 INNER JOIN Award AS T2 ON T1.name = T2.person WHERE T1.birth_country = 'USA' AND T2.result = 'Winner';
### Database Schema
CREATE TABLE "Episode"
(
    episode_id       TEXT
        constraint Episode_pk
            primary key,
    season           INTEGER,
    episode          INTEGER,
    number_in_series INTEGER,
    title            TEXT,
    summary          TEXT,
    air_date         TEXT,
    episode_image    TEXT,
    rating           REAL,
    votes            INTEGER
)

CREATE TABLE Person
(
    name          TEXT
        constraint Person_pk
            primary key,
    birthdate     TEXT,
    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,
    person         TEXT,
    role           TEXT,
    episode_id     TEXT,
    season         TEXT,
    song           TEXT,
    result         TEXT,
    foreign key (person) references Person(name),
    foreign key (episode_id) references Episode(episode_id)
)

CREATE TABLE Character_Award
(
    award_id  INTEGER,
    character TEXT,
    foreign key (award_id) references Award(award_id)
)

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

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

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

### Question
In Season 20 Episode 11, how many times was Doofus included in the credit list?

### Hint
in Season 20 Episode 11 refers to episode_id = 'S20-E11'; 'Doofus' is the nickname of person; include in credit list refers to credited = 'true'

### SQL
SELECT COUNT(*) FROM Person AS T1 INNER JOIN Credit AS T2 ON T1.name = T2.person WHERE T1.nickname = 'Doofus' AND T2.credited = 'true' AND T2.episode_id = 'S20-E11';
### Database Schema
CREATE TABLE "Episode"
(
    episode_id       TEXT
        constraint Episode_pk
            primary key,
    season           INTEGER,
    episode          INTEGER,
    number_in_series INTEGER,
    title            TEXT,
    summary          TEXT,
    air_date         TEXT,
    episode_image    TEXT,
    rating           REAL,
    votes            INTEGER
)

CREATE TABLE Person
(
    name          TEXT
        constraint Person_pk
            primary key,
    birthdate     TEXT,
    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,
    person         TEXT,
    role           TEXT,
    episode_id     TEXT,
    season         TEXT,
    song           TEXT,
    result         TEXT,
    foreign key (person) references Person(name),
    foreign key (episode_id) references Episode(episode_id)
)

CREATE TABLE Character_Award
(
    award_id  INTEGER,
    character TEXT,
    foreign key (award_id) references Award(award_id)
)

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

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

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

### Question
What was the first award won by the cast or crew member of the show? Give the name of the person who won the said award.

### Hint
won refers to result = 'Winner'; first award refers to Min(year)

### SQL
SELECT T2.award, T1.name FROM Person AS T1 INNER JOIN Award AS T2 ON T1.name = T2.person WHERE T2.result = 'Winner' ORDER BY T2.year LIMIT 1;
### Database Schema
CREATE TABLE "Episode"
(
    episode_id       TEXT
        constraint Episode_pk
            primary key,
    season           INTEGER,
    episode          INTEGER,
    number_in_series INTEGER,
    title            TEXT,
    summary          TEXT,
    air_date         TEXT,
    episode_image    TEXT,
    rating           REAL,
    votes            INTEGER
)

CREATE TABLE Person
(
    name          TEXT
        constraint Person_pk
            primary key,
    birthdate     TEXT,
    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,
    person         TEXT,
    role           TEXT,
    episode_id     TEXT,
    season         TEXT,
    song           TEXT,
    result         TEXT,
    foreign key (person) references Person(name),
    foreign key (episode_id) references Episode(episode_id)
)

CREATE TABLE Character_Award
(
    award_id  INTEGER,
    character TEXT,
    foreign key (award_id) references Award(award_id)
)

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

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

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

### Question
In "Sex, Pies and Idiot Scrapes", how many percentage of votes did the 9 star score has?

### Hint
"Sex, Pies and Idiot Scrapes" is the title of episode; 9 star score refers to stars = 9; percentage refers to percent

### SQL
SELECT T2.percent FROM Episode AS T1 INNER JOIN Vote AS T2 ON T2.episode_id = T1.episode_id WHERE T1.title = 'Sex, Pies and Idiot Scrapes' AND T2.stars = 9;
### Database Schema
CREATE TABLE "Episode"
(
    episode_id       TEXT
        constraint Episode_pk
            primary key,
    season           INTEGER,
    episode          INTEGER,
    number_in_series INTEGER,
    title            TEXT,
    summary          TEXT,
    air_date         TEXT,
    episode_image    TEXT,
    rating           REAL,
    votes            INTEGER
)

CREATE TABLE Person
(
    name          TEXT
        constraint Person_pk
            primary key,
    birthdate     TEXT,
    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,
    person         TEXT,
    role           TEXT,
    episode_id     TEXT,
    season         TEXT,
    song           TEXT,
    result         TEXT,
    foreign key (person) references Person(name),
    foreign key (episode_id) references Episode(episode_id)
)

CREATE TABLE Character_Award
(
    award_id  INTEGER,
    character TEXT,
    foreign key (award_id) references Award(award_id)
)

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

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

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

### Question
List the award name and persons who won the award in 2009.

### Hint
won the award refers to result = 'Winner'; in 2009 refers to year = 2009

### SQL
SELECT award, person FROM Award WHERE result = 'Winner' AND SUBSTR(year, 1, 4) = '2009';
### Database Schema
CREATE TABLE "Episode"
(
    episode_id       TEXT
        constraint Episode_pk
            primary key,
    season           INTEGER,
    episode          INTEGER,
    number_in_series INTEGER,
    title            TEXT,
    summary          TEXT,
    air_date         TEXT,
    episode_image    TEXT,
    rating           REAL,
    votes            INTEGER
)

CREATE TABLE Person
(
    name          TEXT
        constraint Person_pk
            primary key,
    birthdate     TEXT,
    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,
    person         TEXT,
    role           TEXT,
    episode_id     TEXT,
    season         TEXT,
    song           TEXT,
    result         TEXT,
    foreign key (person) references Person(name),
    foreign key (episode_id) references Episode(episode_id)
)

CREATE TABLE Character_Award
(
    award_id  INTEGER,
    character TEXT,
    foreign key (award_id) references Award(award_id)
)

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

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

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

### Question
Among the casts who were born in Los Angeles, describe the name and birth date of who have 1.8 m and above in height.

### Hint
"Los Angeles" is the birth_place; 1.8 m and above in height refers to height_meters > = 1.8

### SQL
SELECT name, birthdate FROM Person WHERE birth_place = 'Los Angeles' AND height_meters >= 1.8;
### Database Schema
CREATE TABLE "Episode"
(
    episode_id       TEXT
        constraint Episode_pk
            primary key,
    season           INTEGER,
    episode          INTEGER,
    number_in_series INTEGER,
    title            TEXT,
    summary          TEXT,
    air_date         TEXT,
    episode_image    TEXT,
    rating           REAL,
    votes            INTEGER
)

CREATE TABLE Person
(
    name          TEXT
        constraint Person_pk
            primary key,
    birthdate     TEXT,
    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,
    person         TEXT,
    role           TEXT,
    episode_id     TEXT,
    season         TEXT,
    song           TEXT,
    result         TEXT,
    foreign key (person) references Person(name),
    foreign key (episode_id) references Episode(episode_id)
)

CREATE TABLE Character_Award
(
    award_id  INTEGER,
    character TEXT,
    foreign key (award_id) references Award(award_id)
)

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

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

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

### Question
Provide the episode number, title and rating of which were aired in October, 2008.

### Hint
episode number refers to episode; aired in refers to air_date; October refers to Substr(air_date, 6, 2) = '10': 2008 refers to Substr(air_date, 0, 5) = '2008'

### SQL
SELECT episode, title, rating FROM Episode WHERE SUBSTR(air_date, 1, 7) LIKE '2008-10%';
### Database Schema
CREATE TABLE "Episode"
(
    episode_id       TEXT
        constraint Episode_pk
            primary key,
    season           INTEGER,
    episode          INTEGER,
    number_in_series INTEGER,
    title            TEXT,
    summary          TEXT,
    air_date         TEXT,
    episode_image    TEXT,
    rating           REAL,
    votes            INTEGER
)

CREATE TABLE Person
(
    name          TEXT
        constraint Person_pk
            primary key,
    birthdate     TEXT,
    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,
    person         TEXT,
    role           TEXT,
    episode_id     TEXT,
    season         TEXT,
    song           TEXT,
    result         TEXT,
    foreign key (person) references Person(name),
    foreign key (episode_id) references Episode(episode_id)
)

CREATE TABLE Character_Award
(
    award_id  INTEGER,
    character TEXT,
    foreign key (award_id) references Award(award_id)
)

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

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

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

### Question
Write down the award ID, award name and winner for character named "Homer simpson 20".

### Hint
"Homer simpson 20" is the character; "Winner" is the result; award name refers to award

### SQL
SELECT T1.award_id, T1.award, T1.person FROM Award AS T1 INNER JOIN Character_Award AS T2 ON T1.award_id = T2.award_id WHERE T2.character = 'Homer Simpson' AND T1.result = 'Winner';
### Database Schema
CREATE TABLE "Episode"
(
    episode_id       TEXT
        constraint Episode_pk
            primary key,
    season           INTEGER,
    episode          INTEGER,
    number_in_series INTEGER,
    title            TEXT,
    summary          TEXT,
    air_date         TEXT,
    episode_image    TEXT,
    rating           REAL,
    votes            INTEGER
)

CREATE TABLE Person
(
    name          TEXT
        constraint Person_pk
            primary key,
    birthdate     TEXT,
    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,
    person         TEXT,
    role           TEXT,
    episode_id     TEXT,
    season         TEXT,
    song           TEXT,
    result         TEXT,
    foreign key (person) references Person(name),
    foreign key (episode_id) references Episode(episode_id)
)

CREATE TABLE Character_Award
(
    award_id  INTEGER,
    character TEXT,
    foreign key (award_id) references Award(award_id)
)

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

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

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

### Question
List down the award name, result, credit category and credited status of the "Billy Kimball".

### Hint
"Billy Kimball" is the person; award name refers to award; credited category refers to category; credited status refers to credited; credited = 'true' means the person is included in the credit list and vice versa

### SQL
SELECT DISTINCT T1.award, T1.result, T2.category, T2.credited FROM Award AS T1 INNER JOIN Credit AS T2 ON T2.episode_id = T1.episode_id WHERE T2.person = 'Billy Kimball';