lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE Ingredient
(
    ingredient_id INTEGER
            primary key,
    category      TEXT,
    name          TEXT,
    plural        TEXT
)

CREATE TABLE Recipe
(
    recipe_id  INTEGER
            primary key,
    title      TEXT,
    subtitle   TEXT,
    servings   INTEGER,
    yield_unit TEXT,
    prep_min   INTEGER,
    cook_min   INTEGER,
    stnd_min   INTEGER,
    source     TEXT,
    intro      TEXT,
    directions TEXT
)

CREATE TABLE Nutrition
(
    recipe_id     INTEGER
            primary key,
    protein       REAL,
    carbo         REAL,
    alcohol       REAL,
    total_fat     REAL,
    sat_fat       REAL,
    cholestrl     REAL,
    sodium        REAL,
    iron          REAL,
    vitamin_c     REAL,
    vitamin_a     REAL,
    fiber         REAL,
    pcnt_cal_carb REAL,
    pcnt_cal_fat  REAL,
    pcnt_cal_prot REAL,
    calories      REAL,
    foreign key (recipe_id) references Recipe(recipe_id)
)

CREATE TABLE Quantity
(
    quantity_id   INTEGER
            primary key,
    recipe_id     INTEGER,
    ingredient_id INTEGER,
    max_qty       REAL,
    min_qty       REAL,
    unit          TEXT,
    preparation   TEXT,
    optional      TEXT,
    foreign key (recipe_id) references Recipe(recipe_id),
    foreign key (ingredient_id) references Ingredient(ingredient_id),
    foreign key (recipe_id) references Nutrition(recipe_id)
)

### Question
What ingredients does the longest cooking time recipe have?

### Hint
the longest cooking time refers to MAX(cook_min)

### SQL
SELECT T3.name FROM Recipe AS T1 INNER JOIN Quantity AS T2 ON T1.recipe_id = T2.recipe_id INNER JOIN Ingredient AS T3 ON T3.ingredient_id = T2.ingredient_id ORDER BY T1.cook_min DESC LIMIT 1
### Database Schema
CREATE TABLE Ingredient
(
    ingredient_id INTEGER
            primary key,
    category      TEXT,
    name          TEXT,
    plural        TEXT
)

CREATE TABLE Recipe
(
    recipe_id  INTEGER
            primary key,
    title      TEXT,
    subtitle   TEXT,
    servings   INTEGER,
    yield_unit TEXT,
    prep_min   INTEGER,
    cook_min   INTEGER,
    stnd_min   INTEGER,
    source     TEXT,
    intro      TEXT,
    directions TEXT
)

CREATE TABLE Nutrition
(
    recipe_id     INTEGER
            primary key,
    protein       REAL,
    carbo         REAL,
    alcohol       REAL,
    total_fat     REAL,
    sat_fat       REAL,
    cholestrl     REAL,
    sodium        REAL,
    iron          REAL,
    vitamin_c     REAL,
    vitamin_a     REAL,
    fiber         REAL,
    pcnt_cal_carb REAL,
    pcnt_cal_fat  REAL,
    pcnt_cal_prot REAL,
    calories      REAL,
    foreign key (recipe_id) references Recipe(recipe_id)
)

CREATE TABLE Quantity
(
    quantity_id   INTEGER
            primary key,
    recipe_id     INTEGER,
    ingredient_id INTEGER,
    max_qty       REAL,
    min_qty       REAL,
    unit          TEXT,
    preparation   TEXT,
    optional      TEXT,
    foreign key (recipe_id) references Recipe(recipe_id),
    foreign key (ingredient_id) references Ingredient(ingredient_id),
    foreign key (recipe_id) references Nutrition(recipe_id)
)

### Question
Calculate the percentage of recipes with no cholesterol included and have a cooking time less than 20 minutes among all recipes.

### Hint
no cholesterol refers to cholestrl = 0; cooking time less than 20 minutes refers to cook_min < 20; calculation = MULTIPLY(DIVIDE(COUNT(cholestrl = 0 THEN recipe_id), COUNT(recipe_id)), 100)

