lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE Community_Area
(
    community_area_no   INTEGER
            primary key,
    community_area_name TEXT,
    side                TEXT,
    population          TEXT
)

CREATE TABLE District
(
    district_no   INTEGER
            primary key,
    district_name TEXT,
    address       TEXT,
    zip_code      INTEGER,
    commander     TEXT,
    email         TEXT,
    phone         TEXT,
    fax           TEXT,
    tty           TEXT,
    twitter       TEXT
)

CREATE TABLE FBI_Code
(
    fbi_code_no   TEXT
            primary key,
    title         TEXT,
    description   TEXT,
    crime_against TEXT
)

CREATE TABLE IUCR
(
    iucr_no               TEXT
            primary key,
    primary_description   TEXT,
    secondary_description TEXT,
    index_code            TEXT
)

CREATE TABLE Neighborhood
(
    neighborhood_name TEXT
            primary key,
    community_area_no INTEGER,
    foreign key (community_area_no) references Community_Area(community_area_no)
)

CREATE TABLE Ward
(
    ward_no                INTEGER
            primary key,
    alderman_first_name    TEXT,
    alderman_last_name     TEXT,
    alderman_name_suffix   TEXT,
    ward_office_address    TEXT,
    ward_office_zip        TEXT,
    ward_email             TEXT,
    ward_office_phone      TEXT,
    ward_office_fax        TEXT,
    city_hall_office_room  INTEGER,
    city_hall_office_phone TEXT,
    city_hall_office_fax   TEXT,
    Population             INTEGER
)

CREATE TABLE Crime
(
    report_no            INTEGER
            primary key,
    case_number          TEXT,
    date                 TEXT,
    block                TEXT,
    iucr_no              TEXT,
    location_description TEXT,
    arrest               TEXT,
    domestic             TEXT,
    beat                 INTEGER,
    district_no          INTEGER,
    ward_no              INTEGER,
    community_area_no    INTEGER,
    fbi_code_no          TEXT,
    latitude             TEXT,
    longitude            TEXT,
    foreign key (ward_no) references Ward(ward_no),
    foreign key (iucr_no) references IUCR(iucr_no),
    foreign key (district_no) references District(district_no),
    foreign key (community_area_no) references Community_Area(community_area_no),
    foreign key (fbi_code_no) references FBI_Code(fbi_code_no)
)

### Question
List down the neighborhood areas of Douglas.

### Hint
neighborhood area refers to neighborhood_name; Douglas refers to community_area_name = 'Douglas'

### SQL
SELECT T2.neighborhood_name FROM Community_Area AS T1 INNER JOIN Neighborhood AS T2 ON T2.community_area_no = T1.community_area_no WHERE T1.community_area_name = 'Douglas'
### Database Schema
CREATE TABLE Community_Area
(
    community_area_no   INTEGER
            primary key,
    community_area_name TEXT,
    side                TEXT,
    population          TEXT
)

CREATE TABLE District
(
    district_no   INTEGER
            primary key,
    district_name TEXT,
    address       TEXT,
    zip_code      INTEGER,
    commander     TEXT,
    email         TEXT,
    phone         TEXT,
    fax           TEXT,
    tty           TEXT,
    twitter       TEXT
)

CREATE TABLE FBI_Code
(
    fbi_code_no   TEXT
            primary key,
    title         TEXT,
    description   TEXT,
    crime_against TEXT
)

CREATE TABLE IUCR
(
    iucr_no               TEXT
            primary key,
    primary_description   TEXT,
    secondary_description TEXT,
    index_code            TEXT
)

CREATE TABLE Neighborhood
(
    neighborhood_name TEXT
            primary key,
    community_area_no INTEGER,
    foreign key (community_area_no) references Community_Area(community_area_no)
)

CREATE TABLE Ward
(
    ward_no                INTEGER
            primary key,
    alderman_first_name    TEXT,
    alderman_last_name     TEXT,
    alderman_name_suffix   TEXT,
    ward_office_address    TEXT,
    ward_office_zip        TEXT,
    ward_email             TEXT,
    ward_office_phone      TEXT,
    ward_office_fax        TEXT,
    city_hall_office_room  INTEGER,
    city_hall_office_phone TEXT,
    city_hall_office_fax   TEXT,
    Population             INTEGER
)

