lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE Customers
(
    CustomerID       INTEGER
        constraint Customers_pk
            primary key,
    "Customer Names" TEXT
)

CREATE TABLE Products
(
    ProductID      INTEGER
        constraint Products_pk
            primary key,
    "Product Name" TEXT
)

CREATE TABLE Regions
(
    StateCode TEXT
        constraint Regions_pk
            primary key,
    State     TEXT,
    Region    TEXT
)

CREATE TABLE "Sales Team"
(
    SalesTeamID  INTEGER
        constraint "Sales Team_pk"
            primary key,
    "Sales Team" TEXT,
    Region       TEXT
)

CREATE TABLE "Store Locations"
(
    StoreID            INTEGER
        constraint "Store Locations_pk"
            primary key,
    "City Name"        TEXT,
    County             TEXT,
    StateCode          TEXT
        constraint "Store Locations_Regions_StateCode_fk"
            references Regions(StateCode),
    State              TEXT,
    Type               TEXT,
    Latitude           REAL,
    Longitude          REAL,
    AreaCode           INTEGER,
    Population         INTEGER,
    "Household Income" INTEGER,
    "Median Income"    INTEGER,
    "Land Area"        INTEGER,
    "Water Area"       INTEGER,
    "Time Zone"        TEXT
)

CREATE TABLE "Sales Orders"
(
    OrderNumber        TEXT
        constraint "Sales Orders_pk"
            primary key,
    "Sales Channel"    TEXT,
    WarehouseCode      TEXT,
    ProcuredDate       TEXT,
    OrderDate          TEXT,
    ShipDate           TEXT,
    DeliveryDate       TEXT,
    CurrencyCode       TEXT,
    _SalesTeamID       INTEGER
        constraint "Sales Orders_Sales Team_SalesTeamID_fk"
            references "Sales Team"(SalesTeamID),
    _CustomerID        INTEGER
        constraint "Sales Orders_Customers_CustomerID_fk"
            references Customers(CustomerID),
    _StoreID           INTEGER
        constraint "Sales Orders_Store Locations_StoreID_fk"
            references "Store Locations"(StoreID),
    _ProductID         INTEGER
        constraint "Sales Orders_Products_ProductID_fk"
            references Products(ProductID),
    "Order Quantity"   INTEGER,
    "Discount Applied" REAL,
    "Unit Price"       TEXT,
    "Unit Cost"        TEXT
)

### Question
Which Apollo Ltd customer's order number has the most expensive unit price, indicating the order date?

### Hint
"Apollo Ltd" is the Customer Names; most expensive unit price refers to max(Unit Price)

### SQL
SELECT T1.OrderNumber, T1.OrderDate FROM `Sales Orders` AS T1 INNER JOIN Customers AS T2 ON T2.CustomerID = T1._CustomerID WHERE T2.`Customer Names` = 'Apollo Ltd' ORDER BY T1.`Unit Price` DESC LIMIT 1
### Database Schema
CREATE TABLE Customers
(
    CustomerID       INTEGER
        constraint Customers_pk
            primary key,
    "Customer Names" TEXT
)

CREATE TABLE Products
(
    ProductID      INTEGER
        constraint Products_pk
            primary key,
    "Product Name" TEXT
)

CREATE TABLE Regions
(
    StateCode TEXT
        constraint Regions_pk
            primary key,
    State     TEXT,
    Region    TEXT
)

CREATE TABLE "Sales Team"
(
    SalesTeamID  INTEGER
        constraint "Sales Team_pk"
            primary key,
    "Sales Team" TEXT,
    Region       TEXT
)

CREATE TABLE "Store Locations"
(
    StoreID            INTEGER
        constraint "Store Locations_pk"
            primary key,
    "City Name"        TEXT,
    County             TEXT,
    StateCode          TEXT
        constraint "Store Locations_Regions_StateCode_fk"
            references Regions(StateCode),
    State              TEXT,
    Type               TEXT,
    Latitude           REAL,
    Longitude          REAL,
    AreaCode           INTEGER,
    Population         INTEGER,
    "Household Income" INTEGER,
    "Median Income"    INTEGER,
    "Land Area"        INTEGER,
    "Water Area"       INTEGER,
    "Time Zone"        TEXT
)

