lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE incidents
(
    case_number                TEXT  not null
        primary key,
    date                       DATE          not null,
    location                   TEXT  not null,
    subject_statuses           TEXT  not null,
    subject_weapon             TEXT  not null,
    subjects                   TEXT  not null,
    subject_count              INTEGER           not null,
    officers                  TEXT not null

)

CREATE TABLE officers
(
    case_number TEXT not null,
    race        TEXT         null,
    gender      TEXT         not null,
    last_name   TEXT not null,
    first_name  TEXT null,
    full_name   TEXT not null,
    foreign key (case_number) references incidents (case_number)
)

CREATE TABLE subjects
(
    case_number TEXT not null,
    race        TEXT         not null,
    gender      TEXT         not null,
    last_name   TEXT not null,
    first_name  TEXT null,
    full_name   TEXT not null,
    foreign key (case_number) references incidents (case_number)
)

### Question
Of the black officers, how many of them investigated cases between the years 2010 and 2015?

### Hint
black refers to race = 'B'; between the years 2010 and 2015 refers to date between '2010-01-01' and '2015-12-31'; case refers to case_number

### SQL
SELECT COUNT(T1.case_number) FROM officers AS T1 INNER JOIN incidents AS T2 ON T2.case_number = T1.case_number WHERE T1.race = 'B' AND T2.date BETWEEN '2010-01-01' AND '2015-12-31'
### Database Schema
CREATE TABLE incidents
(
    case_number                TEXT  not null
        primary key,
    date                       DATE          not null,
    location                   TEXT  not null,
    subject_statuses           TEXT  not null,
    subject_weapon             TEXT  not null,
    subjects                   TEXT  not null,
    subject_count              INTEGER           not null,
    officers                  TEXT not null

)

CREATE TABLE officers
(
    case_number TEXT not null,
    race        TEXT         null,
    gender      TEXT         not null,
    last_name   TEXT not null,
    first_name  TEXT null,
    full_name   TEXT not null,
    foreign key (case_number) references incidents (case_number)
)

CREATE TABLE subjects
(
    case_number TEXT not null,
    race        TEXT         not null,
    gender      TEXT         not null,
    last_name   TEXT not null,
    first_name  TEXT null,
    full_name   TEXT not null,
    foreign key (case_number) references incidents (case_number)
)

### Question
How many instances were found in June 2015?

### Hint
in June 2015 refers to date between '2015-06-01' and '2015-06-30'; record number refers to case_number

### SQL
SELECT COUNT(case_number) FROM incidents WHERE date BETWEEN '2015-06-01' AND '2015-06-30'
### Database Schema
CREATE TABLE incidents
(
    case_number                TEXT  not null
        primary key,
    date                       DATE          not null,
    location                   TEXT  not null,
    subject_statuses           TEXT  not null,
    subject_weapon             TEXT  not null,
    subjects                   TEXT  not null,
    subject_count              INTEGER           not null,
    officers                  TEXT not null

)

CREATE TABLE officers
(
    case_number TEXT not null,
    race        TEXT         null,
    gender      TEXT         not null,
    last_name   TEXT not null,
    first_name  TEXT null,
    full_name   TEXT not null,
    foreign key (case_number) references incidents (case_number)
)

CREATE TABLE subjects
(
    case_number TEXT not null,
    race        TEXT         not null,
    gender      TEXT         not null,
    last_name   TEXT not null,
    first_name  TEXT null,
    full_name   TEXT not null,
    foreign key (case_number) references incidents (case_number)
)

### Question
How many people were injured between 2006 and 2014 as a result of a handgun?

### Hint
injured refers to subject_statuses = 'injured'; between 2006 and 2014 refers to date between '2006-01-01' and '2013-12-31'; handgun refers to subject_weapon = 'handgun'; where the incidents took place refers to location

### SQL
SELECT COUNT(location) FROM incidents WHERE subject_weapon = 'Handgun' AND subject_statuses = 'Injured' AND date BETWEEN '2006-01-01' AND '2013-12-31'
### Database Schema
CREATE TABLE incidents
(
    case_number                TEXT  not null
        primary key,
    date                       DATE          not null,
    location                   TEXT  not null,
    subject_statuses           TEXT  not null,
    subject_weapon             TEXT  not null,
    subjects                   TEXT  not null,
    subject_count              INTEGER           not null,
    officers                  TEXT not null

)

CREATE TABLE officers
(
    case_number TEXT not null,
    race        TEXT         null,
    gender      TEXT         not null,
    last_name   TEXT not null,
    first_name  TEXT null,
    full_name   TEXT not null,
    foreign key (case_number) references incidents (case_number)
)