CREATE TABLE Crime
(
    report_no            INTEGER
            primary key,
    case_number          TEXT,
    date                 TEXT,
    block                TEXT,
    iucr_no              TEXT,
    location_description TEXT,
    arrest               TEXT,
    domestic             TEXT,
    beat                 INTEGER,
    district_no          INTEGER,
    ward_no              INTEGER,
    community_area_no    INTEGER,
    fbi_code_no          TEXT,
    latitude             TEXT,
    longitude            TEXT,
    foreign key (ward_no) references Ward(ward_no),
    foreign key (iucr_no) references IUCR(iucr_no),
    foreign key (district_no) references District(district_no),
    foreign key (community_area_no) references Community_Area(community_area_no),
    foreign key (fbi_code_no) references FBI_Code(fbi_code_no)
)

### Question
Calculate the average crime rate per month in the highest populous area.

### Hint
the highest populous refers to max(population); average crime rate per month = divide(count(report_no where population = max(population)), 12)

### SQL
SELECT CAST(COUNT(T2.report_no) AS REAL) / 12 FROM Community_Area AS T1 INNER JOIN Crime AS T2 ON T2.community_area_no = T1.community_area_no GROUP BY T1.community_area_no HAVING COUNT(T1.population) ORDER BY COUNT(T1.population) LIMIT 1
### Database Schema
CREATE TABLE Community_Area
(
    community_area_no   INTEGER
            primary key,
    community_area_name TEXT,
    side                TEXT,
    population          TEXT
)

CREATE TABLE District
(
    district_no   INTEGER
            primary key,
    district_name TEXT,
    address       TEXT,
    zip_code      INTEGER,
    commander     TEXT,
    email         TEXT,
    phone         TEXT,
    fax           TEXT,
    tty           TEXT,
    twitter       TEXT
)

CREATE TABLE FBI_Code
(
    fbi_code_no   TEXT
            primary key,
    title         TEXT,
    description   TEXT,
    crime_against TEXT
)

CREATE TABLE IUCR
(
    iucr_no               TEXT
            primary key,
    primary_description   TEXT,
    secondary_description TEXT,
    index_code            TEXT
)

CREATE TABLE Neighborhood
(
    neighborhood_name TEXT
            primary key,
    community_area_no INTEGER,
    foreign key (community_area_no) references Community_Area(community_area_no)
)

CREATE TABLE Ward
(
    ward_no                INTEGER
            primary key,
    alderman_first_name    TEXT,
    alderman_last_name     TEXT,
    alderman_name_suffix   TEXT,
    ward_office_address    TEXT,
    ward_office_zip        TEXT,
    ward_email             TEXT,
    ward_office_phone      TEXT,
    ward_office_fax        TEXT,
    city_hall_office_room  INTEGER,
    city_hall_office_phone TEXT,
    city_hall_office_fax   TEXT,
    Population             INTEGER
)

CREATE TABLE Crime
(
    report_no            INTEGER
            primary key,
    case_number          TEXT,
    date                 TEXT,
    block                TEXT,
    iucr_no              TEXT,
    location_description TEXT,
    arrest               TEXT,
    domestic             TEXT,
    beat                 INTEGER,
    district_no          INTEGER,
    ward_no              INTEGER,
    community_area_no    INTEGER,
    fbi_code_no          TEXT,
    latitude             TEXT,
    longitude            TEXT,
    foreign key (ward_no) references Ward(ward_no),
    foreign key (iucr_no) references IUCR(iucr_no),
    foreign key (district_no) references District(district_no),
    foreign key (community_area_no) references Community_Area(community_area_no),
    foreign key (fbi_code_no) references FBI_Code(fbi_code_no)
)

### Question
Among the crimes in the Central, calculate the percentage of larceny incidents.

