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
What is the difference of 10 stars votes between the first episode and the last episode?

### Hint
the first episode refers to episode = 1; the last episode refers to episode = 24; 10 stars vote refers to stars = 10; the difference = subtract(votes where episode = 1, votes where episode = 24) where stars = 10

### SQL
SELECT SUM(CASE WHEN T2.episode = 24 THEN T1.votes ELSE 0 END) - SUM(CASE WHEN T2.episode = 1 THEN T1.votes ELSE 0 END) FROM Vote AS T1 INNER JOIN Episode AS T2 ON T2.episode_id = T1.episode_id WHERE T1.stars = 10
### 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 is the episode rating with the most award won?

### Hint
the most award won refers to max(episode_id where result = 'Winner')

### SQL
SELECT T1.rating FROM Episode AS T1 INNER JOIN Award AS T2 ON T1.episode_id = T2.episode_id WHERE T2.result = 'Winner' GROUP BY T1.episode_id ORDER BY COUNT(T2.award_id) 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
How many credits have been displayed from episode 1 until 10?

### Hint
credit displayed refers to credited = 'true'; from episode 1 until 10 refers to episode > = 1 AND episode < = 10

### SQL
SELECT COUNT(T1.person_id) FROM Credit AS T1 INNER JOIN Episode AS T2 ON T1.episode_id = T2.episode_id WHERE T1.credited = 'true' AND T2.episode BETWEEN 1 AND 10
### 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 is the episode that has mafia keyword?

### Hint
mafia keyword refers to Keyword = 'mafia'

### SQL
SELECT T1.episode FROM Episode AS T1 INNER JOIN Keyword AS T2 ON T1.episode_id = T2.episode_id WHERE T2.Keyword = 'mafia'
### 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 is the average star with highest percentage for episodes that have received award?

### Hint
received award refers to result = 'Winner'; the highest percentage refers to max(percent); average star = divide(sum(stars), count(episode_id))

### SQL
SELECT T2.person_id FROM Vote AS T1 INNER JOIN Award AS T2 ON T1.episode_id = T2.episode_id ORDER BY T1.percent 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
What is the average ranking episodes that are nominated for an award?

### Hint
average ranking = divide(sum(rating), sum(episode_id))

### SQL
SELECT SUM(T1.rating) / COUNT(T1.episode) FROM Episode AS T1 INNER JOIN Award AS T2 ON T1.episode_id = T2.episode_id
### 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
How many winners have been awarded a Television award by the "American Bar Association Silver Gavel Awards for Media and the Arts"?

### Hint
winner refers to result = 'Winner'; Television award refers to award = 'Television'; the "American Bar Association Silver Gavel Awards for Media and the Arts" refers to organization = 'American Bar Association Silver Gavel Awards for Media and the Arts'

### SQL
SELECT COUNT(award_id) FROM Award WHERE result = 'Winner' AND award = 'Television' AND organization = 'American Bar Association Silver Gavel Awards for Media and the Arts'
### 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 continent was Michael Preston born on?

### Hint
continent refers to birth_country

### SQL
SELECT birth_country FROM Person WHERE name = 'Michael Preston'
### 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 nominee playing the role of Katrina Ludlow in the Law & Order series?

### Hint
nominee refers to result = 'Nominee'; the role of Katrina Ludlow refers to role = 'Katrina Ludlow'

### SQL
SELECT T2.name FROM Award AS T1 INNER JOIN Person AS T2 ON T1.person_id = T2.person_id WHERE T1.result = 'Nominee' AND T1.role = 'Katrina Ludlow' AND T1.series = 'Law and Order'
### 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 played the role of a teleplay in the episode that won "Best Television Episode"?

### Hint
the role of a teleplay refers to role = 'teleplay'; won refers to result = 'Winner'; "Best Television Episode" refers to award = 'Best Television Episode'

### 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 T1.award = 'Best Television Episode'
### 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 is the date of birth of the actor who played the role of a "writer"?

### Hint
date of birth refers to birthdate

### SQL
SELECT T2.birthdate FROM Award AS T1 INNER JOIN Person AS T2 ON T1.person_id = T2.person_id WHERE T1.role = 'writer'