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
In which year has the greatest number of cases where Handgun was used as weapon?

### Hint
year refers to year(date); the greatest number of cases refers to max(count(case_number)); OS Spray was used as weapon refers to subject_weapon = 'OS Spray'

### SQL
SELECT STRFTIME('%Y', date) FROM incidents WHERE subject_weapon = 'Handgun' GROUP BY STRFTIME('%Y', date) 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
Among the cases dismissed by the grand jury disposition, what percentage of cases is where the subject is injured?

### Hint
dismissed by the grand jury disposition refers to grand_jury_disposition = 'No Bill'; the subject is injured refers to subject_statuses = 'injured'; percentage = divide(count(incidents where subject_statuses = 'injured'), count(incidents)) where grand_jury_disposition = 'No Bill' * 100%

### SQL
SELECT CAST(SUM(subject_statuses = 'Injured') AS REAL) * 100 / COUNT(case_number) FROM incidents WHERE grand_jury_disposition = 'No Bill'
### 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
Did the number of cases with Vehicle as subject weapon increase or decrease from year 2007 to 2008. State the difference.

### Hint
number of cases refers to count(case_number); with Vehicle as subject weapon refers to subject_weapon = 'Vehicle'; year 2007 refers to date between '2007-01-01' and '2007-12-31'; year 2008 refers to date between '2008-01-01' and '2008-12-31'

### SQL
SELECT SUM(IIF(STRFTIME('%Y', date) = '2007', 1, 0)) - SUM(IIF(STRFTIME('%Y', date) = '2008', 1, 0)) FROM incidents WHERE subject_weapon = 'Vehicle'
### 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
Among the 'Handgun' weapon used by subject, how many percent were 'Shoot and Miss'?

### Hint
'Handgun' weapon refers to subject_weapon = 'Handgun'; 'Shoot and Miss' refers to subject_statuses = 'Shoot and Miss'; percent = divide(count(incidents where subject_statuses = 'Shoot and Miss'), count(incidents)) where subject_weapon = 'Handgun' * 100%

### SQL
SELECT CAST(SUM(subject_statuses = 'Shoot and Miss') AS REAL) * 100 / COUNT(case_number) FROM incidents WHERE subject_weapon = 'Handgun'
### 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
Who are the officers involved in cases that are voted as 'No Bill'. List their last name and gender.

### Hint
voted as 'No Bill' refers to grand_jury_disposition = 'No Bill'

### SQL
SELECT T2.last_name, T2.gender FROM incidents AS T1 INNER JOIN officers AS T2 ON T1.case_number = T2.case_number WHERE T1.grand_jury_disposition = 'No Bill'
### 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 are the cases where the subject are female. List the case number, subject status and weapon.

### Hint
female refers to gender = 'F'; weapon refers to subject_weapon

### SQL
SELECT T1.case_number, T1.subject_statuses, T1.subject_weapon FROM incidents AS T1 INNER JOIN subjects AS T2 ON T1.case_number = T2.case_number WHERE T2.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
From the cases where the subject are male, list the case number and the location and subject status.

### Hint
male refers to gender = 'M'

### SQL
SELECT T1.case_number, T1.location, T1.subject_statuses FROM incidents AS T1 INNER JOIN subjects AS T2 ON T1.case_number = T2.case_number WHERE T2.gender = 'M'
### 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
For case(s) where officer 'Evenden, George' is in charged, state the case number and the grand jury disposition?

### Hint
officer 'Evenden, George' refers to full_name = 'Evenden, George'

### SQL
SELECT T1.case_number, T1.grand_jury_disposition FROM incidents AS T1 INNER JOIN officers AS T2 ON T1.case_number = T2.case_number WHERE T2.first_name = 'George' AND T2.last_name = 'Evenden'
### 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
For case number '134472-2015', list the last name of the officers involved and state the subject statuses.

### SQL
SELECT T2.last_name, T1.subject_statuses FROM incidents AS T1 INNER JOIN officers AS T2 ON T1.case_number = T2.case_number WHERE T1.case_number = '134472-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
From the cases where the subject were deceased, list the subject's last name, gender, race and case number.

### Hint
subject were deceased refers to subject_statuses = 'Deceased'

### SQL
SELECT T2.last_name, T2.gender, T2.race, T2.case_number FROM incidents AS T1 INNER JOIN subjects AS T2 ON T1.case_number = T2.case_number WHERE T1.subject_statuses = 'Deceased'
### 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 percentage of subject who are female used the Vehicle as weapon?

### Hint
female refers to gender = 'F'; use Vehicle as weapon refers to subject_weapon = 'Vehicle'; percentage = divide(count(case_number where subject_weapon = 'Vehicle'), count(case_number)) where gender = 'F' * 100%

### SQL
SELECT CAST(SUM(T1.subject_weapon = 'Vehicle') AS REAL) * 100 / COUNT(T1.case_number) FROM incidents T1 INNER JOIN subjects T2 ON T1.case_number = T2.case_number WHERE T2.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
From the 'Injured' statuses of the subject, what is the ratio of weapons used are knife against handgun?

### Hint
the 'Shoot and Miss' status refers to subject_statuses = 'Shoot and Miss'; knife refers to subject_weapon = 'knife'; toy handgun refers to subject_weapon = 'toy handgun'; ratio = divide(count(case_number where subject_weapon = 'knife'), count(case_number where subject_weapon = 'toy handgun')) where subject_statuses = 'Shoot and Miss'

### SQL
SELECT CAST(SUM(T1.subject_weapon = 'Knife') AS REAL) * 100 / SUM(T1.subject_weapon = 'Handgun') FROM incidents AS T1 INNER JOIN subjects AS T2 ON T1.case_number = T2.case_number WHERE T1.subject_statuses = 'Injured'
### 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
List all cases from the year 2012 in which the subject was deceased

### Hint
case refers to case_number; year 2012 refers to date between '2012-01-01' and '2012-12-31'; subject was deceased refers to subject_statuses = 'Deceased'

### SQL
SELECT case_number FROM incidents WHERE STRFTIME('%Y', date) > '2011' AND subject_statuses = 'Deceased'
### 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 all male officers, what percentage are black officers?

### Hint
male refers to gender = 'M'; black refers to race = 'B'; percentage = divide(count(officers where race = 'B'), count(officers)) where gender = 'M' * 100%

### SQL
SELECT CAST(SUM(race = 'B') AS REAL) * 100 / COUNT(case_number) FROM officers WHERE gender = 'M'
### 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 incidents in which the subject's weapon was a vehicle were investigated by a female officer?

### Hint
subject's weapon was a vehicle refers to subject_weapon = 'Vehicle'; female refers to gender = 'F'

### SQL
SELECT COUNT(T1.case_number) FROM incidents AS T1 INNER JOIN officers AS T2 ON T1.case_number = T2.case_number WHERE T1.subject_weapon = 'Vehicle' AND T2.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
In how many cases where the subject was a female was the subject's status listed as Deceased?

### Hint
 female refers to gender = 'F'; subject's status listed as Deceased refers to subject_statuses = 'Deceased'

### SQL
SELECT COUNT(T1.case_number) FROM incidents AS T1 INNER JOIN subjects AS T2 ON T1.case_number = T2.case_number WHERE T2.gender = 'F' AND T1.subject_statuses = 'Deceased'