### Hint
Central refers to side = 'Central'; larceny refers to title = 'Larceny'; percentage = divide(count(report_no where title = 'Larceny'), count(report_no)) where side = 'Central' * 100%

### SQL
SELECT CAST(COUNT(CASE WHEN T3.title = 'Larceny' THEN T2.report_no END) AS REAL) * 100 / COUNT(T2.report_no) FROM Community_Area AS T1 INNER JOIN Crime AS T2 ON T2.community_area_no = T1.community_area_no INNER JOIN FBI_Code AS T3 ON T3.fbi_code_no = T2.fbi_code_no WHERE T1.side = 'Central'
### Database Schema
CREATE TABLE Community_Area
(
    community_area_no   INTEGER
            primary key,
    community_area_name TEXT,
    side                TEXT,
    population          TEXT
)

CREATE TABLE District
(
    district_no   INTEGER
            primary key,
    district_name TEXT,
    address       TEXT,
    zip_code      INTEGER,
    commander     TEXT,
    email         TEXT,
    phone         TEXT,
    fax           TEXT,
    tty           TEXT,
    twitter       TEXT
)

CREATE TABLE FBI_Code
(
    fbi_code_no   TEXT
            primary key,
    title         TEXT,
    description   TEXT,
    crime_against TEXT
)

CREATE TABLE IUCR
(
    iucr_no               TEXT
            primary key,
    primary_description   TEXT,
    secondary_description TEXT,
    index_code            TEXT
)

CREATE TABLE Neighborhood
(
    neighborhood_name TEXT
            primary key,
    community_area_no INTEGER,
    foreign key (community_area_no) references Community_Area(community_area_no)
)

CREATE TABLE Ward
(
    ward_no                INTEGER
            primary key,
    alderman_first_name    TEXT,
    alderman_last_name     TEXT,
    alderman_name_suffix   TEXT,
    ward_office_address    TEXT,
    ward_office_zip        TEXT,
    ward_email             TEXT,
    ward_office_phone      TEXT,
    ward_office_fax        TEXT,
    city_hall_office_room  INTEGER,
    city_hall_office_phone TEXT,
    city_hall_office_fax   TEXT,
    Population             INTEGER
)

CREATE TABLE Crime
(
    report_no            INTEGER
            primary key,
    case_number          TEXT,
    date                 TEXT,
    block                TEXT,
    iucr_no              TEXT,
    location_description TEXT,
    arrest               TEXT,
    domestic             TEXT,
    beat                 INTEGER,
    district_no          INTEGER,
    ward_no              INTEGER,
    community_area_no    INTEGER,
    fbi_code_no          TEXT,
    latitude             TEXT,
    longitude            TEXT,
    foreign key (ward_no) references Ward(ward_no),
    foreign key (iucr_no) references IUCR(iucr_no),
    foreign key (district_no) references District(district_no),
    foreign key (community_area_no) references Community_Area(community_area_no),
    foreign key (fbi_code_no) references FBI_Code(fbi_code_no)
)

### Question
List the location descriptions and aldermen's full names of the arson by explosive.

### Hint
aldermen's full name refers to alderman_name_suffix, alderman_first_name, alderman_last_name; arson by explosive refers to primary_description = 'ARSON' AND secondary_description = 'BY EXPLOSIVE'

### SQL
SELECT T2.location_description, T1.alderman_first_name, T1.alderman_last_name, T1.alderman_name_suffix FROM Ward AS T1 INNER JOIN Crime AS T2 ON T2.ward_no = T1.ward_no INNER JOIN IUCR AS T3 ON T3.iucr_no = T2.iucr_no WHERE T3.primary_description = 'ARSON' AND T3.secondary_description = 'BY EXPLOSIVE'
### Database Schema
CREATE TABLE Community_Area
(
    community_area_no   INTEGER
            primary key,
    community_area_name TEXT,
    side                TEXT,
    population          TEXT
)

CREATE TABLE District
(
    district_no   INTEGER
            primary key,
    district_name TEXT,
    address       TEXT,
    zip_code      INTEGER,
    commander     TEXT,
    email         TEXT,
    phone         TEXT,
    fax           TEXT,
    tty           TEXT,
    twitter       TEXT
)

