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
Give the FBI code description of case No.JB134191.

### Hint
case No.JB134191 refers to case_number = 'JB134191'

### SQL
SELECT description FROM Crime AS T1 INNER JOIN FBI_Code AS T2 ON T1.fbi_code_no = T2.fbi_code_no WHERE T1.case_number = 'JB134191'
### 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
Tell the number of cases with arrests in North Lawndale community.

### Hint
number of cases refers to count(case_number); arrest refers to arrest = 'TRUE'; North Lawndale community refers to community_area_name = 'North Lawndale'

### SQL
SELECT SUM(CASE WHEN T1.community_area_name = 'North Lawndale' THEN 1 ELSE 0 END) FROM Community_Area AS T1 INNER JOIN Crime AS T2 ON T1.community_area_no = T2.community_area_no WHERE T2.arrest = '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
What is the percentage of under $500 thefts among all cases that happened in West Englewood?

### Hint
under $500 refers to secondary_description = '$500 AND UNDER'; theft refers to primary_description = 'THEFT'; West Englewood refers to community_area_name = 'West Englewood'; percentage = divide(count(case_number where secondary_description = '$500 AND UNDER'), count(case_number)) where primary_description = 'THEFT' and community_area_name = 'West Englewood' * 100%

### SQL
SELECT CAST(SUM(CASE WHEN T2.secondary_description = '$500 AND UNDER' THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(T1.case_number) FROM Crime AS T1 INNER JOIN IUCR AS T2 ON T1.iucr_no = T2.iucr_no INNER JOIN Community_Area AS T3 ON T1.community_area_no = T3.community_area_no WHERE T2.primary_description = 'THEFT' AND T3.community_area_name = 'West Englewood'
### 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
What is the percentage of larceny cases among all cases that happened in Edgewater community?

### Hint
larceny case refers to title = 'Larceny'; Edgewater community refers to community_area_name = 'Edgewater'; percentage = divide(count(case_number where title = 'Larceny'), count(case_number)) where community_area_name = 'Edgewater' * 100%

### SQL
SELECT CAST(SUM(CASE WHEN T3.title = 'Larceny' THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(T2.case_number) FROM Community_Area AS T1 INNER JOIN Crime AS T2 ON T1.community_area_no = T2.community_area_no INNER JOIN FBI_Code AS T3 ON T2.fbi_code_no = T3.fbi_code_no WHERE T1.community_area_name = 'Edgewater'
### 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 crimes were committed at 018XX S KOMENSKY AVEin May 2018?

### Hint
 in May 2018 refers to date LIKE '5/%/2018%'

### SQL
SELECT SUM(CASE WHEN date LIKE '5/%/2018%' THEN 1 ELSE 0 END) FROM Crime WHERE block = '018XX S KOMENSKY AVE'
### 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
What is the name of the community with the highest population?

### Hint
name of the community refers to community_area_name; the highest population refers to max(population)

### SQL
SELECT community_area_name FROM Community_Area ORDER BY population DESC 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
How many incidents of domestic violence occurred in an abandoned building in 2018?

### Hint
domestic violence refers to domestic = 'TRUE'; in an abandoned building refers to location_description = 'ABANDONED BUILDING'; in 2018 refers to date LIKE '%2018%'

### SQL
SELECT SUM(CASE WHEN location_description = 'ABANDONED BUILDING' THEN 1 ELSE 0 END) FROM Crime WHERE date LIKE '%2018%' AND 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
What is the population of the district with the least population?

### Hint
the least population refers = min(sum(population))

### SQL
SELECT SUM(population) FROM Community_Area GROUP BY side ORDER BY SUM(population) LIMIT 1