lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE cites
(
    cited_paper_id  TEXT not null,
    citing_paper_id TEXT not null,
    primary key (cited_paper_id, citing_paper_id)
)

CREATE TABLE paper
(
    paper_id    TEXT  not null
        primary key,
    class_label TEXT not null
)

CREATE TABLE content
(
    paper_id     TEXT not null,
    word_cited_id TEXT not null,
    primary key (paper_id, word_cited_id),
    foreign key (paper_id) references paper(paper_id)
)

### Question
Name the paper which is cited most times and the paper which is cited least times? Also, find the number of times each one is cited.

### SQL
SELECT cited_paper_id, COUNT(cited_paper_id), ( SELECT cited_paper_id FROM cites GROUP BY cited_paper_id ORDER BY COUNT(cited_paper_id) ASC LIMIT 1 ), ( SELECT COUNT(cited_paper_id) FROM cites GROUP BY cited_paper_id ORDER BY COUNT(cited_paper_id) ASC LIMIT 1 ) FROM cites GROUP BY cited_paper_id ORDER BY COUNT(cited_paper_id) DESC LIMIT 1
### Database Schema
CREATE TABLE cites
(
    cited_paper_id  TEXT not null,
    citing_paper_id TEXT not null,
    primary key (cited_paper_id, citing_paper_id)
)

CREATE TABLE paper
(
    paper_id    TEXT  not null
        primary key,
    class_label TEXT not null
)

CREATE TABLE content
(
    paper_id     TEXT not null,
    word_cited_id TEXT not null,
    primary key (paper_id, word_cited_id),
    foreign key (paper_id) references paper(paper_id)
)

### Question
On average, how many papers are under the ML class?

### Hint
class refers to class_label; average = divide(count(paper_id where class_label = 'M')), (count(paper_id)));

### SQL
SELECT CAST(COUNT(CASE WHEN class_label = 'ML' THEN paper_id ELSE NULL END) AS REAL) / COUNT(paper_id) FROM paper
### Database Schema
CREATE TABLE cites
(
    cited_paper_id  TEXT not null,
    citing_paper_id TEXT not null,
    primary key (cited_paper_id, citing_paper_id)
)

CREATE TABLE paper
(
    paper_id    TEXT  not null
        primary key,
    class_label TEXT not null
)

CREATE TABLE content
(
    paper_id     TEXT not null,
    word_cited_id TEXT not null,
    primary key (paper_id, word_cited_id),
    foreign key (paper_id) references paper(paper_id)
)

### Question
Find the words cited in papers that are cited by sima01computational?

### Hint
paper cited by refers to citing_paper_id; citing_paper_id = 'sima01computational';

### SQL
SELECT DISTINCT T2.word_cited_id FROM cites AS T1 INNER JOIN content AS T2 ON T1.cited_paper_id = T2.paper_id WHERE T1.citing_paper_id = 'sima01computational'
### Database Schema
CREATE TABLE cites
(
    cited_paper_id  TEXT not null,
    citing_paper_id TEXT not null,
    primary key (cited_paper_id, citing_paper_id)
)

CREATE TABLE paper
(
    paper_id    TEXT  not null
        primary key,
    class_label TEXT not null
)

CREATE TABLE content
(
    paper_id     TEXT not null,
    word_cited_id TEXT not null,
    primary key (paper_id, word_cited_id),
    foreign key (paper_id) references paper(paper_id)
)

### Question
How many papers were cited by schmidt99advanced cited word3555?

### Hint
paper cited by refers to citing_paper_id; citing_paper_id = 'schmidt99advanced';

### SQL
SELECT COUNT(T2.paper_id) FROM cites AS T1 INNER JOIN content AS T2 ON T1.cited_paper_id = T2.paper_id WHERE T1.citing_paper_id = 'schmidt99advanced' AND T2.word_cited_id = 'word3555'
### Database Schema
CREATE TABLE cites
(
    cited_paper_id  TEXT not null,
    citing_paper_id TEXT not null,
    primary key (cited_paper_id, citing_paper_id)
)

CREATE TABLE paper
(
    paper_id    TEXT  not null
        primary key,
    class_label TEXT not null
)

CREATE TABLE content
(
    paper_id     TEXT not null,
    word_cited_id TEXT not null,
    primary key (paper_id, word_cited_id),
    foreign key (paper_id) references paper(paper_id)
)

### Question
Under what classification do the papers that cited word1163 belong?

