lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE Categories
(
    CategoryID INTEGER PRIMARY KEY AUTOINCREMENT,
    CategoryName TEXT,
    Description TEXT
)

CREATE TABLE Customers
(
    CustomerID INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerName TEXT,
    ContactName TEXT,
    Address TEXT,
    City TEXT,
    PostalCode TEXT,
    Country TEXT
)

CREATE TABLE Employees
(
    EmployeeID INTEGER PRIMARY KEY AUTOINCREMENT,
    LastName TEXT,
    FirstName TEXT,
    BirthDate DATE,
    Photo TEXT,
    Notes TEXT
)

CREATE TABLE Shippers(
    ShipperID INTEGER PRIMARY KEY AUTOINCREMENT,
    ShipperName TEXT,
    Phone TEXT
)

CREATE TABLE Suppliers(
    SupplierID INTEGER PRIMARY KEY AUTOINCREMENT,
    SupplierName TEXT,
    ContactName TEXT,
    Address TEXT,
    City TEXT,
    PostalCode TEXT,
    Country TEXT,
    Phone TEXT
)

CREATE TABLE Products(
    ProductID INTEGER PRIMARY KEY AUTOINCREMENT,
    ProductName TEXT,
    SupplierID INTEGER,
    CategoryID INTEGER,
    Unit TEXT,
    Price REAL DEFAULT 0,
	FOREIGN KEY (CategoryID) REFERENCES Categories (CategoryID),
	FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID)
)

CREATE TABLE Orders(
    OrderID INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerID INTEGER,
    EmployeeID INTEGER,
    OrderDate DATETIME,
    ShipperID INTEGER,
    FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID),
    FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
    FOREIGN KEY (ShipperID) REFERENCES Shippers (ShipperID)
)

CREATE TABLE OrderDetails(
    OrderDetailID INTEGER PRIMARY KEY AUTOINCREMENT,
    OrderID INTEGER,
    ProductID INTEGER,
    Quantity INTEGER,
	FOREIGN KEY (OrderID) REFERENCES Orders (OrderID),
	FOREIGN KEY (ProductID) REFERENCES Products (ProductID)
)

### Question
Which country are the majority of the suppliers located?

### Hint
majority of the suppliers located refers to MAX(COUNT(SupplierID))

### SQL
SELECT Country FROM Suppliers GROUP BY Country ORDER BY COUNT(SupplierID) DESC LIMIT 1
### Database Schema
CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE Categories
(
    CategoryID INTEGER PRIMARY KEY AUTOINCREMENT,
    CategoryName TEXT,
    Description TEXT
)

CREATE TABLE Customers
(
    CustomerID INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerName TEXT,
    ContactName TEXT,
    Address TEXT,
    City TEXT,
    PostalCode TEXT,
    Country TEXT
)

CREATE TABLE Employees
(
    EmployeeID INTEGER PRIMARY KEY AUTOINCREMENT,
    LastName TEXT,
    FirstName TEXT,
    BirthDate DATE,
    Photo TEXT,
    Notes TEXT
)

CREATE TABLE Shippers(
    ShipperID INTEGER PRIMARY KEY AUTOINCREMENT,
    ShipperName TEXT,
    Phone TEXT
)

CREATE TABLE Suppliers(
    SupplierID INTEGER PRIMARY KEY AUTOINCREMENT,
    SupplierName TEXT,
    ContactName TEXT,
    Address TEXT,
    City TEXT,
    PostalCode TEXT,
    Country TEXT,
    Phone TEXT
)

CREATE TABLE Products(
    ProductID INTEGER PRIMARY KEY AUTOINCREMENT,
    ProductName TEXT,
    SupplierID INTEGER,
    CategoryID INTEGER,
    Unit TEXT,
    Price REAL DEFAULT 0,
	FOREIGN KEY (CategoryID) REFERENCES Categories (CategoryID),
	FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID)
)

CREATE TABLE Orders(
    OrderID INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerID INTEGER,
    EmployeeID INTEGER,
    OrderDate DATETIME,
    ShipperID INTEGER,
    FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID),
    FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
    FOREIGN KEY (ShipperID) REFERENCES Shippers (ShipperID)
)

