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
Among the crimes in the ward with the most population, how many of them are cases of domestic violence?

### Hint
most population refers to Max(Population); domestic violence refers to domestic = 'TRUE'

### SQL
SELECT COUNT(T1.ward_no) AS num FROM Ward AS T1 INNER JOIN Crime AS T2 ON T1.ward_no = T2.ward_no WHERE T2.domestic = 'TRUE' ORDER BY T1.Population = ( SELECT Population FROM Ward 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
Please list the location coordinates of all the incidents that had happened in the ward represented by alderman Pat Dowell.

### Hint
location coordinates refers to latitude, longitude

### SQL
SELECT T2.latitude, T2.longitude FROM Ward AS T1 INNER JOIN Crime AS T2 ON T1.ward_no = T2.ward_no WHERE T1.alderman_first_name = 'Pat' AND T1.alderman_last_name = 'Dowell' AND T2.latitude IS NOT NULL AND T2.longitude IS NOT NULL
### 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
The ward represented by which alderman had more incidents in January, 2018, Pat Dowell or Sophia King?

### Hint
January, 2018 refers to Substr(date, 1, 1) = '1' AND Substr(date, 5, 4) = '2018'; had more incidents refers to Max(Count(ward_no))

### SQL
SELECT T1.alderman_first_name, T1.alderman_last_name, COUNT(T1.ward_no) AS num FROM Ward AS T1 INNER JOIN Crime AS T2 ON T1.ward_no = T2.ward_no WHERE (SUBSTR(T2.date, 1, 1) = '1' AND SUBSTR(T2.date, 5, 4) = '2018' AND T1.alderman_first_name = 'Pat' AND T1.alderman_last_name = 'Dowell') OR (T1.alderman_first_name = 'Sophia' AND T1.alderman_last_name = 'King') GROUP BY T1.ward_no
### 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
Please list the case numbers of all the incidents with the generic description of "BATTERY" in the IUCR classification.

### Hint
general description refers to primary_description; 'BATTERY' is the primary_description

### SQL
SELECT T2.case_number FROM IUCR AS T1 INNER JOIN Crime AS T2 ON T1.iucr_no = T2.iucr_no WHERE T1.primary_description = 'BATTERY'
### 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 incidents with the generic description of "BATTERY" in the IUCR classification, how many of them do not have arrests made?

### Hint
general description refers to primary_description; 'BATTERY' is the primary_description; do not have arrest made refers to arrest = 'FALSE'

### SQL
SELECT SUM(CASE WHEN T2.arrest = 'FALSE' THEN 1 ELSE 0 END) FROM IUCR AS T1 INNER JOIN Crime AS T2 ON T1.iucr_no = T2.iucr_no WHERE T1.primary_description = 'BATTERY'
### 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
Please list the case numbers of all the crimes whose short description of the kind of crime is "Homicide 1st & 2nd Degree" in the FBI classification.

### Hint
"Homicide 1st & 2nd Degree" is the title

### SQL
SELECT T2.case_number FROM FBI_Code AS T1 INNER JOIN Crime AS T2 ON T1.fbi_code_no = T2.fbi_code_no WHERE T1.title = 'Homicide 1st & 2nd Degree'
### 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 incidents in January, 2018, how many of them were stated "against Property" in the FBI classification?

### Hint
in January 2018 refers to Substr(date, 1, 1) = '1' AND Substr(date, 5, 4) = '2018'; against property refers to crime_against = 'Property'

### SQL
SELECT SUM(CASE WHEN SUBSTR(T2.date, 5, 4) = '2018' THEN 1 ELSE 0 END) FROM FBI_Code AS T1 INNER JOIN Crime AS T2 ON T1.fbi_code_no = T2.fbi_code_no WHERE T1.crime_against = 'Property' AND SUBSTR(T2.date, 1, 1) = '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
District commander Robert A. Rubio was responsible for how many incidents in January, 2018?

### Hint
in January 2018 refers to Substr(date, 1, 1) = '1' AND Substr(date, 5, 4) = '2018'

### SQL
SELECT SUM(CASE WHEN SUBSTR(T2.date, 5, 4) = '2018' THEN 1 ELSE 0 END) FROM District AS T1 INNER JOIN Crime AS T2 ON T1.district_no = T2.district_no WHERE T1.commander = 'Robert A. Rubio' AND SUBSTR(T2.date, 1, 1) = '1'