lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE Customers
(
    CustomerID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Employees
(
    EmployeeID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Products
(
    ProductID INTEGER            not null
        primary key,
    Name      TEXT    not null,
    Price     REAL null
)

CREATE TABLE Sales
(
    SalesID       INTEGER not null
        primary key,
    SalesPersonID INTEGER not null,
    CustomerID    INTEGER not null,
    ProductID     INTEGER not null,
    Quantity      INTEGER not null,
    foreign key (SalesPersonID) references Employees (EmployeeID)
            on update cascade on delete cascade,
        foreign key (CustomerID) references Customers (CustomerID)
            on update cascade on delete cascade,
        foreign key (ProductID) references Products (ProductID)
            on update cascade on delete cascade
)

### Question
Identify the name of the sales person with employee ID 7.

### Hint
name of the sales person = FirstName, MiddleInitial, LastName;

### SQL
SELECT FirstName, MiddleInitial, LastName FROM Employees WHERE EmployeeID = 7
### Database Schema
CREATE TABLE Customers
(
    CustomerID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Employees
(
    EmployeeID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Products
(
    ProductID INTEGER            not null
        primary key,
    Name      TEXT    not null,
    Price     REAL null
)

CREATE TABLE Sales
(
    SalesID       INTEGER not null
        primary key,
    SalesPersonID INTEGER not null,
    CustomerID    INTEGER not null,
    ProductID     INTEGER not null,
    Quantity      INTEGER not null,
    foreign key (SalesPersonID) references Employees (EmployeeID)
            on update cascade on delete cascade,
        foreign key (CustomerID) references Customers (CustomerID)
            on update cascade on delete cascade,
        foreign key (ProductID) references Products (ProductID)
            on update cascade on delete cascade
)

### Question
Name the most expensive and the least expensive products available, excluding free gifts.

### Hint
most expensive product refers to MAX(Price); least expensive product refers to MIN(Price); excluding free gifts refers to not including Price = 0;

### SQL
SELECT Name FROM Products WHERE Price IN (( SELECT MAX(Price) FROM Products ), ( SELECT MIN(Price) FROM Products ))
### Database Schema
CREATE TABLE Customers
(
    CustomerID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Employees
(
    EmployeeID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Products
(
    ProductID INTEGER            not null
        primary key,
    Name      TEXT    not null,
    Price     REAL null
)

CREATE TABLE Sales
(
    SalesID       INTEGER not null
        primary key,
    SalesPersonID INTEGER not null,
    CustomerID    INTEGER not null,
    ProductID     INTEGER not null,
    Quantity      INTEGER not null,
    foreign key (SalesPersonID) references Employees (EmployeeID)
            on update cascade on delete cascade,
        foreign key (CustomerID) references Customers (CustomerID)
            on update cascade on delete cascade,
        foreign key (ProductID) references Products (ProductID)
            on update cascade on delete cascade
)

### Question
Among all the customers who have purchased ML Bottom Bracket, identify the percentage of sales by Albert I. Ringer?

### Hint
ML Bottom Bracket' is name of product; percentage = MULTIPLY(DIVIDE(SUM(CustomerID WHERE FirstName = 'Albert' AND MiddleInitial = 'I' AND LastName = 'Ringer'), COUNT(CustomerID)), 1.0);

### SQL
SELECT CAST(SUM(IIF(T3.FirstName = 'Albert' AND T3.MiddleInitial = 'I' AND T3.LastName = 'Ringer', 1, 0)) AS REAL) * 100 / COUNT(T2.CustomerID) FROM Products AS T1 INNER JOIN Sales AS T2 ON T1.ProductID = T2.ProductID INNER JOIN Employees AS T3 ON T2.SalesPersonID = T3.EmployeeID WHERE T1.Name = 'ML Bottom Bracket'
### Database Schema
CREATE TABLE Customers
(
    CustomerID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Employees
(
    EmployeeID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Products
(
    ProductID INTEGER            not null
        primary key,
    Name      TEXT    not null,
    Price     REAL null
)

CREATE TABLE Sales
(
    SalesID       INTEGER not null
        primary key,
    SalesPersonID INTEGER not null,
    CustomerID    INTEGER not null,
    ProductID     INTEGER not null,
    Quantity      INTEGER not null,
    foreign key (SalesPersonID) references Employees (EmployeeID)
            on update cascade on delete cascade,
        foreign key (CustomerID) references Customers (CustomerID)
            on update cascade on delete cascade,
        foreign key (ProductID) references Products (ProductID)
            on update cascade on delete cascade
)

### Question
How many customers have the first name Abigail?

### SQL
SELECT COUNT(CustomerID) FROM Customers WHERE FirstName = 'Abigail'
### Database Schema
CREATE TABLE Customers
(
    CustomerID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Employees
(
    EmployeeID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Products
(
    ProductID INTEGER            not null
        primary key,
    Name      TEXT    not null,
    Price     REAL null
)

CREATE TABLE Sales
(
    SalesID       INTEGER not null
        primary key,
    SalesPersonID INTEGER not null,
    CustomerID    INTEGER not null,
    ProductID     INTEGER not null,
    Quantity      INTEGER not null,
    foreign key (SalesPersonID) references Employees (EmployeeID)
            on update cascade on delete cascade,
        foreign key (CustomerID) references Customers (CustomerID)
            on update cascade on delete cascade,
        foreign key (ProductID) references Products (ProductID)
            on update cascade on delete cascade
)

### Question
Indicate the quantity of Blade products sold.

### Hint
Blade' is name of product;

### SQL
SELECT DISTINCT T2.Quantity FROM Products AS T1 INNER JOIN Sales AS T2 ON T1.ProductID = T2.ProductID WHERE T1.Name = 'Blade'
### Database Schema
CREATE TABLE Customers
(
    CustomerID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Employees
(
    EmployeeID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Products
(
    ProductID INTEGER            not null
        primary key,
    Name      TEXT    not null,
    Price     REAL null
)

CREATE TABLE Sales
(
    SalesID       INTEGER not null
        primary key,
    SalesPersonID INTEGER not null,
    CustomerID    INTEGER not null,
    ProductID     INTEGER not null,
    Quantity      INTEGER not null,
    foreign key (SalesPersonID) references Employees (EmployeeID)
            on update cascade on delete cascade,
        foreign key (CustomerID) references Customers (CustomerID)
            on update cascade on delete cascade,
        foreign key (ProductID) references Products (ProductID)
            on update cascade on delete cascade
)

### Question
Give the full name of the employee who has sold the most quantity.

### Hint
full name of the employee = FirstName, LastName; most quantity refers to MAX(Quantity);

### SQL
SELECT T1.FirstName, T1.LastName FROM Employees AS T1 INNER JOIN Sales AS T2 ON T1.EmployeeID = T2.SalesPersonID ORDER BY T2.Quantity DESC LIMIT 1
### Database Schema
CREATE TABLE Customers
(
    CustomerID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Employees
(
    EmployeeID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Products
(
    ProductID INTEGER            not null
        primary key,
    Name      TEXT    not null,
    Price     REAL null
)

CREATE TABLE Sales
(
    SalesID       INTEGER not null
        primary key,
    SalesPersonID INTEGER not null,
    CustomerID    INTEGER not null,
    ProductID     INTEGER not null,
    Quantity      INTEGER not null,
    foreign key (SalesPersonID) references Employees (EmployeeID)
            on update cascade on delete cascade,
        foreign key (CustomerID) references Customers (CustomerID)
            on update cascade on delete cascade,
        foreign key (ProductID) references Products (ProductID)
            on update cascade on delete cascade
)

### Question
List the full name of the customer who purchased the most quantity of products.

### Hint
full name of the customer = FirstName, LastName; most quantity refers to MAX(Quantity);

### SQL
SELECT T1.FirstName, T1.LastName FROM Customers AS T1 INNER JOIN Sales AS T2 ON T1.CustomerID = T2.CustomerID ORDER BY T2.Quantity DESC LIMIT 1
### Database Schema
CREATE TABLE Customers
(
    CustomerID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Employees
(
    EmployeeID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Products
(
    ProductID INTEGER            not null
        primary key,
    Name      TEXT    not null,
    Price     REAL null
)

CREATE TABLE Sales
(
    SalesID       INTEGER not null
        primary key,
    SalesPersonID INTEGER not null,
    CustomerID    INTEGER not null,
    ProductID     INTEGER not null,
    Quantity      INTEGER not null,
    foreign key (SalesPersonID) references Employees (EmployeeID)
            on update cascade on delete cascade,
        foreign key (CustomerID) references Customers (CustomerID)
            on update cascade on delete cascade,
        foreign key (ProductID) references Products (ProductID)
            on update cascade on delete cascade
)

### Question
What is the name of the product that is most sold by sale person id 20?

### Hint
most sold refers to MAX(Quantity);

### SQL
SELECT T1.Name FROM Products AS T1 INNER JOIN Sales AS T2 ON T1.ProductID = T2.ProductID WHERE T2.SalesPersonID = 20 ORDER BY T2.Quantity DESC LIMIT 1
### Database Schema
CREATE TABLE Customers
(
    CustomerID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Employees
(
    EmployeeID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Products
(
    ProductID INTEGER            not null
        primary key,
    Name      TEXT    not null,
    Price     REAL null
)

CREATE TABLE Sales
(
    SalesID       INTEGER not null
        primary key,
    SalesPersonID INTEGER not null,
    CustomerID    INTEGER not null,
    ProductID     INTEGER not null,
    Quantity      INTEGER not null,
    foreign key (SalesPersonID) references Employees (EmployeeID)
            on update cascade on delete cascade,
        foreign key (CustomerID) references Customers (CustomerID)
            on update cascade on delete cascade,
        foreign key (ProductID) references Products (ProductID)
            on update cascade on delete cascade
)

### Question
List the first names of employees with trading quantity for more than 500.

### Hint
trading quantity for more than 500 refers to Quantity > 500;

### SQL
SELECT DISTINCT T1.FirstName FROM Employees AS T1 INNER JOIN Sales AS T2 ON T1.EmployeeID = T2.SalesPersonID WHERE T2.Quantity > 500
### Database Schema
CREATE TABLE Customers
(
    CustomerID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Employees
(
    EmployeeID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Products
(
    ProductID INTEGER            not null
        primary key,
    Name      TEXT    not null,
    Price     REAL null
)

CREATE TABLE Sales
(
    SalesID       INTEGER not null
        primary key,
    SalesPersonID INTEGER not null,
    CustomerID    INTEGER not null,
    ProductID     INTEGER not null,
    Quantity      INTEGER not null,
    foreign key (SalesPersonID) references Employees (EmployeeID)
            on update cascade on delete cascade,
        foreign key (CustomerID) references Customers (CustomerID)
            on update cascade on delete cascade,
        foreign key (ProductID) references Products (ProductID)
            on update cascade on delete cascade
)

### Question
List the first names of customers who have purchased products from sale person id 1.

### SQL
SELECT T1.FirstName FROM Customers AS T1 INNER JOIN Sales AS T2 ON T1.CustomerID = T2.CustomerID WHERE T2.SalesPersonID = 1
### Database Schema
CREATE TABLE Customers
(
    CustomerID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Employees
(
    EmployeeID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Products
(
    ProductID INTEGER            not null
        primary key,
    Name      TEXT    not null,
    Price     REAL null
)

CREATE TABLE Sales
(
    SalesID       INTEGER not null
        primary key,
    SalesPersonID INTEGER not null,
    CustomerID    INTEGER not null,
    ProductID     INTEGER not null,
    Quantity      INTEGER not null,
    foreign key (SalesPersonID) references Employees (EmployeeID)
            on update cascade on delete cascade,
        foreign key (CustomerID) references Customers (CustomerID)
            on update cascade on delete cascade,
        foreign key (ProductID) references Products (ProductID)
            on update cascade on delete cascade
)

### Question
Calculate the total trading quantity of Abraham sold to Aaron Alexander.

### Hint
total trading quantity = SUM(Quantity WHERE Employees.FirstName = 'Abraham' AND Customers.FirstName = 'Aaron' AND Customers.LastName = 'Alexander');

### SQL
SELECT SUM(T2.Quantity) FROM Customers AS T1 INNER JOIN Sales AS T2 ON T1.CustomerID = T2.CustomerID INNER JOIN Employees AS T3 ON T2.SalesPersonID = T3.EmployeeID WHERE T2.SalesPersonID = 1 AND T1.FirstName = 'Aaron' AND T1.LastName = 'Alexander' AND T3.FirstName = 'Abraham'
### Database Schema
CREATE TABLE Customers
(
    CustomerID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Employees
(
    EmployeeID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Products
(
    ProductID INTEGER            not null
        primary key,
    Name      TEXT    not null,
    Price     REAL null
)

CREATE TABLE Sales
(
    SalesID       INTEGER not null
        primary key,
    SalesPersonID INTEGER not null,
    CustomerID    INTEGER not null,
    ProductID     INTEGER not null,
    Quantity      INTEGER not null,
    foreign key (SalesPersonID) references Employees (EmployeeID)
            on update cascade on delete cascade,
        foreign key (CustomerID) references Customers (CustomerID)
            on update cascade on delete cascade,
        foreign key (ProductID) references Products (ProductID)
            on update cascade on delete cascade
)

### Question
List the full names of customers who have purchased products in quantity over 600.

### Hint
full names of customers = FirstName, LastName; quantity over 600 refers to quantity > 600;

### SQL
SELECT T1.FirstName, T1.LastName FROM Customers AS T1 INNER JOIN Sales AS T2 ON T1.CustomerID = T2.CustomerID WHERE T2.Quantity > 600
### Database Schema
CREATE TABLE Customers
(
    CustomerID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Employees
(
    EmployeeID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Products
(
    ProductID INTEGER            not null
        primary key,
    Name      TEXT    not null,
    Price     REAL null
)

CREATE TABLE Sales
(
    SalesID       INTEGER not null
        primary key,
    SalesPersonID INTEGER not null,
    CustomerID    INTEGER not null,
    ProductID     INTEGER not null,
    Quantity      INTEGER not null,
    foreign key (SalesPersonID) references Employees (EmployeeID)
            on update cascade on delete cascade,
        foreign key (CustomerID) references Customers (CustomerID)
            on update cascade on delete cascade,
        foreign key (ProductID) references Products (ProductID)
            on update cascade on delete cascade
)

### Question
Among the customers whose first name is Cameron, who bought the product in the most quantity?

### Hint
most quantity refers to MAX(Quantity); who refers to FirstName, LastName;

### SQL
SELECT T1.FirstName, T1.LastName FROM Customers AS T1 INNER JOIN Sales AS T2 ON T1.CustomerID = T2.CustomerID WHERE T1.FirstName = 'Cameron' ORDER BY T2.Quantity DESC LIMIT 1
### Database Schema
CREATE TABLE Customers
(
    CustomerID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Employees
(
    EmployeeID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Products
(
    ProductID INTEGER            not null
        primary key,
    Name      TEXT    not null,
    Price     REAL null
)

CREATE TABLE Sales
(
    SalesID       INTEGER not null
        primary key,
    SalesPersonID INTEGER not null,
    CustomerID    INTEGER not null,
    ProductID     INTEGER not null,
    Quantity      INTEGER not null,
    foreign key (SalesPersonID) references Employees (EmployeeID)
            on update cascade on delete cascade,
        foreign key (CustomerID) references Customers (CustomerID)
            on update cascade on delete cascade,
        foreign key (ProductID) references Products (ProductID)
            on update cascade on delete cascade
)

### Question
Please provide sales ID for products named Hex Nut with a price greater than 100.

### Hint
price greater than 100 refers to price > 100;

### SQL
SELECT T2.SalesID FROM Products AS T1 INNER JOIN Sales AS T2 ON T1.ProductID = T2.ProductID WHERE T1.Name LIKE 'Hex Nut%' AND T1.Price > 100
### Database Schema
CREATE TABLE Customers
(
    CustomerID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Employees
(
    EmployeeID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Products
(
    ProductID INTEGER            not null
        primary key,
    Name      TEXT    not null,
    Price     REAL null
)

CREATE TABLE Sales
(
    SalesID       INTEGER not null
        primary key,
    SalesPersonID INTEGER not null,
    CustomerID    INTEGER not null,
    ProductID     INTEGER not null,
    Quantity      INTEGER not null,
    foreign key (SalesPersonID) references Employees (EmployeeID)
            on update cascade on delete cascade,
        foreign key (CustomerID) references Customers (CustomerID)
            on update cascade on delete cascade,
        foreign key (ProductID) references Products (ProductID)
            on update cascade on delete cascade
)

### Question
Identify customer IDs who bought products priced from 1000 to 2000.

### Hint
priced from 1000 to 2000 refers to Price BETWEEN 1000 AND 2000;

### SQL
SELECT DISTINCT T2.CustomerID FROM Products AS T1 INNER JOIN Sales AS T2 ON T1.ProductID = T2.ProductID WHERE T1.Price BETWEEN 1000 AND 2000
### Database Schema
CREATE TABLE Customers
(
    CustomerID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Employees
(
    EmployeeID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Products
(
    ProductID INTEGER            not null
        primary key,
    Name      TEXT    not null,
    Price     REAL null
)

CREATE TABLE Sales
(
    SalesID       INTEGER not null
        primary key,
    SalesPersonID INTEGER not null,
    CustomerID    INTEGER not null,
    ProductID     INTEGER not null,
    Quantity      INTEGER not null,
    foreign key (SalesPersonID) references Employees (EmployeeID)
            on update cascade on delete cascade,
        foreign key (CustomerID) references Customers (CustomerID)
            on update cascade on delete cascade,
        foreign key (ProductID) references Products (ProductID)
            on update cascade on delete cascade
)

### Question
Calculate the total quantity of products that are gifts.

### Hint
total quantity = SUM(Quantity); gifts refers to Price = 0;

### SQL
SELECT SUM(T2.Quantity) FROM Products AS T1 INNER JOIN Sales AS T2 ON T1.ProductID = T2.ProductID WHERE T1.Price = 0
### Database Schema
CREATE TABLE Customers
(
    CustomerID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Employees
(
    EmployeeID    INTEGER         not null
        primary key,
    FirstName     TEXT not null,
    MiddleInitial TEXT null,
    LastName      TEXT not null
)

CREATE TABLE Products
(
    ProductID INTEGER            not null
        primary key,
    Name      TEXT    not null,
    Price     REAL null
)

CREATE TABLE Sales
(
    SalesID       INTEGER not null
        primary key,
    SalesPersonID INTEGER not null,
    CustomerID    INTEGER not null,
    ProductID     INTEGER not null,
    Quantity      INTEGER not null,
    foreign key (SalesPersonID) references Employees (EmployeeID)
            on update cascade on delete cascade,
        foreign key (CustomerID) references Customers (CustomerID)
            on update cascade on delete cascade,
        foreign key (ProductID) references Products (ProductID)
            on update cascade on delete cascade
)

### Question
Calculate the quantity percentage of the gift products in the total trading quantity.

### Hint
percentage = MULTIPLY(DIVIDE(SUM(Quantity WHERE Price = 0), SUM(Quantity)), 1.0); gift products refers to Price = 0;

### SQL
SELECT CAST(SUM(IIF(T1.Price = 0, T2.Quantity, 0)) AS REAL) * 100 / SUM(T2.Quantity)FROM Products AS T1 INNER JOIN Sales AS T2 ON T1.ProductID = T2.ProductID