CREATE TABLE OrderDetails(
    OrderDetailID INTEGER PRIMARY KEY AUTOINCREMENT,
    OrderID INTEGER,
    ProductID INTEGER,
    Quantity INTEGER,
	FOREIGN KEY (OrderID) REFERENCES Orders (OrderID),
	FOREIGN KEY (ProductID) REFERENCES Products (ProductID)
)

### Question
What is the full name of the employees who report to the Sales Manager?

### Hint
full name refers to LastName, FirstName; the Sales Manager refers to Title = 'Sales Manager'; report to refers to ReportsTo is not NULL;

### SQL
SELECT FirstName, LastName FROM Employees WHERE ReportsTo = ( SELECT EmployeeID FROM Employees WHERE Title = 'Sales Manager' )
### Database Schema
CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE Categories
(
    CategoryID INTEGER PRIMARY KEY AUTOINCREMENT,
    CategoryName TEXT,
    Description TEXT
)

CREATE TABLE Customers
(
    CustomerID INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerName TEXT,
    ContactName TEXT,
    Address TEXT,
    City TEXT,
    PostalCode TEXT,
    Country TEXT
)

CREATE TABLE Employees
(
    EmployeeID INTEGER PRIMARY KEY AUTOINCREMENT,
    LastName TEXT,
    FirstName TEXT,
    BirthDate DATE,
    Photo TEXT,
    Notes TEXT
)

CREATE TABLE Shippers(
    ShipperID INTEGER PRIMARY KEY AUTOINCREMENT,
    ShipperName TEXT,
    Phone TEXT
)

CREATE TABLE Suppliers(
    SupplierID INTEGER PRIMARY KEY AUTOINCREMENT,
    SupplierName TEXT,
    ContactName TEXT,
    Address TEXT,
    City TEXT,
    PostalCode TEXT,
    Country TEXT,
    Phone TEXT
)

CREATE TABLE Products(
    ProductID INTEGER PRIMARY KEY AUTOINCREMENT,
    ProductName TEXT,
    SupplierID INTEGER,
    CategoryID INTEGER,
    Unit TEXT,
    Price REAL DEFAULT 0,
	FOREIGN KEY (CategoryID) REFERENCES Categories (CategoryID),
	FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID)
)

CREATE TABLE Orders(
    OrderID INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerID INTEGER,
    EmployeeID INTEGER,
    OrderDate DATETIME,
    ShipperID INTEGER,
    FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID),
    FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
    FOREIGN KEY (ShipperID) REFERENCES Shippers (ShipperID)
)

CREATE TABLE OrderDetails(
    OrderDetailID INTEGER PRIMARY KEY AUTOINCREMENT,
    OrderID INTEGER,
    ProductID INTEGER,
    Quantity INTEGER,
	FOREIGN KEY (OrderID) REFERENCES Orders (OrderID),
	FOREIGN KEY (ProductID) REFERENCES Products (ProductID)
)

### Question
In August of 1996, how many orders were placed by the customer with the highest amount of orders?

### Hint
August of 1996 refers to OrderDate = '1996-8'; highest amount of orders refers to MAX(COUNT(OrderID))

### SQL
SELECT COUNT(OrderID) FROM Orders WHERE OrderDate LIKE '1996-08%' GROUP BY CustomerID ORDER BY COUNT(OrderID) DESC LIMIT 1
### Database Schema
CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE Categories
(
    CategoryID INTEGER PRIMARY KEY AUTOINCREMENT,
    CategoryName TEXT,
    Description TEXT
)

CREATE TABLE Customers
(
    CustomerID INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerName TEXT,
    ContactName TEXT,
    Address TEXT,
    City TEXT,
    PostalCode TEXT,
    Country TEXT
)

CREATE TABLE Employees
(
    EmployeeID INTEGER PRIMARY KEY AUTOINCREMENT,
    LastName TEXT,
    FirstName TEXT,
    BirthDate DATE,
    Photo TEXT,
    Notes TEXT
)