CREATE TABLE FBI_Code
(
    fbi_code_no   TEXT
            primary key,
    title         TEXT,
    description   TEXT,
    crime_against TEXT
)

CREATE TABLE IUCR
(
    iucr_no               TEXT
            primary key,
    primary_description   TEXT,
    secondary_description TEXT,
    index_code            TEXT
)

CREATE TABLE Neighborhood
(
    neighborhood_name TEXT
            primary key,
    community_area_no INTEGER,
    foreign key (community_area_no) references Community_Area(community_area_no)
)

CREATE TABLE Ward
(
    ward_no                INTEGER
            primary key,
    alderman_first_name    TEXT,
    alderman_last_name     TEXT,
    alderman_name_suffix   TEXT,
    ward_office_address    TEXT,
    ward_office_zip        TEXT,
    ward_email             TEXT,
    ward_office_phone      TEXT,
    ward_office_fax        TEXT,
    city_hall_office_room  INTEGER,
    city_hall_office_phone TEXT,
    city_hall_office_fax   TEXT,
    Population             INTEGER
)

CREATE TABLE Crime
(
    report_no            INTEGER
            primary key,
    case_number          TEXT,
    date                 TEXT,
    block                TEXT,
    iucr_no              TEXT,
    location_description TEXT,
    arrest               TEXT,
    domestic             TEXT,
    beat                 INTEGER,
    district_no          INTEGER,
    ward_no              INTEGER,
    community_area_no    INTEGER,
    fbi_code_no          TEXT,
    latitude             TEXT,
    longitude            TEXT,
    foreign key (ward_no) references Ward(ward_no),
    foreign key (iucr_no) references IUCR(iucr_no),
    foreign key (district_no) references District(district_no),
    foreign key (community_area_no) references Community_Area(community_area_no),
    foreign key (fbi_code_no) references FBI_Code(fbi_code_no)
)

### Question
Provide the occurrence date and location of the deceptive practice due to the unlawful use of recorded sound.

### Hint
location refers to latitude, longitude; deceptive practice refers to primary_description = 'DECEPTIVE PRACTICE'; unlawful use of recorded sound refers to secondary_description = 'UNLAWFUL USE OF RECORDED SOUND'

### SQL
SELECT T2.date, T2.latitude, T2.longitude FROM IUCR AS T1 INNER JOIN Crime AS T2 ON T2.iucr_no = T1.iucr_no WHERE T1.primary_description = 'DECEPTIVE PRACTICE' AND T1.secondary_description = 'UNLAWFUL USE OF RECORDED SOUND'
### Database Schema
CREATE TABLE Community_Area
(
    community_area_no   INTEGER
            primary key,
    community_area_name TEXT,
    side                TEXT,
    population          TEXT
)

CREATE TABLE District
(
    district_no   INTEGER
            primary key,
    district_name TEXT,
    address       TEXT,
    zip_code      INTEGER,
    commander     TEXT,
    email         TEXT,
    phone         TEXT,
    fax           TEXT,
    tty           TEXT,
    twitter       TEXT
)

CREATE TABLE FBI_Code
(
    fbi_code_no   TEXT
            primary key,
    title         TEXT,
    description   TEXT,
    crime_against TEXT
)

CREATE TABLE IUCR
(
    iucr_no               TEXT
            primary key,
    primary_description   TEXT,
    secondary_description TEXT,
    index_code            TEXT
)

CREATE TABLE Neighborhood
(
    neighborhood_name TEXT
            primary key,
    community_area_no INTEGER,
    foreign key (community_area_no) references Community_Area(community_area_no)
)

CREATE TABLE Ward
(
    ward_no                INTEGER
            primary key,
    alderman_first_name    TEXT,
    alderman_last_name     TEXT,
    alderman_name_suffix   TEXT,
    ward_office_address    TEXT,
    ward_office_zip        TEXT,
    ward_email             TEXT,
    ward_office_phone      TEXT,
    ward_office_fax        TEXT,
    city_hall_office_room  INTEGER,
    city_hall_office_phone TEXT,
    city_hall_office_fax   TEXT,
    Population             INTEGER
)

