lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE employee
(
    employee_id INTEGER
            primary key,
    first_name  TEXT,
    last_name   TEXT,
    address     TEXT,
    city        TEXT,
    state       TEXT,
    zip         INTEGER,
    phone       TEXT,
    title       TEXT,
    salary      INTEGER,
    supervisor  INTEGER,
    foreign key (supervisor) references employee(employee_id)
)

CREATE TABLE establishment
(
    license_no    INTEGER
            primary key,
    dba_name      TEXT,
    aka_name      TEXT,
    facility_type TEXT,
    risk_level    INTEGER,
    address       TEXT,
    city          TEXT,
    state         TEXT,
    zip           INTEGER,
    latitude      REAL,
    longitude     REAL,
    ward          INTEGER
)

CREATE TABLE inspection
(
    inspection_id   INTEGER
            primary key,
    inspection_date DATE,
    inspection_type TEXT,
    results         TEXT,
    employee_id     INTEGER,
    license_no      INTEGER,
    followup_to     INTEGER,
    foreign key (employee_id) references employee(employee_id),
    foreign key (license_no) references establishment(license_no),
    foreign key (followup_to) references inspection(inspection_id)
)

CREATE TABLE inspection_point
(
    point_id    INTEGER
            primary key,
    Description TEXT,
    category    TEXT,
    code        TEXT,
    fine        INTEGER,
    point_level TEXT
)

CREATE TABLE violation
(
    inspection_id     INTEGER,
    point_id          INTEGER,
    fine              INTEGER,
    inspector_comment TEXT,
    primary key (inspection_id, point_id),
    foreign key (inspection_id) references inspection(inspection_id),
    foreign key (point_id) references inspection_point(point_id)
)

### Question
Provide the fine paid and the complete address of the establishment with inspection ID 48216.

### Hint
complete address refers to state, city, address

### SQL
SELECT DISTINCT T3.fine, T1.state, T1.city, T1.address FROM establishment AS T1 INNER JOIN inspection AS T2 ON T1.license_no = T2.license_no INNER JOIN violation AS T3 ON T2.inspection_id = T3.inspection_id WHERE T2.inspection_id = 48216
### Database Schema
CREATE TABLE employee
(
    employee_id INTEGER
            primary key,
    first_name  TEXT,
    last_name   TEXT,
    address     TEXT,
    city        TEXT,
    state       TEXT,
    zip         INTEGER,
    phone       TEXT,
    title       TEXT,
    salary      INTEGER,
    supervisor  INTEGER,
    foreign key (supervisor) references employee(employee_id)
)

CREATE TABLE establishment
(
    license_no    INTEGER
            primary key,
    dba_name      TEXT,
    aka_name      TEXT,
    facility_type TEXT,
    risk_level    INTEGER,
    address       TEXT,
    city          TEXT,
    state         TEXT,
    zip           INTEGER,
    latitude      REAL,
    longitude     REAL,
    ward          INTEGER
)

CREATE TABLE inspection
(
    inspection_id   INTEGER
            primary key,
    inspection_date DATE,
    inspection_type TEXT,
    results         TEXT,
    employee_id     INTEGER,
    license_no      INTEGER,
    followup_to     INTEGER,
    foreign key (employee_id) references employee(employee_id),
    foreign key (license_no) references establishment(license_no),
    foreign key (followup_to) references inspection(inspection_id)
)

CREATE TABLE inspection_point
(
    point_id    INTEGER
            primary key,
    Description TEXT,
    category    TEXT,
    code        TEXT,
    fine        INTEGER,
    point_level TEXT
)

CREATE TABLE violation
(
    inspection_id     INTEGER,
    point_id          INTEGER,
    fine              INTEGER,
    inspector_comment TEXT,
    primary key (inspection_id, point_id),
    foreign key (inspection_id) references inspection(inspection_id),
    foreign key (point_id) references inspection_point(point_id)
)

### Question
What is the inspection ID of the inspection with critical point level, $500 fine, and inspector comment "CDI ON 5-17-10"?