CREATE TABLE "Sales Orders"
(
    OrderNumber        TEXT
        constraint "Sales Orders_pk"
            primary key,
    "Sales Channel"    TEXT,
    WarehouseCode      TEXT,
    ProcuredDate       TEXT,
    OrderDate          TEXT,
    ShipDate           TEXT,
    DeliveryDate       TEXT,
    CurrencyCode       TEXT,
    _SalesTeamID       INTEGER
        constraint "Sales Orders_Sales Team_SalesTeamID_fk"
            references "Sales Team"(SalesTeamID),
    _CustomerID        INTEGER
        constraint "Sales Orders_Customers_CustomerID_fk"
            references Customers(CustomerID),
    _StoreID           INTEGER
        constraint "Sales Orders_Store Locations_StoreID_fk"
            references "Store Locations"(StoreID),
    _ProductID         INTEGER
        constraint "Sales Orders_Products_ProductID_fk"
            references Products(ProductID),
    "Order Quantity"   INTEGER,
    "Discount Applied" REAL,
    "Unit Price"       TEXT,
    "Unit Cost"        TEXT
)

### Question
Provide order number, warehouse code of customers Elorac, Corp.

### Hint
"Elorac, Corp" is the Customer Names

### SQL
SELECT DISTINCT T1.OrderNumber, T1.WarehouseCode FROM `Sales Orders` AS T1 INNER JOIN Customers AS T2 ON T2.CustomerID = T1._CustomerID WHERE T2.`Customer Names` = 'Elorac, Corp'
### Database Schema
CREATE TABLE Customers
(
    CustomerID       INTEGER
        constraint Customers_pk
            primary key,
    "Customer Names" TEXT
)

CREATE TABLE Products
(
    ProductID      INTEGER
        constraint Products_pk
            primary key,
    "Product Name" TEXT
)

CREATE TABLE Regions
(
    StateCode TEXT
        constraint Regions_pk
            primary key,
    State     TEXT,
    Region    TEXT
)

CREATE TABLE "Sales Team"
(
    SalesTeamID  INTEGER
        constraint "Sales Team_pk"
            primary key,
    "Sales Team" TEXT,
    Region       TEXT
)

CREATE TABLE "Store Locations"
(
    StoreID            INTEGER
        constraint "Store Locations_pk"
            primary key,
    "City Name"        TEXT,
    County             TEXT,
    StateCode          TEXT
        constraint "Store Locations_Regions_StateCode_fk"
            references Regions(StateCode),
    State              TEXT,
    Type               TEXT,
    Latitude           REAL,
    Longitude          REAL,
    AreaCode           INTEGER,
    Population         INTEGER,
    "Household Income" INTEGER,
    "Median Income"    INTEGER,
    "Land Area"        INTEGER,
    "Water Area"       INTEGER,
    "Time Zone"        TEXT
)

CREATE TABLE "Sales Orders"
(
    OrderNumber        TEXT
        constraint "Sales Orders_pk"
            primary key,
    "Sales Channel"    TEXT,
    WarehouseCode      TEXT,
    ProcuredDate       TEXT,
    OrderDate          TEXT,
    ShipDate           TEXT,
    DeliveryDate       TEXT,
    CurrencyCode       TEXT,
    _SalesTeamID       INTEGER
        constraint "Sales Orders_Sales Team_SalesTeamID_fk"
            references "Sales Team"(SalesTeamID),
    _CustomerID        INTEGER
        constraint "Sales Orders_Customers_CustomerID_fk"
            references Customers(CustomerID),
    _StoreID           INTEGER
        constraint "Sales Orders_Store Locations_StoreID_fk"
            references "Store Locations"(StoreID),
    _ProductID         INTEGER
        constraint "Sales Orders_Products_ProductID_fk"
            references Products(ProductID),
    "Order Quantity"   INTEGER,
    "Discount Applied" REAL,
    "Unit Price"       TEXT,
    "Unit Cost"        TEXT
)

