lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_name` VARCHAR(80),
`customer_details` VARCHAR(255)
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`invoice_date` DATETIME,
`invoice_details` VARCHAR(255)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(10) NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_name` VARCHAR(80),
`product_details` VARCHAR(255)
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`order_id` INTEGER NOT NULL,
`order_item_status` VARCHAR(10) NOT NULL,
`order_item_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

CREATE TABLE `Shipments` (
`shipment_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`shipment_tracking_number` VARCHAR(80),
`shipment_date` DATETIME,
`other_shipment_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` )
)

CREATE TABLE `Shipment_Items` (
`shipment_id` INTEGER NOT NULL,
`order_item_id` INTEGER NOT NULL,
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`shipment_id` ) REFERENCES `Shipments`(`shipment_id` )
)

### Question
What is the id of the order which has the most items?

### SQL
SELECT T1.order_id FROM orders AS T1 JOIN order_items AS T2 ON T1.order_id = T2.order_id GROUP BY T1.order_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_name` VARCHAR(80),
`customer_details` VARCHAR(255)
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`invoice_date` DATETIME,
`invoice_details` VARCHAR(255)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(10) NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_name` VARCHAR(80),
`product_details` VARCHAR(255)
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`order_id` INTEGER NOT NULL,
`order_item_status` VARCHAR(10) NOT NULL,
`order_item_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

CREATE TABLE `Shipments` (
`shipment_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`shipment_tracking_number` VARCHAR(80),
`shipment_date` DATETIME,
`other_shipment_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` )
)

CREATE TABLE `Shipment_Items` (
`shipment_id` INTEGER NOT NULL,
`order_item_id` INTEGER NOT NULL,
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`shipment_id` ) REFERENCES `Shipments`(`shipment_id` )
)

### Question
Which order deals with the most items? Return the order id.

### SQL
SELECT T1.order_id FROM orders AS T1 JOIN order_items AS T2 ON T1.order_id = T2.order_id GROUP BY T1.order_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_name` VARCHAR(80),
`customer_details` VARCHAR(255)
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`invoice_date` DATETIME,
`invoice_details` VARCHAR(255)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(10) NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_name` VARCHAR(80),
`product_details` VARCHAR(255)
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`order_id` INTEGER NOT NULL,
`order_item_status` VARCHAR(10) NOT NULL,
`order_item_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

CREATE TABLE `Shipments` (
`shipment_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`shipment_tracking_number` VARCHAR(80),
`shipment_date` DATETIME,
`other_shipment_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` )
)

CREATE TABLE `Shipment_Items` (
`shipment_id` INTEGER NOT NULL,
`order_item_id` INTEGER NOT NULL,
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`shipment_id` ) REFERENCES `Shipments`(`shipment_id` )
)

### Question
What is the name of the customer who has the largest number of orders?

### SQL
SELECT T1.customer_name FROM customers AS T1 JOIN orders AS T2 ON T1.customer_id = T2.customer_id GROUP BY T1.customer_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_name` VARCHAR(80),
`customer_details` VARCHAR(255)
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`invoice_date` DATETIME,
`invoice_details` VARCHAR(255)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(10) NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_name` VARCHAR(80),
`product_details` VARCHAR(255)
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`order_id` INTEGER NOT NULL,
`order_item_status` VARCHAR(10) NOT NULL,
`order_item_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

CREATE TABLE `Shipments` (
`shipment_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`shipment_tracking_number` VARCHAR(80),
`shipment_date` DATETIME,
`other_shipment_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` )
)

CREATE TABLE `Shipment_Items` (
`shipment_id` INTEGER NOT NULL,
`order_item_id` INTEGER NOT NULL,
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`shipment_id` ) REFERENCES `Shipments`(`shipment_id` )
)

### Question
Find the name of the customer who made the most orders.

### SQL
SELECT T1.customer_name FROM customers AS T1 JOIN orders AS T2 ON T1.customer_id = T2.customer_id GROUP BY T1.customer_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_name` VARCHAR(80),
`customer_details` VARCHAR(255)
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`invoice_date` DATETIME,
`invoice_details` VARCHAR(255)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(10) NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_name` VARCHAR(80),
`product_details` VARCHAR(255)
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`order_id` INTEGER NOT NULL,
`order_item_status` VARCHAR(10) NOT NULL,
`order_item_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

CREATE TABLE `Shipments` (
`shipment_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`shipment_tracking_number` VARCHAR(80),
`shipment_date` DATETIME,
`other_shipment_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` )
)

CREATE TABLE `Shipment_Items` (
`shipment_id` INTEGER NOT NULL,
`order_item_id` INTEGER NOT NULL,
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`shipment_id` ) REFERENCES `Shipments`(`shipment_id` )
)

### Question
Find the invoice numbers which are created before 1989-09-03 or after 2007-12-25.