CREATE TABLE Crime
(
    report_no            INTEGER
            primary key,
    case_number          TEXT,
    date                 TEXT,
    block                TEXT,
    iucr_no              TEXT,
    location_description TEXT,
    arrest               TEXT,
    domestic             TEXT,
    beat                 INTEGER,
    district_no          INTEGER,
    ward_no              INTEGER,
    community_area_no    INTEGER,
    fbi_code_no          TEXT,
    latitude             TEXT,
    longitude            TEXT,
    foreign key (ward_no) references Ward(ward_no),
    foreign key (iucr_no) references IUCR(iucr_no),
    foreign key (district_no) references District(district_no),
    foreign key (community_area_no) references Community_Area(community_area_no),
    foreign key (fbi_code_no) references FBI_Code(fbi_code_no)
)

### Question
Among the criminal sexual assaults in the district of Adnardo Gutierrez, how many cases happened in the residence?

### Hint
criminal sexual assault refers to title = 'Criminal Sexual Assault'; Adnardo Gutierrez refers to commander = 'Adnardo Gutierrez'; in the residence refers to location_description = 'RESIDENCE'

### SQL
SELECT COUNT(T2.report_no) FROM District AS T1 INNER JOIN Crime AS T2 ON T2.district_no = T1.district_no INNER JOIN FBI_Code AS T3 ON T3.fbi_code_no = T2.fbi_code_no WHERE T3.title = 'Criminal Sexual Assault' AND T1.commander = 'Adnardo Gutierrez' AND T2.location_description = 'RESIDENCE'
### Database Schema
CREATE TABLE Community_Area
(
    community_area_no   INTEGER
            primary key,
    community_area_name TEXT,
    side                TEXT,
    population          TEXT
)

CREATE TABLE District
(
    district_no   INTEGER
            primary key,
    district_name TEXT,
    address       TEXT,
    zip_code      INTEGER,
    commander     TEXT,
    email         TEXT,
    phone         TEXT,
    fax           TEXT,
    tty           TEXT,
    twitter       TEXT
)

CREATE TABLE FBI_Code
(
    fbi_code_no   TEXT
            primary key,
    title         TEXT,
    description   TEXT,
    crime_against TEXT
)

CREATE TABLE IUCR
(
    iucr_no               TEXT
            primary key,
    primary_description   TEXT,
    secondary_description TEXT,
    index_code            TEXT
)

CREATE TABLE Neighborhood
(
    neighborhood_name TEXT
            primary key,
    community_area_no INTEGER,
    foreign key (community_area_no) references Community_Area(community_area_no)
)

CREATE TABLE Ward
(
    ward_no                INTEGER
            primary key,
    alderman_first_name    TEXT,
    alderman_last_name     TEXT,
    alderman_name_suffix   TEXT,
    ward_office_address    TEXT,
    ward_office_zip        TEXT,
    ward_email             TEXT,
    ward_office_phone      TEXT,
    ward_office_fax        TEXT,
    city_hall_office_room  INTEGER,
    city_hall_office_phone TEXT,
    city_hall_office_fax   TEXT,
    Population             INTEGER
)

CREATE TABLE Crime
(
    report_no            INTEGER
            primary key,
    case_number          TEXT,
    date                 TEXT,
    block                TEXT,
    iucr_no              TEXT,
    location_description TEXT,
    arrest               TEXT,
    domestic             TEXT,
    beat                 INTEGER,
    district_no          INTEGER,
    ward_no              INTEGER,
    community_area_no    INTEGER,
    fbi_code_no          TEXT,
    latitude             TEXT,
    longitude            TEXT,
    foreign key (ward_no) references Ward(ward_no),
    foreign key (iucr_no) references IUCR(iucr_no),
    foreign key (district_no) references District(district_no),
    foreign key (community_area_no) references Community_Area(community_area_no),
    foreign key (fbi_code_no) references FBI_Code(fbi_code_no)
)

