lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE divisions
(
    division TEXT not null
        primary key,
    name     TEXT,
    country  TEXT
)

CREATE TABLE matchs
(
    Div      TEXT,
    Date     DATE,
    HomeTeam TEXT,
    AwayTeam TEXT,
    FTHG     INTEGER,
    FTAG     INTEGER,
    FTR      TEXT,
    season   INTEGER,
    foreign key (Div) references divisions(division)
)

### Question
Which country had the game that Away team made the most goals?

### Hint
the most goals refer to MAX(FTAG), which is a short name for Final-time Away-team Goals;

### SQL
SELECT T2.country FROM matchs AS T1 INNER JOIN divisions AS T2 ON T1.Div = T2.division GROUP BY T2.country ORDER BY SUM(T1.FTAG) DESC LIMIT 1
### Database Schema
CREATE TABLE divisions
(
    division TEXT not null
        primary key,
    name     TEXT,
    country  TEXT
)

CREATE TABLE matchs
(
    Div      TEXT,
    Date     DATE,
    HomeTeam TEXT,
    AwayTeam TEXT,
    FTHG     INTEGER,
    FTAG     INTEGER,
    FTR      TEXT,
    season   INTEGER,
    foreign key (Div) references divisions(division)
)

### Question
For a game had a score of 1-8 in the year of 2011, what division was that game in? Give the full name of the division.

### Hint
2011 refers to season; a score of 1-8 refers to FTHG = '1' and FTAG = '8';

### SQL
SELECT T2.division, T2.name FROM matchs AS T1 INNER JOIN divisions AS T2 ON T1.Div = T2.division WHERE T1.season = 2011 AND T1.FTHG = 1 AND T1.FTAG = 8
### Database Schema
CREATE TABLE divisions
(
    division TEXT not null
        primary key,
    name     TEXT,
    country  TEXT
)

CREATE TABLE matchs
(
    Div      TEXT,
    Date     DATE,
    HomeTeam TEXT,
    AwayTeam TEXT,
    FTHG     INTEGER,
    FTAG     INTEGER,
    FTR      TEXT,
    season   INTEGER,
    foreign key (Div) references divisions(division)
)

### Question
Which division had the most games with more than 5 total field goals on 2020/2/22? Give the full name of the division?

### Hint
more than 5 total field goals refers to SUM(FTHG, FTAG)>5, which are short names for Final-time Home-team Goals and Final-time Away-team Goals; 2020/2/22 is a date;

### SQL
SELECT T2.division, T2.name FROM matchs AS T1 INNER JOIN divisions AS T2 ON T1.Div = T2.division WHERE T1.Date = '2020-02-22' AND T1.FTAG + T1.FTHG > 5 ORDER BY T1.FTAG + T1.FTHG DESC LIMIT 1
### Database Schema
CREATE TABLE divisions
(
    division TEXT not null
        primary key,
    name     TEXT,
    country  TEXT
)

CREATE TABLE matchs
(
    Div      TEXT,
    Date     DATE,
    HomeTeam TEXT,
    AwayTeam TEXT,
    FTHG     INTEGER,
    FTAG     INTEGER,
    FTR      TEXT,
    season   INTEGER,
    foreign key (Div) references divisions(division)
)

### Question
Give the full name of the divison that had the most 0-0 games.

### Hint
the most 0-0 games means a no-score draw and refers to MAX(COUNT(Div where FTHG = '0' and FTAG = '0'));

### SQL
SELECT T2.name FROM matchs AS T1 INNER JOIN divisions AS T2 ON T1.Div = T2.division WHERE T1.FTAG = 0 AND T1.FTHG = 0 GROUP BY T2.division ORDER BY COUNT(T1.FTAG) DESC LIMIT 1
### Database Schema
CREATE TABLE divisions
(
    division TEXT not null
        primary key,
    name     TEXT,
    country  TEXT
)

CREATE TABLE matchs
(
    Div      TEXT,
    Date     DATE,
    HomeTeam TEXT,
    AwayTeam TEXT,
    FTHG     INTEGER,
    FTAG     INTEGER,
    FTR      TEXT,
    season   INTEGER,
    foreign key (Div) references divisions(division)
)

