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
List down product ids that were purchased by customers called Abby.

### SQL
SELECT DISTINCT T1.ProductID FROM Sales AS T1 INNER JOIN Customers AS T2 ON T1.CustomerID = T2.CustomerID WHERE T2.FirstName = 'Abby'
### 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 all of the product ids that were placed by Meander.

### SQL
SELECT DISTINCT T2.ProductID FROM Employees AS T1 INNER JOIN Sales AS T2 ON T1.EmployeeID = T2.SalesPersonID WHERE T1.FirstName = 'Meander'
### 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 last name of sales person for sales id 100?

### SQL
SELECT T1.LastName FROM Employees AS T1 INNER JOIN Sales AS T2 ON T1.EmployeeID = T2.SalesPersonID WHERE T2.SalesID = 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
What is the first name of employee who handled sales for customer called Abigail?

### SQL
SELECT DISTINCT T3.FirstName 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 T1.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
How many free gifts have customer with id 11782 received?

### Hint
free gifts refers to Price = 0;

### SQL
SELECT COUNT(T1.ProductID) FROM Products AS T1 INNER JOIN Sales AS T2 ON T1.ProductID = T2.ProductID WHERE T2.CustomerID = 11782 AND 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
What is the full name of customers who dealt with sales person with id 5?

### Hint
full name = FirstName, MiddleInitial, LastName;

### SQL
SELECT T1.FirstName, T1.MiddleInitial, T1.LastName FROM Customers AS T1 INNER JOIN Sales AS T2 ON T1.CustomerID = T2.CustomerID WHERE T2.SalesPersonID = 5
### 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 down all of the sales IDs for sales handled by sales people with first name starting with alphabet "s".

### Hint
first name starting with alphabet "s" refers to FirstName LIKE 's%';

### SQL
SELECT T1.SalesID FROM Sales AS T1 INNER JOIN Employees AS T2 ON T1.SalesPersonID = T2.EmployeeID WHERE SUBSTR(T2.FirstName, 1, 1) = 's'
### 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 customers with IDs from 1 to 100, what is the highest price of products they purchased?

### Hint
IDs from 1 to 100 refers to CustomerID BETWEEN 1 AND 100 ; highest price refers to MAX(Price);

### SQL
SELECT T1.Price FROM Products AS T1 INNER JOIN Sales AS T2 ON T1.ProductID = T2.ProductID WHERE T2.CustomerID BETWEEN 1 AND 100 ORDER BY T1.Price 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
Among customers with the last name of Valdez, who purchased the highest quantity?

### Hint
highest quantity refers to MAX(Quantity);

### SQL
SELECT T1.FirstName FROM Customers AS T1 INNER JOIN Sales AS T2 ON T1.CustomerID = T2.CustomerID WHERE T1.LastName = 'Valdez' 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
Sum up the number sales ids handled by employees called Morningstar, Heather and Dean.

### Hint
SUM = ADD(SUM(SalesID WHERE FirstName = 'Morningstar'), SUM(SalesID WHERE FirstName = 'Heather'), SUM(SalesID WHERE FirstName = 'Dean'));

### SQL
SELECT SUM(IIF(T2.FirstName = 'Morningstar', 1, 0)) + SUM(IIF(T2.FirstName = 'Heather', 1, 0)) + SUM(IIF(T2.FirstName = 'Dean', 1, 0)) AS num FROM Sales AS T1 INNER JOIN Employees AS T2 ON T1.SalesPersonID = T2.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
Has Alex purchased product with id 498?

### Hint
WHEN ProductID = 498 AND FirstName = 'Alex' means that Alex purchased a product; WHEN ProductID = 498 AND FirstName NOT 'Alex' means Alex has not purchased a product;

### SQL
SELECT IIF(T1.ProductID = 498, 'YES', 'NO') FROM Sales AS T1 INNER JOIN Customers AS T2 ON T1.CustomerID = T2.CustomerID WHERE T2.FirstName = 'Alex'
### 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 price of products purchased by Adam.

### Hint
total price = SUM(MULTIPLY(Price, Quantity));

### SQL
SELECT SUM(T3.Price * 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 T1.FirstName = 'Adam'
### 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 price for products from id 400 to 500.

### Hint
total price = SUM(MULTIPLY(Price, Quantity)); from id 400 to 500 refers to ProductID BETWEEN 400 AND 500;

### SQL
SELECT SUM(T1.Price * T2.quantity) FROM Products AS T1 INNER JOIN Sales AS T2 ON T1.ProductID = T2.ProductID WHERE T1.ProductID BETWEEN 400 AND 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
Calculate the total quantity of products with name starting with alphabet "c".

### Hint
name starting with alphabet "c" refers to Name LIKE 'C%';

### SQL
SELECT SUM(T2.Quantity) FROM Products AS T1 INNER JOIN Sales AS T2 ON T1.ProductID = T2.ProductID WHERE SUBSTR(T1.Name, 1, 1) = 'C'
### 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 purchased by customer called Adrian.

### SQL
SELECT SUM(T2.Quantity) FROM Customers AS T1 INNER JOIN Sales AS T2 ON T1.CustomerID = T2.CustomerID WHERE T1.FirstName = 'Adam'
### 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 product ID of the top five products, by descending order, in terms of price.

### Hint
top 5 products in terms of Price refers to MAX(Price) LIMIT 5;

### SQL
SELECT ProductID FROM Products ORDER BY Price DESC LIMIT 5
### 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 products, how many of them are freebies?

### Hint
freebies refers to Price = 0;

### SQL
SELECT COUNT(ProductID) FROM Products WHERE Price = 0