lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE "chapters"
(
    id          INTEGER
        primary key autoincrement,
    Act         INTEGER not null,
    Scene       INTEGER not null,
    Description TEXT    not null,
    work_id     INTEGER not null
        references works
)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE "characters"
(
    id          INTEGER
        primary key autoincrement,
    CharName    TEXT not null,
    Abbrev      TEXT not null,
    Description TEXT not null
)

CREATE TABLE "paragraphs"
(
    id           INTEGER
        primary key autoincrement,
    ParagraphNum INTEGER           not null,
    PlainText    TEXT              not null,
    character_id INTEGER           not null
        references characters,
    chapter_id   INTEGER default 0 not null
        references chapters
)

CREATE TABLE "works"
(
    id        INTEGER
        primary key autoincrement,
    Title     TEXT    not null,
    LongTitle TEXT    not null,
    Date      INTEGER not null,
    GenreType TEXT    not null
)

### Question
Which work is the character Lord Abergavenny from? Please give its short or abbreviated title.

### Hint
Lord Abergavenny refers to CharName = 'Lord Abergavenny'; short or abbreviated title refers to Title

### SQL
SELECT DISTINCT T1.Title FROM works AS T1 INNER JOIN chapters AS T2 ON T1.id = T2.work_id INNER JOIN paragraphs AS T3 ON T2.id = T3.chapter_id INNER JOIN characters AS T4 ON T3.character_id = T4.id WHERE T4.CharName = 'Lord Abergavenny'
### Database Schema
CREATE TABLE "chapters"
(
    id          INTEGER
        primary key autoincrement,
    Act         INTEGER not null,
    Scene       INTEGER not null,
    Description TEXT    not null,
    work_id     INTEGER not null
        references works
)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE "characters"
(
    id          INTEGER
        primary key autoincrement,
    CharName    TEXT not null,
    Abbrev      TEXT not null,
    Description TEXT not null
)

CREATE TABLE "paragraphs"
(
    id           INTEGER
        primary key autoincrement,
    ParagraphNum INTEGER           not null,
    PlainText    TEXT              not null,
    character_id INTEGER           not null
        references characters,
    chapter_id   INTEGER default 0 not null
        references chapters
)

CREATE TABLE "works"
(
    id        INTEGER
        primary key autoincrement,
    Title     TEXT    not null,
    LongTitle TEXT    not null,
    Date      INTEGER not null,
    GenreType TEXT    not null
)

### Question
Please list the character names of all the characters from the work Twelfth Night.

### Hint
character names refers to CharName; Twelfth Night refers to Title = 'Twelfth Night'

### SQL
SELECT DISTINCT T4.CharName FROM works AS T1 INNER JOIN chapters AS T2 ON T1.id = T2.work_id INNER JOIN paragraphs AS T3 ON T2.id = T3.chapter_id INNER JOIN characters AS T4 ON T3.character_id = T4.id WHERE T1.Title = 'Twelfth Night'
### Database Schema
CREATE TABLE "chapters"
(
    id          INTEGER
        primary key autoincrement,
    Act         INTEGER not null,
    Scene       INTEGER not null,
    Description TEXT    not null,
    work_id     INTEGER not null
        references works
)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE "characters"
(
    id          INTEGER
        primary key autoincrement,
    CharName    TEXT not null,
    Abbrev      TEXT not null,
    Description TEXT not null
)

CREATE TABLE "paragraphs"
(
    id           INTEGER
        primary key autoincrement,
    ParagraphNum INTEGER           not null,
    PlainText    TEXT              not null,
    character_id INTEGER           not null
        references characters,
    chapter_id   INTEGER default 0 not null
        references chapters
)

CREATE TABLE "works"
(
    id        INTEGER
        primary key autoincrement,
    Title     TEXT    not null,
    LongTitle TEXT    not null,
    Date      INTEGER not null,
    GenreType TEXT    not null
)