### Question
Name of customers who have ordered Cocktail Glasses by online sales channel.

### Hint
"Cocktail Glasses" is the Product Name; customer refers to Customer Names

### SQL
SELECT T FROM ( SELECT DISTINCT CASE  WHEN T3.`Product Name` = 'Cocktail Glasses' AND T2.`Sales Channel` = 'Online' THEN T1.`Customer Names` END AS T FROM Customers T1 INNER JOIN `Sales Orders` T2 ON T2._CustomerID = T1.CustomerID INNER JOIN Products T3 ON T3.ProductID = T2._ProductID ) WHERE T IS NOT NULL
### Database Schema
CREATE TABLE Customers
(
    CustomerID       INTEGER
        constraint Customers_pk
            primary key,
    "Customer Names" TEXT
)

CREATE TABLE Products
(
    ProductID      INTEGER
        constraint Products_pk
            primary key,
    "Product Name" TEXT
)

CREATE TABLE Regions
(
    StateCode TEXT
        constraint Regions_pk
            primary key,
    State     TEXT,
    Region    TEXT
)

CREATE TABLE "Sales Team"
(
    SalesTeamID  INTEGER
        constraint "Sales Team_pk"
            primary key,
    "Sales Team" TEXT,
    Region       TEXT
)

CREATE TABLE "Store Locations"
(
    StoreID            INTEGER
        constraint "Store Locations_pk"
            primary key,
    "City Name"        TEXT,
    County             TEXT,
    StateCode          TEXT
        constraint "Store Locations_Regions_StateCode_fk"
            references Regions(StateCode),
    State              TEXT,
    Type               TEXT,
    Latitude           REAL,
    Longitude          REAL,
    AreaCode           INTEGER,
    Population         INTEGER,
    "Household Income" INTEGER,
    "Median Income"    INTEGER,
    "Land Area"        INTEGER,
    "Water Area"       INTEGER,
    "Time Zone"        TEXT
)

CREATE TABLE "Sales Orders"
(
    OrderNumber        TEXT
        constraint "Sales Orders_pk"
            primary key,
    "Sales Channel"    TEXT,
    WarehouseCode      TEXT,
    ProcuredDate       TEXT,
    OrderDate          TEXT,
    ShipDate           TEXT,
    DeliveryDate       TEXT,
    CurrencyCode       TEXT,
    _SalesTeamID       INTEGER
        constraint "Sales Orders_Sales Team_SalesTeamID_fk"
            references "Sales Team"(SalesTeamID),
    _CustomerID        INTEGER
        constraint "Sales Orders_Customers_CustomerID_fk"
            references Customers(CustomerID),
    _StoreID           INTEGER
        constraint "Sales Orders_Store Locations_StoreID_fk"
            references "Store Locations"(StoreID),
    _ProductID         INTEGER
        constraint "Sales Orders_Products_ProductID_fk"
            references Products(ProductID),
    "Order Quantity"   INTEGER,
    "Discount Applied" REAL,
    "Unit Price"       TEXT,
    "Unit Cost"        TEXT
)

### Question
Which store in Arizona has the most net profit?

### Hint
"Arizona" is the name of State; most net profit = Max(Subtract( Unit Price, Unit Cost))

### SQL
SELECT T2.StoreID FROM `Sales Orders` AS T1 INNER JOIN `Store Locations` AS T2 ON T2.StoreID = T1._StoreID WHERE T2.State = 'Arizona' ORDER BY T1.`Unit Price` - T1.`Unit Cost` DESC LIMIT 1
### Database Schema
CREATE TABLE Customers
(
    CustomerID       INTEGER
        constraint Customers_pk
            primary key,
    "Customer Names" TEXT
)

CREATE TABLE Products
(
    ProductID      INTEGER
        constraint Products_pk
            primary key,
    "Product Name" TEXT
)