CREATE TABLE subjects
(
    case_number TEXT not null,
    race        TEXT         not null,
    gender      TEXT         not null,
    last_name   TEXT not null,
    first_name  TEXT null,
    full_name   TEXT not null,
    foreign key (case_number) references incidents (case_number)
)

### Question
What is the most common type of weapon that causes death?

### Hint
the most common type of weapon refers to max(count(subject_weapon)); causes death refers to subject_statuses = 'Deceased'

### SQL
SELECT subject_weapon FROM incidents WHERE subject_statuses = 'Deceased' GROUP BY subject_weapon ORDER BY COUNT(case_number) DESC LIMIT 1
### Database Schema
CREATE TABLE incidents
(
    case_number                TEXT  not null
        primary key,
    date                       DATE          not null,
    location                   TEXT  not null,
    subject_statuses           TEXT  not null,
    subject_weapon             TEXT  not null,
    subjects                   TEXT  not null,
    subject_count              INTEGER           not null,
    officers                  TEXT not null

)

CREATE TABLE officers
(
    case_number TEXT not null,
    race        TEXT         null,
    gender      TEXT         not null,
    last_name   TEXT not null,
    first_name  TEXT null,
    full_name   TEXT not null,
    foreign key (case_number) references incidents (case_number)
)

CREATE TABLE subjects
(
    case_number TEXT not null,
    race        TEXT         not null,
    gender      TEXT         not null,
    last_name   TEXT not null,
    first_name  TEXT null,
    full_name   TEXT not null,
    foreign key (case_number) references incidents (case_number)
)

### Question
What is the proportion of white males and females in the police force?

### Hint
white refers to race = 'W'; male refers to gender = 'M'; female refers to gender = 'F'; proportion of white males = divide(count(officers where race = 'W' and gender = 'M'), count(officers)) * 100%; proportion of white females = divide(count(officers where race = 'W' and gender = 'F'), count(officers)) * 100%

### SQL
SELECT CAST(SUM(gender = 'M') AS REAL) / SUM(gender = 'F') FROM officers WHERE race = 'W'
### Database Schema
CREATE TABLE incidents
(
    case_number                TEXT  not null
        primary key,
    date                       DATE          not null,
    location                   TEXT  not null,
    subject_statuses           TEXT  not null,
    subject_weapon             TEXT  not null,
    subjects                   TEXT  not null,
    subject_count              INTEGER           not null,
    officers                  TEXT not null

)

CREATE TABLE officers
(
    case_number TEXT not null,
    race        TEXT         null,
    gender      TEXT         not null,
    last_name   TEXT not null,
    first_name  TEXT null,
    full_name   TEXT not null,
    foreign key (case_number) references incidents (case_number)
)

CREATE TABLE subjects
(
    case_number TEXT not null,
    race        TEXT         not null,
    gender      TEXT         not null,
    last_name   TEXT not null,
    first_name  TEXT null,
    full_name   TEXT not null,
    foreign key (case_number) references incidents (case_number)
)

### Question
How many more black female victims than white female victims were discovered?

### Hint
black refers to race = 'B'; female refers to gender = 'F'; white refers to race = 'W'; result = subtract(count(victims where race = 'B'), count(victims where race = 'W')) where gender = 'F'

### SQL
SELECT SUM(race = 'B') - SUM(race = 'W') FROM subjects WHERE gender = 'F'
### Database Schema
CREATE TABLE incidents
(
    case_number                TEXT  not null
        primary key,
    date                       DATE          not null,
    location                   TEXT  not null,
    subject_statuses           TEXT  not null,
    subject_weapon             TEXT  not null,
    subjects                   TEXT  not null,
    subject_count              INTEGER           not null,
    officers                  TEXT not null

)

CREATE TABLE officers
(
    case_number TEXT not null,
    race        TEXT         null,
    gender      TEXT         not null,
    last_name   TEXT not null,
    first_name  TEXT null,
    full_name   TEXT not null,
    foreign key (case_number) references incidents (case_number)
)

CREATE TABLE subjects
(
    case_number TEXT not null,
    race        TEXT         not null,
    gender      TEXT         not null,
    last_name   TEXT not null,
    first_name  TEXT null,
    full_name   TEXT not null,
    foreign key (case_number) references incidents (case_number)
)

### Question
What percentage of deaths were caused by rifles?

### Hint
rifle refers to subject_weapon = 'rifles'; death refers to subject_statuses = 'Deceased'; percentage = divide(count(incidents where subject_weapon = 'rifles'), count(incidents)) where subject_statuses = 'Deceased' * 100%