### Question
How many paragraphs are there in Act 1, Scene 1 in Twelfth Night?

### Hint
Twelfth Night refers to Title = 'Twelfth Night'

### SQL
SELECT SUM(T3.ParagraphNum) FROM works AS T1 INNER JOIN chapters AS T2 ON T1.id = T2.work_id INNER JOIN paragraphs AS T3 ON T2.id = T3.chapter_id WHERE T2.Act = 1 AND T2.Scene = 1 AND T1.Title = 'Twelfth Night'
### Database Schema
CREATE TABLE "chapters"
(
    id          INTEGER
        primary key autoincrement,
    Act         INTEGER not null,
    Scene       INTEGER not null,
    Description TEXT    not null,
    work_id     INTEGER not null
        references works
)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE "characters"
(
    id          INTEGER
        primary key autoincrement,
    CharName    TEXT not null,
    Abbrev      TEXT not null,
    Description TEXT not null
)

CREATE TABLE "paragraphs"
(
    id           INTEGER
        primary key autoincrement,
    ParagraphNum INTEGER           not null,
    PlainText    TEXT              not null,
    character_id INTEGER           not null
        references characters,
    chapter_id   INTEGER default 0 not null
        references chapters
)

CREATE TABLE "works"
(
    id        INTEGER
        primary key autoincrement,
    Title     TEXT    not null,
    LongTitle TEXT    not null,
    Date      INTEGER not null,
    GenreType TEXT    not null
)

### Question
Please list all the paragraphs in Act 1, Scene 1 in Twelfth Night.

### Hint
Twelfth Night refers to Title = 'Twelfth Night'; list the paragraphs refers to PlainText

### SQL
SELECT T3.PlainText FROM works AS T1 INNER JOIN chapters AS T2 ON T1.id = T2.work_id INNER JOIN paragraphs AS T3 ON T2.id = T3.chapter_id WHERE T2.Act = 1 AND T2.Scene = 1 AND T1.Title = 'Twelfth Night'
### Database Schema
CREATE TABLE "chapters"
(
    id          INTEGER
        primary key autoincrement,
    Act         INTEGER not null,
    Scene       INTEGER not null,
    Description TEXT    not null,
    work_id     INTEGER not null
        references works
)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE "characters"
(
    id          INTEGER
        primary key autoincrement,
    CharName    TEXT not null,
    Abbrev      TEXT not null,
    Description TEXT not null
)

CREATE TABLE "paragraphs"
(
    id           INTEGER
        primary key autoincrement,
    ParagraphNum INTEGER           not null,
    PlainText    TEXT              not null,
    character_id INTEGER           not null
        references characters,
    chapter_id   INTEGER default 0 not null
        references chapters
)

CREATE TABLE "works"
(
    id        INTEGER
        primary key autoincrement,
    Title     TEXT    not null,
    LongTitle TEXT    not null,
    Date      INTEGER not null,
    GenreType TEXT    not null
)

### Question
How many paragraphs contain the character Lord Abergavenny?

### Hint
Lord Abergavenny refers to CharName = 'Lord Abergavenny'

### SQL
SELECT SUM(T1.ParagraphNum) FROM paragraphs AS T1 INNER JOIN characters AS T2 ON T1.character_id = T2.id WHERE T2.CharName = 'Lord Abergavenny'
### Database Schema
CREATE TABLE "chapters"
(
    id          INTEGER
        primary key autoincrement,
    Act         INTEGER not null,
    Scene       INTEGER not null,
    Description TEXT    not null,
    work_id     INTEGER not null
        references works
)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE "characters"
(
    id          INTEGER
        primary key autoincrement,
    CharName    TEXT not null,
    Abbrev      TEXT not null,
    Description TEXT not null
)

CREATE TABLE "paragraphs"
(
    id           INTEGER
        primary key autoincrement,
    ParagraphNum INTEGER           not null,
    PlainText    TEXT              not null,
    character_id INTEGER           not null
        references characters,
    chapter_id   INTEGER default 0 not null
        references chapters
)