### Hint
critical point level refers to point_level = 'Critical'; $500 fine refers to fine = 500; inspector comment "CDI ON 5-17-10" refers to inspector_comment = 'CDI ON 5-17-10'

### SQL
SELECT T2.inspection_id FROM inspection_point AS T1 INNER JOIN violation AS T2 ON T1.point_id = T2.point_id WHERE T2.fine = 500 AND T1.point_level = 'Critical' AND T2.inspector_comment = 'CDI ON 5-17-10'
### Database Schema
CREATE TABLE employee
(
    employee_id INTEGER
            primary key,
    first_name  TEXT,
    last_name   TEXT,
    address     TEXT,
    city        TEXT,
    state       TEXT,
    zip         INTEGER,
    phone       TEXT,
    title       TEXT,
    salary      INTEGER,
    supervisor  INTEGER,
    foreign key (supervisor) references employee(employee_id)
)

CREATE TABLE establishment
(
    license_no    INTEGER
            primary key,
    dba_name      TEXT,
    aka_name      TEXT,
    facility_type TEXT,
    risk_level    INTEGER,
    address       TEXT,
    city          TEXT,
    state         TEXT,
    zip           INTEGER,
    latitude      REAL,
    longitude     REAL,
    ward          INTEGER
)

CREATE TABLE inspection
(
    inspection_id   INTEGER
            primary key,
    inspection_date DATE,
    inspection_type TEXT,
    results         TEXT,
    employee_id     INTEGER,
    license_no      INTEGER,
    followup_to     INTEGER,
    foreign key (employee_id) references employee(employee_id),
    foreign key (license_no) references establishment(license_no),
    foreign key (followup_to) references inspection(inspection_id)
)

CREATE TABLE inspection_point
(
    point_id    INTEGER
            primary key,
    Description TEXT,
    category    TEXT,
    code        TEXT,
    fine        INTEGER,
    point_level TEXT
)

CREATE TABLE violation
(
    inspection_id     INTEGER,
    point_id          INTEGER,
    fine              INTEGER,
    inspector_comment TEXT,
    primary key (inspection_id, point_id),
    foreign key (inspection_id) references inspection(inspection_id),
    foreign key (point_id) references inspection_point(point_id)
)

### Question
What are the inspection description and inspector's comments in the inspection ID 164795?

### Hint
inspection description refers to Description; inspector's comment refers to inspector_comment

### SQL
SELECT T1.Description, T2.inspector_comment FROM inspection_point AS T1 INNER JOIN violation AS T2 ON T1.point_id = T2.point_id WHERE T2.inspection_id = 44247
### Database Schema
CREATE TABLE employee
(
    employee_id INTEGER
            primary key,
    first_name  TEXT,
    last_name   TEXT,
    address     TEXT,
    city        TEXT,
    state       TEXT,
    zip         INTEGER,
    phone       TEXT,
    title       TEXT,
    salary      INTEGER,
    supervisor  INTEGER,
    foreign key (supervisor) references employee(employee_id)
)

CREATE TABLE establishment
(
    license_no    INTEGER
            primary key,
    dba_name      TEXT,
    aka_name      TEXT,
    facility_type TEXT,
    risk_level    INTEGER,
    address       TEXT,
    city          TEXT,
    state         TEXT,
    zip           INTEGER,
    latitude      REAL,
    longitude     REAL,
    ward          INTEGER
)

CREATE TABLE inspection
(
    inspection_id   INTEGER
            primary key,
    inspection_date DATE,
    inspection_type TEXT,
    results         TEXT,
    employee_id     INTEGER,
    license_no      INTEGER,
    followup_to     INTEGER,
    foreign key (employee_id) references employee(employee_id),
    foreign key (license_no) references establishment(license_no),
    foreign key (followup_to) references inspection(inspection_id)
)

CREATE TABLE inspection_point
(
    point_id    INTEGER
            primary key,
    Description TEXT,
    category    TEXT,
    code        TEXT,
    fine        INTEGER,
    point_level TEXT
)

