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
Of the 10 products with the highest unit price, identify by their ID the ones that have generated the least satisfaction.

### Hint
High reorder level generally means high user satisfaction of the product and vice versa; the least satisfaction refers to MIN(ReorderLevel); the highest unit price refers to MAX(UnitPrice);

### SQL
SELECT ProductID FROM Products ORDER BY ReorderLevel ASC, UnitPrice 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 non-discontinued products are there in the dairy category?

### Hint
non-discontinued products in the dairy category refer to ProductID where Discontinued = 0 and CategoryName = 'Dairy Products';

### SQL
SELECT COUNT(T1.CategoryID) FROM Categories AS T1 INNER JOIN Products AS T2 ON T1.CategoryID = T2.CategoryID WHERE T1.CategoryName = 'Dairy Products' AND T2.Discontinued = 0
### 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 territories is the Inside Sales Coordinator in charge of?

### Hint
territories refer to TerritoryDescription; Title = 'Inside Sales Coordinator';

### SQL
SELECT T3.TerritoryDescription FROM Employees AS T1 INNER JOIN EmployeeTerritories AS T2 ON T1.EmployeeID = T2.EmployeeID INNER JOIN Territories AS T3 ON T2.TerritoryID = T3.TerritoryID WHERE T1.Title = 'Inside Sales Coordinator'
### 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
Indicate the name of the companies that have freighted products for a value greater than 2,000,000.

### Hint
freighted products for a value greater than 2,000,000 refer to Freight > 2000000; name of companies refers to CompanyName;

### SQL
SELECT T1.CompanyName FROM Customers AS T1 INNER JOIN Orders AS T2 ON T1.CustomerID = T2.CustomerID WHERE T2.Freight > 2000000
### 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
Through which companies have products been shipped the most times to the city of Aachen?

### Hint
shipped the most times refer to MAX(COUNT(ShipVia)); city of Aachen refers to ShipCity = 'Aache'; companies refers to CompanyName;

### SQL
SELECT T2.CompanyName FROM Orders AS T1 INNER JOIN Shippers AS T2 ON T1.ShipVia = T2.ShipperID WHERE T1.ShipCity = 'Aachen' GROUP BY T2.CompanyName ORDER BY COUNT(T1.ShipVia) 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
List the full name of all employees who work in the Northern region.

### Hint
full names = FirstName, LastName; Northern region refers to RegionDescription = 'Northern';

### SQL
SELECT DISTINCT T1.FirstName, T1.LastName FROM Employees AS T1 INNER JOIN EmployeeTerritories AS T2 ON T1.EmployeeID = T2.EmployeeID INNER JOIN Territories AS T3 ON T2.TerritoryID = T3.TerritoryID INNER JOIN Region AS T4 ON T3.RegionID = T4.RegionID WHERE T4.RegionDescription = 'Northern'
### 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 name of the contact person of the Pavlova supplier company?

### Hint
contact person refers to ContactName; Pavlova is the name of the product;

### SQL
SELECT T2.ContactName FROM Products AS T1 INNER JOIN Suppliers AS T2 ON T1.SupplierID = T2.SupplierID WHERE T1.ProductName = 'Pavlova'
### 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
Indicate the name of the products that have been shipped to the city of Paris.

### Hint
shipped to the city of Paris refers to ShipCity = 'Paris';

### SQL
SELECT T3.ProductName FROM Orders AS T1 INNER JOIN `Order Details` AS T2 ON T1.OrderID = T2.OrderID INNER JOIN Products AS T3 ON T2.ProductID = T3.ProductID WHERE T1.ShipCity = 'Paris'
### 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 product is the least shipped to the postal code 28023?

### Hint
the least shipped product refers ProductName where MIN(Quantity);

### SQL
SELECT T4.ProductName 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 INNER JOIN Products AS T4 ON T3.ProductID = T4.ProductID WHERE T1.PostalCode = 28023 ORDER BY T3.Quantity 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 employee in charge of the Southern region who is to report to Andrew Fuller?

### Hint
full names = FirstName, LastName; report to represents a hierarchical relationship where the person being reported to is usually the direct supervisor of the reporter; Andrew Fuller refers to Employees WHERE FirstName = 'Andrew' AND LastName = 'Fuller'; Southern region refers to RegionDescription = 'Southern';

### SQL
SELECT DISTINCT T1.FirstName, T1.LastName FROM Employees AS T1 INNER JOIN EmployeeTerritories AS T2 ON T1.EmployeeID = T2.EmployeeID INNER JOIN Territories AS T3 ON T2.TerritoryID = T3.TerritoryID INNER JOIN Region AS T4 ON T3.RegionID = T4.RegionID WHERE T4.RegionDescription = 'Southern' AND T1.ReportsTo = ( SELECT EmployeeID FROM Employees WHERE FirstName = 'Andrew' AND LastName = 'Fuller' )