CREATE TABLE "works"
(
    id        INTEGER
        primary key autoincrement,
    Title     TEXT    not null,
    LongTitle TEXT    not null,
    Date      INTEGER not null,
    GenreType TEXT    not null
)

### Question
Please list the IDs of the paragraphs in which the character "son to Tamora" appears.

### Hint
character "son to Tamora"  refers to characters.Description = 'son to Tamora'

### SQL
SELECT T1.id FROM paragraphs AS T1 INNER JOIN characters AS T2 ON T1.character_id = T2.id WHERE T2.Description = 'son to Tamora'
### Database Schema
CREATE TABLE "chapters"
(
    id          INTEGER
        primary key autoincrement,
    Act         INTEGER not null,
    Scene       INTEGER not null,
    Description TEXT    not null,
    work_id     INTEGER not null
        references works
)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE "characters"
(
    id          INTEGER
        primary key autoincrement,
    CharName    TEXT not null,
    Abbrev      TEXT not null,
    Description TEXT not null
)

CREATE TABLE "paragraphs"
(
    id           INTEGER
        primary key autoincrement,
    ParagraphNum INTEGER           not null,
    PlainText    TEXT              not null,
    character_id INTEGER           not null
        references characters,
    chapter_id   INTEGER default 0 not null
        references chapters
)

CREATE TABLE "works"
(
    id        INTEGER
        primary key autoincrement,
    Title     TEXT    not null,
    LongTitle TEXT    not null,
    Date      INTEGER not null,
    GenreType TEXT    not null
)

### Question
For how many times has the scene "OLIVIA’S house." appeared in Twelfth Night?

### Hint
"OLIVIA’S house."  refers to chapters.Description = 'OLIVIA’S house.'; Twelfth Night refers to Title = 'Twelfth Night'

### SQL
SELECT COUNT(T2.id) FROM works AS T1 INNER JOIN chapters AS T2 ON T1.id = T2.work_id WHERE T2.Description = 'OLIVIA’S house.' AND T1.Title = 'Twelfth Night'
### Database Schema
CREATE TABLE "chapters"
(
    id          INTEGER
        primary key autoincrement,
    Act         INTEGER not null,
    Scene       INTEGER not null,
    Description TEXT    not null,
    work_id     INTEGER not null
        references works
)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE "characters"
(
    id          INTEGER
        primary key autoincrement,
    CharName    TEXT not null,
    Abbrev      TEXT not null,
    Description TEXT not null
)

CREATE TABLE "paragraphs"
(
    id           INTEGER
        primary key autoincrement,
    ParagraphNum INTEGER           not null,
    PlainText    TEXT              not null,
    character_id INTEGER           not null
        references characters,
    chapter_id   INTEGER default 0 not null
        references chapters
)

CREATE TABLE "works"
(
    id        INTEGER
        primary key autoincrement,
    Title     TEXT    not null,
    LongTitle TEXT    not null,
    Date      INTEGER not null,
    GenreType TEXT    not null
)

### Question
How many characters are there in Twelfth Night?

### Hint
Twelfth Night refers to Title = 'Twelfth Night'

### SQL
SELECT COUNT(DISTINCT T4.id) FROM works AS T1 INNER JOIN chapters AS T2 ON T1.id = T2.work_id INNER JOIN paragraphs AS T3 ON T2.id = T3.chapter_id INNER JOIN characters AS T4 ON T3.character_id = T4.id WHERE T1.Title = 'Twelfth Night'
### Database Schema
CREATE TABLE "chapters"
(
    id          INTEGER
        primary key autoincrement,
    Act         INTEGER not null,
    Scene       INTEGER not null,
    Description TEXT    not null,
    work_id     INTEGER not null
        references works
)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE "characters"
(
    id          INTEGER
        primary key autoincrement,
    CharName    TEXT not null,
    Abbrev      TEXT not null,
    Description TEXT not null
)