### SQL
SELECT CAST(SUM(subject_statuses = 'Deceased') AS REAL) * 100 / COUNT(case_number) FROM incidents WHERE subject_weapon = 'Rifle'
### Database Schema
CREATE TABLE incidents
(
    case_number                TEXT  not null
        primary key,
    date                       DATE          not null,
    location                   TEXT  not null,
    subject_statuses           TEXT  not null,
    subject_weapon             TEXT  not null,
    subjects                   TEXT  not null,
    subject_count              INTEGER           not null,
    officers                  TEXT not null

)

CREATE TABLE officers
(
    case_number TEXT not null,
    race        TEXT         null,
    gender      TEXT         not null,
    last_name   TEXT not null,
    first_name  TEXT null,
    full_name   TEXT not null,
    foreign key (case_number) references incidents (case_number)
)

CREATE TABLE subjects
(
    case_number TEXT not null,
    race        TEXT         not null,
    gender      TEXT         not null,
    last_name   TEXT not null,
    first_name  TEXT null,
    full_name   TEXT not null,
    foreign key (case_number) references incidents (case_number)
)

### Question
Which type of weapon was used to attack the victim in the record number 031347-2015? What is the victim's race and gender?

### Hint
type of weapon refers to subject_weapon; record number 031347-2015 refers to case_number = '031347-2015'

### SQL
SELECT T1.subject_weapon, T2.race, T2.gender FROM incidents AS T1 INNER JOIN subjects AS T2 ON T1.case_number = T2.case_number WHERE T1.case_number = '031347-2015'
### Database Schema
CREATE TABLE incidents
(
    case_number                TEXT  not null
        primary key,
    date                       DATE          not null,
    location                   TEXT  not null,
    subject_statuses           TEXT  not null,
    subject_weapon             TEXT  not null,
    subjects                   TEXT  not null,
    subject_count              INTEGER           not null,
    officers                  TEXT not null

)

CREATE TABLE officers
(
    case_number TEXT not null,
    race        TEXT         null,
    gender      TEXT         not null,
    last_name   TEXT not null,
    first_name  TEXT null,
    full_name   TEXT not null,
    foreign key (case_number) references incidents (case_number)
)

CREATE TABLE subjects
(
    case_number TEXT not null,
    race        TEXT         not null,
    gender      TEXT         not null,
    last_name   TEXT not null,
    first_name  TEXT null,
    full_name   TEXT not null,
    foreign key (case_number) references incidents (case_number)
)

### Question
Which near-death incident did a policeman by the name of Ruben Fredirick look into? What is the victim in this incident's race and gender?

### Hint
near-death refers to subject_statuses = 'Deceased Injured'; incident refers to case_number; Ruben Fredirick refers to full_name = 'Ruben Fredirick'

### SQL
SELECT T1.case_number, T3.race, T3.gender FROM incidents AS T1 INNER JOIN officers AS T2 ON T1.case_number = T2.case_number INNER JOIN subjects AS T3 ON T1.case_number = T3.case_number WHERE T2.first_name = 'Fredirick' AND T2.last_name = 'Ruben'
### Database Schema
CREATE TABLE incidents
(
    case_number                TEXT  not null
        primary key,
    date                       DATE          not null,
    location                   TEXT  not null,
    subject_statuses           TEXT  not null,
    subject_weapon             TEXT  not null,
    subjects                   TEXT  not null,
    subject_count              INTEGER           not null,
    officers                  TEXT not null

)

CREATE TABLE officers
(
    case_number TEXT not null,
    race        TEXT         null,
    gender      TEXT         not null,
    last_name   TEXT not null,
    first_name  TEXT null,
    full_name   TEXT not null,
    foreign key (case_number) references incidents (case_number)
)

CREATE TABLE subjects
(
    case_number TEXT not null,
    race        TEXT         not null,
    gender      TEXT         not null,
    last_name   TEXT not null,
    first_name  TEXT null,
    full_name   TEXT not null,
    foreign key (case_number) references incidents (case_number)
)

### Question
What proportion of male police officers looked into events where people were injured?

### Hint
male refers to gender = 'M'; people were injured refers to subject_statuses = 'Injured'; proportion = divide(count(case_number where gender = 'M'), count(case_number)) where subject_statuses = 'Injured' * 100%

### SQL
SELECT CAST(SUM(T2.gender = 'M') AS REAL) * 100 / COUNT(T1.case_number) FROM incidents T1 INNER JOIN officers T2 ON T1.case_number = T2.case_number WHERE T1.subject_statuses = 'Injured'
### Database Schema
CREATE TABLE Classification
(
    GeneID       TEXT   not null
        primary key,
    Localization TEXT not null
)