### Question
How many percent of domestic violence cases were arrested in West Pullman?

### Hint
domestic violence refers to domestic = 'TRUE'; arrested refers to arrest = 'TRUE'; West Pullman refers to community_area_name = 'West Pullman'; percent = divide(count(report_no where arrest = 'TRUE'), count(report_no)) where domestic = 'TRUE' and community_area_name = 'West Pullman' * 100%

### SQL
SELECT CAST(COUNT(CASE WHEN T2.arrest = 'TRUE' THEN T2.report_no END) AS REAL) * 100 / COUNT(T2.report_no) FROM Community_Area AS T1 INNER JOIN Crime AS T2 ON T2.community_area_no = T1.community_area_no WHERE T1.community_area_name = 'West Pullman' AND T2.domestic = 'TRUE'
### Database Schema
CREATE TABLE Community_Area
(
    community_area_no   INTEGER
            primary key,
    community_area_name TEXT,
    side                TEXT,
    population          TEXT
)

CREATE TABLE District
(
    district_no   INTEGER
            primary key,
    district_name TEXT,
    address       TEXT,
    zip_code      INTEGER,
    commander     TEXT,
    email         TEXT,
    phone         TEXT,
    fax           TEXT,
    tty           TEXT,
    twitter       TEXT
)

CREATE TABLE FBI_Code
(
    fbi_code_no   TEXT
            primary key,
    title         TEXT,
    description   TEXT,
    crime_against TEXT
)

CREATE TABLE IUCR
(
    iucr_no               TEXT
            primary key,
    primary_description   TEXT,
    secondary_description TEXT,
    index_code            TEXT
)

CREATE TABLE Neighborhood
(
    neighborhood_name TEXT
            primary key,
    community_area_no INTEGER,
    foreign key (community_area_no) references Community_Area(community_area_no)
)

CREATE TABLE Ward
(
    ward_no                INTEGER
            primary key,
    alderman_first_name    TEXT,
    alderman_last_name     TEXT,
    alderman_name_suffix   TEXT,
    ward_office_address    TEXT,
    ward_office_zip        TEXT,
    ward_email             TEXT,
    ward_office_phone      TEXT,
    ward_office_fax        TEXT,
    city_hall_office_room  INTEGER,
    city_hall_office_phone TEXT,
    city_hall_office_fax   TEXT,
    Population             INTEGER
)

CREATE TABLE Crime
(
    report_no            INTEGER
            primary key,
    case_number          TEXT,
    date                 TEXT,
    block                TEXT,
    iucr_no              TEXT,
    location_description TEXT,
    arrest               TEXT,
    domestic             TEXT,
    beat                 INTEGER,
    district_no          INTEGER,
    ward_no              INTEGER,
    community_area_no    INTEGER,
    fbi_code_no          TEXT,
    latitude             TEXT,
    longitude            TEXT,
    foreign key (ward_no) references Ward(ward_no),
    foreign key (iucr_no) references IUCR(iucr_no),
    foreign key (district_no) references District(district_no),
    foreign key (community_area_no) references Community_Area(community_area_no),
    foreign key (fbi_code_no) references FBI_Code(fbi_code_no)
)

### Question
Calculate the percentage of the domestic violence cases handled by Christopher Taliaferro. Among them, list report numbers of cases that happened in the bank.

### Hint
domestic violence refers to domestic = 'TRUE'; report number refers to report_no; in the bank refers to location_description = 'BANK'; percentage = divide(count(report_no where domestic = 'TRUE'), count(report_no)) * 100%

### SQL
SELECT CAST(COUNT(CASE WHEN T1.domestic = 'TRUE' THEN T1.report_no END) AS REAL) * 100 / COUNT(T1.report_no), COUNT(CASE WHEN T1.domestic = 'TRUE' AND T1.location_description = 'BANK' THEN T1.report_no END) AS "number" FROM Crime AS T1 INNER JOIN Ward AS T2 ON T2.ward_no = T1.ward_no WHERE T2.alderman_first_name = 'Christopher' AND T2.alderman_last_name = 'Taliaferro'