CREATE TABLE "paragraphs"
(
    id           INTEGER
        primary key autoincrement,
    ParagraphNum INTEGER           not null,
    PlainText    TEXT              not null,
    character_id INTEGER           not null
        references characters,
    chapter_id   INTEGER default 0 not null
        references chapters
)

CREATE TABLE "works"
(
    id        INTEGER
        primary key autoincrement,
    Title     TEXT    not null,
    LongTitle TEXT    not null,
    Date      INTEGER not null,
    GenreType TEXT    not null
)

### Question
Please give the title of the work of Shakespeare that has the most characters.

### Hint
most characters refers to max(count(character_id))

### SQL
SELECT T.Title FROM ( SELECT T1.Title, COUNT(T3.character_id) AS num FROM works T1 INNER JOIN chapters T2 ON T1.id = T2.work_id INNER JOIN paragraphs T3 ON T2.id = T3.chapter_id INNER JOIN characters T4 ON T3.character_id = T4.id GROUP BY T3.character_id, T1.Title ) T ORDER BY T.num DESC LIMIT 1
### Database Schema
CREATE TABLE "chapters"
(
    id          INTEGER
        primary key autoincrement,
    Act         INTEGER not null,
    Scene       INTEGER not null,
    Description TEXT    not null,
    work_id     INTEGER not null
        references works
)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE "characters"
(
    id          INTEGER
        primary key autoincrement,
    CharName    TEXT not null,
    Abbrev      TEXT not null,
    Description TEXT not null
)

CREATE TABLE "paragraphs"
(
    id           INTEGER
        primary key autoincrement,
    ParagraphNum INTEGER           not null,
    PlainText    TEXT              not null,
    character_id INTEGER           not null
        references characters,
    chapter_id   INTEGER default 0 not null
        references chapters
)

CREATE TABLE "works"
(
    id        INTEGER
        primary key autoincrement,
    Title     TEXT    not null,
    LongTitle TEXT    not null,
    Date      INTEGER not null,
    GenreType TEXT    not null
)

### Question
What is the average number of characters in all the works of Shakespeare?

### Hint
average number = divide(sum(character_id), count(work_id))

### SQL
SELECT SUM(DISTINCT T4.id) / COUNT(T1.id) FROM works AS T1 INNER JOIN chapters AS T2 ON T1.id = T2.work_id INNER JOIN paragraphs AS T3 ON T2.id = T3.chapter_id INNER JOIN characters AS T4 ON T3.character_id = T4.id
### Database Schema
CREATE TABLE "chapters"
(
    id          INTEGER
        primary key autoincrement,
    Act         INTEGER not null,
    Scene       INTEGER not null,
    Description TEXT    not null,
    work_id     INTEGER not null
        references works
)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE "characters"
(
    id          INTEGER
        primary key autoincrement,
    CharName    TEXT not null,
    Abbrev      TEXT not null,
    Description TEXT not null
)

CREATE TABLE "paragraphs"
(
    id           INTEGER
        primary key autoincrement,
    ParagraphNum INTEGER           not null,
    PlainText    TEXT              not null,
    character_id INTEGER           not null
        references characters,
    chapter_id   INTEGER default 0 not null
        references chapters
)

CREATE TABLE "works"
(
    id        INTEGER
        primary key autoincrement,
    Title     TEXT    not null,
    LongTitle TEXT    not null,
    Date      INTEGER not null,
    GenreType TEXT    not null
)

### Question
How many scenes are there on average in one act in Twelfth Night?

### Hint
Twelfth Night refers to Title = 'Twelfth Night'; average scene = divide(sum(Scene), count(Act))