### SQL
SELECT invoice_number FROM invoices WHERE invoice_date < "1989-09-03" OR invoice_date > "2007-12-25"
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_name` VARCHAR(80),
`customer_details` VARCHAR(255)
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`invoice_date` DATETIME,
`invoice_details` VARCHAR(255)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(10) NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_name` VARCHAR(80),
`product_details` VARCHAR(255)
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`order_id` INTEGER NOT NULL,
`order_item_status` VARCHAR(10) NOT NULL,
`order_item_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

CREATE TABLE `Shipments` (
`shipment_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`shipment_tracking_number` VARCHAR(80),
`shipment_date` DATETIME,
`other_shipment_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` )
)

CREATE TABLE `Shipment_Items` (
`shipment_id` INTEGER NOT NULL,
`order_item_id` INTEGER NOT NULL,
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`shipment_id` ) REFERENCES `Shipments`(`shipment_id` )
)

### Question
What are the invoice numbers created before 1989-09-03 or after 2007-12-25?

### SQL
SELECT invoice_number FROM invoices WHERE invoice_date < "1989-09-03" OR invoice_date > "2007-12-25"
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_name` VARCHAR(80),
`customer_details` VARCHAR(255)
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`invoice_date` DATETIME,
`invoice_details` VARCHAR(255)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(10) NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_name` VARCHAR(80),
`product_details` VARCHAR(255)
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`order_id` INTEGER NOT NULL,
`order_item_status` VARCHAR(10) NOT NULL,
`order_item_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

CREATE TABLE `Shipments` (
`shipment_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`shipment_tracking_number` VARCHAR(80),
`shipment_date` DATETIME,
`other_shipment_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` )
)

CREATE TABLE `Shipment_Items` (
`shipment_id` INTEGER NOT NULL,
`order_item_id` INTEGER NOT NULL,
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`shipment_id` ) REFERENCES `Shipments`(`shipment_id` )
)

### Question
Find the distinct details of invoices which are created before 1989-09-03 or after 2007-12-25.

### SQL
SELECT DISTINCT invoice_details FROM invoices WHERE invoice_date < "1989-09-03" OR invoice_date > "2007-12-25"
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_name` VARCHAR(80),
`customer_details` VARCHAR(255)
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`invoice_date` DATETIME,
`invoice_details` VARCHAR(255)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(10) NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_name` VARCHAR(80),
`product_details` VARCHAR(255)
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`order_id` INTEGER NOT NULL,
`order_item_status` VARCHAR(10) NOT NULL,
`order_item_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

CREATE TABLE `Shipments` (
`shipment_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`shipment_tracking_number` VARCHAR(80),
`shipment_date` DATETIME,
`other_shipment_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` )
)

CREATE TABLE `Shipment_Items` (
`shipment_id` INTEGER NOT NULL,
`order_item_id` INTEGER NOT NULL,
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`shipment_id` ) REFERENCES `Shipments`(`shipment_id` )
)

### Question
What are the distinct details of invoices created before 1989-09-03 or after 2007-12-25?

### SQL
SELECT DISTINCT invoice_details FROM invoices WHERE invoice_date < "1989-09-03" OR invoice_date > "2007-12-25"
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_name` VARCHAR(80),
`customer_details` VARCHAR(255)
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`invoice_date` DATETIME,
`invoice_details` VARCHAR(255)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(10) NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_name` VARCHAR(80),
`product_details` VARCHAR(255)
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`order_id` INTEGER NOT NULL,
`order_item_status` VARCHAR(10) NOT NULL,
`order_item_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

CREATE TABLE `Shipments` (
`shipment_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`shipment_tracking_number` VARCHAR(80),
`shipment_date` DATETIME,
`other_shipment_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` )
)

CREATE TABLE `Shipment_Items` (
`shipment_id` INTEGER NOT NULL,
`order_item_id` INTEGER NOT NULL,
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`shipment_id` ) REFERENCES `Shipments`(`shipment_id` )
)

### Question
For each customer who has at least two orders, find the customer name and number of orders made.

### SQL
SELECT T2.customer_name ,  count(*) FROM orders AS T1 JOIN customers AS T2 ON T1.customer_id = T2.customer_id GROUP BY T2.customer_id HAVING count(*)  >=  2
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_name` VARCHAR(80),
`customer_details` VARCHAR(255)
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`invoice_date` DATETIME,
`invoice_details` VARCHAR(255)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(10) NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_name` VARCHAR(80),
`product_details` VARCHAR(255)
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`order_id` INTEGER NOT NULL,
`order_item_status` VARCHAR(10) NOT NULL,
`order_item_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

CREATE TABLE `Shipments` (
`shipment_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`shipment_tracking_number` VARCHAR(80),
`shipment_date` DATETIME,
`other_shipment_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` )
)

