lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15),
`product_name` VARCHAR(80),
`product_price` DOUBLE NULL
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method_code` VARCHAR(15),
`customer_number` VARCHAR(20),
`customer_name` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80)
)

CREATE TABLE `Contacts` (
`contact_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(50),
`contact_phone` VARCHAR(80)
)

CREATE TABLE `Customer_Address_History` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_from` DATETIME NOT NULL,
`date_to` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_date` DATETIME NOT NULL,
`order_status_code` VARCHAR(15),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER NOT NULL ,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(80),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Show names and phones of customers who do not have address information.

### SQL
SELECT customer_name ,  customer_phone FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM customer_address_history)
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15),
`product_name` VARCHAR(80),
`product_price` DOUBLE NULL
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method_code` VARCHAR(15),
`customer_number` VARCHAR(20),
`customer_name` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80)
)

CREATE TABLE `Contacts` (
`contact_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(50),
`contact_phone` VARCHAR(80)
)

CREATE TABLE `Customer_Address_History` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_from` DATETIME NOT NULL,
`date_to` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_date` DATETIME NOT NULL,
`order_status_code` VARCHAR(15),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER NOT NULL ,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(80),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Show the name of the customer who has the most orders.

### SQL
SELECT T1.customer_name FROM customers AS T1 JOIN customer_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 `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15),
`product_name` VARCHAR(80),
`product_price` DOUBLE NULL
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method_code` VARCHAR(15),
`customer_number` VARCHAR(20),
`customer_name` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80)
)

CREATE TABLE `Contacts` (
`contact_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(50),
`contact_phone` VARCHAR(80)
)

CREATE TABLE `Customer_Address_History` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_from` DATETIME NOT NULL,
`date_to` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_date` DATETIME NOT NULL,
`order_status_code` VARCHAR(15),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER NOT NULL ,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(80),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Show the product type codes which have at least two products.

### SQL
SELECT product_type_code FROM products GROUP BY product_type_code HAVING count(*)  >=  2
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15),
`product_name` VARCHAR(80),
`product_price` DOUBLE NULL
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method_code` VARCHAR(15),
`customer_number` VARCHAR(20),
`customer_name` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80)
)

CREATE TABLE `Contacts` (
`contact_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(50),
`contact_phone` VARCHAR(80)
)

CREATE TABLE `Customer_Address_History` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_from` DATETIME NOT NULL,
`date_to` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_date` DATETIME NOT NULL,
`order_status_code` VARCHAR(15),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER NOT NULL ,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(80),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Show the names of customers who have both an order in completed status and an order in part status.

### SQL
SELECT T1.customer_name FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.order_status_code  =  'Completed' INTERSECT SELECT T1.customer_name FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.order_status_code  =  'Part'
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15),
`product_name` VARCHAR(80),
`product_price` DOUBLE NULL
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method_code` VARCHAR(15),
`customer_number` VARCHAR(20),
`customer_name` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80)
)

CREATE TABLE `Contacts` (
`contact_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(50),
`contact_phone` VARCHAR(80)
)

CREATE TABLE `Customer_Address_History` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_from` DATETIME NOT NULL,
`date_to` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_date` DATETIME NOT NULL,
`order_status_code` VARCHAR(15),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER NOT NULL ,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(80),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Show the name, phone, and payment method code for all customers in descending order of customer number.

### SQL
SELECT customer_name ,  customer_phone ,  payment_method_code FROM customers ORDER BY customer_number DESC
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15),
`product_name` VARCHAR(80),
`product_price` DOUBLE NULL
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method_code` VARCHAR(15),
`customer_number` VARCHAR(20),
`customer_name` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80)
)