### SQL
SELECT SUM(T2.Scene) / COUNT(T2.Act) FROM works AS T1 INNER JOIN chapters AS T2 ON T1.id = T2.work_id WHERE T1.Title = 'Twelfth Night'
### Database Schema
CREATE TABLE "chapters"
(
    id          INTEGER
        primary key autoincrement,
    Act         INTEGER not null,
    Scene       INTEGER not null,
    Description TEXT    not null,
    work_id     INTEGER not null
        references works
)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE "characters"
(
    id          INTEGER
        primary key autoincrement,
    CharName    TEXT not null,
    Abbrev      TEXT not null,
    Description TEXT not null
)

CREATE TABLE "paragraphs"
(
    id           INTEGER
        primary key autoincrement,
    ParagraphNum INTEGER           not null,
    PlainText    TEXT              not null,
    character_id INTEGER           not null
        references characters,
    chapter_id   INTEGER default 0 not null
        references chapters
)

CREATE TABLE "works"
(
    id        INTEGER
        primary key autoincrement,
    Title     TEXT    not null,
    LongTitle TEXT    not null,
    Date      INTEGER not null,
    GenreType TEXT    not null
)

### Question
How many comedies did Shakespeare create?

### Hint
comedies refers to GenreType = 'Comedy'

### SQL
SELECT COUNT(id) FROM works WHERE GenreType = 'Comedy'
### Database Schema
CREATE TABLE "chapters"
(
    id          INTEGER
        primary key autoincrement,
    Act         INTEGER not null,
    Scene       INTEGER not null,
    Description TEXT    not null,
    work_id     INTEGER not null
        references works
)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE "characters"
(
    id          INTEGER
        primary key autoincrement,
    CharName    TEXT not null,
    Abbrev      TEXT not null,
    Description TEXT not null
)

CREATE TABLE "paragraphs"
(
    id           INTEGER
        primary key autoincrement,
    ParagraphNum INTEGER           not null,
    PlainText    TEXT              not null,
    character_id INTEGER           not null
        references characters,
    chapter_id   INTEGER default 0 not null
        references chapters
)

CREATE TABLE "works"
(
    id        INTEGER
        primary key autoincrement,
    Title     TEXT    not null,
    LongTitle TEXT    not null,
    Date      INTEGER not null,
    GenreType TEXT    not null
)

### Question
When did Shakespeare write the first poem?

### Hint
first poem refers to GenreType = 'Poem' and Date = 'min'

### SQL
SELECT MIN(Date) FROM works WHERE GenreType = 'Poem'
### Database Schema
CREATE TABLE "chapters"
(
    id          INTEGER
        primary key autoincrement,
    Act         INTEGER not null,
    Scene       INTEGER not null,
    Description TEXT    not null,
    work_id     INTEGER not null
        references works
)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE "characters"
(
    id          INTEGER
        primary key autoincrement,
    CharName    TEXT not null,
    Abbrev      TEXT not null,
    Description TEXT not null
)

CREATE TABLE "paragraphs"
(
    id           INTEGER
        primary key autoincrement,
    ParagraphNum INTEGER           not null,
    PlainText    TEXT              not null,
    character_id INTEGER           not null
        references characters,
    chapter_id   INTEGER default 0 not null
        references chapters
)

CREATE TABLE "works"
(
    id        INTEGER
        primary key autoincrement,
    Title     TEXT    not null,
    LongTitle TEXT    not null,
    Date      INTEGER not null,
    GenreType TEXT    not null
)

### Question
Give the abbreviation name for the character "Earl of Westmoreland".

### Hint
abbreviation name refers to Abbrev; character "Earl of Westmoreland" refers to CharName = 'Earl of Westmoreland'

### SQL
SELECT DISTINCT Abbrev FROM characters WHERE CharName = 'Earl of Westmoreland'
### Database Schema
CREATE TABLE "chapters"
(
    id          INTEGER
        primary key autoincrement,
    Act         INTEGER not null,
    Scene       INTEGER not null,
    Description TEXT    not null,
    work_id     INTEGER not null
        references works
)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE "characters"
(
    id          INTEGER
        primary key autoincrement,
    CharName    TEXT not null,
    Abbrev      TEXT not null,
    Description TEXT not null
)

