lianghsun/bird-text2sql-bench huggingface dataset:
### 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 all the employees who work in California.

### Hint
social security numbers refers to ssn; California refers to state = 'CA'

### SQL
SELECT T1.ssn FROM employee AS T1  INNER JOIN location AS T2  ON T1.locationID = T2.locationID WHERE T2.state = 'CA'
### 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 work as a Trainee, how many of them have a salary of over &20,000 a year?

### Hint
Trainee is a position title; salary of over 20,000 refers to salary > '20000'

### SQL
SELECT COUNT(*) FROM employee AS T1  INNER JOIN position AS T2  ON T1.positionID = T2.positionID WHERE CAST(REPLACE(SUBSTR(T1.salary, 4), ',', '') AS REAL) > 20000 AND 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
What is the average salary of the employees who work as a Trainee?

### Hint
average = DIVIDE( SUM(salary), COUNT(positiontitle) where positiontitle = 'Trainee'; Trainee is a position title

### SQL
SELECT AVG(CAST(REPLACE(SUBSTR(T1.salary, 4), ',', '') AS REAL)) AS avg 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
By what percentage is the average salary of Trainees higher than the minimum salary of this postion?

### Hint
AVG(salary); Trainee is a position title; minimum salary refers to minsalary; calculation = DIVIDE(SUBTRACT(AVG(salary), minsalary), minsalary) * 100

### SQL
SELECT 100 * (AVG(CAST(REPLACE(SUBSTR(T1.salary, 4), ',', '') AS REAL)) - CAST(REPLACE(SUBSTR(T2.minsalary, 4), ',', '') AS REAL)) / CAST(REPLACE(SUBSTR(T2.minsalary, 4), ',', '') AS REAL) AS per 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
Give the number of female employees.

### Hint
number of female employees means COUNT(gender = 'F')

### SQL
SELECT COUNT(*) FROM employee WHERE gender = 'F'
### 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
State the name of the city where Jose Rodriguez works.

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

### SQL
SELECT T2.locationcity FROM employee AS T1  INNER JOIN location AS T2  ON T1.locationID = T2.locationID WHERE T1.firstname = 'Jose' AND T1.lastname = 'Rodriguez'
### 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 state does Emily Wood work?

### Hint
Emily Wood is the full name of an employee; full name = firstname, lastname;

### SQL
SELECT T2.state FROM employee AS T1  INNER JOIN location AS T2  ON T1.locationID = T2.locationID WHERE T1.firstname = 'Emily' AND T1.lastname = 'Wood'
### 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 education required for David Whitehead to reach his current position?

### Hint
David Whitehead is the full name of an employee; full name = firstname, lastname

### SQL
SELECT T2.educationrequired FROM employee AS T1  INNER JOIN position AS T2  ON T1.positionID = T2.positionID WHERE T1.firstname = 'David' AND T1.lastname = 'Whitehead' 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 are there in the "Miami" office?

### Hint
Miami office refers to locationcity = 'Miami'

### SQL
SELECT COUNT(*) FROM employee AS T1  INNER JOIN location AS T2  ON T1.locationID = T2.locationID WHERE T2.locationcity = 'Miami'
### 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
Who is the highest paid employee in "Boston"? Give the full name.

### Hint
Boston refers to locationcity = 'Boston'; the highest paid employee refers to MAX(salary); full name = firstname, lastname

### SQL
SELECT T1.firstname, T1.lastname FROM employee AS T1  INNER JOIN location AS T2  ON T1.locationID = T2.locationID WHERE T2.locationcity = 'Boston' ORDER BY T1.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
Who is the employee in “New York City” with a good performance? Give the social security number of the employee.

### Hint
New York City refers to locationcity = 'New York City'; good performance refers to performance = 'Good'; social security number refers to ssn

### SQL
SELECT T1.firstname, T1.lastname, T1.ssn FROM employee AS T1  INNER JOIN location AS T2  ON T1.locationID = T2.locationID WHERE T2.locationcity = 'New York City' 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
How many "account representatives" are there in Chicago with a good performance?

### Hint
account representatives is a position title; Chicago refers to locationcity = 'Chicago'; good performance refers to performance = 'Good'

### 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 = 'Account Representative' AND T2.locationcity = 'Chicago' 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 Kenneth Charles's position title?

### Hint
Kenneth Charles is the full name of an employee; full name = firstname, lastname

### SQL
SELECT T2.positiontitle FROM employee AS T1  INNER JOIN position AS T2  ON T1.positionID = T2.positionID WHERE T1.firstname = 'Kenneth' AND T1.lastname = 'Charles'
### 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
Give the full address of the office of the highest paid manager.

### Hint
the highest paid refers to MAX(salary); manager is a position title

### SQL
SELECT T2.address 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 = 'Manager' ORDER BY T1.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
What is the max salary for 'Tracy Coulter' if he/she stays on his/her position?

### Hint
Tracy Coulter is the full name of an employee; full name = firstname, lastname

### SQL
SELECT T2.maxsalary FROM employee AS T1  INNER JOIN position AS T2  ON T1.positionID = T2.positionID WHERE T1.firstname = 'Tracy' AND T1.lastname = 'Coulter'
### 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
If Jose Rodriguez tried his best, how many percentage can his salary raise without changing his position?

### Hint
Jose Rodriguez is the full name of an employee; full name = firstname, lastname; calculation = DIVIDE(SUBTRACT(maxsalary, salary), salary) * 100

### SQL
SELECT 100 * (CAST(REPLACE(SUBSTR(T2.maxsalary, 4), ',', '') AS REAL) - CAST(REPLACE(SUBSTR(T1.salary, 4), ',', '') AS REAL)) / CAST(REPLACE(SUBSTR(T1.salary, 4), ',', '') AS REAL) AS per FROM employee AS T1  INNER JOIN position AS T2  ON T1.positionID = T2.positionID WHERE T1.firstname = 'Jose' AND T1.lastname = 'Rodriguez'
### 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 whose performance is poor have a salary of over $50,000 per year?

### Hint
performance is poor refers to performance = 'Poor'; salary of over $50,000 refers to salary > '50000'

### SQL
SELECT COUNT(*) FROM employee WHERE performance = 'Poor' AND CAST(REPLACE(SUBSTR(salary, 4), ',', '') AS REAL) > 50000
### 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
Who is the employee with the highest salary? Specify his/her full name.

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

### SQL
SELECT firstname, lastname FROM employee WHERE CAST(REPLACE(SUBSTR(salary, 4), ',', '') AS REAL) = ( SELECT MAX(CAST(REPLACE(SUBSTR(salary, 4), ',', '') AS REAL)) FROM employee )
### 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 positions have a maximum salary of no more than US$1000,000?

### Hint
maximum salary of no more than US$1000,000 refers to maxsalary < '100000';

### SQL
SELECT COUNT(*) FROM position WHERE CAST(REPLACE(SUBSTR(maxsalary, 4), ',', '') AS REAL) < 100000
### 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 is the salary of the first ever employee that was hired?

### Hint
first-ever employee that was hired refers to MIN(hiredate)

### SQL
SELECT salary FROM employee ORDER BY hiredate ASC LIMIT 1