CREATE TABLE `Contacts` (
`contact_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(50),
`contact_phone` VARCHAR(80)
)

CREATE TABLE `Customer_Address_History` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_from` DATETIME NOT NULL,
`date_to` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_date` DATETIME NOT NULL,
`order_status_code` VARCHAR(15),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER NOT NULL ,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(80),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Show the product name and total order quantity for each product.

### SQL
SELECT T1.product_name ,  sum(T2.order_quantity) FROM products AS T1 JOIN order_items AS T2 ON T1.product_id  =  T2.product_id GROUP BY T1.product_id
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15),
`product_name` VARCHAR(80),
`product_price` DOUBLE NULL
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method_code` VARCHAR(15),
`customer_number` VARCHAR(20),
`customer_name` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80)
)

CREATE TABLE `Contacts` (
`contact_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(50),
`contact_phone` VARCHAR(80)
)

CREATE TABLE `Customer_Address_History` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_from` DATETIME NOT NULL,
`date_to` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_date` DATETIME NOT NULL,
`order_status_code` VARCHAR(15),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER NOT NULL ,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(80),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Show the minimum, maximum, average price for all products.

### SQL
SELECT min(product_price) ,  max(product_price) ,  avg(product_price) FROM products
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15),
`product_name` VARCHAR(80),
`product_price` DOUBLE NULL
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method_code` VARCHAR(15),
`customer_number` VARCHAR(20),
`customer_name` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80)
)

CREATE TABLE `Contacts` (
`contact_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(50),
`contact_phone` VARCHAR(80)
)

CREATE TABLE `Customer_Address_History` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_from` DATETIME NOT NULL,
`date_to` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_date` DATETIME NOT NULL,
`order_status_code` VARCHAR(15),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER NOT NULL ,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(80),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
How many products have a price higher than the average?

### SQL
SELECT count(*) FROM products WHERE product_price  >  (SELECT avg(product_price) FROM products)
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15),
`product_name` VARCHAR(80),
`product_price` DOUBLE NULL
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method_code` VARCHAR(15),
`customer_number` VARCHAR(20),
`customer_name` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80)
)

CREATE TABLE `Contacts` (
`contact_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(50),
`contact_phone` VARCHAR(80)
)

CREATE TABLE `Customer_Address_History` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_from` DATETIME NOT NULL,
`date_to` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_date` DATETIME NOT NULL,
`order_status_code` VARCHAR(15),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER NOT NULL ,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(80),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Show the customer name, customer address city, date from, and date to for each customer address history.

### SQL
SELECT T2.customer_name ,  T3.city ,  T1.date_from ,  T1.date_to FROM customer_address_history AS T1 JOIN customers AS T2 ON T1.customer_id  =  T2.customer_id JOIN addresses AS T3 ON T1.address_id  =  T3.address_id
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15),
`product_name` VARCHAR(80),
`product_price` DOUBLE NULL
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method_code` VARCHAR(15),
`customer_number` VARCHAR(20),
`customer_name` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80)
)

CREATE TABLE `Contacts` (
`contact_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(50),
`contact_phone` VARCHAR(80)
)

CREATE TABLE `Customer_Address_History` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_from` DATETIME NOT NULL,
`date_to` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_date` DATETIME NOT NULL,
`order_status_code` VARCHAR(15),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER NOT NULL ,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(80),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Show the names of customers who use Credit Card payment method and have more than 2 orders.

### SQL
SELECT T1.customer_name FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id WHERE T1.payment_method_code  =  'Credit Card' GROUP BY T1.customer_id HAVING count(*)  >  2
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15),
`product_name` VARCHAR(80),
`product_price` DOUBLE NULL
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method_code` VARCHAR(15),
`customer_number` VARCHAR(20),
`customer_name` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80)
)

CREATE TABLE `Contacts` (
`contact_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(50),
`contact_phone` VARCHAR(80)
)

CREATE TABLE `Customer_Address_History` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_from` DATETIME NOT NULL,
`date_to` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_date` DATETIME NOT NULL,
`order_status_code` VARCHAR(15),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER NOT NULL ,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(80),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
What are the name and phone of the customer with the most ordered product quantity?

### SQL
SELECT  T1.customer_name ,  T1.customer_phone FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id JOIN order_items AS T3 ON T3.order_id  =  T2.order_id GROUP BY T1.customer_id ORDER BY sum(T3.order_quantity) DESC LIMIT 1
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15),
`product_name` VARCHAR(80),
`product_price` DOUBLE NULL
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method_code` VARCHAR(15),
`customer_number` VARCHAR(20),
`customer_name` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80)
)