CREATE TABLE Regions
(
    StateCode TEXT
        constraint Regions_pk
            primary key,
    State     TEXT,
    Region    TEXT
)

CREATE TABLE "Sales Team"
(
    SalesTeamID  INTEGER
        constraint "Sales Team_pk"
            primary key,
    "Sales Team" TEXT,
    Region       TEXT
)

CREATE TABLE "Store Locations"
(
    StoreID            INTEGER
        constraint "Store Locations_pk"
            primary key,
    "City Name"        TEXT,
    County             TEXT,
    StateCode          TEXT
        constraint "Store Locations_Regions_StateCode_fk"
            references Regions(StateCode),
    State              TEXT,
    Type               TEXT,
    Latitude           REAL,
    Longitude          REAL,
    AreaCode           INTEGER,
    Population         INTEGER,
    "Household Income" INTEGER,
    "Median Income"    INTEGER,
    "Land Area"        INTEGER,
    "Water Area"       INTEGER,
    "Time Zone"        TEXT
)

CREATE TABLE "Sales Orders"
(
    OrderNumber        TEXT
        constraint "Sales Orders_pk"
            primary key,
    "Sales Channel"    TEXT,
    WarehouseCode      TEXT,
    ProcuredDate       TEXT,
    OrderDate          TEXT,
    ShipDate           TEXT,
    DeliveryDate       TEXT,
    CurrencyCode       TEXT,
    _SalesTeamID       INTEGER
        constraint "Sales Orders_Sales Team_SalesTeamID_fk"
            references "Sales Team"(SalesTeamID),
    _CustomerID        INTEGER
        constraint "Sales Orders_Customers_CustomerID_fk"
            references Customers(CustomerID),
    _StoreID           INTEGER
        constraint "Sales Orders_Store Locations_StoreID_fk"
            references "Store Locations"(StoreID),
    _ProductID         INTEGER
        constraint "Sales Orders_Products_ProductID_fk"
            references Products(ProductID),
    "Order Quantity"   INTEGER,
    "Discount Applied" REAL,
    "Unit Price"       TEXT,
    "Unit Cost"        TEXT
)

### Question
How much more is the Florida store's computer product unit price than the Texas store?

### Hint
"Florida" and "Texas" are both the name of State; Computer product refers to Product Name = 'Computers; difference in unit price = Subtract (Unit Price where State = 'Florida', Unit Price where State = 'Texas')

### SQL
SELECT SUM(CASE WHEN T3.State = 'Florida' THEN T2.`Unit Price` ELSE 0 END) - SUM(CASE WHEN T3.State = 'Texas' THEN T2.`Unit Price` ELSE 0 END) FROM Products AS T1 INNER JOIN `Sales Orders` AS T2 ON T2._ProductID = T1.ProductID INNER JOIN `Store Locations` AS T3 ON T3.StoreID = T2._StoreID WHERE T1.`Product Name` = 'Computers'
### Database Schema
CREATE TABLE Customers
(
    CustomerID       INTEGER
        constraint Customers_pk
            primary key,
    "Customer Names" TEXT
)

CREATE TABLE Products
(
    ProductID      INTEGER
        constraint Products_pk
            primary key,
    "Product Name" TEXT
)

CREATE TABLE Regions
(
    StateCode TEXT
        constraint Regions_pk
            primary key,
    State     TEXT,
    Region    TEXT
)

CREATE TABLE "Sales Team"
(
    SalesTeamID  INTEGER
        constraint "Sales Team_pk"
            primary key,
    "Sales Team" TEXT,
    Region       TEXT
)

CREATE TABLE "Store Locations"
(
    StoreID            INTEGER
        constraint "Store Locations_pk"
            primary key,
    "City Name"        TEXT,
    County             TEXT,
    StateCode          TEXT
        constraint "Store Locations_Regions_StateCode_fk"
            references Regions(StateCode),
    State              TEXT,
    Type               TEXT,
    Latitude           REAL,
    Longitude          REAL,
    AreaCode           INTEGER,
    Population         INTEGER,
    "Household Income" INTEGER,
    "Median Income"    INTEGER,
    "Land Area"        INTEGER,
    "Water Area"       INTEGER,
    "Time Zone"        TEXT
)