CREATE TABLE violation
(
    inspection_id     INTEGER,
    point_id          INTEGER,
    fine              INTEGER,
    inspector_comment TEXT,
    primary key (inspection_id, point_id),
    foreign key (inspection_id) references inspection(inspection_id),
    foreign key (point_id) references inspection_point(point_id)
)

### Question
What are the inspector's comments and clean operating requirement code for inspection ID 54216 and point ID 34?

### Hint
inspector's comment refers to inspector_comment; clean operating requirement code refers to code

### SQL
SELECT T2.inspector_comment, T1.code FROM inspection_point AS T1 INNER JOIN violation AS T2 ON T1.point_id = T2.point_id WHERE T2.inspection_id = 54216 AND T2.point_id = 34
### Database Schema
CREATE TABLE employee
(
    employee_id INTEGER
            primary key,
    first_name  TEXT,
    last_name   TEXT,
    address     TEXT,
    city        TEXT,
    state       TEXT,
    zip         INTEGER,
    phone       TEXT,
    title       TEXT,
    salary      INTEGER,
    supervisor  INTEGER,
    foreign key (supervisor) references employee(employee_id)
)

CREATE TABLE establishment
(
    license_no    INTEGER
            primary key,
    dba_name      TEXT,
    aka_name      TEXT,
    facility_type TEXT,
    risk_level    INTEGER,
    address       TEXT,
    city          TEXT,
    state         TEXT,
    zip           INTEGER,
    latitude      REAL,
    longitude     REAL,
    ward          INTEGER
)

CREATE TABLE inspection
(
    inspection_id   INTEGER
            primary key,
    inspection_date DATE,
    inspection_type TEXT,
    results         TEXT,
    employee_id     INTEGER,
    license_no      INTEGER,
    followup_to     INTEGER,
    foreign key (employee_id) references employee(employee_id),
    foreign key (license_no) references establishment(license_no),
    foreign key (followup_to) references inspection(inspection_id)
)

CREATE TABLE inspection_point
(
    point_id    INTEGER
            primary key,
    Description TEXT,
    category    TEXT,
    code        TEXT,
    fine        INTEGER,
    point_level TEXT
)

CREATE TABLE violation
(
    inspection_id     INTEGER,
    point_id          INTEGER,
    fine              INTEGER,
    inspector_comment TEXT,
    primary key (inspection_id, point_id),
    foreign key (inspection_id) references inspection(inspection_id),
    foreign key (point_id) references inspection_point(point_id)
)

### Question
Among the establishments that failed in the inspection, what is the percentage of establishments with the highest risk level?

### Hint
failed in inspection refers to results = 'Fail'; the highest risk level refers to max(risk_level); percentage = divide(count(license_no where risk_level = max(risk_level)), count(license_no)) * 100% where results = 'Fail'

### SQL
SELECT CAST(COUNT(CASE WHEN T1.risk_level = 3 THEN T1.license_no END) AS REAL) * 100 / COUNT(T1.risk_level) FROM establishment AS T1 INNER JOIN inspection AS T2 ON T1.license_no = T2.license_no WHERE T2.results = 'Fail'
### Database Schema
CREATE TABLE employee
(
    employee_id INTEGER
            primary key,
    first_name  TEXT,
    last_name   TEXT,
    address     TEXT,
    city        TEXT,
    state       TEXT,
    zip         INTEGER,
    phone       TEXT,
    title       TEXT,
    salary      INTEGER,
    supervisor  INTEGER,
    foreign key (supervisor) references employee(employee_id)
)

CREATE TABLE establishment
(
    license_no    INTEGER
            primary key,
    dba_name      TEXT,
    aka_name      TEXT,
    facility_type TEXT,
    risk_level    INTEGER,
    address       TEXT,
    city          TEXT,
    state         TEXT,
    zip           INTEGER,
    latitude      REAL,
    longitude     REAL,
    ward          INTEGER
)