CREATE TABLE Shippers(
    ShipperID INTEGER PRIMARY KEY AUTOINCREMENT,
    ShipperName TEXT,
    Phone TEXT
)

CREATE TABLE Suppliers(
    SupplierID INTEGER PRIMARY KEY AUTOINCREMENT,
    SupplierName TEXT,
    ContactName TEXT,
    Address TEXT,
    City TEXT,
    PostalCode TEXT,
    Country TEXT,
    Phone TEXT
)

CREATE TABLE Products(
    ProductID INTEGER PRIMARY KEY AUTOINCREMENT,
    ProductName TEXT,
    SupplierID INTEGER,
    CategoryID INTEGER,
    Unit TEXT,
    Price REAL DEFAULT 0,
	FOREIGN KEY (CategoryID) REFERENCES Categories (CategoryID),
	FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID)
)

CREATE TABLE Orders(
    OrderID INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerID INTEGER,
    EmployeeID INTEGER,
    OrderDate DATETIME,
    ShipperID INTEGER,
    FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID),
    FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
    FOREIGN KEY (ShipperID) REFERENCES Shippers (ShipperID)
)

CREATE TABLE OrderDetails(
    OrderDetailID INTEGER PRIMARY KEY AUTOINCREMENT,
    OrderID INTEGER,
    ProductID INTEGER,
    Quantity INTEGER,
	FOREIGN KEY (OrderID) REFERENCES Orders (OrderID),
	FOREIGN KEY (ProductID) REFERENCES Products (ProductID)
)

### Question
How much is the salary of the first employee that was hired?

### Hint
first employee that was hired refers to MIN(HireDate)

### SQL
SELECT Salary FROM Employees WHERE HireDate = ( SELECT MIN(HireDate) FROM Employees )
### Database Schema
CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE Categories
(
    CategoryID INTEGER PRIMARY KEY AUTOINCREMENT,
    CategoryName TEXT,
    Description TEXT
)

CREATE TABLE Customers
(
    CustomerID INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerName TEXT,
    ContactName TEXT,
    Address TEXT,
    City TEXT,
    PostalCode TEXT,
    Country TEXT
)

CREATE TABLE Employees
(
    EmployeeID INTEGER PRIMARY KEY AUTOINCREMENT,
    LastName TEXT,
    FirstName TEXT,
    BirthDate DATE,
    Photo TEXT,
    Notes TEXT
)

CREATE TABLE Shippers(
    ShipperID INTEGER PRIMARY KEY AUTOINCREMENT,
    ShipperName TEXT,
    Phone TEXT
)

CREATE TABLE Suppliers(
    SupplierID INTEGER PRIMARY KEY AUTOINCREMENT,
    SupplierName TEXT,
    ContactName TEXT,
    Address TEXT,
    City TEXT,
    PostalCode TEXT,
    Country TEXT,
    Phone TEXT
)

CREATE TABLE Products(
    ProductID INTEGER PRIMARY KEY AUTOINCREMENT,
    ProductName TEXT,
    SupplierID INTEGER,
    CategoryID INTEGER,
    Unit TEXT,
    Price REAL DEFAULT 0,
	FOREIGN KEY (CategoryID) REFERENCES Categories (CategoryID),
	FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID)
)

CREATE TABLE Orders(
    OrderID INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerID INTEGER,
    EmployeeID INTEGER,
    OrderDate DATETIME,
    ShipperID INTEGER,
    FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID),
    FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
    FOREIGN KEY (ShipperID) REFERENCES Shippers (ShipperID)
)

CREATE TABLE OrderDetails(
    OrderDetailID INTEGER PRIMARY KEY AUTOINCREMENT,
    OrderID INTEGER,
    ProductID INTEGER,
    Quantity INTEGER,
	FOREIGN KEY (OrderID) REFERENCES Orders (OrderID),
	FOREIGN KEY (ProductID) REFERENCES Products (ProductID)
)

### Question
How old was the oldest employee at the time he or she was hired?