CREATE TABLE `Shipment_Items` (
`shipment_id` INTEGER NOT NULL,
`order_item_id` INTEGER NOT NULL,
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`shipment_id` ) REFERENCES `Shipments`(`shipment_id` )
)

### Question
Which customers have made at least two orders? Give me each customer name and number of orders made.

### SQL
SELECT T2.customer_name ,  count(*) FROM orders AS T1 JOIN customers AS T2 ON T1.customer_id = T2.customer_id GROUP BY T2.customer_id HAVING count(*)  >=  2
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_name` VARCHAR(80),
`customer_details` VARCHAR(255)
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`invoice_date` DATETIME,
`invoice_details` VARCHAR(255)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(10) NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_name` VARCHAR(80),
`product_details` VARCHAR(255)
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`order_id` INTEGER NOT NULL,
`order_item_status` VARCHAR(10) NOT NULL,
`order_item_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

CREATE TABLE `Shipments` (
`shipment_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`shipment_tracking_number` VARCHAR(80),
`shipment_date` DATETIME,
`other_shipment_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` )
)

CREATE TABLE `Shipment_Items` (
`shipment_id` INTEGER NOT NULL,
`order_item_id` INTEGER NOT NULL,
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`shipment_id` ) REFERENCES `Shipments`(`shipment_id` )
)

### Question
Find the name of the customers who have at most two orders.

### SQL
SELECT T2.customer_name FROM orders AS T1 JOIN customers AS T2 ON T1.customer_id = T2.customer_id GROUP BY T2.customer_id HAVING count(*)  <=  2
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_name` VARCHAR(80),
`customer_details` VARCHAR(255)
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`invoice_date` DATETIME,
`invoice_details` VARCHAR(255)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(10) NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_name` VARCHAR(80),
`product_details` VARCHAR(255)
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`order_id` INTEGER NOT NULL,
`order_item_status` VARCHAR(10) NOT NULL,
`order_item_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

CREATE TABLE `Shipments` (
`shipment_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`shipment_tracking_number` VARCHAR(80),
`shipment_date` DATETIME,
`other_shipment_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` )
)

CREATE TABLE `Shipment_Items` (
`shipment_id` INTEGER NOT NULL,
`order_item_id` INTEGER NOT NULL,
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`shipment_id` ) REFERENCES `Shipments`(`shipment_id` )
)

### Question
What are the names of the customers who have made two or less orders?

### SQL
SELECT T2.customer_name FROM orders AS T1 JOIN customers AS T2 ON T1.customer_id = T2.customer_id GROUP BY T2.customer_id HAVING count(*)  <=  2
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_name` VARCHAR(80),
`customer_details` VARCHAR(255)
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`invoice_date` DATETIME,
`invoice_details` VARCHAR(255)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(10) NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_name` VARCHAR(80),
`product_details` VARCHAR(255)
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`order_id` INTEGER NOT NULL,
`order_item_status` VARCHAR(10) NOT NULL,
`order_item_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

CREATE TABLE `Shipments` (
`shipment_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`shipment_tracking_number` VARCHAR(80),
`shipment_date` DATETIME,
`other_shipment_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` )
)

CREATE TABLE `Shipment_Items` (
`shipment_id` INTEGER NOT NULL,
`order_item_id` INTEGER NOT NULL,
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`shipment_id` ) REFERENCES `Shipments`(`shipment_id` )
)

### Question
List the names of the customers who have once bought product "food".

### SQL
SELECT T1.customer_name FROM customers AS T1 JOIN orders AS T2 JOIN order_items AS T3 JOIN products AS T4 ON T1.customer_id = T2.customer_id AND T2.order_id = T3.order_id AND T3.product_id = T4.product_id WHERE T4.product_name = "food" GROUP BY T1.customer_id HAVING count(*)  >=  1
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_name` VARCHAR(80),
`customer_details` VARCHAR(255)
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`invoice_date` DATETIME,
`invoice_details` VARCHAR(255)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(10) NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_name` VARCHAR(80),
`product_details` VARCHAR(255)
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`order_id` INTEGER NOT NULL,
`order_item_status` VARCHAR(10) NOT NULL,
`order_item_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

CREATE TABLE `Shipments` (
`shipment_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`shipment_tracking_number` VARCHAR(80),
`shipment_date` DATETIME,
`other_shipment_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` )
)

CREATE TABLE `Shipment_Items` (
`shipment_id` INTEGER NOT NULL,
`order_item_id` INTEGER NOT NULL,
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`shipment_id` ) REFERENCES `Shipments`(`shipment_id` )
)

### Question
What are the names of the customers who bought product "food" at least once?