CREATE TABLE "paragraphs"
(
    id           INTEGER
        primary key autoincrement,
    ParagraphNum INTEGER           not null,
    PlainText    TEXT              not null,
    character_id INTEGER           not null
        references characters,
    chapter_id   INTEGER default 0 not null
        references chapters
)

CREATE TABLE "works"
(
    id        INTEGER
        primary key autoincrement,
    Title     TEXT    not null,
    LongTitle TEXT    not null,
    Date      INTEGER not null,
    GenreType TEXT    not null
)

### Question
Which chapter has the most paragraphs? Give the description of the chapter.

### Hint
most paragraphs refers to max(count(chapter_id))

### SQL
SELECT T1.Description FROM chapters AS T1 INNER JOIN paragraphs AS T2 ON T1.id = T2.chapter_id ORDER BY T2.ParagraphNum DESC LIMIT 1
### Database Schema
CREATE TABLE "chapters"
(
    id          INTEGER
        primary key autoincrement,
    Act         INTEGER not null,
    Scene       INTEGER not null,
    Description TEXT    not null,
    work_id     INTEGER not null
        references works
)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE "characters"
(
    id          INTEGER
        primary key autoincrement,
    CharName    TEXT not null,
    Abbrev      TEXT not null,
    Description TEXT not null
)

CREATE TABLE "paragraphs"
(
    id           INTEGER
        primary key autoincrement,
    ParagraphNum INTEGER           not null,
    PlainText    TEXT              not null,
    character_id INTEGER           not null
        references characters,
    chapter_id   INTEGER default 0 not null
        references chapters
)

CREATE TABLE "works"
(
    id        INTEGER
        primary key autoincrement,
    Title     TEXT    not null,
    LongTitle TEXT    not null,
    Date      INTEGER not null,
    GenreType TEXT    not null
)

### Question
Which character was mentioned in the paragraph "Would he do so, I'ld beg your precious mistress, Which he counts but a trifle."? Give the character name.

### Hint
paragraph "Would he do so, I'ld beg your precious mistress, Which he counts but a trifle."  refers to PlainText = 'Would he do so, I'ld beg your precious mistress, Which he counts but a trifle.'

### SQL
SELECT T1.CharName FROM characters AS T1 INNER JOIN paragraphs AS T2 ON T1.id = T2.character_id WHERE T2.PlainText = 'Would he do so, I''ld beg your precious mistress,Which he counts but a trifle.'
### Database Schema
CREATE TABLE "chapters"
(
    id          INTEGER
        primary key autoincrement,
    Act         INTEGER not null,
    Scene       INTEGER not null,
    Description TEXT    not null,
    work_id     INTEGER not null
        references works
)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE "characters"
(
    id          INTEGER
        primary key autoincrement,
    CharName    TEXT not null,
    Abbrev      TEXT not null,
    Description TEXT not null
)

CREATE TABLE "paragraphs"
(
    id           INTEGER
        primary key autoincrement,
    ParagraphNum INTEGER           not null,
    PlainText    TEXT              not null,
    character_id INTEGER           not null
        references characters,
    chapter_id   INTEGER default 0 not null
        references chapters
)

CREATE TABLE "works"
(
    id        INTEGER
        primary key autoincrement,
    Title     TEXT    not null,
    LongTitle TEXT    not null,
    Date      INTEGER not null,
    GenreType TEXT    not null
)

### Question
How many characters are there in Titus Andronicus?

### Hint
Titus Andronicus refers to Title = 'Titus Andronicus'

### SQL
SELECT COUNT(DISTINCT T3.character_id) FROM works AS T1 INNER JOIN chapters AS T2 ON T1.id = T2.work_id INNER JOIN paragraphs AS T3 ON T2.id = T3.chapter_id WHERE T1.Title = 'Titus Andronicus'