### Hint
oldest employee at the time he or she was hired refers to MAX(SUBTRACT(HireDate, Birthdate))

### SQL
SELECT MAX(TIMESTAMPDIFF(YEAR, BirthDate, HireDate)) FROM Employees
### Database Schema
CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE Categories
(
    CategoryID INTEGER PRIMARY KEY AUTOINCREMENT,
    CategoryName TEXT,
    Description TEXT
)

CREATE TABLE Customers
(
    CustomerID INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerName TEXT,
    ContactName TEXT,
    Address TEXT,
    City TEXT,
    PostalCode TEXT,
    Country TEXT
)

CREATE TABLE Employees
(
    EmployeeID INTEGER PRIMARY KEY AUTOINCREMENT,
    LastName TEXT,
    FirstName TEXT,
    BirthDate DATE,
    Photo TEXT,
    Notes TEXT
)

CREATE TABLE Shippers(
    ShipperID INTEGER PRIMARY KEY AUTOINCREMENT,
    ShipperName TEXT,
    Phone TEXT
)

CREATE TABLE Suppliers(
    SupplierID INTEGER PRIMARY KEY AUTOINCREMENT,
    SupplierName TEXT,
    ContactName TEXT,
    Address TEXT,
    City TEXT,
    PostalCode TEXT,
    Country TEXT,
    Phone TEXT
)

CREATE TABLE Products(
    ProductID INTEGER PRIMARY KEY AUTOINCREMENT,
    ProductName TEXT,
    SupplierID INTEGER,
    CategoryID INTEGER,
    Unit TEXT,
    Price REAL DEFAULT 0,
	FOREIGN KEY (CategoryID) REFERENCES Categories (CategoryID),
	FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID)
)

CREATE TABLE Orders(
    OrderID INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerID INTEGER,
    EmployeeID INTEGER,
    OrderDate DATETIME,
    ShipperID INTEGER,
    FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID),
    FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
    FOREIGN KEY (ShipperID) REFERENCES Shippers (ShipperID)
)

CREATE TABLE OrderDetails(
    OrderDetailID INTEGER PRIMARY KEY AUTOINCREMENT,
    OrderID INTEGER,
    ProductID INTEGER,
    Quantity INTEGER,
	FOREIGN KEY (OrderID) REFERENCES Orders (OrderID),
	FOREIGN KEY (ProductID) REFERENCES Products (ProductID)
)

### Question
What is the total sales amount of all discontinued products?

### Hint
discontinued products refers to Discontinued = 1; total sales amount refers to SUM(MULTIPLY(UnitPrice, Quantity))

### SQL
SELECT SUM(T2.UnitPrice * T2.Quantity) FROM Products AS T1 INNER JOIN `Order Details` AS T2 ON T1.ProductID = T2.ProductID WHERE T1.Discontinued = 1
### Database Schema
CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE Categories
(
    CategoryID INTEGER PRIMARY KEY AUTOINCREMENT,
    CategoryName TEXT,
    Description TEXT
)

CREATE TABLE Customers
(
    CustomerID INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerName TEXT,
    ContactName TEXT,
    Address TEXT,
    City TEXT,
    PostalCode TEXT,
    Country TEXT
)

CREATE TABLE Employees
(
    EmployeeID INTEGER PRIMARY KEY AUTOINCREMENT,
    LastName TEXT,
    FirstName TEXT,
    BirthDate DATE,
    Photo TEXT,
    Notes TEXT
)

CREATE TABLE Shippers(
    ShipperID INTEGER PRIMARY KEY AUTOINCREMENT,
    ShipperName TEXT,
    Phone TEXT
)

CREATE TABLE Suppliers(
    SupplierID INTEGER PRIMARY KEY AUTOINCREMENT,
    SupplierName TEXT,
    ContactName TEXT,
    Address TEXT,
    City TEXT,
    PostalCode TEXT,
    Country TEXT,
    Phone TEXT
)