CREATE TABLE inspection
(
    inspection_id   INTEGER
            primary key,
    inspection_date DATE,
    inspection_type TEXT,
    results         TEXT,
    employee_id     INTEGER,
    license_no      INTEGER,
    followup_to     INTEGER,
    foreign key (employee_id) references employee(employee_id),
    foreign key (license_no) references establishment(license_no),
    foreign key (followup_to) references inspection(inspection_id)
)

CREATE TABLE inspection_point
(
    point_id    INTEGER
            primary key,
    Description TEXT,
    category    TEXT,
    code        TEXT,
    fine        INTEGER,
    point_level TEXT
)

CREATE TABLE violation
(
    inspection_id     INTEGER,
    point_id          INTEGER,
    fine              INTEGER,
    inspector_comment TEXT,
    primary key (inspection_id, point_id),
    foreign key (inspection_id) references inspection(inspection_id),
    foreign key (point_id) references inspection_point(point_id)
)

### Question
Among the employees that receive a salary between $75000 to $85000, what is the difference between the number of employees which undergone an inspection that fined 100 and 500?

### Hint
salary between $75000 and $85000 refers to 75000 < = salary < = 80000; difference = subtract(count(inspection_id where fine = 100), count(inspection_id where fine = 500)) where 75000 < = salary < = 80000

### SQL
SELECT SUM(CASE WHEN T3.fine = 100 THEN 1 ELSE 0 END) - SUM(CASE WHEN T3.fine = 500 THEN 1 ELSE 0 END) FROM employee AS T1 INNER JOIN inspection AS T2 ON T1.employee_id = T2.employee_id INNER JOIN violation AS T3 ON T2.inspection_id = T3.inspection_id WHERE T1.salary BETWEEN 75000 AND 80000
### Database Schema
CREATE TABLE employee
(
    employee_id INTEGER
            primary key,
    first_name  TEXT,
    last_name   TEXT,
    address     TEXT,
    city        TEXT,
    state       TEXT,
    zip         INTEGER,
    phone       TEXT,
    title       TEXT,
    salary      INTEGER,
    supervisor  INTEGER,
    foreign key (supervisor) references employee(employee_id)
)

CREATE TABLE establishment
(
    license_no    INTEGER
            primary key,
    dba_name      TEXT,
    aka_name      TEXT,
    facility_type TEXT,
    risk_level    INTEGER,
    address       TEXT,
    city          TEXT,
    state         TEXT,
    zip           INTEGER,
    latitude      REAL,
    longitude     REAL,
    ward          INTEGER
)

CREATE TABLE inspection
(
    inspection_id   INTEGER
            primary key,
    inspection_date DATE,
    inspection_type TEXT,
    results         TEXT,
    employee_id     INTEGER,
    license_no      INTEGER,
    followup_to     INTEGER,
    foreign key (employee_id) references employee(employee_id),
    foreign key (license_no) references establishment(license_no),
    foreign key (followup_to) references inspection(inspection_id)
)

CREATE TABLE inspection_point
(
    point_id    INTEGER
            primary key,
    Description TEXT,
    category    TEXT,
    code        TEXT,
    fine        INTEGER,
    point_level TEXT
)

CREATE TABLE violation
(
    inspection_id     INTEGER,
    point_id          INTEGER,
    fine              INTEGER,
    inspector_comment TEXT,
    primary key (inspection_id, point_id),
    foreign key (inspection_id) references inspection(inspection_id),
    foreign key (point_id) references inspection_point(point_id)
)

### Question
How many inspections were done in January 2011?

### Hint
in January 2011 refers to inspection_date like '2011-01%'

### SQL
SELECT COUNT(inspection_id) FROM inspection WHERE strftime('%Y-%m', inspection_date) = '2011-01'
### Database Schema
CREATE TABLE employee
(
    employee_id INTEGER
            primary key,
    first_name  TEXT,
    last_name   TEXT,
    address     TEXT,
    city        TEXT,
    state       TEXT,
    zip         INTEGER,
    phone       TEXT,
    title       TEXT,
    salary      INTEGER,
    supervisor  INTEGER,
    foreign key (supervisor) references employee(employee_id)
)