CREATE TABLE `Contacts` (
`contact_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(50),
`contact_phone` VARCHAR(80)
)

CREATE TABLE `Customer_Address_History` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_from` DATETIME NOT NULL,
`date_to` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_date` DATETIME NOT NULL,
`order_status_code` VARCHAR(15),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER NOT NULL ,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(80),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Show the product type and name for the products with price higher than 1000 or lower than 500.

### SQL
SELECT product_type_code ,  product_name FROM products WHERE product_price  >  1000 OR product_price  <  500
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
Find the name of dorms only for female (F gender).

### SQL
SELECT dorm_name FROM dorm WHERE gender  =  'F'
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
What are the names of the all-female dorms?

### SQL
SELECT dorm_name FROM dorm WHERE gender  =  'F'
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
Find the name of dorms that can accommodate more than 300 students.

### SQL
SELECT dorm_name FROM dorm WHERE student_capacity  >  300
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
What are the names of all the dorms that can accomdate more than 300 students?

### SQL
SELECT dorm_name FROM dorm WHERE student_capacity  >  300
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
How many female students (sex is F) whose age is below 25?

### SQL
SELECT count(*) FROM student WHERE sex  =  'F' AND age  <  25
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
How many girl students who are younger than 25?

### SQL
SELECT count(*) FROM student WHERE sex  =  'F' AND age  <  25
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
Find the first name of students who is older than 20.

### SQL
SELECT fname FROM student WHERE age  >  20
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
What are the first names of all students who are older than 20?

### SQL
SELECT fname FROM student WHERE age  >  20
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
Find the first name of students living in city PHL whose age is between 20 and 25.

### SQL
SELECT fname FROM student WHERE city_code  =  'PHL' AND age BETWEEN 20 AND 25
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
What is the first name of the students who are in age 20 to 25 and living in PHL city?

### SQL
SELECT fname FROM student WHERE city_code  =  'PHL' AND age BETWEEN 20 AND 25
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
How many dorms are there?

### SQL
SELECT count(*) FROM dorm
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
How many dorms are in the database?

### SQL
SELECT count(*) FROM dorm
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
Find the number of distinct amenities.

### SQL
SELECT count(*) FROM dorm_amenity
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
How many diffrent dorm amenities are there?

### SQL
SELECT count(*) FROM dorm_amenity
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
Find the total capacity of all dorms.

### SQL
SELECT sum(student_capacity) FROM dorm
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
What is the total student capacity of all dorms?

### SQL
SELECT sum(student_capacity) FROM dorm
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
How many students are there?

### SQL
SELECT count(*) FROM student
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
How many students exist?

### SQL
SELECT count(*) FROM student
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
Find the average age of all students living in the each city.

### SQL
SELECT avg(age) ,  city_code FROM student GROUP BY city_code
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
What is the average age for each city and what are those cities?

### SQL
SELECT avg(age) ,  city_code FROM student GROUP BY city_code
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
Find the average and total capacity of dorms for the students with gender X.

### SQL
SELECT avg(student_capacity) ,  sum(student_capacity) FROM dorm WHERE gender  =  'X'
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
What is the average and total capacity for all dorms who are of gender X?

### SQL
SELECT avg(student_capacity) ,  sum(student_capacity) FROM dorm WHERE gender  =  'X'
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
Find the number of dorms that have some amenity.

### SQL
SELECT count(DISTINCT dormid) FROM has_amenity
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
How many dorms have amenities?

### SQL
SELECT count(DISTINCT dormid) FROM has_amenity