CREATE TABLE Products(
    ProductID INTEGER PRIMARY KEY AUTOINCREMENT,
    ProductName TEXT,
    SupplierID INTEGER,
    CategoryID INTEGER,
    Unit TEXT,
    Price REAL DEFAULT 0,
	FOREIGN KEY (CategoryID) REFERENCES Categories (CategoryID),
	FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID)
)

CREATE TABLE Orders(
    OrderID INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerID INTEGER,
    EmployeeID INTEGER,
    OrderDate DATETIME,
    ShipperID INTEGER,
    FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID),
    FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
    FOREIGN KEY (ShipperID) REFERENCES Shippers (ShipperID)
)

CREATE TABLE OrderDetails(
    OrderDetailID INTEGER PRIMARY KEY AUTOINCREMENT,
    OrderID INTEGER,
    ProductID INTEGER,
    Quantity INTEGER,
	FOREIGN KEY (OrderID) REFERENCES Orders (OrderID),
	FOREIGN KEY (ProductID) REFERENCES Products (ProductID)
)

### Question
What is the category of the product that has the highest number of discontinued products?

### Hint
discontinued products refers to Discontinued = 1; highest number of discontinued products refers to MAX(Discontinued = 1)

### SQL
SELECT T2.CategoryName FROM Products AS T1 INNER JOIN Categories AS T2 ON T1.CategoryID = T2.CategoryID WHERE T1.Discontinued = 1 GROUP BY T2.CategoryName ORDER BY COUNT(T1.ProductID) DESC LIMIT 1
### Database Schema
CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE Categories
(
    CategoryID INTEGER PRIMARY KEY AUTOINCREMENT,
    CategoryName TEXT,
    Description TEXT
)

CREATE TABLE Customers
(
    CustomerID INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerName TEXT,
    ContactName TEXT,
    Address TEXT,
    City TEXT,
    PostalCode TEXT,
    Country TEXT
)

CREATE TABLE Employees
(
    EmployeeID INTEGER PRIMARY KEY AUTOINCREMENT,
    LastName TEXT,
    FirstName TEXT,
    BirthDate DATE,
    Photo TEXT,
    Notes TEXT
)

CREATE TABLE Shippers(
    ShipperID INTEGER PRIMARY KEY AUTOINCREMENT,
    ShipperName TEXT,
    Phone TEXT
)

CREATE TABLE Suppliers(
    SupplierID INTEGER PRIMARY KEY AUTOINCREMENT,
    SupplierName TEXT,
    ContactName TEXT,
    Address TEXT,
    City TEXT,
    PostalCode TEXT,
    Country TEXT,
    Phone TEXT
)

CREATE TABLE Products(
    ProductID INTEGER PRIMARY KEY AUTOINCREMENT,
    ProductName TEXT,
    SupplierID INTEGER,
    CategoryID INTEGER,
    Unit TEXT,
    Price REAL DEFAULT 0,
	FOREIGN KEY (CategoryID) REFERENCES Categories (CategoryID),
	FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID)
)

CREATE TABLE Orders(
    OrderID INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerID INTEGER,
    EmployeeID INTEGER,
    OrderDate DATETIME,
    ShipperID INTEGER,
    FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID),
    FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
    FOREIGN KEY (ShipperID) REFERENCES Shippers (ShipperID)
)

CREATE TABLE OrderDetails(
    OrderDetailID INTEGER PRIMARY KEY AUTOINCREMENT,
    OrderID INTEGER,
    ProductID INTEGER,
    Quantity INTEGER,
	FOREIGN KEY (OrderID) REFERENCES Orders (OrderID),
	FOREIGN KEY (ProductID) REFERENCES Products (ProductID)
)

### Question
How many condiments were sold in 1997?

### Hint
"Condiments" is the CategoryName; in 1997 refers to year(OrderDate) = 1997;

### SQL
SELECT COUNT(T2.ProductID) FROM Categories AS T1 INNER JOIN Products AS T2 ON T1.CategoryID = T2.CategoryID INNER JOIN `Order Details` AS T3 ON T2.ProductID = T3.ProductID INNER JOIN Orders AS T4 ON T3.OrderID = T4.OrderID WHERE T1.CategoryName = 'Condiments' AND T1.CategoryID = 2 AND T4.OrderDate LIKE '1997%'
### Database Schema
CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE Categories
(
    CategoryID INTEGER PRIMARY KEY AUTOINCREMENT,
    CategoryName TEXT,
    Description TEXT
)

