lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE routes (
   rid integer PRIMARY KEY,
   dst_apid integer,              -- Id of destination airport
   dst_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the destination airport
   src_apid bigint,               -- Id of source airport
   src_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the source airport
   alid bigint,                   -- Id of airline
   airline varchar(4),            -- 2-letter (IATA) or 3-letter (ICAO) code of the airline
   codeshare text,                -- "Y" if this flight is a codeshare (that is, not operated by 
                                  -- Airline, but another carrier), empty otherwise
   FOREIGN KEY(dst_apid) REFERENCES airports(apid),
   FOREIGN KEY(src_apid) REFERENCES airports(apid),
   FOREIGN KEY(alid)     REFERENCES airlines(alid)
)

CREATE TABLE airports (
   apid integer PRIMARY KEY,      -- Id of the airport
   name text NOT NULL,            -- Name of airport
   city text,                     -- Main city served by airport
   country text,                  -- Country or territory where airport is located
   x real,                        -- Latitude of airport: Decimal degrees, usually to six
                                  -- significant digits. Negative is South, positive is North
   y real,                        -- Longitude of airport: Decimal degrees, usually to six 
                                  -- significant digits. Negative is West, positive is East
   elevation bigint,              -- Altitude of airport measured in feets
   iata character varchar(3),     -- 3-letter IATA code. empty or null if not assigned/unknown
   icao character varchar(4)      -- 4-letter ICAO code. empty or null if not assigned
   
)

CREATE TABLE airlines (
   alid integer PRIMARY KEY,      -- Id of the airline
   name text,                     -- Name of the airline
   iata varchar(2),               -- 2-letter IATA code. empty or null if not assigned/unknown 
   icao varchar(3),               -- 3-letter ICAO code. empty or null if not assigned
   callsign text,                 -- Airline callsign
   country text,                  -- Country or territory where airline is incorporated
   active varchar(2)              -- "Y" if the airline is or has until recently been operational,
)

### Question
Find the name of airline which runs the most number of routes.

### SQL
SELECT T1.name FROM airlines AS T1 JOIN routes AS T2 ON T1.alid  =  T2.alid GROUP BY T1.name ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE routes (
   rid integer PRIMARY KEY,
   dst_apid integer,              -- Id of destination airport
   dst_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the destination airport
   src_apid bigint,               -- Id of source airport
   src_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the source airport
   alid bigint,                   -- Id of airline
   airline varchar(4),            -- 2-letter (IATA) or 3-letter (ICAO) code of the airline
   codeshare text,                -- "Y" if this flight is a codeshare (that is, not operated by 
                                  -- Airline, but another carrier), empty otherwise
   FOREIGN KEY(dst_apid) REFERENCES airports(apid),
   FOREIGN KEY(src_apid) REFERENCES airports(apid),
   FOREIGN KEY(alid)     REFERENCES airlines(alid)
)

CREATE TABLE airports (
   apid integer PRIMARY KEY,      -- Id of the airport
   name text NOT NULL,            -- Name of airport
   city text,                     -- Main city served by airport
   country text,                  -- Country or territory where airport is located
   x real,                        -- Latitude of airport: Decimal degrees, usually to six
                                  -- significant digits. Negative is South, positive is North
   y real,                        -- Longitude of airport: Decimal degrees, usually to six 
                                  -- significant digits. Negative is West, positive is East
   elevation bigint,              -- Altitude of airport measured in feets
   iata character varchar(3),     -- 3-letter IATA code. empty or null if not assigned/unknown
   icao character varchar(4)      -- 4-letter ICAO code. empty or null if not assigned
   
)

CREATE TABLE airlines (
   alid integer PRIMARY KEY,      -- Id of the airline
   name text,                     -- Name of the airline
   iata varchar(2),               -- 2-letter IATA code. empty or null if not assigned/unknown 
   icao varchar(3),               -- 3-letter ICAO code. empty or null if not assigned
   callsign text,                 -- Airline callsign
   country text,                  -- Country or territory where airline is incorporated
   active varchar(2)              -- "Y" if the airline is or has until recently been operational,
)