CREATE TABLE establishment
(
    license_no    INTEGER
            primary key,
    dba_name      TEXT,
    aka_name      TEXT,
    facility_type TEXT,
    risk_level    INTEGER,
    address       TEXT,
    city          TEXT,
    state         TEXT,
    zip           INTEGER,
    latitude      REAL,
    longitude     REAL,
    ward          INTEGER
)

CREATE TABLE inspection
(
    inspection_id   INTEGER
            primary key,
    inspection_date DATE,
    inspection_type TEXT,
    results         TEXT,
    employee_id     INTEGER,
    license_no      INTEGER,
    followup_to     INTEGER,
    foreign key (employee_id) references employee(employee_id),
    foreign key (license_no) references establishment(license_no),
    foreign key (followup_to) references inspection(inspection_id)
)

CREATE TABLE inspection_point
(
    point_id    INTEGER
            primary key,
    Description TEXT,
    category    TEXT,
    code        TEXT,
    fine        INTEGER,
    point_level TEXT
)

CREATE TABLE violation
(
    inspection_id     INTEGER,
    point_id          INTEGER,
    fine              INTEGER,
    inspector_comment TEXT,
    primary key (inspection_id, point_id),
    foreign key (inspection_id) references inspection(inspection_id),
    foreign key (point_id) references inspection_point(point_id)
)

### Question
How many inspections failed in 2014?

### Hint
failed refers to results = 'Fail'; in 2014 refers to inspection_date like '2014%'

### SQL
SELECT COUNT(inspection_id) FROM inspection WHERE strftime('%Y', inspection_date) = '2014' AND results = 'Fail'
### Database Schema
CREATE TABLE employee
(
    employee_id INTEGER
            primary key,
    first_name  TEXT,
    last_name   TEXT,
    address     TEXT,
    city        TEXT,
    state       TEXT,
    zip         INTEGER,
    phone       TEXT,
    title       TEXT,
    salary      INTEGER,
    supervisor  INTEGER,
    foreign key (supervisor) references employee(employee_id)
)

CREATE TABLE establishment
(
    license_no    INTEGER
            primary key,
    dba_name      TEXT,
    aka_name      TEXT,
    facility_type TEXT,
    risk_level    INTEGER,
    address       TEXT,
    city          TEXT,
    state         TEXT,
    zip           INTEGER,
    latitude      REAL,
    longitude     REAL,
    ward          INTEGER
)

CREATE TABLE inspection
(
    inspection_id   INTEGER
            primary key,
    inspection_date DATE,
    inspection_type TEXT,
    results         TEXT,
    employee_id     INTEGER,
    license_no      INTEGER,
    followup_to     INTEGER,
    foreign key (employee_id) references employee(employee_id),
    foreign key (license_no) references establishment(license_no),
    foreign key (followup_to) references inspection(inspection_id)
)

CREATE TABLE inspection_point
(
    point_id    INTEGER
            primary key,
    Description TEXT,
    category    TEXT,
    code        TEXT,
    fine        INTEGER,
    point_level TEXT
)

CREATE TABLE violation
(
    inspection_id     INTEGER,
    point_id          INTEGER,
    fine              INTEGER,
    inspector_comment TEXT,
    primary key (inspection_id, point_id),
    foreign key (inspection_id) references inspection(inspection_id),
    foreign key (point_id) references inspection_point(point_id)
)

### Question
Calculate the percentage of inspections with the fine for a minor food safety problem.

### Hint
fine for a minor food safety problem refers to fine = 100; percentage = divide(count(inspection_id where fine = 100), sum(inspection_id)) * 100%

### SQL
SELECT CAST(COUNT(CASE WHEN fine = 100 THEN inspection_id END) AS REAL) * 100 / COUNT(inspection_id) FROM violation
### Database Schema
CREATE TABLE employee
(
    employee_id INTEGER
            primary key,
    first_name  TEXT,
    last_name   TEXT,
    address     TEXT,
    city        TEXT,
    state       TEXT,
    zip         INTEGER,
    phone       TEXT,
    title       TEXT,
    salary      INTEGER,
    supervisor  INTEGER,
    foreign key (supervisor) references employee(employee_id)
)