### SQL
SELECT DISTINCT T1.class_label FROM paper AS T1 INNER JOIN content AS T2 ON T1.paper_id = T2.paper_id WHERE T2.word_cited_id = 'word1163'
### Database Schema
CREATE TABLE cites
(
    cited_paper_id  TEXT not null,
    citing_paper_id TEXT not null,
    primary key (cited_paper_id, citing_paper_id)
)

CREATE TABLE paper
(
    paper_id    TEXT  not null
        primary key,
    class_label TEXT not null
)

CREATE TABLE content
(
    paper_id     TEXT not null,
    word_cited_id TEXT not null,
    primary key (paper_id, word_cited_id),
    foreign key (paper_id) references paper(paper_id)
)

### Question
Among the papers under DB classification, which paper has the highest number of words cited?

### Hint
classification refers to class_label; class_label = 'DB';

### SQL
SELECT T1.paper_id FROM paper AS T1 INNER JOIN content AS T2 ON T1.paper_id = T2.paper_id WHERE T1.class_label = 'DB' GROUP BY T1.paper_id ORDER BY COUNT(T2.word_cited_id) DESC LIMIT 1
### Database Schema
CREATE TABLE cites
(
    cited_paper_id  TEXT not null,
    citing_paper_id TEXT not null,
    primary key (cited_paper_id, citing_paper_id)
)

CREATE TABLE paper
(
    paper_id    TEXT  not null
        primary key,
    class_label TEXT not null
)

CREATE TABLE content
(
    paper_id     TEXT not null,
    word_cited_id TEXT not null,
    primary key (paper_id, word_cited_id),
    foreign key (paper_id) references paper(paper_id)
)

### Question
In the papers classified as ML, how many cited butz01algorithmic?

### Hint
classification refers to class_label; paper cited by refers to citing_paper_id; citing_paper_id = 'butz01algorithmic';

### SQL
SELECT COUNT(T1.paper_id) FROM paper AS T1 INNER JOIN cites AS T2 ON T1.paper_id = T2.citing_paper_id WHERE T1.class_label = 'ML' AND T2.cited_paper_id = 'butz01algorithmic'
### 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
Which crew member of the simpson 20s is the oldest?

### Hint
oldest refers to Min(birthdate)

### SQL
SELECT name FROM Person WHERE birthdate IS NOT NULL ORDER BY birthdate ASC 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
What's the nickname for Dan Castellaneta?

### Hint
"Dan Castellaneta" is the name of Person

### SQL
SELECT nickname FROM Person WHERE name = 'Dan Castellaneta';
### 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 crew members of the simpson 20s born in the New York city, how many of them were born after the year 1970?

### Hint
born in New York city refers to birth_region = 'New York'; born after year 1970 refers to ('%Y', birthdate) > 1970

### SQL
SELECT COUNT(name) FROM Person WHERE birth_region = 'New York' AND SUBSTR(birthdate, 1, 4) > '1970';
### 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 which country was the winner of the Outstanding Voice-Over Performance award of 2009 born?

### Hint
"Outstanding Voice-Over Performance" is the award; 2009 refers to year = 2009; 'Winner' is the result; country refers to birth_country

### SQL
SELECT T1.birth_country FROM Person AS T1 INNER JOIN Award AS T2 ON T1.name = T2.person WHERE T2.award = 'Outstanding Voice-Over Performance' AND T2.year = 2009 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
Please list the names of all the awards won by the crew member whose nickname is Doofus.

### Hint
award won refers to result = 'Winner'

### SQL
SELECT T2.award FROM Person AS T1 INNER JOIN Award AS T2 ON T1.name = T2.person WHERE T1.nickname = 'Doofus' 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
How many crew members who were born in the USA were nominated for the Outstanding Animated Program (For Programming Less Than One Hour) award in 2009?

### Hint
born in USA refers to birth_country = 'USA'; were nominated refers to result = 'Nominee'; 'Outstanding Animated Program (For Programming Less Than One Hour)' is the award; in 2009 refers to year = 2009

### 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 = 'Nominee' AND T2.award = 'Outstanding Animated Program (For Programming Less Than One Hour)' AND T2.year = 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
Which character won the Outstanding Voice-Over Performance award in 2009?

### Hint
won refers to result = 'Winner'

### SQL
SELECT DISTINCT T1.character FROM Character_Award AS T1 INNER JOIN Award AS T2 ON T1.award_id = T2.award_id WHERE T2.award = 'Outstanding Voice-Over Performance' AND T2.year = 2009 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
Please list all the keywords of the episode Lost Verizon.

### Hint
"Lost Verizon" is the title of episode

### SQL
SELECT T2.keyword FROM Episode AS T1 INNER JOIN Keyword AS T2 ON T1.episode_id = T2.episode_id WHERE T1.title = 'Lost Verizon';