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
State the salary of the employee who did the most inspections.

### Hint
the most inspections refers to max(count(employee_id))

### SQL
SELECT T1.salary FROM employee AS T1 INNER JOIN ( SELECT T.employee_id, COUNT(T.inspection_id) FROM inspection AS T GROUP BY T.employee_id ORDER BY COUNT(T.inspection_id) DESC LIMIT 1 ) AS T2 ON T1.employee_id = T2.employee_id
### 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 average number of inspections did risk level 3 taverns have?

### Hint
risk level 3 refers to risk_level = '3'; tavern refers to facility_type = 'TAVERN'; average number = divide(count(inspection_id), sum(license_no)) where risk_level = '3' and facility_type = 'TAVERN'

### SQL
SELECT CAST(COUNT(T2.inspection_id) AS REAL) / COUNT(DISTINCT T1.license_no) FROM establishment AS T1 INNER JOIN inspection AS T2 ON T1.license_no = T2.license_no WHERE T1.risk_level = 3 AND T1.facility_type = 'TAVERN'
### 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
State the inspection pass rate of Pockets Restaurant.

### Hint
Pockets refers to dba_name = 'POCKETS'; Restaurant refers to facility_type = 'Restaurant'; pass refers to results = 'Pass'; the inspection pass rate = divide(sum(inspection_id where results = 'Pass'), count(license_no)) where dba_name = 'POCKETS' and facility_type = 'Restaurant'

### SQL
SELECT CAST(COUNT(CASE WHEN T2.results = 'Pass' THEN T2.inspection_id ELSE NULL END) AS REAL) * 100 / COUNT(T2.inspection_id) FROM establishment AS T1 INNER JOIN inspection AS T2 ON T1.license_no = T2.license_no WHERE T1.dba_name = 'POCKETS' AND T1.facility_type = 'Restaurant'
### 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 sanitarian employees in Chicago are from the zip code 60617?

### Hint
sanitarian refers to title = 'Sanitarian'; in Chicago refers to city = 'Chicago'; zip code 60617 refers to zip = 60617

### SQL
SELECT COUNT(employee_id) FROM employee WHERE zip = '60617'
### 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 assumed name of the business located at 2903 W Irving Park Rd?

### Hint
assumed name refers to dba_name; 2903 W Irving Park Rd refers to address = '2903 W IRVING PARK RD '

### SQL
SELECT DISTINCT dba_name FROM establishment WHERE address = '2903 W IRVING PARK RD '
### 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 full name of the employee with the lowest salary?

### Hint
full name refers to first_name, last_name; the lowest salary refers to min(salary)

### SQL
SELECT first_name, last_name FROM employee ORDER BY salary ASC LIMIT 1
### 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 establishments that are doing business as Homemade Pizza have a risk level of 2?

### Hint
Homemade Pizza refers to dba_name = 'HOMEMADE PIZZA'; a risk level of 2 refers to risk_level = 2

### SQL
SELECT COUNT(license_no) FROM establishment WHERE risk_level = 2 AND dba_name = 'HOMEMADE PIZZA'
### 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 with critical food safety problems are under inspection point id 3?

### Hint
critical food safety problems refers to fine = 500; point_id = 3

### SQL
SELECT COUNT(inspection_id) FROM violation WHERE point_id = 3 AND fine = 500
### 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 employees are under Gregory Cardenas?

### SQL
SELECT COUNT(T1.employee_id) FROM employee AS T1 WHERE T1.supervisor = ( SELECT employee_id FROM employee WHERE first_name = 'Gregory' AND last_name = 'Cardenas' )
### 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
When did Renaldi's Pizza had its first inspection?

### Hint
Renaldi's Pizza refers to dba_name = 'RENALDI''S PIZZA'; first inspection refers to min(inspection_date)

### SQL
SELECT MIN(T2.inspection_date) FROM establishment AS T1 INNER JOIN inspection AS T2 ON T1.license_no = T2.license_no WHERE T1.dba_name = 'RENALDI''S PIZZA'
### 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 full name of the employee who was responsible for the most inspection in March 2016?

### Hint
full name refers to first_name, last_name; the most inspection refers to max(count(employee_id)); in March 2016 refers to inspection_date like '2016-03%'

### SQL
SELECT T3.first_name, T3.last_name FROM ( SELECT T1.employee_id, COUNT(T1.inspection_id) FROM inspection AS T1 WHERE strftime('%Y-%m', T1.inspection_date) = '2016-03' GROUP BY T1.employee_id ORDER BY COUNT(T1.inspection_id) DESC LIMIT 1 ) AS T2 INNER JOIN employee AS T3 ON T2.employee_id = T3.employee_id