### Question
How many Scottish League One games took place on the day that "Pro Vercelli" and "Pescara"had a 5-2 game?

### Hint
Pro Vercelli and Pescara are names of teams; HomeTeam = 'Pro Vercelli'; AwayTeam = 'Pescara'; 5-2 is a score where FTHG = '5' and FTAG = '2'; Scottish League One is a name of division; games refer to Div;

### SQL
SELECT COUNT(T1.Date) FROM matchs AS T1 INNER JOIN divisions AS T2 ON T1.Div = T2.division WHERE T2.name = 'Scottish League One' AND T1.Date = ( SELECT Date FROM matchs WHERE FTHG = 5 AND FTAG = 2 AND HomeTeam = 'Pro Vercelli' AND AwayTeam = 'Pescara' )
### Database Schema
CREATE TABLE divisions
(
    division TEXT not null
        primary key,
    name     TEXT,
    country  TEXT
)

CREATE TABLE matchs
(
    Div      TEXT,
    Date     DATE,
    HomeTeam TEXT,
    AwayTeam TEXT,
    FTHG     INTEGER,
    FTAG     INTEGER,
    FTR      TEXT,
    season   INTEGER,
    foreign key (Div) references divisions(division)
)

### Question
List the number of games that ended up with 5-0 in Greece.

### Hint
5-0 is a score where FTHG = '5' and FTAG = '0'; Greece is a name of country; games refer to Div;

### SQL
SELECT COUNT(T1.Div) FROM matchs AS T1 INNER JOIN divisions AS T2 ON T1.Div = T2.division WHERE T2.country = 'Greece' AND T1.FTHG = 5 AND T1.FTAG = 0
### Database Schema
CREATE TABLE divisions
(
    division TEXT not null
        primary key,
    name     TEXT,
    country  TEXT
)

CREATE TABLE matchs
(
    Div      TEXT,
    Date     DATE,
    HomeTeam TEXT,
    AwayTeam TEXT,
    FTHG     INTEGER,
    FTAG     INTEGER,
    FTR      TEXT,
    season   INTEGER,
    foreign key (Div) references divisions(division)
)

### Question
Which country did Bradford Team belongs to?

### Hint
Bradford team refers to HomeTeam = 'Bradford' or AwayTeam = 'Bradford';

### SQL
SELECT DISTINCT T2.country FROM matchs AS T1 INNER JOIN divisions AS T2 ON T1.Div = T2.division WHERE T1.HomeTeam = 'Bradford' OR T1.AwayTeam = 'Bradford'
### Database Schema
CREATE TABLE divisions
(
    division TEXT not null
        primary key,
    name     TEXT,
    country  TEXT
)

CREATE TABLE matchs
(
    Div      TEXT,
    Date     DATE,
    HomeTeam TEXT,
    AwayTeam TEXT,
    FTHG     INTEGER,
    FTAG     INTEGER,
    FTR      TEXT,
    season   INTEGER,
    foreign key (Div) references divisions(division)
)

### Question
How many Eredivisie teams have played in 2008?

### Hint
Eredivisie is the name of division; 2008 refers to season; teams refer to HomeTeam;

### SQL
SELECT COUNT(DISTINCT T1.HomeTeam) FROM matchs AS T1 INNER JOIN divisions AS T2 ON T1.Div = T2.division WHERE T2.name = 'Eredivisie' AND T1.season = 2008
### Database Schema
CREATE TABLE divisions
(
    division TEXT not null
        primary key,
    name     TEXT,
    country  TEXT
)

CREATE TABLE matchs
(
    Div      TEXT,
    Date     DATE,
    HomeTeam TEXT,
    AwayTeam TEXT,
    FTHG     INTEGER,
    FTAG     INTEGER,
    FTR      TEXT,
    season   INTEGER,
    foreign key (Div) references divisions(division)
)

### Question
What's the home win ratio of the Bundesliga division in 2021?