CREATE TABLE Genes
(
    GeneID       TEXT   not null,
    Essential    TEXT not null,
    Class        TEXT not null,
    Complex      TEXT null,
    Phenotype    TEXT not null,
    Motif        TEXT not null,
    Chromosome   INTEGER          not null,
    Function     TEXT not null,
    Localization TEXT not null,
    foreign key (GeneID) references Classification (GeneID)
            on update cascade on delete cascade
)

CREATE TABLE Interactions
(
    GeneID1         TEXT     not null,
    GeneID2         TEXT      not null,
    Type            TEXT    not null,
    Expression_Corr REAL not null,
    primary key (GeneID1, GeneID2),
    foreign key (GeneID1) references Classification (GeneID)
            on update cascade on delete cascade,
    foreign key (GeneID2) references Classification (GeneID)
            on update cascade on delete cascade
)

### Question
For the genes that are located in the plasma membrane, please list their number of chromosomes.

### SQL
SELECT T1.Chromosome FROM Genes AS T1 INNER JOIN Classification AS T2 ON T1.GeneID = T2.GeneID WHERE T2.Localization = 'plasma membrane'
### Database Schema
CREATE TABLE Classification
(
    GeneID       TEXT   not null
        primary key,
    Localization TEXT not null
)

CREATE TABLE Genes
(
    GeneID       TEXT   not null,
    Essential    TEXT not null,
    Class        TEXT not null,
    Complex      TEXT null,
    Phenotype    TEXT not null,
    Motif        TEXT not null,
    Chromosome   INTEGER          not null,
    Function     TEXT not null,
    Localization TEXT not null,
    foreign key (GeneID) references Classification (GeneID)
            on update cascade on delete cascade
)

CREATE TABLE Interactions
(
    GeneID1         TEXT     not null,
    GeneID2         TEXT      not null,
    Type            TEXT    not null,
    Expression_Corr REAL not null,
    primary key (GeneID1, GeneID2),
    foreign key (GeneID1) references Classification (GeneID)
            on update cascade on delete cascade,
    foreign key (GeneID2) references Classification (GeneID)
            on update cascade on delete cascade
)

### Question
How many non-essential genes are located in the nucleus?

### SQL
SELECT COUNT(T1.GeneID) FROM Genes AS T1 INNER JOIN Classification AS T2 ON T1.GeneID = T2.GeneID WHERE T2.Localization = 'nucleus' AND T1.Essential = 'Non-Essential'
### Database Schema
CREATE TABLE Classification
(
    GeneID       TEXT   not null
        primary key,
    Localization TEXT not null
)

CREATE TABLE Genes
(
    GeneID       TEXT   not null,
    Essential    TEXT not null,
    Class        TEXT not null,
    Complex      TEXT null,
    Phenotype    TEXT not null,
    Motif        TEXT not null,
    Chromosome   INTEGER          not null,
    Function     TEXT not null,
    Localization TEXT not null,
    foreign key (GeneID) references Classification (GeneID)
            on update cascade on delete cascade
)

CREATE TABLE Interactions
(
    GeneID1         TEXT     not null,
    GeneID2         TEXT      not null,
    Type            TEXT    not null,
    Expression_Corr REAL not null,
    primary key (GeneID1, GeneID2),
    foreign key (GeneID1) references Classification (GeneID)
            on update cascade on delete cascade,
    foreign key (GeneID2) references Classification (GeneID)
            on update cascade on delete cascade
)

### Question
Among the genes with nucleic acid metabolism defects, how many of them can be found in the vacuole?

### SQL
SELECT COUNT(T1.GeneID) FROM Genes AS T1 INNER JOIN Classification AS T2 ON T1.GeneID = T2.GeneID WHERE T2.Localization = 'vacuole' AND T1.Phenotype = 'Nucleic acid metabolism defects'
### Database Schema
CREATE TABLE Classification
(
    GeneID       TEXT   not null
        primary key,
    Localization TEXT not null
)

CREATE TABLE Genes
(
    GeneID       TEXT   not null,
    Essential    TEXT not null,
    Class        TEXT not null,
    Complex      TEXT null,
    Phenotype    TEXT not null,
    Motif        TEXT not null,
    Chromosome   INTEGER          not null,
    Function     TEXT not null,
    Localization TEXT not null,
    foreign key (GeneID) references Classification (GeneID)
            on update cascade on delete cascade
)

CREATE TABLE Interactions
(
    GeneID1         TEXT     not null,
    GeneID2         TEXT      not null,
    Type            TEXT    not null,
    Expression_Corr REAL not null,
    primary key (GeneID1, GeneID2),
    foreign key (GeneID1) references Classification (GeneID)
            on update cascade on delete cascade,
    foreign key (GeneID2) references Classification (GeneID)
            on update cascade on delete cascade
)