### SQL
SELECT CAST(SUM(CASE WHEN T1.cook_min < 20 AND T2.cholestrl = 0 THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(*) FROM Recipe AS T1 INNER JOIN Nutrition AS T2 ON T1.recipe_id = T2.recipe_id
### Database Schema
CREATE TABLE Ingredient
(
    ingredient_id INTEGER
            primary key,
    category      TEXT,
    name          TEXT,
    plural        TEXT
)

CREATE TABLE Recipe
(
    recipe_id  INTEGER
            primary key,
    title      TEXT,
    subtitle   TEXT,
    servings   INTEGER,
    yield_unit TEXT,
    prep_min   INTEGER,
    cook_min   INTEGER,
    stnd_min   INTEGER,
    source     TEXT,
    intro      TEXT,
    directions TEXT
)

CREATE TABLE Nutrition
(
    recipe_id     INTEGER
            primary key,
    protein       REAL,
    carbo         REAL,
    alcohol       REAL,
    total_fat     REAL,
    sat_fat       REAL,
    cholestrl     REAL,
    sodium        REAL,
    iron          REAL,
    vitamin_c     REAL,
    vitamin_a     REAL,
    fiber         REAL,
    pcnt_cal_carb REAL,
    pcnt_cal_fat  REAL,
    pcnt_cal_prot REAL,
    calories      REAL,
    foreign key (recipe_id) references Recipe(recipe_id)
)

CREATE TABLE Quantity
(
    quantity_id   INTEGER
            primary key,
    recipe_id     INTEGER,
    ingredient_id INTEGER,
    max_qty       REAL,
    min_qty       REAL,
    unit          TEXT,
    preparation   TEXT,
    optional      TEXT,
    foreign key (recipe_id) references Recipe(recipe_id),
    foreign key (ingredient_id) references Ingredient(ingredient_id),
    foreign key (recipe_id) references Nutrition(recipe_id)
)

### Question
Among all recipes containing cheese, what is the percentage of recipes with calories greater than 200?

### Hint
cheese is a category; calories greater than 200 refers to calories > 200; calculation = MULTIPLY(DIVIDE(COUNT(calories > 200 THEN recipe_id), COUNT(recipe_id)), 100)

### SQL
SELECT CAST(SUM(CASE WHEN T4.calories > 200 THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(*) FROM Recipe AS T1 INNER JOIN Quantity AS T2 ON T1.recipe_id = T2.recipe_id INNER JOIN Ingredient AS T3 ON T3.ingredient_id = T2.ingredient_id INNER JOIN Nutrition AS T4 ON T4.recipe_id = T1.recipe_id WHERE T3.category = 'cheese'
### Database Schema
CREATE TABLE location
(
    locationID   INTEGER
        constraint location_pk
            primary key,
    locationcity TEXT,
    address      TEXT,
    state        TEXT,
    zipcode      INTEGER,
    officephone  TEXT
)

CREATE TABLE position
(
    positionID        INTEGER
        constraint position_pk
            primary key,
    positiontitle     TEXT,
    educationrequired TEXT,
    minsalary         TEXT,
    maxsalary         TEXT
)

CREATE TABLE employee
(
    ssn         TEXT
            primary key,
    lastname    TEXT,
    firstname   TEXT,
    hiredate    TEXT,
    salary      TEXT,
    gender      TEXT,
    performance TEXT,
    positionID  INTEGER,
    locationID  INTEGER,
    foreign key (locationID) references location(locationID),
    foreign key (positionID) references position(positionID)
)

### Question
Which employee has the highest salary? Please give his or her full name.

### Hint
the highest salary refers to MAX(salary); full name = firstname, lastname

### SQL
SELECT firstname, lastname FROM employee ORDER BY salary DESC LIMIT 1
### Database Schema
CREATE TABLE location
(
    locationID   INTEGER
        constraint location_pk
            primary key,
    locationcity TEXT,
    address      TEXT,
    state        TEXT,
    zipcode      INTEGER,
    officephone  TEXT
)

CREATE TABLE position
(
    positionID        INTEGER
        constraint position_pk
            primary key,
    positiontitle     TEXT,
    educationrequired TEXT,
    minsalary         TEXT,
    maxsalary         TEXT
)

CREATE TABLE employee
(
    ssn         TEXT
            primary key,
    lastname    TEXT,
    firstname   TEXT,
    hiredate    TEXT,
    salary      TEXT,
    gender      TEXT,
    performance TEXT,
    positionID  INTEGER,
    locationID  INTEGER,
    foreign key (locationID) references location(locationID),
    foreign key (positionID) references position(positionID)
)

### Question
How many emplyees have a good job performance?

### Hint
good job performance refers to performance = 'Good'

### SQL
SELECT COUNT(*) FROM employee WHERE performance = 'Good'
### Database Schema
CREATE TABLE location
(
    locationID   INTEGER
        constraint location_pk
            primary key,
    locationcity TEXT,
    address      TEXT,
    state        TEXT,
    zipcode      INTEGER,
    officephone  TEXT
)

CREATE TABLE position
(
    positionID        INTEGER
        constraint position_pk
            primary key,
    positiontitle     TEXT,
    educationrequired TEXT,
    minsalary         TEXT,
    maxsalary         TEXT
)

CREATE TABLE employee
(
    ssn         TEXT
            primary key,
    lastname    TEXT,
    firstname   TEXT,
    hiredate    TEXT,
    salary      TEXT,
    gender      TEXT,
    performance TEXT,
    positionID  INTEGER,
    locationID  INTEGER,
    foreign key (locationID) references location(locationID),
    foreign key (positionID) references position(positionID)
)

### Question
Please list the social security numbers of the male employees with a salary of over $70,000 a year.

### Hint
social security numbers refers to ssn; male employees refers to gender = 'M'; salary of over $70,000 a year refers to salary > '70000'

### SQL
SELECT ssn FROM employee WHERE gender = 'M' AND CAST(REPLACE(SUBSTR(salary, 4), ',', '') AS REAL) > 70000
### Database Schema
CREATE TABLE location
(
    locationID   INTEGER
        constraint location_pk
            primary key,
    locationcity TEXT,
    address      TEXT,
    state        TEXT,
    zipcode      INTEGER,
    officephone  TEXT
)

CREATE TABLE position
(
    positionID        INTEGER
        constraint position_pk
            primary key,
    positiontitle     TEXT,
    educationrequired TEXT,
    minsalary         TEXT,
    maxsalary         TEXT
)

CREATE TABLE employee
(
    ssn         TEXT
            primary key,
    lastname    TEXT,
    firstname   TEXT,
    hiredate    TEXT,
    salary      TEXT,
    gender      TEXT,
    performance TEXT,
    positionID  INTEGER,
    locationID  INTEGER,
    foreign key (locationID) references location(locationID),
    foreign key (positionID) references position(positionID)
)

### Question
What is the required education for the position of regional manager?

### Hint
required education refers to educationrequired; position of regional manager refers to  positiontitle = 'Regional Manager'

### SQL
SELECT educationrequired FROM position WHERE positiontitle = 'Regional Manager'
### Database Schema
CREATE TABLE location
(
    locationID   INTEGER
        constraint location_pk
            primary key,
    locationcity TEXT,
    address      TEXT,
    state        TEXT,
    zipcode      INTEGER,
    officephone  TEXT
)

CREATE TABLE position
(
    positionID        INTEGER
        constraint position_pk
            primary key,
    positiontitle     TEXT,
    educationrequired TEXT,
    minsalary         TEXT,
    maxsalary         TEXT
)

CREATE TABLE employee
(
    ssn         TEXT
            primary key,
    lastname    TEXT,
    firstname   TEXT,
    hiredate    TEXT,
    salary      TEXT,
    gender      TEXT,
    performance TEXT,
    positionID  INTEGER,
    locationID  INTEGER,
    foreign key (locationID) references location(locationID),
    foreign key (positionID) references position(positionID)
)

### Question
Which position has a lower minimum salary, Account Representative or Trainee?

### Hint
position of Account Representative refers to positiontitle = 'Account Representative'; position of Trainee refers to positiontitle = 'Trainee'; lower minimum salary refers to MIN(minsalary)

### SQL
SELECT positiontitle FROM position WHERE positiontitle = 'Account Representative' OR positiontitle = 'Trainee' ORDER BY minsalary ASC LIMIT 1
### Database Schema
CREATE TABLE location
(
    locationID   INTEGER
        constraint location_pk
            primary key,
    locationcity TEXT,
    address      TEXT,
    state        TEXT,
    zipcode      INTEGER,
    officephone  TEXT
)

CREATE TABLE position
(
    positionID        INTEGER
        constraint position_pk
            primary key,
    positiontitle     TEXT,
    educationrequired TEXT,
    minsalary         TEXT,
    maxsalary         TEXT
)

CREATE TABLE employee
(
    ssn         TEXT
            primary key,
    lastname    TEXT,
    firstname   TEXT,
    hiredate    TEXT,
    salary      TEXT,
    gender      TEXT,
    performance TEXT,
    positionID  INTEGER,
    locationID  INTEGER,
    foreign key (locationID) references location(locationID),
    foreign key (positionID) references position(positionID)
)

### Question
In which city's office does Sandy Adams work at?

### Hint
Sandy Adams is the fullname of an employee; full name = firstname, lastname; city refers to locationcity

### SQL
SELECT T2.locationcity FROM employee AS T1  INNER JOIN location AS T2  ON T1.locationID = T2.locationID WHERE T1.lastname = 'Adams' AND T1.firstname = 'Sandy'
### Database Schema
CREATE TABLE location
(
    locationID   INTEGER
        constraint location_pk
            primary key,
    locationcity TEXT,
    address      TEXT,
    state        TEXT,
    zipcode      INTEGER,
    officephone  TEXT
)

CREATE TABLE position
(
    positionID        INTEGER
        constraint position_pk
            primary key,
    positiontitle     TEXT,
    educationrequired TEXT,
    minsalary         TEXT,
    maxsalary         TEXT
)

CREATE TABLE employee
(
    ssn         TEXT
            primary key,
    lastname    TEXT,
    firstname   TEXT,
    hiredate    TEXT,
    salary      TEXT,
    gender      TEXT,
    performance TEXT,
    positionID  INTEGER,
    locationID  INTEGER,
    foreign key (locationID) references location(locationID),
    foreign key (positionID) references position(positionID)
)

### Question
Among the employees working at the office in New York, how many of them have a good job performance?

### Hint
Sandy Adams is the fullname of an employee; full name = firstname, lastname; New York refers to state = 'NY'; good job performance refers to performance = 'Good';

### SQL
SELECT COUNT(*) FROM employee AS T1 INNER JOIN location AS T2  ON T1.locationID = T2.locationID WHERE T2.state = 'NY' AND T1.performance = 'Good'
### Database Schema
CREATE TABLE location
(
    locationID   INTEGER
        constraint location_pk
            primary key,
    locationcity TEXT,
    address      TEXT,
    state        TEXT,
    zipcode      INTEGER,
    officephone  TEXT
)

CREATE TABLE position
(
    positionID        INTEGER
        constraint position_pk
            primary key,
    positiontitle     TEXT,
    educationrequired TEXT,
    minsalary         TEXT,
    maxsalary         TEXT
)

CREATE TABLE employee
(
    ssn         TEXT
            primary key,
    lastname    TEXT,
    firstname   TEXT,
    hiredate    TEXT,
    salary      TEXT,
    gender      TEXT,
    performance TEXT,
    positionID  INTEGER,
    locationID  INTEGER,
    foreign key (locationID) references location(locationID),
    foreign key (positionID) references position(positionID)
)

### Question
What is the office phone number of the location at which Sandy Adams works?

### Hint
Sandy Adams is the fullname of an employee; full name = firstname, lastname;

### SQL
SELECT T2.officephone FROM employee AS T1  INNER JOIN location AS T2  ON T1.locationID = T2.locationID WHERE T1.lastname = 'Adams' AND T1.firstname = 'Sandy'
### Database Schema
CREATE TABLE location
(
    locationID   INTEGER
        constraint location_pk
            primary key,
    locationcity TEXT,
    address      TEXT,
    state        TEXT,
    zipcode      INTEGER,
    officephone  TEXT
)

CREATE TABLE position
(
    positionID        INTEGER
        constraint position_pk
            primary key,
    positiontitle     TEXT,
    educationrequired TEXT,
    minsalary         TEXT,
    maxsalary         TEXT
)

CREATE TABLE employee
(
    ssn         TEXT
            primary key,
    lastname    TEXT,
    firstname   TEXT,
    hiredate    TEXT,
    salary      TEXT,
    gender      TEXT,
    performance TEXT,
    positionID  INTEGER,
    locationID  INTEGER,
    foreign key (locationID) references location(locationID),
    foreign key (positionID) references position(positionID)
)

### Question
How many male employees work at the address 450 Peachtree Rd?

### Hint
male employees refers to gender = 'M'

### SQL
SELECT COUNT(*) FROM employee AS T1  INNER JOIN location AS T2  ON T1.locationID = T2.locationID WHERE T2.address = '450 Peachtree Rd' AND T1.gender = 'M'
### Database Schema
CREATE TABLE location
(
    locationID   INTEGER
        constraint location_pk
            primary key,
    locationcity TEXT,
    address      TEXT,
    state        TEXT,
    zipcode      INTEGER,
    officephone  TEXT
)

CREATE TABLE position
(
    positionID        INTEGER
        constraint position_pk
            primary key,
    positiontitle     TEXT,
    educationrequired TEXT,
    minsalary         TEXT,
    maxsalary         TEXT
)

CREATE TABLE employee
(
    ssn         TEXT
            primary key,
    lastname    TEXT,
    firstname   TEXT,
    hiredate    TEXT,
    salary      TEXT,
    gender      TEXT,
    performance TEXT,
    positionID  INTEGER,
    locationID  INTEGER,
    foreign key (locationID) references location(locationID),
    foreign key (positionID) references position(positionID)
)

### Question
How many employees work as an Account Representative?

### Hint
work as an Account Representative refers to positiontitle = 'Account Representative'

### SQL
SELECT COUNT(*) FROM employee AS T1  INNER JOIN position AS T2  ON T1.positionID = T2.positionID WHERE T2.positiontitle = 'Account Representative'
### Database Schema
CREATE TABLE location
(
    locationID   INTEGER
        constraint location_pk
            primary key,
    locationcity TEXT,
    address      TEXT,
    state        TEXT,
    zipcode      INTEGER,
    officephone  TEXT
)

CREATE TABLE position
(
    positionID        INTEGER
        constraint position_pk
            primary key,
    positiontitle     TEXT,
    educationrequired TEXT,
    minsalary         TEXT,
    maxsalary         TEXT
)

CREATE TABLE employee
(
    ssn         TEXT
            primary key,
    lastname    TEXT,
    firstname   TEXT,
    hiredate    TEXT,
    salary      TEXT,
    gender      TEXT,
    performance TEXT,
    positionID  INTEGER,
    locationID  INTEGER,
    foreign key (locationID) references location(locationID),
    foreign key (positionID) references position(positionID)
)

### Question
How much higher is James Johnson's salary from the minimum salary of his title?

### Hint
James Johnson is the fullname of an employee; full name = firstname, lastname; minimum salary refers to minsalary; calculation = SUBTRACT(salary, minsalary)

### SQL
SELECT CAST(REPLACE(SUBSTR(T1.salary, 4), ',', '') AS REAL) - CAST(REPLACE(SUBSTR(T2.minsalary, 4), ',', '') AS REAL) AS diff FROM employee AS T1  INNER JOIN position AS T2  ON T1.positionID = T2.positionID WHERE T1.lastname = 'Johnson' AND T1.firstname = 'James'
### Database Schema
CREATE TABLE location
(
    locationID   INTEGER
        constraint location_pk
            primary key,
    locationcity TEXT,
    address      TEXT,
    state        TEXT,
    zipcode      INTEGER,
    officephone  TEXT
)

CREATE TABLE position
(
    positionID        INTEGER
        constraint position_pk
            primary key,
    positiontitle     TEXT,
    educationrequired TEXT,
    minsalary         TEXT,
    maxsalary         TEXT
)

CREATE TABLE employee
(
    ssn         TEXT
            primary key,
    lastname    TEXT,
    firstname   TEXT,
    hiredate    TEXT,
    salary      TEXT,
    gender      TEXT,
    performance TEXT,
    positionID  INTEGER,
    locationID  INTEGER,
    foreign key (locationID) references location(locationID),
    foreign key (positionID) references position(positionID)
)

### Question
Among the employees who are Trainees, how many of them work in New York?

### Hint
Trainees is a position title; California refers to state = 'NY'

### SQL
SELECT COUNT(*) FROM employee AS T1  INNER JOIN location AS T2  ON T1.locationID = T2.locationID INNER JOIN position AS T3  ON T3.positionID = T1.positionID WHERE T3.positiontitle = 'Trainee' AND T2.state = 'NY'
### Database Schema
CREATE TABLE location
(
    locationID   INTEGER
        constraint location_pk
            primary key,
    locationcity TEXT,
    address      TEXT,
    state        TEXT,
    zipcode      INTEGER,
    officephone  TEXT
)

CREATE TABLE position
(
    positionID        INTEGER
        constraint position_pk
            primary key,
    positiontitle     TEXT,
    educationrequired TEXT,
    minsalary         TEXT,
    maxsalary         TEXT
)

CREATE TABLE employee
(
    ssn         TEXT
            primary key,
    lastname    TEXT,
    firstname   TEXT,
    hiredate    TEXT,
    salary      TEXT,
    gender      TEXT,
    performance TEXT,
    positionID  INTEGER,
    locationID  INTEGER,
    foreign key (locationID) references location(locationID),
    foreign key (positionID) references position(positionID)
)

### Question
Please list the full names of the employees who are working as a Trainee.

### Hint
full name = firstname, lastname; trainees is a position title

### SQL
SELECT T1.firstname, T1.lastname FROM employee AS T1  INNER JOIN position AS T2  ON T1.positionID = T2.positionID WHERE T2.positiontitle = 'Trainee'
### Database Schema
CREATE TABLE location
(
    locationID   INTEGER
        constraint location_pk
            primary key,
    locationcity TEXT,
    address      TEXT,
    state        TEXT,
    zipcode      INTEGER,
    officephone  TEXT
)

CREATE TABLE position
(
    positionID        INTEGER
        constraint position_pk
            primary key,
    positiontitle     TEXT,
    educationrequired TEXT,
    minsalary         TEXT,
    maxsalary         TEXT
)

CREATE TABLE employee
(
    ssn         TEXT
            primary key,
    lastname    TEXT,
    firstname   TEXT,
    hiredate    TEXT,
    salary      TEXT,
    gender      TEXT,
    performance TEXT,
    positionID  INTEGER,
    locationID  INTEGER,
    foreign key (locationID) references location(locationID),
    foreign key (positionID) references position(positionID)
)

### Question
Which employee's job position requires a higher education level, Jose Rodriguez or Sandy Adams?

### Hint
Jose Rodriguez AND Sandy Adams are the fullname of employee; full name = firstname, lastname; higher education level refers to MAX(educationrequired)

### SQL
SELECT T1.firstname, T1.lastname FROM employee AS T1  INNER JOIN position AS T2  ON T1.positionID = T2.positionID WHERE (T1.lastname = 'Adams' AND T1.firstname = 'Sandy') OR (T1.lastname = 'Rodriguez' AND T1.firstname = 'Jose') ORDER BY T2.educationrequired DESC LIMIT 1
### Database Schema
CREATE TABLE location
(
    locationID   INTEGER
        constraint location_pk
            primary key,
    locationcity TEXT,
    address      TEXT,
    state        TEXT,
    zipcode      INTEGER,
    officephone  TEXT
)

CREATE TABLE position
(
    positionID        INTEGER
        constraint position_pk
            primary key,
    positiontitle     TEXT,
    educationrequired TEXT,
    minsalary         TEXT,
    maxsalary         TEXT
)

CREATE TABLE employee
(
    ssn         TEXT
            primary key,
    lastname    TEXT,
    firstname   TEXT,
    hiredate    TEXT,
    salary      TEXT,
    gender      TEXT,
    performance TEXT,
    positionID  INTEGER,
    locationID  INTEGER,
    foreign key (locationID) references location(locationID),
    foreign key (positionID) references position(positionID)
)

### Question
Please list the zip codes of the offices where all the male employees with a good job performance work at.

### Hint
male employees refers to gender = 'M'; good job performance refers to performance = 'Good'

### SQL
SELECT T2.zipcode FROM employee AS T1  INNER JOIN location AS T2  ON T1.locationID = T2.locationID WHERE T1.gender = 'M' AND T1.performance = 'Good'