### Question
What is the name of the airline with the most routes?

### SQL
SELECT T1.name FROM airlines AS T1 JOIN routes AS T2 ON T1.alid  =  T2.alid GROUP BY T1.name ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE routes (
   rid integer PRIMARY KEY,
   dst_apid integer,              -- Id of destination airport
   dst_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the destination airport
   src_apid bigint,               -- Id of source airport
   src_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the source airport
   alid bigint,                   -- Id of airline
   airline varchar(4),            -- 2-letter (IATA) or 3-letter (ICAO) code of the airline
   codeshare text,                -- "Y" if this flight is a codeshare (that is, not operated by 
                                  -- Airline, but another carrier), empty otherwise
   FOREIGN KEY(dst_apid) REFERENCES airports(apid),
   FOREIGN KEY(src_apid) REFERENCES airports(apid),
   FOREIGN KEY(alid)     REFERENCES airlines(alid)
)

CREATE TABLE airports (
   apid integer PRIMARY KEY,      -- Id of the airport
   name text NOT NULL,            -- Name of airport
   city text,                     -- Main city served by airport
   country text,                  -- Country or territory where airport is located
   x real,                        -- Latitude of airport: Decimal degrees, usually to six
                                  -- significant digits. Negative is South, positive is North
   y real,                        -- Longitude of airport: Decimal degrees, usually to six 
                                  -- significant digits. Negative is West, positive is East
   elevation bigint,              -- Altitude of airport measured in feets
   iata character varchar(3),     -- 3-letter IATA code. empty or null if not assigned/unknown
   icao character varchar(4)      -- 4-letter ICAO code. empty or null if not assigned
   
)

CREATE TABLE airlines (
   alid integer PRIMARY KEY,      -- Id of the airline
   name text,                     -- Name of the airline
   iata varchar(2),               -- 2-letter IATA code. empty or null if not assigned/unknown 
   icao varchar(3),               -- 3-letter ICAO code. empty or null if not assigned
   callsign text,                 -- Airline callsign
   country text,                  -- Country or territory where airline is incorporated
   active varchar(2)              -- "Y" if the airline is or has until recently been operational,
)

### Question
Find the busiest source airport that runs most number of routes in China.

### SQL
SELECT T1.name FROM airports AS T1 JOIN routes AS T2 ON T1.apid  =  T2.src_apid WHERE T1.country  =  'China' GROUP BY T1.name ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE routes (
   rid integer PRIMARY KEY,
   dst_apid integer,              -- Id of destination airport
   dst_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the destination airport
   src_apid bigint,               -- Id of source airport
   src_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the source airport
   alid bigint,                   -- Id of airline
   airline varchar(4),            -- 2-letter (IATA) or 3-letter (ICAO) code of the airline
   codeshare text,                -- "Y" if this flight is a codeshare (that is, not operated by 
                                  -- Airline, but another carrier), empty otherwise
   FOREIGN KEY(dst_apid) REFERENCES airports(apid),
   FOREIGN KEY(src_apid) REFERENCES airports(apid),
   FOREIGN KEY(alid)     REFERENCES airlines(alid)
)

CREATE TABLE airports (
   apid integer PRIMARY KEY,      -- Id of the airport
   name text NOT NULL,            -- Name of airport
   city text,                     -- Main city served by airport
   country text,                  -- Country or territory where airport is located
   x real,                        -- Latitude of airport: Decimal degrees, usually to six
                                  -- significant digits. Negative is South, positive is North
   y real,                        -- Longitude of airport: Decimal degrees, usually to six 
                                  -- significant digits. Negative is West, positive is East
   elevation bigint,              -- Altitude of airport measured in feets
   iata character varchar(3),     -- 3-letter IATA code. empty or null if not assigned/unknown
   icao character varchar(4)      -- 4-letter ICAO code. empty or null if not assigned
   
)