CREATE TABLE establishment
(
    license_no    INTEGER
            primary key,
    dba_name      TEXT,
    aka_name      TEXT,
    facility_type TEXT,
    risk_level    INTEGER,
    address       TEXT,
    city          TEXT,
    state         TEXT,
    zip           INTEGER,
    latitude      REAL,
    longitude     REAL,
    ward          INTEGER
)

CREATE TABLE inspection
(
    inspection_id   INTEGER
            primary key,
    inspection_date DATE,
    inspection_type TEXT,
    results         TEXT,
    employee_id     INTEGER,
    license_no      INTEGER,
    followup_to     INTEGER,
    foreign key (employee_id) references employee(employee_id),
    foreign key (license_no) references establishment(license_no),
    foreign key (followup_to) references inspection(inspection_id)
)

CREATE TABLE inspection_point
(
    point_id    INTEGER
            primary key,
    Description TEXT,
    category    TEXT,
    code        TEXT,
    fine        INTEGER,
    point_level TEXT
)

CREATE TABLE violation
(
    inspection_id     INTEGER,
    point_id          INTEGER,
    fine              INTEGER,
    inspector_comment TEXT,
    primary key (inspection_id, point_id),
    foreign key (inspection_id) references inspection(inspection_id),
    foreign key (point_id) references inspection_point(point_id)
)

### Question
List the point IDs and fines of the inspections done on 7th August 2010.

### Hint
on 7th August 2010 refers to inspection_date = '2010-08-07'

### SQL
SELECT T2.point_id, T2.fine FROM inspection AS T1 INNER JOIN violation AS T2 ON T1.inspection_id = T2.inspection_id WHERE T1.inspection_date = '2010-08-07'
### Database Schema
CREATE TABLE employee
(
    employee_id INTEGER
            primary key,
    first_name  TEXT,
    last_name   TEXT,
    address     TEXT,
    city        TEXT,
    state       TEXT,
    zip         INTEGER,
    phone       TEXT,
    title       TEXT,
    salary      INTEGER,
    supervisor  INTEGER,
    foreign key (supervisor) references employee(employee_id)
)

CREATE TABLE establishment
(
    license_no    INTEGER
            primary key,
    dba_name      TEXT,
    aka_name      TEXT,
    facility_type TEXT,
    risk_level    INTEGER,
    address       TEXT,
    city          TEXT,
    state         TEXT,
    zip           INTEGER,
    latitude      REAL,
    longitude     REAL,
    ward          INTEGER
)

CREATE TABLE inspection
(
    inspection_id   INTEGER
            primary key,
    inspection_date DATE,
    inspection_type TEXT,
    results         TEXT,
    employee_id     INTEGER,
    license_no      INTEGER,
    followup_to     INTEGER,
    foreign key (employee_id) references employee(employee_id),
    foreign key (license_no) references establishment(license_no),
    foreign key (followup_to) references inspection(inspection_id)
)

CREATE TABLE inspection_point
(
    point_id    INTEGER
            primary key,
    Description TEXT,
    category    TEXT,
    code        TEXT,
    fine        INTEGER,
    point_level TEXT
)

CREATE TABLE violation
(
    inspection_id     INTEGER,
    point_id          INTEGER,
    fine              INTEGER,
    inspector_comment TEXT,
    primary key (inspection_id, point_id),
    foreign key (inspection_id) references inspection(inspection_id),
    foreign key (point_id) references inspection_point(point_id)
)

### Question
How many inspections were done under the personnel category?

### Hint
under the personnel category refers to category = 'Personnel'

### SQL
SELECT COUNT(T1.inspection_id) FROM violation AS T1 INNER JOIN inspection_point AS T2 ON T1.point_id = T2.point_id WHERE T2.category = 'Personnel'