### Question
Please list the location of the genes that have the most chromosomes.

### SQL
SELECT T2.Localization FROM Genes AS T1 INNER JOIN Classification AS T2 ON T1.GeneID = T2.GeneID ORDER BY T1.Chromosome DESC LIMIT 1
### Database Schema
CREATE TABLE Classification
(
    GeneID       TEXT   not null
        primary key,
    Localization TEXT not null
)

CREATE TABLE Genes
(
    GeneID       TEXT   not null,
    Essential    TEXT not null,
    Class        TEXT not null,
    Complex      TEXT null,
    Phenotype    TEXT not null,
    Motif        TEXT not null,
    Chromosome   INTEGER          not null,
    Function     TEXT not null,
    Localization TEXT not null,
    foreign key (GeneID) references Classification (GeneID)
            on update cascade on delete cascade
)

CREATE TABLE Interactions
(
    GeneID1         TEXT     not null,
    GeneID2         TEXT      not null,
    Type            TEXT    not null,
    Expression_Corr REAL not null,
    primary key (GeneID1, GeneID2),
    foreign key (GeneID1) references Classification (GeneID)
            on update cascade on delete cascade,
    foreign key (GeneID2) references Classification (GeneID)
            on update cascade on delete cascade
)

### Question
Among the pairs of genes that are both located in the nucleus, what is the highest expression correlation score?

### SQL
SELECT T2.Expression_Corr FROM Genes AS T1 INNER JOIN Interactions AS T2 ON T1.GeneID = T2.GeneID1 INNER JOIN Genes AS T3 ON T3.GeneID = T2.GeneID2 WHERE T1.Localization = 'nucleus' AND T3.Localization = 'nucleus' ORDER BY T2.Expression_Corr DESC LIMIT 1
### Database Schema
CREATE TABLE Classification
(
    GeneID       TEXT   not null
        primary key,
    Localization TEXT not null
)

CREATE TABLE Genes
(
    GeneID       TEXT   not null,
    Essential    TEXT not null,
    Class        TEXT not null,
    Complex      TEXT null,
    Phenotype    TEXT not null,
    Motif        TEXT not null,
    Chromosome   INTEGER          not null,
    Function     TEXT not null,
    Localization TEXT not null,
    foreign key (GeneID) references Classification (GeneID)
            on update cascade on delete cascade
)

CREATE TABLE Interactions
(
    GeneID1         TEXT     not null,
    GeneID2         TEXT      not null,
    Type            TEXT    not null,
    Expression_Corr REAL not null,
    primary key (GeneID1, GeneID2),
    foreign key (GeneID1) references Classification (GeneID)
            on update cascade on delete cascade,
    foreign key (GeneID2) references Classification (GeneID)
            on update cascade on delete cascade
)

### Question
What are the functions of the pair of genes that have the lowest expression correlation score?a

### SQL
SELECT T1.Function FROM Genes AS T1 INNER JOIN Interactions AS T2 ON T1.GeneID = T2.GeneID1 ORDER BY T2.Expression_Corr ASC LIMIT 1
### Database Schema
CREATE TABLE Classification
(
    GeneID       TEXT   not null
        primary key,
    Localization TEXT not null
)

CREATE TABLE Genes
(
    GeneID       TEXT   not null,
    Essential    TEXT not null,
    Class        TEXT not null,
    Complex      TEXT null,
    Phenotype    TEXT not null,
    Motif        TEXT not null,
    Chromosome   INTEGER          not null,
    Function     TEXT not null,
    Localization TEXT not null,
    foreign key (GeneID) references Classification (GeneID)
            on update cascade on delete cascade
)

CREATE TABLE Interactions
(
    GeneID1         TEXT     not null,
    GeneID2         TEXT      not null,
    Type            TEXT    not null,
    Expression_Corr REAL not null,
    primary key (GeneID1, GeneID2),
    foreign key (GeneID1) references Classification (GeneID)
            on update cascade on delete cascade,
    foreign key (GeneID2) references Classification (GeneID)
            on update cascade on delete cascade
)

### Question
Among the pairs of genes that are not from the class of motorproteins, how many of them are negatively correlated?

### Hint
If Expression_Corr < 0, it means the negatively correlated

### SQL
SELECT COUNT(T1.GeneID) FROM Genes AS T1 INNER JOIN Interactions AS T2 ON T1.GeneID = T2.GeneID1 WHERE T2.Expression_Corr < 0 AND T1.Class = 'Motorproteins'