### SQL
SELECT T1.customer_name FROM customers AS T1 JOIN orders AS T2 JOIN order_items AS T3 JOIN products AS T4 ON T1.customer_id = T2.customer_id AND T2.order_id = T3.order_id AND T3.product_id = T4.product_id WHERE T4.product_name = "food" GROUP BY T1.customer_id HAVING count(*)  >=  1
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_name` VARCHAR(80),
`customer_details` VARCHAR(255)
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`invoice_date` DATETIME,
`invoice_details` VARCHAR(255)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(10) NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_name` VARCHAR(80),
`product_details` VARCHAR(255)
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`order_id` INTEGER NOT NULL,
`order_item_status` VARCHAR(10) NOT NULL,
`order_item_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

CREATE TABLE `Shipments` (
`shipment_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`shipment_tracking_number` VARCHAR(80),
`shipment_date` DATETIME,
`other_shipment_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` )
)

CREATE TABLE `Shipment_Items` (
`shipment_id` INTEGER NOT NULL,
`order_item_id` INTEGER NOT NULL,
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`shipment_id` ) REFERENCES `Shipments`(`shipment_id` )
)

### Question
List the names of customers who have once canceled the purchase of the product "food" (the item status is "Cancel").

### SQL
SELECT T1.customer_name FROM customers AS T1 JOIN orders AS T2 JOIN order_items AS T3 JOIN products AS T4 ON T1.customer_id = T2.customer_id AND T2.order_id = T3.order_id AND T3.product_id = T4.product_id WHERE T3.order_item_status = "Cancel" AND T4.product_name = "food" GROUP BY T1.customer_id HAVING count(*)  >=  1
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_name` VARCHAR(80),
`customer_details` VARCHAR(255)
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`invoice_date` DATETIME,
`invoice_details` VARCHAR(255)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(10) NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_name` VARCHAR(80),
`product_details` VARCHAR(255)
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`order_id` INTEGER NOT NULL,
`order_item_status` VARCHAR(10) NOT NULL,
`order_item_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

CREATE TABLE `Shipments` (
`shipment_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`shipment_tracking_number` VARCHAR(80),
`shipment_date` DATETIME,
`other_shipment_details` VARCHAR(255),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` )
)

CREATE TABLE `Shipment_Items` (
`shipment_id` INTEGER NOT NULL,
`order_item_id` INTEGER NOT NULL,
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`shipment_id` ) REFERENCES `Shipments`(`shipment_id` )
)

### Question
Which customers have ever canceled the purchase of the product "food" (the item status is "Cancel")?

### SQL
SELECT T1.customer_name FROM customers AS T1 JOIN orders AS T2 JOIN order_items AS T3 JOIN products AS T4 ON T1.customer_id = T2.customer_id AND T2.order_id = T3.order_id AND T3.product_id = T4.product_id WHERE T3.order_item_status = "Cancel" AND T4.product_name = "food" GROUP BY T1.customer_id HAVING count(*)  >=  1
### Database Schema
CREATE TABLE "architect" (
"id" text,
"name" text,
"nationality" text,
"gender" text,
primary key("id")
)

CREATE TABLE "bridge" (
"architect_id" int,
"id" int,
"name" text,
"location" text,
"length_meters" real,
"length_feet" real,
primary key("id"),
foreign key ("architect_id" ) references `architect`("id")
)

CREATE TABLE "mill" (
"architect_id" int,
"id" int,
"location" text,
"name" text,
"type" text,
"built_year" int,
"notes" text,
primary key ("id"),
foreign key ("architect_id" ) references `architect`("id")
)

### Question
How many architects are female?

### SQL
SELECT count(*) FROM architect WHERE gender  =  'female'
### Database Schema
CREATE TABLE "architect" (
"id" text,
"name" text,
"nationality" text,
"gender" text,
primary key("id")
)

CREATE TABLE "bridge" (
"architect_id" int,
"id" int,
"name" text,
"location" text,
"length_meters" real,
"length_feet" real,
primary key("id"),
foreign key ("architect_id" ) references `architect`("id")
)

CREATE TABLE "mill" (
"architect_id" int,
"id" int,
"location" text,
"name" text,
"type" text,
"built_year" int,
"notes" text,
primary key ("id"),
foreign key ("architect_id" ) references `architect`("id")
)

### Question
List the name, nationality and id of all male architects ordered by their names lexicographically.

### SQL
SELECT name ,  nationality ,  id FROM architect WHERE gender  =  'male' ORDER BY name
### Database Schema
CREATE TABLE "architect" (
"id" text,
"name" text,
"nationality" text,
"gender" text,
primary key("id")
)

CREATE TABLE "bridge" (
"architect_id" int,
"id" int,
"name" text,
"location" text,
"length_meters" real,
"length_feet" real,
primary key("id"),
foreign key ("architect_id" ) references `architect`("id")
)

CREATE TABLE "mill" (
"architect_id" int,
"id" int,
"location" text,
"name" text,
"type" text,
"built_year" int,
"notes" text,
primary key ("id"),
foreign key ("architect_id" ) references `architect`("id")
)

### Question
What is the maximum length in meters for the bridges and what are the architects' names?

### SQL
SELECT max(T1.length_meters) ,  T2.name FROM bridge AS T1 JOIN architect AS T2 ON T1.architect_id  =  T2.id
### Database Schema
CREATE TABLE "architect" (
"id" text,
"name" text,
"nationality" text,
"gender" text,
primary key("id")
)

CREATE TABLE "bridge" (
"architect_id" int,
"id" int,
"name" text,
"location" text,
"length_meters" real,
"length_feet" real,
primary key("id"),
foreign key ("architect_id" ) references `architect`("id")
)

CREATE TABLE "mill" (
"architect_id" int,
"id" int,
"location" text,
"name" text,
"type" text,
"built_year" int,
"notes" text,
primary key ("id"),
foreign key ("architect_id" ) references `architect`("id")
)

### Question
What is the average length in feet of the bridges?

### SQL
SELECT avg(length_feet) FROM bridge
### Database Schema
CREATE TABLE "architect" (
"id" text,
"name" text,
"nationality" text,
"gender" text,
primary key("id")
)

CREATE TABLE "bridge" (
"architect_id" int,
"id" int,
"name" text,
"location" text,
"length_meters" real,
"length_feet" real,
primary key("id"),
foreign key ("architect_id" ) references `architect`("id")
)

CREATE TABLE "mill" (
"architect_id" int,
"id" int,
"location" text,
"name" text,
"type" text,
"built_year" int,
"notes" text,
primary key ("id"),
foreign key ("architect_id" ) references `architect`("id")
)

### Question
What are the names and year of construction for the mills of 'Grondzeiler' type?

### SQL
SELECT name ,  built_year FROM mill WHERE TYPE  =  'Grondzeiler'
### Database Schema
CREATE TABLE "architect" (
"id" text,
"name" text,
"nationality" text,
"gender" text,
primary key("id")
)

CREATE TABLE "bridge" (
"architect_id" int,
"id" int,
"name" text,
"location" text,
"length_meters" real,
"length_feet" real,
primary key("id"),
foreign key ("architect_id" ) references `architect`("id")
)

CREATE TABLE "mill" (
"architect_id" int,
"id" int,
"location" text,
"name" text,
"type" text,
"built_year" int,
"notes" text,
primary key ("id"),
foreign key ("architect_id" ) references `architect`("id")
)

### Question
What are the distinct names and nationalities of the architects who have ever built a mill?

### SQL
SELECT DISTINCT T1.name ,  T1.nationality FROM architect AS T1 JOIN mill AS t2 ON T1.id  =  T2.architect_id
### Database Schema
CREATE TABLE "architect" (
"id" text,
"name" text,
"nationality" text,
"gender" text,
primary key("id")
)

CREATE TABLE "bridge" (
"architect_id" int,
"id" int,
"name" text,
"location" text,
"length_meters" real,
"length_feet" real,
primary key("id"),
foreign key ("architect_id" ) references `architect`("id")
)

CREATE TABLE "mill" (
"architect_id" int,
"id" int,
"location" text,
"name" text,
"type" text,
"built_year" int,
"notes" text,
primary key ("id"),
foreign key ("architect_id" ) references `architect`("id")
)

### Question
What are the names of the mills which are not located in 'Donceel'?

### SQL
SELECT name FROM mill WHERE LOCATION != 'Donceel'
### Database Schema
CREATE TABLE "architect" (
"id" text,
"name" text,
"nationality" text,
"gender" text,
primary key("id")
)

CREATE TABLE "bridge" (
"architect_id" int,
"id" int,
"name" text,
"location" text,
"length_meters" real,
"length_feet" real,
primary key("id"),
foreign key ("architect_id" ) references `architect`("id")
)

CREATE TABLE "mill" (
"architect_id" int,
"id" int,
"location" text,
"name" text,
"type" text,
"built_year" int,
"notes" text,
primary key ("id"),
foreign key ("architect_id" ) references `architect`("id")
)

### Question
What are the distinct types of mills that are built by American or Canadian architects?

### SQL
SELECT DISTINCT T1.type FROM mill AS T1 JOIN architect AS t2 ON T1.architect_id  =  T2.id WHERE T2.nationality  =  'American' OR T2.nationality  =  'Canadian'
### Database Schema
CREATE TABLE "architect" (
"id" text,
"name" text,
"nationality" text,
"gender" text,
primary key("id")
)

CREATE TABLE "bridge" (
"architect_id" int,
"id" int,
"name" text,
"location" text,
"length_meters" real,
"length_feet" real,
primary key("id"),
foreign key ("architect_id" ) references `architect`("id")
)

CREATE TABLE "mill" (
"architect_id" int,
"id" int,
"location" text,
"name" text,
"type" text,
"built_year" int,
"notes" text,
primary key ("id"),
foreign key ("architect_id" ) references `architect`("id")
)

### Question
What are the ids and names of the architects who built at least 3 bridges ?

### SQL
SELECT T1.id ,  T1.name FROM architect AS T1 JOIN bridge AS T2 ON T1.id  =  T2.architect_id GROUP BY T1.id HAVING count(*)  >=  3
### Database Schema
CREATE TABLE "architect" (
"id" text,
"name" text,
"nationality" text,
"gender" text,
primary key("id")
)

CREATE TABLE "bridge" (
"architect_id" int,
"id" int,
"name" text,
"location" text,
"length_meters" real,
"length_feet" real,
primary key("id"),
foreign key ("architect_id" ) references `architect`("id")
)

CREATE TABLE "mill" (
"architect_id" int,
"id" int,
"location" text,
"name" text,
"type" text,
"built_year" int,
"notes" text,
primary key ("id"),
foreign key ("architect_id" ) references `architect`("id")
)

### Question
What is the id, name and nationality of the architect who built most mills?

### SQL
SELECT T1.id ,  T1.name ,  T1.nationality FROM architect AS T1 JOIN mill AS T2 ON T1.id  =  T2.architect_id GROUP BY T1.id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "architect" (
"id" text,
"name" text,
"nationality" text,
"gender" text,
primary key("id")
)

CREATE TABLE "bridge" (
"architect_id" int,
"id" int,
"name" text,
"location" text,
"length_meters" real,
"length_feet" real,
primary key("id"),
foreign key ("architect_id" ) references `architect`("id")
)

CREATE TABLE "mill" (
"architect_id" int,
"id" int,
"location" text,
"name" text,
"type" text,
"built_year" int,
"notes" text,
primary key ("id"),
foreign key ("architect_id" ) references `architect`("id")
)

### Question
What are the ids, names and genders of the architects who built two bridges or one mill?

### SQL
SELECT T1.id ,  T1.name ,  T1.gender FROM architect AS T1 JOIN bridge AS T2 ON T1.id  =  T2.architect_id GROUP BY T1.id HAVING count(*)  =  2 UNION SELECT T1.id ,  T1.name ,  T1.gender FROM architect AS T1 JOIN mill AS T2 ON T1.id  =  T2.architect_id GROUP BY T1.id HAVING count(*)  =  1
### Database Schema
CREATE TABLE "architect" (
"id" text,
"name" text,
"nationality" text,
"gender" text,
primary key("id")
)

CREATE TABLE "bridge" (
"architect_id" int,
"id" int,
"name" text,
"location" text,
"length_meters" real,
"length_feet" real,
primary key("id"),
foreign key ("architect_id" ) references `architect`("id")
)

CREATE TABLE "mill" (
"architect_id" int,
"id" int,
"location" text,
"name" text,
"type" text,
"built_year" int,
"notes" text,
primary key ("id"),
foreign key ("architect_id" ) references `architect`("id")
)

### Question
What is the location of the bridge named 'Kolob Arch' or 'Rainbow Bridge'?

### SQL
SELECT LOCATION FROM bridge WHERE name  =  'Kolob Arch' OR name  =  'Rainbow Bridge'
### Database Schema
CREATE TABLE "architect" (
"id" text,
"name" text,
"nationality" text,
"gender" text,
primary key("id")
)

CREATE TABLE "bridge" (
"architect_id" int,
"id" int,
"name" text,
"location" text,
"length_meters" real,
"length_feet" real,
primary key("id"),
foreign key ("architect_id" ) references `architect`("id")
)

CREATE TABLE "mill" (
"architect_id" int,
"id" int,
"location" text,
"name" text,
"type" text,
"built_year" int,
"notes" text,
primary key ("id"),
foreign key ("architect_id" ) references `architect`("id")
)

### Question
Which of the mill names contains the french word 'Moulin'?

### SQL
SELECT name FROM mill WHERE name LIKE '%Moulin%'
### Database Schema
CREATE TABLE "architect" (
"id" text,
"name" text,
"nationality" text,
"gender" text,
primary key("id")
)

CREATE TABLE "bridge" (
"architect_id" int,
"id" int,
"name" text,
"location" text,
"length_meters" real,
"length_feet" real,
primary key("id"),
foreign key ("architect_id" ) references `architect`("id")
)

CREATE TABLE "mill" (
"architect_id" int,
"id" int,
"location" text,
"name" text,
"type" text,
"built_year" int,
"notes" text,
primary key ("id"),
foreign key ("architect_id" ) references `architect`("id")
)

### Question
What are the distinct name of the mills built by the architects who have also built a bridge longer than 80 meters?

### SQL
SELECT DISTINCT T1.name FROM mill AS T1 JOIN architect AS t2 ON T1.architect_id  =  T2.id JOIN bridge AS T3 ON T3.architect_id  =  T2.id WHERE T3.length_meters  >  80
### Database Schema
CREATE TABLE "architect" (
"id" text,
"name" text,
"nationality" text,
"gender" text,
primary key("id")
)

CREATE TABLE "bridge" (
"architect_id" int,
"id" int,
"name" text,
"location" text,
"length_meters" real,
"length_feet" real,
primary key("id"),
foreign key ("architect_id" ) references `architect`("id")
)

CREATE TABLE "mill" (
"architect_id" int,
"id" int,
"location" text,
"name" text,
"type" text,
"built_year" int,
"notes" text,
primary key ("id"),
foreign key ("architect_id" ) references `architect`("id")
)

### Question
What is the most common mill type, and how many are there?

### SQL
SELECT TYPE ,  count(*) FROM mill GROUP BY TYPE ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "architect" (
"id" text,
"name" text,
"nationality" text,
"gender" text,
primary key("id")
)

CREATE TABLE "bridge" (
"architect_id" int,
"id" int,
"name" text,
"location" text,
"length_meters" real,
"length_feet" real,
primary key("id"),
foreign key ("architect_id" ) references `architect`("id")
)

CREATE TABLE "mill" (
"architect_id" int,
"id" int,
"location" text,
"name" text,
"type" text,
"built_year" int,
"notes" text,
primary key ("id"),
foreign key ("architect_id" ) references `architect`("id")
)

### Question
How many architects haven't built a mill before year 1850?

### SQL
SELECT count(*) FROM architect WHERE id NOT IN ( SELECT architect_id FROM mill WHERE built_year  <  1850 );
### Database Schema
CREATE TABLE "architect" (
"id" text,
"name" text,
"nationality" text,
"gender" text,
primary key("id")
)

CREATE TABLE "bridge" (
"architect_id" int,
"id" int,
"name" text,
"location" text,
"length_meters" real,
"length_feet" real,
primary key("id"),
foreign key ("architect_id" ) references `architect`("id")
)

CREATE TABLE "mill" (
"architect_id" int,
"id" int,
"location" text,
"name" text,
"type" text,
"built_year" int,
"notes" text,
primary key ("id"),
foreign key ("architect_id" ) references `architect`("id")
)

### Question
show the name of all bridges that was designed by american archtect, and sort the result by the bridge feet length.

### SQL
SELECT t1.name FROM bridge AS t1 JOIN architect AS t2 ON t1.architect_id  =  t2.id WHERE t2.nationality  =  'American' ORDER BY t1.length_feet
### Database Schema
CREATE TABLE "book_club" (
"book_club_id" int,
"Year" int,
"Author_or_Editor" text,
"Book_Title" text,
"Publisher" text,
"Category" text,
"Result" text,
PRIMARY KEY ("book_club_id")
)

CREATE TABLE "movie" (
"movie_id" int,
"Title" text,
"Year" int,
"Director" text,
"Budget_million" real,
"Gross_worldwide" int,
PRIMARY KEY("movie_id")
)

CREATE TABLE "culture_company" (
"Company_name" text,
"Type" text,
"Incorporated_in" text,
"Group_Equity_Shareholding" real,
"book_club_id" text,
"movie_id" text,
PRIMARY KEY("Company_name"),
FOREIGN KEY ("book_club_id") REFERENCES "book_club"("book_club_id"),
FOREIGN KEY ("movie_id") REFERENCES "movie"("movie_id")
)

### Question
How many book clubs are there?

### SQL
SELECT count(*) FROM book_club
### Database Schema
CREATE TABLE "book_club" (
"book_club_id" int,
"Year" int,
"Author_or_Editor" text,
"Book_Title" text,
"Publisher" text,
"Category" text,
"Result" text,
PRIMARY KEY ("book_club_id")
)

CREATE TABLE "movie" (
"movie_id" int,
"Title" text,
"Year" int,
"Director" text,
"Budget_million" real,
"Gross_worldwide" int,
PRIMARY KEY("movie_id")
)

CREATE TABLE "culture_company" (
"Company_name" text,
"Type" text,
"Incorporated_in" text,
"Group_Equity_Shareholding" real,
"book_club_id" text,
"movie_id" text,
PRIMARY KEY("Company_name"),
FOREIGN KEY ("book_club_id") REFERENCES "book_club"("book_club_id"),
FOREIGN KEY ("movie_id") REFERENCES "movie"("movie_id")
)

### Question
Count the number of book clubs.

### SQL
SELECT count(*) FROM book_club
### Database Schema
CREATE TABLE "book_club" (
"book_club_id" int,
"Year" int,
"Author_or_Editor" text,
"Book_Title" text,
"Publisher" text,
"Category" text,
"Result" text,
PRIMARY KEY ("book_club_id")
)

CREATE TABLE "movie" (
"movie_id" int,
"Title" text,
"Year" int,
"Director" text,
"Budget_million" real,
"Gross_worldwide" int,
PRIMARY KEY("movie_id")
)

CREATE TABLE "culture_company" (
"Company_name" text,
"Type" text,
"Incorporated_in" text,
"Group_Equity_Shareholding" real,
"book_club_id" text,
"movie_id" text,
PRIMARY KEY("Company_name"),
FOREIGN KEY ("book_club_id") REFERENCES "book_club"("book_club_id"),
FOREIGN KEY ("movie_id") REFERENCES "movie"("movie_id")
)

### Question
show the titles, and authors or editors for all books made after the year 1989.

### SQL
SELECT book_title ,  author_or_editor FROM book_club WHERE YEAR  >  1989
### Database Schema
CREATE TABLE "book_club" (
"book_club_id" int,
"Year" int,
"Author_or_Editor" text,
"Book_Title" text,
"Publisher" text,
"Category" text,
"Result" text,
PRIMARY KEY ("book_club_id")
)

CREATE TABLE "movie" (
"movie_id" int,
"Title" text,
"Year" int,
"Director" text,
"Budget_million" real,
"Gross_worldwide" int,
PRIMARY KEY("movie_id")
)

CREATE TABLE "culture_company" (
"Company_name" text,
"Type" text,
"Incorporated_in" text,
"Group_Equity_Shareholding" real,
"book_club_id" text,
"movie_id" text,
PRIMARY KEY("Company_name"),
FOREIGN KEY ("book_club_id") REFERENCES "book_club"("book_club_id"),
FOREIGN KEY ("movie_id") REFERENCES "movie"("movie_id")
)

### Question
What are the titles and authors or editors that correspond to books made after 1989?

### SQL
SELECT book_title ,  author_or_editor FROM book_club WHERE YEAR  >  1989
### Database Schema
CREATE TABLE "book_club" (
"book_club_id" int,
"Year" int,
"Author_or_Editor" text,
"Book_Title" text,
"Publisher" text,
"Category" text,
"Result" text,
PRIMARY KEY ("book_club_id")
)

CREATE TABLE "movie" (
"movie_id" int,
"Title" text,
"Year" int,
"Director" text,
"Budget_million" real,
"Gross_worldwide" int,
PRIMARY KEY("movie_id")
)

CREATE TABLE "culture_company" (
"Company_name" text,
"Type" text,
"Incorporated_in" text,
"Group_Equity_Shareholding" real,
"book_club_id" text,
"movie_id" text,
PRIMARY KEY("Company_name"),
FOREIGN KEY ("book_club_id") REFERENCES "book_club"("book_club_id"),
FOREIGN KEY ("movie_id") REFERENCES "movie"("movie_id")
)

### Question
Show all distinct publishers for books.

### SQL
SELECT DISTINCT publisher FROM book_club
### Database Schema
CREATE TABLE "book_club" (
"book_club_id" int,
"Year" int,
"Author_or_Editor" text,
"Book_Title" text,
"Publisher" text,
"Category" text,
"Result" text,
PRIMARY KEY ("book_club_id")
)

CREATE TABLE "movie" (
"movie_id" int,
"Title" text,
"Year" int,
"Director" text,
"Budget_million" real,
"Gross_worldwide" int,
PRIMARY KEY("movie_id")
)

CREATE TABLE "culture_company" (
"Company_name" text,
"Type" text,
"Incorporated_in" text,
"Group_Equity_Shareholding" real,
"book_club_id" text,
"movie_id" text,
PRIMARY KEY("Company_name"),
FOREIGN KEY ("book_club_id") REFERENCES "book_club"("book_club_id"),
FOREIGN KEY ("movie_id") REFERENCES "movie"("movie_id")
)

### Question
What are all the different book publishers?

### SQL
SELECT DISTINCT publisher FROM book_club
### Database Schema
CREATE TABLE "book_club" (
"book_club_id" int,
"Year" int,
"Author_or_Editor" text,
"Book_Title" text,
"Publisher" text,
"Category" text,
"Result" text,
PRIMARY KEY ("book_club_id")
)

CREATE TABLE "movie" (
"movie_id" int,
"Title" text,
"Year" int,
"Director" text,
"Budget_million" real,
"Gross_worldwide" int,
PRIMARY KEY("movie_id")
)

CREATE TABLE "culture_company" (
"Company_name" text,
"Type" text,
"Incorporated_in" text,
"Group_Equity_Shareholding" real,
"book_club_id" text,
"movie_id" text,
PRIMARY KEY("Company_name"),
FOREIGN KEY ("book_club_id") REFERENCES "book_club"("book_club_id"),
FOREIGN KEY ("movie_id") REFERENCES "movie"("movie_id")
)

### Question
Show the years, book titles, and publishers for all books, in descending order by year.

### SQL
SELECT YEAR ,  book_title ,  publisher FROM book_club ORDER BY YEAR DESC