CREATE TABLE "Sales Orders"
(
    OrderNumber        TEXT
        constraint "Sales Orders_pk"
            primary key,
    "Sales Channel"    TEXT,
    WarehouseCode      TEXT,
    ProcuredDate       TEXT,
    OrderDate          TEXT,
    ShipDate           TEXT,
    DeliveryDate       TEXT,
    CurrencyCode       TEXT,
    _SalesTeamID       INTEGER
        constraint "Sales Orders_Sales Team_SalesTeamID_fk"
            references "Sales Team"(SalesTeamID),
    _CustomerID        INTEGER
        constraint "Sales Orders_Customers_CustomerID_fk"
            references Customers(CustomerID),
    _StoreID           INTEGER
        constraint "Sales Orders_Store Locations_StoreID_fk"
            references "Store Locations"(StoreID),
    _ProductID         INTEGER
        constraint "Sales Orders_Products_ProductID_fk"
            references Products(ProductID),
    "Order Quantity"   INTEGER,
    "Discount Applied" REAL,
    "Unit Price"       TEXT,
    "Unit Cost"        TEXT
)

### Question
Among sales teams in Midwest region, which sales team has an order quantity greater than 5?

### Hint
order quantity greater than 5 refers to Order Quantity > 5

### SQL
SELECT DISTINCT T2.`Sales Team` FROM `Sales Orders` AS T1 INNER JOIN `Sales Team` AS T2 ON T2.SalesTeamID = T1._SalesTeamID WHERE T2.Region = 'Midwest' AND T1.`Order Quantity` > 5
### Database Schema
CREATE TABLE Customers
(
    CustomerID       INTEGER
        constraint Customers_pk
            primary key,
    "Customer Names" TEXT
)

CREATE TABLE Products
(
    ProductID      INTEGER
        constraint Products_pk
            primary key,
    "Product Name" TEXT
)

CREATE TABLE Regions
(
    StateCode TEXT
        constraint Regions_pk
            primary key,
    State     TEXT,
    Region    TEXT
)

CREATE TABLE "Sales Team"
(
    SalesTeamID  INTEGER
        constraint "Sales Team_pk"
            primary key,
    "Sales Team" TEXT,
    Region       TEXT
)

CREATE TABLE "Store Locations"
(
    StoreID            INTEGER
        constraint "Store Locations_pk"
            primary key,
    "City Name"        TEXT,
    County             TEXT,
    StateCode          TEXT
        constraint "Store Locations_Regions_StateCode_fk"
            references Regions(StateCode),
    State              TEXT,
    Type               TEXT,
    Latitude           REAL,
    Longitude          REAL,
    AreaCode           INTEGER,
    Population         INTEGER,
    "Household Income" INTEGER,
    "Median Income"    INTEGER,
    "Land Area"        INTEGER,
    "Water Area"       INTEGER,
    "Time Zone"        TEXT
)

CREATE TABLE "Sales Orders"
(
    OrderNumber        TEXT
        constraint "Sales Orders_pk"
            primary key,
    "Sales Channel"    TEXT,
    WarehouseCode      TEXT,
    ProcuredDate       TEXT,
    OrderDate          TEXT,
    ShipDate           TEXT,
    DeliveryDate       TEXT,
    CurrencyCode       TEXT,
    _SalesTeamID       INTEGER
        constraint "Sales Orders_Sales Team_SalesTeamID_fk"
            references "Sales Team"(SalesTeamID),
    _CustomerID        INTEGER
        constraint "Sales Orders_Customers_CustomerID_fk"
            references Customers(CustomerID),
    _StoreID           INTEGER
        constraint "Sales Orders_Store Locations_StoreID_fk"
            references "Store Locations"(StoreID),
    _ProductID         INTEGER
        constraint "Sales Orders_Products_ProductID_fk"
            references Products(ProductID),
    "Order Quantity"   INTEGER,
    "Discount Applied" REAL,
    "Unit Price"       TEXT,
    "Unit Cost"        TEXT
)