CREATE TABLE airlines (
   alid integer PRIMARY KEY,      -- Id of the airline
   name text,                     -- Name of the airline
   iata varchar(2),               -- 2-letter IATA code. empty or null if not assigned/unknown 
   icao varchar(3),               -- 3-letter ICAO code. empty or null if not assigned
   callsign text,                 -- Airline callsign
   country text,                  -- Country or territory where airline is incorporated
   active varchar(2)              -- "Y" if the airline is or has until recently been operational,
)

### Question
What is the name of the airport with the most number of routes that start in China?

### SQL
SELECT T1.name FROM airports AS T1 JOIN routes AS T2 ON T1.apid  =  T2.src_apid WHERE T1.country  =  'China' GROUP BY T1.name ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE routes (
   rid integer PRIMARY KEY,
   dst_apid integer,              -- Id of destination airport
   dst_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the destination airport
   src_apid bigint,               -- Id of source airport
   src_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the source airport
   alid bigint,                   -- Id of airline
   airline varchar(4),            -- 2-letter (IATA) or 3-letter (ICAO) code of the airline
   codeshare text,                -- "Y" if this flight is a codeshare (that is, not operated by 
                                  -- Airline, but another carrier), empty otherwise
   FOREIGN KEY(dst_apid) REFERENCES airports(apid),
   FOREIGN KEY(src_apid) REFERENCES airports(apid),
   FOREIGN KEY(alid)     REFERENCES airlines(alid)
)

CREATE TABLE airports (
   apid integer PRIMARY KEY,      -- Id of the airport
   name text NOT NULL,            -- Name of airport
   city text,                     -- Main city served by airport
   country text,                  -- Country or territory where airport is located
   x real,                        -- Latitude of airport: Decimal degrees, usually to six
                                  -- significant digits. Negative is South, positive is North
   y real,                        -- Longitude of airport: Decimal degrees, usually to six 
                                  -- significant digits. Negative is West, positive is East
   elevation bigint,              -- Altitude of airport measured in feets
   iata character varchar(3),     -- 3-letter IATA code. empty or null if not assigned/unknown
   icao character varchar(4)      -- 4-letter ICAO code. empty or null if not assigned
   
)

CREATE TABLE airlines (
   alid integer PRIMARY KEY,      -- Id of the airline
   name text,                     -- Name of the airline
   iata varchar(2),               -- 2-letter IATA code. empty or null if not assigned/unknown 
   icao varchar(3),               -- 3-letter ICAO code. empty or null if not assigned
   callsign text,                 -- Airline callsign
   country text,                  -- Country or territory where airline is incorporated
   active varchar(2)              -- "Y" if the airline is or has until recently been operational,
)

### Question
Find the busiest destination airport that runs most number of routes in China.

### SQL
SELECT T1.name FROM airports AS T1 JOIN routes AS T2 ON T1.apid  =  T2.dst_apid WHERE T1.country  =  'China' GROUP BY T1.name ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE routes (
   rid integer PRIMARY KEY,
   dst_apid integer,              -- Id of destination airport
   dst_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the destination airport
   src_apid bigint,               -- Id of source airport
   src_ap varchar(4),             -- 3-letter (IATA) or 4-letter (ICAO) code of the source airport
   alid bigint,                   -- Id of airline
   airline varchar(4),            -- 2-letter (IATA) or 3-letter (ICAO) code of the airline
   codeshare text,                -- "Y" if this flight is a codeshare (that is, not operated by 
                                  -- Airline, but another carrier), empty otherwise
   FOREIGN KEY(dst_apid) REFERENCES airports(apid),
   FOREIGN KEY(src_apid) REFERENCES airports(apid),
   FOREIGN KEY(alid)     REFERENCES airlines(alid)
)

CREATE TABLE airports (
   apid integer PRIMARY KEY,      -- Id of the airport
   name text NOT NULL,            -- Name of airport
   city text,                     -- Main city served by airport
   country text,                  -- Country or territory where airport is located
   x real,                        -- Latitude of airport: Decimal degrees, usually to six
                                  -- significant digits. Negative is South, positive is North
   y real,                        -- Longitude of airport: Decimal degrees, usually to six 
                                  -- significant digits. Negative is West, positive is East
   elevation bigint,              -- Altitude of airport measured in feets
   iata character varchar(3),     -- 3-letter IATA code. empty or null if not assigned/unknown
   icao character varchar(4)      -- 4-letter ICAO code. empty or null if not assigned
   
)

