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
Write down the name of products whose sale quantity is more than 950.

### Hint
quantity is more than 950 refers to Quantity > 950;

### SQL
SELECT DISTINCT T1.Name FROM Products AS T1 INNER JOIN Sales AS T2 ON T1.ProductID = T2.ProductID WHERE T2.Quantity > 950
### 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 full name of employee who sold 1000 units?

### Hint
full name of employee = FirstName, MiddleInitial, LastName; units refers to quantity; Quantity = 100

### SQL
SELECT DISTINCT T2.FirstName, T2.MiddleInitial, T2.LastName FROM Sales AS T1 INNER JOIN Employees AS T2 ON T1.SalesPersonID = T2.EmployeeID WHERE T1.Quantity = 1000
### 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
Tally the product name and quantity of the first ten sales.

### Hint
first ten sales refers to SalesID BETWEEN 1 AND 10;

### SQL
SELECT T3.Name, T2.Quantity FROM Customers AS T1 INNER JOIN Sales AS T2 ON T1.CustomerID = T2.CustomerID INNER JOIN Products AS T3 ON T2.ProductID = T3.ProductID WHERE T2.SalesID BETWEEN 1 AND 10
### 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 total sales amount for Reflector?

### Hint
total sales amount = SUM(MULTIPLY(Price, Quantity)); 'Reflector' is name of product;

### SQL
SELECT SUM(T1.Price * T2.quantity) FROM Products AS T1 INNER JOIN Sales AS T2 ON T1.ProductID = T2.ProductID WHERE T1.Name = 'Reflector'
### 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 best selling colour for HL Mountain Frame, 42?

### Hint
best selling colour refers to name of product with higher total sales; SUM(SalesID WHERE Name = 'HL Mountain Frame - Silver, 42') > SUM(Name = 'HL Mountain Frame - Black, 42') means Silver is the best selling colour, otherwise Black is the best seling colour;

### SQL
SELECT IIF(SUM(IIF(T1.Name = 'HL Mountain Frame - Silver, 42', T2.SalesID, 0)) - SUM(IIF(T1.Name = 'HL Mountain Frame - Black, 42', T2.SalesID, 0)) > 0, 'Silver', 'Black') FROM Products AS T1 INNER JOIN Sales AS T2 ON T1.ProductID = T2.ProductID
### 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 difference in price between HL Mountain Frame - Black, 42 and LL Mountain Frame - Black, 42?

### Hint
difference = SUBTRACT((Price WHERE Name = 'HL Mountain Frame - Black, 42'), (Price WHERE Name = 'HL Mountain Frame - Black, 42'));

### SQL
SELECT ( SELECT Price FROM Products WHERE Name = 'HL Mountain Frame - Black, 42' ) - ( SELECT Price FROM Products WHERE Name = 'LL Mountain Frame - Black, 42' ) AS num
### 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 number of sales closed by Michel E. DeFrance?

### SQL
SELECT COUNT(T1.SalesID) FROM Sales AS T1 INNER JOIN Employees AS T2 ON T1.SalesPersonID = T2.EmployeeID WHERE T2.FirstName = 'Michel' AND T2.MiddleInitial = 'e' AND T2.LastName = 'DeFrance'
### 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 average number of customers per sales person?

### Hint
average = DIVIDE(COUNT(CustomerID), COUNT(EmployeeID));

### SQL
SELECT CAST(COUNT(T1.CustomerID) AS REAL) / COUNT(T3.EmployeeID) FROM Customers AS T1 INNER JOIN Sales AS T2 ON T1.CustomerID = T2.CustomerID INNER JOIN Employees AS T3 ON T2.SalesPersonID = T3.EmployeeID
### 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 customers handled by Innes E. del Castillo, how many have purchased Short-Sleeve Classic Jersey, L?

### Hint
Short-Sleeve Classic Jersey, L' is name of product;

### SQL
SELECT 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 T3.FirstName = 'Innes' AND T3.LastName = 'del Castillo' AND T1.Name = 'Short-Sleeve Classic Jersey, L' AND T3.MiddleInitial = 'e'
### 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 sales person who helped Elizabeth A. White to purchase Road-250 Black, 48.

### Hint
name of the sales person = FirstName, MiddleInitial, LastName; 'Road-250 Black, 48' is name of product;

### SQL
SELECT DISTINCT T3.FirstName, T3.MiddleInitial, T3.LastName FROM Products AS T1 INNER JOIN Sales AS T2 ON T1.ProductID = T2.ProductID INNER JOIN Employees AS T3 ON T2.SalesPersonID = T3.EmployeeID INNER JOIN Customers AS T4 ON T2.CustomerID = T4.CustomerID WHERE T4.MiddleInitial = 'A' AND T4.LastName = 'White' AND T1.Name = 'Road-250 Black, 48' AND T4.FirstName = 'Elizabeth'
### 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 sales people managed to sell Headlights - Weatherproof?

### Hint
Headlights - Weatherproof' is name of product

### SQL
SELECT COUNT(T2.SalesPersonID) FROM Products AS T1 INNER JOIN Sales AS T2 ON T1.ProductID = T2.ProductID WHERE T1.Name = 'Headlights - Weatherproof'
### 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 revenue produced through sales of HL Road Frame - Red, 56.

### Hint
revenue = MULTIPLY(SUM(Quantity, Price)); 'HL Road Frame - Red, 56' is name of product;

### SQL
SELECT SUM(T2.Quantity * T1.Price) FROM Products AS T1 INNER JOIN Sales AS T2 ON T1.ProductID = T2.ProductID WHERE T1.Name = 'HL Road Frame - Red, 56'
### 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 sales transactions were given by the customer named Joe L. Lopez?

### Hint
sales transactions refers to SalesID;

### SQL
SELECT COUNT(T1.SalesID) FROM Sales AS T1 INNER JOIN Customers AS T2 ON T1.CustomerID = T2.CustomerID WHERE T2.FirstName = 'Joe' AND T2.MiddleInitial = 'L' AND T2.LastName = 'Lopez'
### 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 customers who received 'Touring Rim' as a free gift.

### Hint
name of the customer = FirstName, MiddleInitial, LastName; 'Touring Rim' is name of product;

### SQL
SELECT DISTINCT T1.FirstName, T1.MiddleInitial, T1.LastName FROM Customers AS T1 INNER JOIN Sales AS T2 ON T1.CustomerID = T2.CustomerID INNER JOIN Products AS T3 ON T2.ProductID = T3.ProductID WHERE T3.Name = 'Touring Rim' AND T3.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
Find the number of customers handled by each of the sales people.

### SQL
SELECT COUNT(CustomerID) FROM Sales GROUP BY SalesPersonID
### 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 sales people are handling all the customers?

### SQL
SELECT COUNT(EmployeeID) FROM Employees