### Hint
home win refers to FTR = 'H', where H stands for home victory; season = '2021'; Bundesliga is a name of division; DIVIDE(COUNT(Div where FTR = 'H, season = '2021' and name = 'Bundesliga'), COUNT(Div where season = '2021' and name = 'Bundesliga')) as percentage;

### SQL
SELECT CAST(COUNT(CASE WHEN T1.FTR = 'H' THEN 1 ELSE NULL END) AS REAL) * 100 / COUNT(T1.FTR) FROM matchs AS T1 INNER JOIN divisions AS T2 ON T1.Div = T2.division WHERE T1.season = 2021 AND T2.name = 'Bundesliga'
### Database Schema
CREATE TABLE divisions
(
    division TEXT not null
        primary key,
    name     TEXT,
    country  TEXT
)

CREATE TABLE matchs
(
    Div      TEXT,
    Date     DATE,
    HomeTeam TEXT,
    AwayTeam TEXT,
    FTHG     INTEGER,
    FTAG     INTEGER,
    FTR      TEXT,
    season   INTEGER,
    foreign key (Div) references divisions(division)
)

### Question
For all the games ended up with 1-1, what percentage of them are from Liga NOS division?

### Hint
1-1 is a score where FTHG = '1' and FTAG = '1'; Liga NOS is the name of division; DIVIDE(COUNT(Div where FTHG = '1', FTAG = '1', name = 'Liga NOS'), COUNT(Div where FTHG = '1' and FTAG = '1')) as percentage;

### SQL
SELECT CAST(COUNT(CASE WHEN T2.name = 'Liga NOS' THEN T1.Div ELSE NULL END) AS REAL) * 100 / COUNT(T1.Div) FROM matchs AS T1 INNER JOIN divisions AS T2 ON T1.Div = T2.division WHERE T1.FTHG = 1 AND FTAG = 1
### Database Schema
CREATE TABLE divisions
(
    division TEXT not null
        primary key,
    name     TEXT,
    country  TEXT
)

CREATE TABLE matchs
(
    Div      TEXT,
    Date     DATE,
    HomeTeam TEXT,
    AwayTeam TEXT,
    FTHG     INTEGER,
    FTAG     INTEGER,
    FTR      TEXT,
    season   INTEGER,
    foreign key (Div) references divisions(division)
)

### Question
How many matches were held during the 2021 season's Premier League?

### Hint
Premier League is the name of division;

### SQL
SELECT COUNT(T1.Div) FROM matchs AS T1 INNER JOIN divisions AS T2 ON T1.Div = T2.division WHERE T1.season = 2021 AND T2.name = 'Premier League'
### Database Schema
CREATE TABLE divisions
(
    division TEXT not null
        primary key,
    name     TEXT,
    country  TEXT
)

CREATE TABLE matchs
(
    Div      TEXT,
    Date     DATE,
    HomeTeam TEXT,
    AwayTeam TEXT,
    FTHG     INTEGER,
    FTAG     INTEGER,
    FTR      TEXT,
    season   INTEGER,
    foreign key (Div) references divisions(division)
)

### Question
Which team was the home team in the match of the Bundesliga division on 2020/10/2?

### Hint
Bundesliga is the name of division; Date = '2020/10/2';

### SQL
SELECT T1.HomeTeam FROM matchs AS T1 INNER JOIN divisions AS T2 ON T1.Div = T2.division WHERE T1.Date = '2020-10-02' AND T2.name = 'Bundesliga'
### Database Schema
CREATE TABLE divisions
(
    division TEXT not null
        primary key,
    name     TEXT,
    country  TEXT
)

CREATE TABLE matchs
(
    Div      TEXT,
    Date     DATE,
    HomeTeam TEXT,
    AwayTeam TEXT,
    FTHG     INTEGER,
    FTAG     INTEGER,
    FTR      TEXT,
    season   INTEGER,
    foreign key (Div) references divisions(division)
)

### Question
Which team won the match of the Bundesliga division on 2020/10/2?

### Hint
Bundesliga is the name of division; Date = '2020/10/2'; won the match refers to FTR = 'H';

### SQL
SELECT CASE WHEN T1.FTR = 'H' THEN T1.HomeTeam WHEN T1.FTR = 'A' THEN T1.AwayTeam END WINNER FROM matchs AS T1 INNER JOIN divisions AS T2 ON T1.Div = T2.division WHERE T1.Date = '2020-10-02' AND T2.name = 'Bundesliga'
### Database Schema
CREATE TABLE divisions
(
    division TEXT not null
        primary key,
    name     TEXT,
    country  TEXT
)

CREATE TABLE matchs
(
    Div      TEXT,
    Date     DATE,
    HomeTeam TEXT,
    AwayTeam TEXT,
    FTHG     INTEGER,
    FTAG     INTEGER,
    FTR      TEXT,
    season   INTEGER,
    foreign key (Div) references divisions(division)
)

### Question
Which team has the most victories as the home team in matches of the Bundesliga division?

### Hint
Bundesliga is the name of division; the most victories as the home team refers to MAX(COUNT(FTR = 'H'));

### SQL
SELECT T1.HomeTeam FROM matchs AS T1 INNER JOIN divisions AS T2 ON T1.Div = T2.division WHERE T2.name = 'Bundesliga' AND T1.FTR = 'H' GROUP BY T1.HomeTeam ORDER BY COUNT(T1.FTR) DESC LIMIT 1
### Database Schema
CREATE TABLE divisions
(
    division TEXT not null
        primary key,
    name     TEXT,
    country  TEXT
)

CREATE TABLE matchs
(
    Div      TEXT,
    Date     DATE,
    HomeTeam TEXT,
    AwayTeam TEXT,
    FTHG     INTEGER,
    FTAG     INTEGER,
    FTR      TEXT,
    season   INTEGER,
    foreign key (Div) references divisions(division)
)

### Question
How many times did the team Werder Bremen win as the away team in matches of the Bundesliga division?

### Hint
Bundesliga is the name of division; win as the away team refers to FTR = 'A', where 'A' stands for away victory;

### SQL
SELECT COUNT(T1.Div) FROM matchs AS T1 INNER JOIN divisions AS T2 ON T1.Div = T2.division WHERE T2.name = 'Bundesliga' AND T1.AwayTeam = 'Werder Bremen' AND T1.FTR = 'A'
### Database Schema
CREATE TABLE divisions
(
    division TEXT not null
        primary key,
    name     TEXT,
    country  TEXT
)

CREATE TABLE matchs
(
    Div      TEXT,
    Date     DATE,
    HomeTeam TEXT,
    AwayTeam TEXT,
    FTHG     INTEGER,
    FTAG     INTEGER,
    FTR      TEXT,
    season   INTEGER,
    foreign key (Div) references divisions(division)
)

### Question
How many matches of the Bundesliga division ended with an away victory in the 2021 season?

### Hint
Bundesliga is the name of division; away victory refers to FTR = 'A', where 'A' stands for away victory;

### SQL
SELECT COUNT(T1.Div) FROM matchs AS T1 INNER JOIN divisions AS T2 ON T1.Div = T2.division WHERE T2.name = 'Bundesliga' AND T1.FTR = 'A' AND T1.season = 2021
### Database Schema
CREATE TABLE divisions
(
    division TEXT not null
        primary key,
    name     TEXT,
    country  TEXT
)

CREATE TABLE matchs
(
    Div      TEXT,
    Date     DATE,
    HomeTeam TEXT,
    AwayTeam TEXT,
    FTHG     INTEGER,
    FTAG     INTEGER,
    FTR      TEXT,
    season   INTEGER,
    foreign key (Div) references divisions(division)
)

### Question
Of the matches in all seasons of the Bundesliga division, how many of them ended with a tie?

### Hint
Bundesliga is the name of division; tie refers to FTR = 'D', where D stands for draft;

### SQL
SELECT COUNT(T1.Div) FROM matchs AS T1 INNER JOIN divisions AS T2 ON T1.Div = T2.division WHERE T2.name = 'Bundesliga' AND T1.FTR = 'D'
### Database Schema
CREATE TABLE divisions
(
    division TEXT not null
        primary key,
    name     TEXT,
    country  TEXT
)

CREATE TABLE matchs
(
    Div      TEXT,
    Date     DATE,
    HomeTeam TEXT,
    AwayTeam TEXT,
    FTHG     INTEGER,
    FTAG     INTEGER,
    FTR      TEXT,
    season   INTEGER,
    foreign key (Div) references divisions(division)
)

### Question
How many home victories does the Bundesliga division have in more or less than the Premier League division in the 2021 season?

### Hint
Bundesliga and the Premier League are names of division; home victories refer to FTR = 'H', where H stands for home victory; SUBTRACT(COUNT(FTR = 'H' where season = 2021, name = 'Bundesliga'), COUNT(FTR = 'H' where season = 2021, name = 'Premier League'));

### SQL
SELECT COUNT(CASE WHEN T2.name = 'Bundesliga' THEN 1 ELSE NULL END) - COUNT(CASE WHEN T2.name = 'Premier League' THEN 1 ELSE NULL END) FROM matchs AS T1 INNER JOIN divisions AS T2 ON T1.Div = T2.division WHERE T1.season = 2021 AND T1.FTR = 'H'
### Database Schema
CREATE TABLE divisions
(
    division TEXT not null
        primary key,
    name     TEXT,
    country  TEXT
)

CREATE TABLE matchs
(
    Div      TEXT,
    Date     DATE,
    HomeTeam TEXT,
    AwayTeam TEXT,
    FTHG     INTEGER,
    FTAG     INTEGER,
    FTR      TEXT,
    season   INTEGER,
    foreign key (Div) references divisions(division)
)

### Question
Please list the home teams in the matches of the Bundesliga division that ended with a home victory in the 2021 season.

### Hint
Bundesliga is the name of division; home victory refers to refer to FTR = 'H', where H stands for home victory;

### SQL
SELECT DISTINCT T1.HomeTeam FROM matchs AS T1 INNER JOIN divisions AS T2 ON T1.Div = T2.division WHERE T1.season = 2021 AND T1.FTR = 'H' AND T2.name = 'Bundesliga'
### Database Schema
CREATE TABLE divisions
(
    division TEXT not null
        primary key,
    name     TEXT,
    country  TEXT
)

CREATE TABLE matchs
(
    Div      TEXT,
    Date     DATE,
    HomeTeam TEXT,
    AwayTeam TEXT,
    FTHG     INTEGER,
    FTAG     INTEGER,
    FTR      TEXT,
    season   INTEGER,
    foreign key (Div) references divisions(division)
)

### Question
Which team had more home victories in the 2021 season's matches of the Bundesliga division, Augsburg or Mainz?

### Hint
Bundesliga is the name of division; more home victories refer to MAX(FTR = 'H)'; Augsburg and Mainz are names of teams and refer to HomeTeam;

### SQL
SELECT CASE WHEN COUNT(CASE WHEN T1.HomeTeam = 'Augsburg' THEN 1 ELSE NULL END) - COUNT(CASE WHEN T1.HomeTeam = ' Mainz' THEN 1 ELSE NULL END) > 0 THEN 'Augsburg' ELSE 'Mainz' END FROM matchs AS T1 INNER JOIN divisions AS T2 ON T1.Div = T2.division WHERE T1.season = 2021 AND T1.FTR = 'H'
### Database Schema
CREATE TABLE divisions
(
    division TEXT not null
        primary key,
    name     TEXT,
    country  TEXT
)

CREATE TABLE matchs
(
    Div      TEXT,
    Date     DATE,
    HomeTeam TEXT,
    AwayTeam TEXT,
    FTHG     INTEGER,
    FTAG     INTEGER,
    FTR      TEXT,
    season   INTEGER,
    foreign key (Div) references divisions(division)
)

### Question
Which team had the most final-time home-team goals in the 2021 season's matches of the Bundesliga division?

### Hint
Bundesliga is the name of division; the most final-time home-team goals refers to MAX(FTHG);

### SQL
SELECT T1.HomeTeam FROM matchs AS T1 INNER JOIN divisions AS T2 ON T1.Div = T2.division WHERE T2.name = 'Bundesliga' AND T1.season = 2021 ORDER BY T1.FTHG DESC LIMIT 1
### Database Schema
CREATE TABLE divisions
(
    division TEXT not null
        primary key,
    name     TEXT,
    country  TEXT
)

CREATE TABLE matchs
(
    Div      TEXT,
    Date     DATE,
    HomeTeam TEXT,
    AwayTeam TEXT,
    FTHG     INTEGER,
    FTAG     INTEGER,
    FTR      TEXT,
    season   INTEGER,
    foreign key (Div) references divisions(division)
)

### Question
How many final-time home-team goals were there in total in all the matches of the Bundesliga division in the 2021 season?

### Hint
Bundesliga is the name of division; final-time home-team goals refers to FTHG;

### SQL
SELECT SUM(T1.FTHG) FROM matchs AS T1 INNER JOIN divisions AS T2 ON T1.Div = T2.division WHERE T2.name = 'Bundesliga' AND T1.season = 2021
### Database Schema
CREATE TABLE divisions
(
    division TEXT not null
        primary key,
    name     TEXT,
    country  TEXT
)

CREATE TABLE matchs
(
    Div      TEXT,
    Date     DATE,
    HomeTeam TEXT,
    AwayTeam TEXT,
    FTHG     INTEGER,
    FTAG     INTEGER,
    FTR      TEXT,
    season   INTEGER,
    foreign key (Div) references divisions(division)
)

### Question
What's the winning rate of Club Brugge in the 2021 Premier League?

### Hint
Premier League is name of division; season = 2021; Club Brugge is name of team; Club Brugge wins implies HomeTeam = 'Club Brugge' and FTR = 'H' and AwayTeam = 'Club Brugge' and FTR = 'A'; DIVIDE(SUM(COUNT(FTR = 'H' where HomeTeam = 'Club Brugge', name = 'Premier League', season = 2021), COUNT(FTR = 'A'where AwayTeam = 'Club Brugge', name = 'Premier League', season = 2021)), COUNT(Div where name = 'Premier League', season = 2021));

### SQL
SELECT CAST(COUNT(CASE WHEN T1.FTR = 'H' THEN 1 ELSE NULL END) + COUNT(CASE WHEN T1.FTR = 'A' THEN 1 ELSE NULL END) AS REAL) * 100 / COUNT(t1.FTR) FROM matchs AS T1 INNER JOIN divisions AS T2 ON T1.Div = T2.division WHERE T1.season = 2021 AND T1.AwayTeam = 'Club Brugge' OR T1.HomeTeam = 'Club Brugge'
### 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 of Shakespeare's works were finished before the year 1602?

### Hint
finished before the year 1602 refers to Date < 1602

### SQL
SELECT COUNT(id) FROM works WHERE Date < 1602
### 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 in Act 1 in Twelfth Night?

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

### SQL
SELECT COUNT(T1.id) FROM works AS T1 INNER JOIN chapters AS T2 ON T1.id = T2.work_id WHERE T2.Act = 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
What is the description of Act 1, Scene 2 in Twelfth Night?

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

### SQL
SELECT T2.Description FROM works AS T1 INNER JOIN chapters AS T2 ON T1.id = T2.work_id WHERE T1.Title = 'Twelfth Night' AND T2.Act = 1 AND T2.Scene = 2
### 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 more scenes are there in Act 1 than in Act 5 in Twelfth Night?

### Hint
in Twelfth Night refers to Title = 'Twelfth Night'; How many more scenes = subtract(sum(Act = 1), sum(Act = 5))

### SQL
SELECT SUM(IIF(T2.Act = 1, 1, 0)) - SUM(IIF(T2.Act = 5, 1, 0)) AS more FROM works AS T1 INNER JOIN chapters AS T2 ON T1.id = T2.work_id WHERE T1.Title = 'Twelfth Night'