CREATE TABLE Customers
(
    CustomerID INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerName TEXT,
    ContactName TEXT,
    Address TEXT,
    City TEXT,
    PostalCode TEXT,
    Country TEXT
)

CREATE TABLE Employees
(
    EmployeeID INTEGER PRIMARY KEY AUTOINCREMENT,
    LastName TEXT,
    FirstName TEXT,
    BirthDate DATE,
    Photo TEXT,
    Notes TEXT
)

CREATE TABLE Shippers(
    ShipperID INTEGER PRIMARY KEY AUTOINCREMENT,
    ShipperName TEXT,
    Phone TEXT
)

CREATE TABLE Suppliers(
    SupplierID INTEGER PRIMARY KEY AUTOINCREMENT,
    SupplierName TEXT,
    ContactName TEXT,
    Address TEXT,
    City TEXT,
    PostalCode TEXT,
    Country TEXT,
    Phone TEXT
)

CREATE TABLE Products(
    ProductID INTEGER PRIMARY KEY AUTOINCREMENT,
    ProductName TEXT,
    SupplierID INTEGER,
    CategoryID INTEGER,
    Unit TEXT,
    Price REAL DEFAULT 0,
	FOREIGN KEY (CategoryID) REFERENCES Categories (CategoryID),
	FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID)
)

CREATE TABLE Orders(
    OrderID INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerID INTEGER,
    EmployeeID INTEGER,
    OrderDate DATETIME,
    ShipperID INTEGER,
    FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID),
    FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
    FOREIGN KEY (ShipperID) REFERENCES Shippers (ShipperID)
)

CREATE TABLE OrderDetails(
    OrderDetailID INTEGER PRIMARY KEY AUTOINCREMENT,
    OrderID INTEGER,
    ProductID INTEGER,
    Quantity INTEGER,
	FOREIGN KEY (OrderID) REFERENCES Orders (OrderID),
	FOREIGN KEY (ProductID) REFERENCES Products (ProductID)
)

### Question
Who is the customer who purchased the highest number of products in a single order?

### Hint
highest number of products refers to MAX(COUNT(ProductID))

### SQL
SELECT T1.CompanyName FROM Customers AS T1 INNER JOIN Orders AS T2 ON T1.CustomerID = T2.CustomerID INNER JOIN `Order Details` AS T3 ON T2.OrderID = T3.OrderID GROUP BY T1.CompanyName ORDER BY COUNT(T3.ProductID) DESC LIMIT 1
### Database Schema
CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE Categories
(
    CategoryID INTEGER PRIMARY KEY AUTOINCREMENT,
    CategoryName TEXT,
    Description TEXT
)

CREATE TABLE Customers
(
    CustomerID INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerName TEXT,
    ContactName TEXT,
    Address TEXT,
    City TEXT,
    PostalCode TEXT,
    Country TEXT
)

CREATE TABLE Employees
(
    EmployeeID INTEGER PRIMARY KEY AUTOINCREMENT,
    LastName TEXT,
    FirstName TEXT,
    BirthDate DATE,
    Photo TEXT,
    Notes TEXT
)

CREATE TABLE Shippers(
    ShipperID INTEGER PRIMARY KEY AUTOINCREMENT,
    ShipperName TEXT,
    Phone TEXT
)

CREATE TABLE Suppliers(
    SupplierID INTEGER PRIMARY KEY AUTOINCREMENT,
    SupplierName TEXT,
    ContactName TEXT,
    Address TEXT,
    City TEXT,
    PostalCode TEXT,
    Country TEXT,
    Phone TEXT
)