CREATE TABLE airlines (
   alid integer PRIMARY KEY,      -- Id of the airline
   name text,                     -- Name of the airline
   iata varchar(2),               -- 2-letter IATA code. empty or null if not assigned/unknown 
   icao varchar(3),               -- 3-letter ICAO code. empty or null if not assigned
   callsign text,                 -- Airline callsign
   country text,                  -- Country or territory where airline is incorporated
   active varchar(2)              -- "Y" if the airline is or has until recently been operational,
)

### Question
What is the name of the airport that is the destination of the most number of routes that start in China?

### SQL
SELECT T1.name FROM airports AS T1 JOIN routes AS T2 ON T1.apid  =  T2.dst_apid WHERE T1.country  =  'China' GROUP BY T1.name 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 id of the most recent order?

### SQL
SELECT order_id FROM orders ORDER BY date_order_placed 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 id of the order made most recently.

### SQL
SELECT order_id FROM orders ORDER BY date_order_placed 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 are the order id and customer id of the oldest order?

### SQL
SELECT order_id ,  customer_id FROM orders ORDER BY date_order_placed 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 order id and customer id associated with the oldest order.

### SQL
SELECT order_id ,  customer_id FROM orders ORDER BY date_order_placed 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 id of the order whose shipment tracking number is "3452".

### SQL
SELECT order_id FROM shipments WHERE shipment_tracking_number = "3452"
### 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's shipment tracking number is "3452"? Give me the id of the order.

### SQL
SELECT order_id FROM shipments WHERE shipment_tracking_number = "3452"
### 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 ids of all the order items whose product id is 11.

### SQL
SELECT order_item_id FROM order_items WHERE product_id = 11
### 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 all the order items whose product id is 11. What are the order item ids?

### SQL
SELECT order_item_id FROM order_items WHERE product_id = 11
### 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 name of all the distinct customers who have orders with status "Packing".

### SQL
SELECT DISTINCT T1.customer_name FROM customers AS T1 JOIN orders AS T2 ON T1.customer_id = T2.customer_id WHERE T2.order_status = "Packing"
### 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 orders with status "Packing"? Give me the customer names.

### SQL
SELECT DISTINCT T1.customer_name FROM customers AS T1 JOIN orders AS T2 ON T1.customer_id = T2.customer_id WHERE T2.order_status = "Packing"
### 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 details of all the distinct customers who have orders with status "On Road".

### SQL
SELECT DISTINCT T1.customer_details FROM customers AS T1 JOIN orders AS T2 ON T1.customer_id = T2.customer_id WHERE T2.order_status = "On Road"
### 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 customers who have orders with status "On Road"? Give me the customer details?

### SQL
SELECT DISTINCT T1.customer_details FROM customers AS T1 JOIN orders AS T2 ON T1.customer_id = T2.customer_id WHERE T2.order_status = "On Road"
### 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 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
Which customer made the most orders? Find the customer name.

### 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
What is the customer id of the customer who has the most orders?

### SQL
SELECT T1.customer_id 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 id of the customer who made the most orders.

### SQL
SELECT T1.customer_id 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
Give me a list of id and status of orders which belong to the customer named "Jeramie".

### SQL
SELECT T2.order_id ,  T2.order_status FROM customers AS T1 JOIN orders AS T2 ON T1.customer_id = T2.customer_id WHERE T1.customer_name = "Jeramie"
### 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 orders are made by the customer named "Jeramie"? Give me the order ids and status.

### SQL
SELECT T2.order_id ,  T2.order_status FROM customers AS T1 JOIN orders AS T2 ON T1.customer_id = T2.customer_id WHERE T1.customer_name = "Jeramie"