### Question
Please indicate store id in the state of California that have been applied 20% discount in store.

### Hint
"California" is the name of State; in store refers to Sales Channel = 'In-Store'; 20% discount refers to Discount Applied = '0.2'

### SQL
SELECT T FROM ( SELECT DISTINCT CASE  WHEN T2.State = 'California' AND T1.`Sales Channel` = 'In-Store' AND T1.`Discount Applied` = 0.2 THEN T2.StoreID END AS T FROM `Sales Orders` T1 INNER JOIN `Store Locations` T2 ON T2.StoreID = T1._StoreID ) WHERE T IS NOT NULL
### Database Schema
CREATE TABLE Customers
(
    CustomerID       INTEGER
        constraint Customers_pk
            primary key,
    "Customer Names" TEXT
)

CREATE TABLE Products
(
    ProductID      INTEGER
        constraint Products_pk
            primary key,
    "Product Name" TEXT
)

CREATE TABLE Regions
(
    StateCode TEXT
        constraint Regions_pk
            primary key,
    State     TEXT,
    Region    TEXT
)

CREATE TABLE "Sales Team"
(
    SalesTeamID  INTEGER
        constraint "Sales Team_pk"
            primary key,
    "Sales Team" TEXT,
    Region       TEXT
)

CREATE TABLE "Store Locations"
(
    StoreID            INTEGER
        constraint "Store Locations_pk"
            primary key,
    "City Name"        TEXT,
    County             TEXT,
    StateCode          TEXT
        constraint "Store Locations_Regions_StateCode_fk"
            references Regions(StateCode),
    State              TEXT,
    Type               TEXT,
    Latitude           REAL,
    Longitude          REAL,
    AreaCode           INTEGER,
    Population         INTEGER,
    "Household Income" INTEGER,
    "Median Income"    INTEGER,
    "Land Area"        INTEGER,
    "Water Area"       INTEGER,
    "Time Zone"        TEXT
)

CREATE TABLE "Sales Orders"
(
    OrderNumber        TEXT
        constraint "Sales Orders_pk"
            primary key,
    "Sales Channel"    TEXT,
    WarehouseCode      TEXT,
    ProcuredDate       TEXT,
    OrderDate          TEXT,
    ShipDate           TEXT,
    DeliveryDate       TEXT,
    CurrencyCode       TEXT,
    _SalesTeamID       INTEGER
        constraint "Sales Orders_Sales Team_SalesTeamID_fk"
            references "Sales Team"(SalesTeamID),
    _CustomerID        INTEGER
        constraint "Sales Orders_Customers_CustomerID_fk"
            references Customers(CustomerID),
    _StoreID           INTEGER
        constraint "Sales Orders_Store Locations_StoreID_fk"
            references "Store Locations"(StoreID),
    _ProductID         INTEGER
        constraint "Sales Orders_Products_ProductID_fk"
            references Products(ProductID),
    "Order Quantity"   INTEGER,
    "Discount Applied" REAL,
    "Unit Price"       TEXT,
    "Unit Cost"        TEXT
)

### Question
List the name of the customer with the most number of order quantity from 2018 to 2020.

### Hint
name of customer refers to Customer Names; from 2018 to 2020 refers to OrderDate between '1/1/2018' and '31/12/2020'; most number of order quantity refers to Order Quantity = 8

### SQL
SELECT T1.`Customer Names` FROM Customers AS T1 INNER JOIN `Sales Orders` AS T2 ON T2._CustomerID = T1.CustomerID WHERE T2.OrderDate LIKE '%/%/18' OR T2.OrderDate LIKE '%/%/19' OR T2.OrderDate LIKE '%/%/20' ORDER BY T2.`Order Quantity` DESC LIMIT 1