CREATE TABLE Products(
    ProductID INTEGER PRIMARY KEY AUTOINCREMENT,
    ProductName TEXT,
    SupplierID INTEGER,
    CategoryID INTEGER,
    Unit TEXT,
    Price REAL DEFAULT 0,
	FOREIGN KEY (CategoryID) REFERENCES Categories (CategoryID),
	FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID)
)

CREATE TABLE Orders(
    OrderID INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerID INTEGER,
    EmployeeID INTEGER,
    OrderDate DATETIME,
    ShipperID INTEGER,
    FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID),
    FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
    FOREIGN KEY (ShipperID) REFERENCES Shippers (ShipperID)
)

CREATE TABLE OrderDetails(
    OrderDetailID INTEGER PRIMARY KEY AUTOINCREMENT,
    OrderID INTEGER,
    ProductID INTEGER,
    Quantity INTEGER,
	FOREIGN KEY (OrderID) REFERENCES Orders (OrderID),
	FOREIGN KEY (ProductID) REFERENCES Products (ProductID)
)

### Question
What is the monthly average number of products shipped via Federal Shipping for the year 1996?

### Hint
monthly average number of products refers to DIVIDE(SUM(OrderID), 12); shipped via Federal Shipping refers to CompanyName = 'Federal Shipping'; for the year 1996 refers to year(ShippedDate) = 1996

### SQL
SELECT CAST(SUM(T1.ShipVia) AS REAL) / 12 FROM Orders AS T1 INNER JOIN Shippers AS T2 ON T1.ShipVia = T2.ShipperID WHERE T2.CompanyName = 'Federal Shipping' AND T1.ShippedDate LIKE '1996%'
### Database Schema
CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE Categories
(
    CategoryID INTEGER PRIMARY KEY AUTOINCREMENT,
    CategoryName TEXT,
    Description TEXT
)

CREATE TABLE Customers
(
    CustomerID INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerName TEXT,
    ContactName TEXT,
    Address TEXT,
    City TEXT,
    PostalCode TEXT,
    Country TEXT
)

CREATE TABLE Employees
(
    EmployeeID INTEGER PRIMARY KEY AUTOINCREMENT,
    LastName TEXT,
    FirstName TEXT,
    BirthDate DATE,
    Photo TEXT,
    Notes TEXT
)

CREATE TABLE Shippers(
    ShipperID INTEGER PRIMARY KEY AUTOINCREMENT,
    ShipperName TEXT,
    Phone TEXT
)

CREATE TABLE Suppliers(
    SupplierID INTEGER PRIMARY KEY AUTOINCREMENT,
    SupplierName TEXT,
    ContactName TEXT,
    Address TEXT,
    City TEXT,
    PostalCode TEXT,
    Country TEXT,
    Phone TEXT
)

CREATE TABLE Products(
    ProductID INTEGER PRIMARY KEY AUTOINCREMENT,
    ProductName TEXT,
    SupplierID INTEGER,
    CategoryID INTEGER,
    Unit TEXT,
    Price REAL DEFAULT 0,
	FOREIGN KEY (CategoryID) REFERENCES Categories (CategoryID),
	FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID)
)

CREATE TABLE Orders(
    OrderID INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerID INTEGER,
    EmployeeID INTEGER,
    OrderDate DATETIME,
    ShipperID INTEGER,
    FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID),
    FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
    FOREIGN KEY (ShipperID) REFERENCES Shippers (ShipperID)
)

CREATE TABLE OrderDetails(
    OrderDetailID INTEGER PRIMARY KEY AUTOINCREMENT,
    OrderID INTEGER,
    ProductID INTEGER,
    Quantity INTEGER,
	FOREIGN KEY (OrderID) REFERENCES Orders (OrderID),
	FOREIGN KEY (ProductID) REFERENCES Products (ProductID)
)

### Question
Which products are being supplied by "G'day, Mate"? List all of their names.

### Hint
supplied by "G'day, Mate" refers to CompanyName = 'G''day, Mate';

### SQL
SELECT T1.ProductName FROM Products AS T1 INNER JOIN Suppliers AS T2 ON T1.SupplierID = T2.SupplierID WHERE T2.CompanyName LIKE 'G%day, Mate'