lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE `Premises` (
`premise_id` INTEGER PRIMARY KEY,
`premises_type` VARCHAR(15) NOT NULL,
`premise_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15) NOT NULL,
`product_name` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_login` VARCHAR(80),
`customer_password` VARCHAR(10)
)

CREATE TABLE `Mailshot_Campaigns` (
`mailshot_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15),
`mailshot_name` VARCHAR(80),
`mailshot_start_date` DATETIME,
`mailshot_end_date` DATETIME
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`premise_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type_code` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`premise_id` ) REFERENCES `Premises`(`premise_id` )
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status_code` VARCHAR(15) NOT NULL,
`shipping_method_code` VARCHAR(15) NOT NULL,
`order_placed_datetime` DATETIME NOT NULL,
`order_delivered_datetime` DATETIME,
`order_shipping_charges` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Mailshot_Customers` (
`mailshot_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`outcome_code` VARCHAR(15) NOT NULL,
`mailshot_customer_date` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`mailshot_id` ) REFERENCES `Mailshot_Campaigns`(`mailshot_id` )
)

CREATE TABLE `Order_Items` (
`item_id` INTEGER NOT NULL ,
`order_item_status_code` VARCHAR(15) NOT NULL,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`item_status_code` VARCHAR(15),
`item_delivered_datetime` DATETIME,
`item_order_quantity` VARCHAR(80),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
How many premises are there?

### SQL
SELECT count(*) FROM premises
### Database Schema
CREATE TABLE `Premises` (
`premise_id` INTEGER PRIMARY KEY,
`premises_type` VARCHAR(15) NOT NULL,
`premise_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15) NOT NULL,
`product_name` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_login` VARCHAR(80),
`customer_password` VARCHAR(10)
)

CREATE TABLE `Mailshot_Campaigns` (
`mailshot_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15),
`mailshot_name` VARCHAR(80),
`mailshot_start_date` DATETIME,
`mailshot_end_date` DATETIME
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`premise_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type_code` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`premise_id` ) REFERENCES `Premises`(`premise_id` )
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status_code` VARCHAR(15) NOT NULL,
`shipping_method_code` VARCHAR(15) NOT NULL,
`order_placed_datetime` DATETIME NOT NULL,
`order_delivered_datetime` DATETIME,
`order_shipping_charges` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Mailshot_Customers` (
`mailshot_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`outcome_code` VARCHAR(15) NOT NULL,
`mailshot_customer_date` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`mailshot_id` ) REFERENCES `Mailshot_Campaigns`(`mailshot_id` )
)

CREATE TABLE `Order_Items` (
`item_id` INTEGER NOT NULL ,
`order_item_status_code` VARCHAR(15) NOT NULL,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`item_status_code` VARCHAR(15),
`item_delivered_datetime` DATETIME,
`item_order_quantity` VARCHAR(80),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
What are all the distinct premise types?

### SQL
SELECT DISTINCT premises_type FROM premises
### Database Schema
CREATE TABLE `Premises` (
`premise_id` INTEGER PRIMARY KEY,
`premises_type` VARCHAR(15) NOT NULL,
`premise_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15) NOT NULL,
`product_name` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_login` VARCHAR(80),
`customer_password` VARCHAR(10)
)

CREATE TABLE `Mailshot_Campaigns` (
`mailshot_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15),
`mailshot_name` VARCHAR(80),
`mailshot_start_date` DATETIME,
`mailshot_end_date` DATETIME
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`premise_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type_code` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`premise_id` ) REFERENCES `Premises`(`premise_id` )
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status_code` VARCHAR(15) NOT NULL,
`shipping_method_code` VARCHAR(15) NOT NULL,
`order_placed_datetime` DATETIME NOT NULL,
`order_delivered_datetime` DATETIME,
`order_shipping_charges` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Mailshot_Customers` (
`mailshot_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`outcome_code` VARCHAR(15) NOT NULL,
`mailshot_customer_date` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`mailshot_id` ) REFERENCES `Mailshot_Campaigns`(`mailshot_id` )
)

CREATE TABLE `Order_Items` (
`item_id` INTEGER NOT NULL ,
`order_item_status_code` VARCHAR(15) NOT NULL,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`item_status_code` VARCHAR(15),
`item_delivered_datetime` DATETIME,
`item_order_quantity` VARCHAR(80),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Find the types and details for all premises and order by the premise type.

### SQL
SELECT premises_type ,  premise_details FROM premises ORDER BY premises_type
### Database Schema
CREATE TABLE `Premises` (
`premise_id` INTEGER PRIMARY KEY,
`premises_type` VARCHAR(15) NOT NULL,
`premise_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15) NOT NULL,
`product_name` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_login` VARCHAR(80),
`customer_password` VARCHAR(10)
)

CREATE TABLE `Mailshot_Campaigns` (
`mailshot_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15),
`mailshot_name` VARCHAR(80),
`mailshot_start_date` DATETIME,
`mailshot_end_date` DATETIME
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`premise_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type_code` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`premise_id` ) REFERENCES `Premises`(`premise_id` )
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status_code` VARCHAR(15) NOT NULL,
`shipping_method_code` VARCHAR(15) NOT NULL,
`order_placed_datetime` DATETIME NOT NULL,
`order_delivered_datetime` DATETIME,
`order_shipping_charges` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Mailshot_Customers` (
`mailshot_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`outcome_code` VARCHAR(15) NOT NULL,
`mailshot_customer_date` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`mailshot_id` ) REFERENCES `Mailshot_Campaigns`(`mailshot_id` )
)

CREATE TABLE `Order_Items` (
`item_id` INTEGER NOT NULL ,
`order_item_status_code` VARCHAR(15) NOT NULL,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`item_status_code` VARCHAR(15),
`item_delivered_datetime` DATETIME,
`item_order_quantity` VARCHAR(80),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Show each premise type and the number of premises in that type.

### SQL
SELECT premises_type ,  count(*) FROM premises GROUP BY premises_type
### Database Schema
CREATE TABLE `Premises` (
`premise_id` INTEGER PRIMARY KEY,
`premises_type` VARCHAR(15) NOT NULL,
`premise_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15) NOT NULL,
`product_name` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_login` VARCHAR(80),
`customer_password` VARCHAR(10)
)

CREATE TABLE `Mailshot_Campaigns` (
`mailshot_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15),
`mailshot_name` VARCHAR(80),
`mailshot_start_date` DATETIME,
`mailshot_end_date` DATETIME
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`premise_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type_code` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`premise_id` ) REFERENCES `Premises`(`premise_id` )
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status_code` VARCHAR(15) NOT NULL,
`shipping_method_code` VARCHAR(15) NOT NULL,
`order_placed_datetime` DATETIME NOT NULL,
`order_delivered_datetime` DATETIME,
`order_shipping_charges` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Mailshot_Customers` (
`mailshot_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`outcome_code` VARCHAR(15) NOT NULL,
`mailshot_customer_date` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`mailshot_id` ) REFERENCES `Mailshot_Campaigns`(`mailshot_id` )
)

CREATE TABLE `Order_Items` (
`item_id` INTEGER NOT NULL ,
`order_item_status_code` VARCHAR(15) NOT NULL,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`item_status_code` VARCHAR(15),
`item_delivered_datetime` DATETIME,
`item_order_quantity` VARCHAR(80),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Show all distinct product categories along with the number of mailshots in each category.

### SQL
SELECT product_category ,  count(*) FROM mailshot_campaigns GROUP BY product_category
### Database Schema
CREATE TABLE `Premises` (
`premise_id` INTEGER PRIMARY KEY,
`premises_type` VARCHAR(15) NOT NULL,
`premise_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15) NOT NULL,
`product_name` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_login` VARCHAR(80),
`customer_password` VARCHAR(10)
)

CREATE TABLE `Mailshot_Campaigns` (
`mailshot_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15),
`mailshot_name` VARCHAR(80),
`mailshot_start_date` DATETIME,
`mailshot_end_date` DATETIME
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`premise_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type_code` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`premise_id` ) REFERENCES `Premises`(`premise_id` )
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status_code` VARCHAR(15) NOT NULL,
`shipping_method_code` VARCHAR(15) NOT NULL,
`order_placed_datetime` DATETIME NOT NULL,
`order_delivered_datetime` DATETIME,
`order_shipping_charges` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Mailshot_Customers` (
`mailshot_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`outcome_code` VARCHAR(15) NOT NULL,
`mailshot_customer_date` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`mailshot_id` ) REFERENCES `Mailshot_Campaigns`(`mailshot_id` )
)

CREATE TABLE `Order_Items` (
`item_id` INTEGER NOT NULL ,
`order_item_status_code` VARCHAR(15) NOT NULL,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`item_status_code` VARCHAR(15),
`item_delivered_datetime` DATETIME,
`item_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 and phone of the customer without any mailshot.

### SQL
SELECT customer_name ,  customer_phone FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM mailshot_customers)
### Database Schema
CREATE TABLE `Premises` (
`premise_id` INTEGER PRIMARY KEY,
`premises_type` VARCHAR(15) NOT NULL,
`premise_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15) NOT NULL,
`product_name` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_login` VARCHAR(80),
`customer_password` VARCHAR(10)
)

CREATE TABLE `Mailshot_Campaigns` (
`mailshot_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15),
`mailshot_name` VARCHAR(80),
`mailshot_start_date` DATETIME,
`mailshot_end_date` DATETIME
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`premise_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type_code` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`premise_id` ) REFERENCES `Premises`(`premise_id` )
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status_code` VARCHAR(15) NOT NULL,
`shipping_method_code` VARCHAR(15) NOT NULL,
`order_placed_datetime` DATETIME NOT NULL,
`order_delivered_datetime` DATETIME,
`order_shipping_charges` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Mailshot_Customers` (
`mailshot_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`outcome_code` VARCHAR(15) NOT NULL,
`mailshot_customer_date` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`mailshot_id` ) REFERENCES `Mailshot_Campaigns`(`mailshot_id` )
)

CREATE TABLE `Order_Items` (
`item_id` INTEGER NOT NULL ,
`order_item_status_code` VARCHAR(15) NOT NULL,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`item_status_code` VARCHAR(15),
`item_delivered_datetime` DATETIME,
`item_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 and phone for customers with a mailshot with outcome code 'No Response'.

### SQL
SELECT T1.customer_name ,  T1.customer_phone FROM customers AS T1 JOIN mailshot_customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.outcome_code  =  'No Response'
### Database Schema
CREATE TABLE `Premises` (
`premise_id` INTEGER PRIMARY KEY,
`premises_type` VARCHAR(15) NOT NULL,
`premise_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15) NOT NULL,
`product_name` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_login` VARCHAR(80),
`customer_password` VARCHAR(10)
)

CREATE TABLE `Mailshot_Campaigns` (
`mailshot_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15),
`mailshot_name` VARCHAR(80),
`mailshot_start_date` DATETIME,
`mailshot_end_date` DATETIME
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`premise_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type_code` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`premise_id` ) REFERENCES `Premises`(`premise_id` )
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status_code` VARCHAR(15) NOT NULL,
`shipping_method_code` VARCHAR(15) NOT NULL,
`order_placed_datetime` DATETIME NOT NULL,
`order_delivered_datetime` DATETIME,
`order_shipping_charges` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Mailshot_Customers` (
`mailshot_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`outcome_code` VARCHAR(15) NOT NULL,
`mailshot_customer_date` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`mailshot_id` ) REFERENCES `Mailshot_Campaigns`(`mailshot_id` )
)

CREATE TABLE `Order_Items` (
`item_id` INTEGER NOT NULL ,
`order_item_status_code` VARCHAR(15) NOT NULL,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`item_status_code` VARCHAR(15),
`item_delivered_datetime` DATETIME,
`item_order_quantity` VARCHAR(80),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Show the outcome code of mailshots along with the number of mailshots in each outcome code.

### SQL
SELECT outcome_code ,  count(*) FROM mailshot_customers GROUP BY outcome_code
### Database Schema
CREATE TABLE `Premises` (
`premise_id` INTEGER PRIMARY KEY,
`premises_type` VARCHAR(15) NOT NULL,
`premise_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15) NOT NULL,
`product_name` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_login` VARCHAR(80),
`customer_password` VARCHAR(10)
)

CREATE TABLE `Mailshot_Campaigns` (
`mailshot_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15),
`mailshot_name` VARCHAR(80),
`mailshot_start_date` DATETIME,
`mailshot_end_date` DATETIME
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`premise_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type_code` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`premise_id` ) REFERENCES `Premises`(`premise_id` )
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status_code` VARCHAR(15) NOT NULL,
`shipping_method_code` VARCHAR(15) NOT NULL,
`order_placed_datetime` DATETIME NOT NULL,
`order_delivered_datetime` DATETIME,
`order_shipping_charges` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Mailshot_Customers` (
`mailshot_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`outcome_code` VARCHAR(15) NOT NULL,
`mailshot_customer_date` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`mailshot_id` ) REFERENCES `Mailshot_Campaigns`(`mailshot_id` )
)

CREATE TABLE `Order_Items` (
`item_id` INTEGER NOT NULL ,
`order_item_status_code` VARCHAR(15) NOT NULL,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`item_status_code` VARCHAR(15),
`item_delivered_datetime` DATETIME,
`item_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 at least 2 mailshots with outcome code 'Order'.

### SQL
SELECT T2.customer_name FROM mailshot_customers AS T1 JOIN customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE outcome_code  =  'Order' GROUP BY T1.customer_id HAVING count(*)  >=  2
### Database Schema
CREATE TABLE `Premises` (
`premise_id` INTEGER PRIMARY KEY,
`premises_type` VARCHAR(15) NOT NULL,
`premise_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15) NOT NULL,
`product_name` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_login` VARCHAR(80),
`customer_password` VARCHAR(10)
)

CREATE TABLE `Mailshot_Campaigns` (
`mailshot_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15),
`mailshot_name` VARCHAR(80),
`mailshot_start_date` DATETIME,
`mailshot_end_date` DATETIME
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`premise_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type_code` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`premise_id` ) REFERENCES `Premises`(`premise_id` )
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status_code` VARCHAR(15) NOT NULL,
`shipping_method_code` VARCHAR(15) NOT NULL,
`order_placed_datetime` DATETIME NOT NULL,
`order_delivered_datetime` DATETIME,
`order_shipping_charges` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Mailshot_Customers` (
`mailshot_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`outcome_code` VARCHAR(15) NOT NULL,
`mailshot_customer_date` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`mailshot_id` ) REFERENCES `Mailshot_Campaigns`(`mailshot_id` )
)

CREATE TABLE `Order_Items` (
`item_id` INTEGER NOT NULL ,
`order_item_status_code` VARCHAR(15) NOT NULL,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`item_status_code` VARCHAR(15),
`item_delivered_datetime` DATETIME,
`item_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 the most mailshots.

### SQL
SELECT T2.customer_name FROM mailshot_customers AS T1 JOIN customers AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T1.customer_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE `Premises` (
`premise_id` INTEGER PRIMARY KEY,
`premises_type` VARCHAR(15) NOT NULL,
`premise_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15) NOT NULL,
`product_name` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_login` VARCHAR(80),
`customer_password` VARCHAR(10)
)

CREATE TABLE `Mailshot_Campaigns` (
`mailshot_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15),
`mailshot_name` VARCHAR(80),
`mailshot_start_date` DATETIME,
`mailshot_end_date` DATETIME
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`premise_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type_code` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`premise_id` ) REFERENCES `Premises`(`premise_id` )
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status_code` VARCHAR(15) NOT NULL,
`shipping_method_code` VARCHAR(15) NOT NULL,
`order_placed_datetime` DATETIME NOT NULL,
`order_delivered_datetime` DATETIME,
`order_shipping_charges` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Mailshot_Customers` (
`mailshot_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`outcome_code` VARCHAR(15) NOT NULL,
`mailshot_customer_date` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`mailshot_id` ) REFERENCES `Mailshot_Campaigns`(`mailshot_id` )
)

CREATE TABLE `Order_Items` (
`item_id` INTEGER NOT NULL ,
`order_item_status_code` VARCHAR(15) NOT NULL,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`item_status_code` VARCHAR(15),
`item_delivered_datetime` DATETIME,
`item_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 payment method of customers who have both mailshots in 'Order' outcome and mailshots in 'No Response' outcome.

### SQL
SELECT T2.customer_name ,  T2.payment_method FROM mailshot_customers AS T1 JOIN customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE T1.outcome_code  =  'Order' INTERSECT SELECT T2.customer_name ,  T2.payment_method FROM mailshot_customers AS T1 JOIN customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE T1.outcome_code  =  'No Response'
### Database Schema
CREATE TABLE `Premises` (
`premise_id` INTEGER PRIMARY KEY,
`premises_type` VARCHAR(15) NOT NULL,
`premise_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15) NOT NULL,
`product_name` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_login` VARCHAR(80),
`customer_password` VARCHAR(10)
)

CREATE TABLE `Mailshot_Campaigns` (
`mailshot_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15),
`mailshot_name` VARCHAR(80),
`mailshot_start_date` DATETIME,
`mailshot_end_date` DATETIME
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`premise_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type_code` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`premise_id` ) REFERENCES `Premises`(`premise_id` )
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status_code` VARCHAR(15) NOT NULL,
`shipping_method_code` VARCHAR(15) NOT NULL,
`order_placed_datetime` DATETIME NOT NULL,
`order_delivered_datetime` DATETIME,
`order_shipping_charges` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Mailshot_Customers` (
`mailshot_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`outcome_code` VARCHAR(15) NOT NULL,
`mailshot_customer_date` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`mailshot_id` ) REFERENCES `Mailshot_Campaigns`(`mailshot_id` )
)

CREATE TABLE `Order_Items` (
`item_id` INTEGER NOT NULL ,
`order_item_status_code` VARCHAR(15) NOT NULL,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`item_status_code` VARCHAR(15),
`item_delivered_datetime` DATETIME,
`item_order_quantity` VARCHAR(80),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Show the premise type and address type code for all customer addresses.

### SQL
SELECT T2.premises_type ,  T1.address_type_code FROM customer_addresses AS T1 JOIN premises AS T2 ON T1.premise_id  =  T2.premise_id
### Database Schema
CREATE TABLE `Premises` (
`premise_id` INTEGER PRIMARY KEY,
`premises_type` VARCHAR(15) NOT NULL,
`premise_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15) NOT NULL,
`product_name` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_login` VARCHAR(80),
`customer_password` VARCHAR(10)
)

CREATE TABLE `Mailshot_Campaigns` (
`mailshot_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15),
`mailshot_name` VARCHAR(80),
`mailshot_start_date` DATETIME,
`mailshot_end_date` DATETIME
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`premise_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type_code` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`premise_id` ) REFERENCES `Premises`(`premise_id` )
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status_code` VARCHAR(15) NOT NULL,
`shipping_method_code` VARCHAR(15) NOT NULL,
`order_placed_datetime` DATETIME NOT NULL,
`order_delivered_datetime` DATETIME,
`order_shipping_charges` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Mailshot_Customers` (
`mailshot_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`outcome_code` VARCHAR(15) NOT NULL,
`mailshot_customer_date` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`mailshot_id` ) REFERENCES `Mailshot_Campaigns`(`mailshot_id` )
)

CREATE TABLE `Order_Items` (
`item_id` INTEGER NOT NULL ,
`order_item_status_code` VARCHAR(15) NOT NULL,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`item_status_code` VARCHAR(15),
`item_delivered_datetime` DATETIME,
`item_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 distinct address type codes for all customer addresses?

### SQL
SELECT DISTINCT address_type_code FROM customer_addresses
### Database Schema
CREATE TABLE `Premises` (
`premise_id` INTEGER PRIMARY KEY,
`premises_type` VARCHAR(15) NOT NULL,
`premise_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15) NOT NULL,
`product_name` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_login` VARCHAR(80),
`customer_password` VARCHAR(10)
)

CREATE TABLE `Mailshot_Campaigns` (
`mailshot_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15),
`mailshot_name` VARCHAR(80),
`mailshot_start_date` DATETIME,
`mailshot_end_date` DATETIME
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`premise_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type_code` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`premise_id` ) REFERENCES `Premises`(`premise_id` )
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status_code` VARCHAR(15) NOT NULL,
`shipping_method_code` VARCHAR(15) NOT NULL,
`order_placed_datetime` DATETIME NOT NULL,
`order_delivered_datetime` DATETIME,
`order_shipping_charges` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Mailshot_Customers` (
`mailshot_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`outcome_code` VARCHAR(15) NOT NULL,
`mailshot_customer_date` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`mailshot_id` ) REFERENCES `Mailshot_Campaigns`(`mailshot_id` )
)

CREATE TABLE `Order_Items` (
`item_id` INTEGER NOT NULL ,
`order_item_status_code` VARCHAR(15) NOT NULL,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`item_status_code` VARCHAR(15),
`item_delivered_datetime` DATETIME,
`item_order_quantity` VARCHAR(80),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Show the shipping charge and customer id for customer orders with order status Cancelled or Paid.

### SQL
SELECT order_shipping_charges ,  customer_id FROM customer_orders WHERE order_status_code  =  'Cancelled' OR order_status_code  =  'Paid'
### Database Schema
CREATE TABLE `Premises` (
`premise_id` INTEGER PRIMARY KEY,
`premises_type` VARCHAR(15) NOT NULL,
`premise_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15) NOT NULL,
`product_name` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_login` VARCHAR(80),
`customer_password` VARCHAR(10)
)

CREATE TABLE `Mailshot_Campaigns` (
`mailshot_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15),
`mailshot_name` VARCHAR(80),
`mailshot_start_date` DATETIME,
`mailshot_end_date` DATETIME
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`premise_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type_code` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`premise_id` ) REFERENCES `Premises`(`premise_id` )
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status_code` VARCHAR(15) NOT NULL,
`shipping_method_code` VARCHAR(15) NOT NULL,
`order_placed_datetime` DATETIME NOT NULL,
`order_delivered_datetime` DATETIME,
`order_shipping_charges` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Mailshot_Customers` (
`mailshot_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`outcome_code` VARCHAR(15) NOT NULL,
`mailshot_customer_date` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`mailshot_id` ) REFERENCES `Mailshot_Campaigns`(`mailshot_id` )
)

CREATE TABLE `Order_Items` (
`item_id` INTEGER NOT NULL ,
`order_item_status_code` VARCHAR(15) NOT NULL,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`item_status_code` VARCHAR(15),
`item_delivered_datetime` DATETIME,
`item_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 having an order with shipping method FedEx and order status Paid.

### SQL
SELECT T1.customer_name FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id WHERE shipping_method_code  =  'FedEx' AND order_status_code  =  'Paid'
### 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 Faculty (
       FacID 	       INTEGER PRIMARY KEY,
       Lname		VARCHAR(15),
       Fname		VARCHAR(15),
       Rank		VARCHAR(15),
       Sex		VARCHAR(1),
       Phone		INTEGER,
       Room		VARCHAR(5),
       Building		VARCHAR(13)
)

CREATE TABLE Department (
       DNO   		INTEGER PRIMARY KEY,
       Division		VARCHAR(2),
       DName		VARCHAR(25),
       Room		VARCHAR(5),
       Building		VARCHAR(13),
       DPhone		INTEGER
)

CREATE TABLE Member_of (
       FacID 	       INTEGER,
       DNO	       INTEGER,
       Appt_Type       VARCHAR(15),
       FOREIGN KEY(FacID) REFERENCES Faculty(FacID),
       FOREIGN KEY(DNO) REFERENCES Department(DNO)
)

CREATE TABLE Course (
       CID   	    	VARCHAR(7) PRIMARY KEY,
       CName		VARCHAR(40),
       Credits		INTEGER,
       Instructor	INTEGER,
       Days		VARCHAR(5),
       Hours		VARCHAR(11),
       DNO		INTEGER,
       FOREIGN KEY(Instructor) REFERENCES Faculty(FacID),
       FOREIGN KEY(DNO) REFERENCES Department(DNO)
)

CREATE TABLE Minor_in (
       StuID 	      INTEGER,
       DNO		INTEGER,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(DNO) REFERENCES Department(DNO)
)

CREATE TABLE Enrolled_in (
       StuID 		 INTEGER,
       CID		VARCHAR(7),
       Grade		VARCHAR(2),
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(CID) REFERENCES Course(CID),
       FOREIGN KEY(Grade) REFERENCES Gradeconversion(lettergrade)
)

CREATE TABLE Gradeconversion (
       lettergrade	     VARCHAR(2) PRIMARY KEY,
       gradepoint	     FLOAT
)

### Question
How many courses are there in total?

### SQL
SELECT count(*) FROM COURSE
### 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 Faculty (
       FacID 	       INTEGER PRIMARY KEY,
       Lname		VARCHAR(15),
       Fname		VARCHAR(15),
       Rank		VARCHAR(15),
       Sex		VARCHAR(1),
       Phone		INTEGER,
       Room		VARCHAR(5),
       Building		VARCHAR(13)
)

CREATE TABLE Department (
       DNO   		INTEGER PRIMARY KEY,
       Division		VARCHAR(2),
       DName		VARCHAR(25),
       Room		VARCHAR(5),
       Building		VARCHAR(13),
       DPhone		INTEGER
)

CREATE TABLE Member_of (
       FacID 	       INTEGER,
       DNO	       INTEGER,
       Appt_Type       VARCHAR(15),
       FOREIGN KEY(FacID) REFERENCES Faculty(FacID),
       FOREIGN KEY(DNO) REFERENCES Department(DNO)
)

CREATE TABLE Course (
       CID   	    	VARCHAR(7) PRIMARY KEY,
       CName		VARCHAR(40),
       Credits		INTEGER,
       Instructor	INTEGER,
       Days		VARCHAR(5),
       Hours		VARCHAR(11),
       DNO		INTEGER,
       FOREIGN KEY(Instructor) REFERENCES Faculty(FacID),
       FOREIGN KEY(DNO) REFERENCES Department(DNO)
)

CREATE TABLE Minor_in (
       StuID 	      INTEGER,
       DNO		INTEGER,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(DNO) REFERENCES Department(DNO)
)

CREATE TABLE Enrolled_in (
       StuID 		 INTEGER,
       CID		VARCHAR(7),
       Grade		VARCHAR(2),
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(CID) REFERENCES Course(CID),
       FOREIGN KEY(Grade) REFERENCES Gradeconversion(lettergrade)
)

CREATE TABLE Gradeconversion (
       lettergrade	     VARCHAR(2) PRIMARY KEY,
       gradepoint	     FLOAT
)

### Question
Count the number of courses.

### SQL
SELECT count(*) FROM COURSE
### 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 Faculty (
       FacID 	       INTEGER PRIMARY KEY,
       Lname		VARCHAR(15),
       Fname		VARCHAR(15),
       Rank		VARCHAR(15),
       Sex		VARCHAR(1),
       Phone		INTEGER,
       Room		VARCHAR(5),
       Building		VARCHAR(13)
)

CREATE TABLE Department (
       DNO   		INTEGER PRIMARY KEY,
       Division		VARCHAR(2),
       DName		VARCHAR(25),
       Room		VARCHAR(5),
       Building		VARCHAR(13),
       DPhone		INTEGER
)

CREATE TABLE Member_of (
       FacID 	       INTEGER,
       DNO	       INTEGER,
       Appt_Type       VARCHAR(15),
       FOREIGN KEY(FacID) REFERENCES Faculty(FacID),
       FOREIGN KEY(DNO) REFERENCES Department(DNO)
)

CREATE TABLE Course (
       CID   	    	VARCHAR(7) PRIMARY KEY,
       CName		VARCHAR(40),
       Credits		INTEGER,
       Instructor	INTEGER,
       Days		VARCHAR(5),
       Hours		VARCHAR(11),
       DNO		INTEGER,
       FOREIGN KEY(Instructor) REFERENCES Faculty(FacID),
       FOREIGN KEY(DNO) REFERENCES Department(DNO)
)

CREATE TABLE Minor_in (
       StuID 	      INTEGER,
       DNO		INTEGER,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(DNO) REFERENCES Department(DNO)
)

CREATE TABLE Enrolled_in (
       StuID 		 INTEGER,
       CID		VARCHAR(7),
       Grade		VARCHAR(2),
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(CID) REFERENCES Course(CID),
       FOREIGN KEY(Grade) REFERENCES Gradeconversion(lettergrade)
)

CREATE TABLE Gradeconversion (
       lettergrade	     VARCHAR(2) PRIMARY KEY,
       gradepoint	     FLOAT
)

### Question
How many courses have more than 2 credits?

### SQL
SELECT count(*) FROM COURSE WHERE Credits  >  2
### 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 Faculty (
       FacID 	       INTEGER PRIMARY KEY,
       Lname		VARCHAR(15),
       Fname		VARCHAR(15),
       Rank		VARCHAR(15),
       Sex		VARCHAR(1),
       Phone		INTEGER,
       Room		VARCHAR(5),
       Building		VARCHAR(13)
)

CREATE TABLE Department (
       DNO   		INTEGER PRIMARY KEY,
       Division		VARCHAR(2),
       DName		VARCHAR(25),
       Room		VARCHAR(5),
       Building		VARCHAR(13),
       DPhone		INTEGER
)

CREATE TABLE Member_of (
       FacID 	       INTEGER,
       DNO	       INTEGER,
       Appt_Type       VARCHAR(15),
       FOREIGN KEY(FacID) REFERENCES Faculty(FacID),
       FOREIGN KEY(DNO) REFERENCES Department(DNO)
)

CREATE TABLE Course (
       CID   	    	VARCHAR(7) PRIMARY KEY,
       CName		VARCHAR(40),
       Credits		INTEGER,
       Instructor	INTEGER,
       Days		VARCHAR(5),
       Hours		VARCHAR(11),
       DNO		INTEGER,
       FOREIGN KEY(Instructor) REFERENCES Faculty(FacID),
       FOREIGN KEY(DNO) REFERENCES Department(DNO)
)

CREATE TABLE Minor_in (
       StuID 	      INTEGER,
       DNO		INTEGER,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(DNO) REFERENCES Department(DNO)
)

CREATE TABLE Enrolled_in (
       StuID 		 INTEGER,
       CID		VARCHAR(7),
       Grade		VARCHAR(2),
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(CID) REFERENCES Course(CID),
       FOREIGN KEY(Grade) REFERENCES Gradeconversion(lettergrade)
)

CREATE TABLE Gradeconversion (
       lettergrade	     VARCHAR(2) PRIMARY KEY,
       gradepoint	     FLOAT
)

### Question
Count the number of courses with more than 2 credits.

### SQL
SELECT count(*) FROM COURSE WHERE Credits  >  2
### 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 Faculty (
       FacID 	       INTEGER PRIMARY KEY,
       Lname		VARCHAR(15),
       Fname		VARCHAR(15),
       Rank		VARCHAR(15),
       Sex		VARCHAR(1),
       Phone		INTEGER,
       Room		VARCHAR(5),
       Building		VARCHAR(13)
)

CREATE TABLE Department (
       DNO   		INTEGER PRIMARY KEY,
       Division		VARCHAR(2),
       DName		VARCHAR(25),
       Room		VARCHAR(5),
       Building		VARCHAR(13),
       DPhone		INTEGER
)

CREATE TABLE Member_of (
       FacID 	       INTEGER,
       DNO	       INTEGER,
       Appt_Type       VARCHAR(15),
       FOREIGN KEY(FacID) REFERENCES Faculty(FacID),
       FOREIGN KEY(DNO) REFERENCES Department(DNO)
)

CREATE TABLE Course (
       CID   	    	VARCHAR(7) PRIMARY KEY,
       CName		VARCHAR(40),
       Credits		INTEGER,
       Instructor	INTEGER,
       Days		VARCHAR(5),
       Hours		VARCHAR(11),
       DNO		INTEGER,
       FOREIGN KEY(Instructor) REFERENCES Faculty(FacID),
       FOREIGN KEY(DNO) REFERENCES Department(DNO)
)

CREATE TABLE Minor_in (
       StuID 	      INTEGER,
       DNO		INTEGER,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(DNO) REFERENCES Department(DNO)
)

CREATE TABLE Enrolled_in (
       StuID 		 INTEGER,
       CID		VARCHAR(7),
       Grade		VARCHAR(2),
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(CID) REFERENCES Course(CID),
       FOREIGN KEY(Grade) REFERENCES Gradeconversion(lettergrade)
)

CREATE TABLE Gradeconversion (
       lettergrade	     VARCHAR(2) PRIMARY KEY,
       gradepoint	     FLOAT
)

### Question
List all names of courses with 1 credit?

### SQL
SELECT CName FROM COURSE WHERE Credits  =  1
### 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 Faculty (
       FacID 	       INTEGER PRIMARY KEY,
       Lname		VARCHAR(15),
       Fname		VARCHAR(15),
       Rank		VARCHAR(15),
       Sex		VARCHAR(1),
       Phone		INTEGER,
       Room		VARCHAR(5),
       Building		VARCHAR(13)
)

CREATE TABLE Department (
       DNO   		INTEGER PRIMARY KEY,
       Division		VARCHAR(2),
       DName		VARCHAR(25),
       Room		VARCHAR(5),
       Building		VARCHAR(13),
       DPhone		INTEGER
)

CREATE TABLE Member_of (
       FacID 	       INTEGER,
       DNO	       INTEGER,
       Appt_Type       VARCHAR(15),
       FOREIGN KEY(FacID) REFERENCES Faculty(FacID),
       FOREIGN KEY(DNO) REFERENCES Department(DNO)
)

CREATE TABLE Course (
       CID   	    	VARCHAR(7) PRIMARY KEY,
       CName		VARCHAR(40),
       Credits		INTEGER,
       Instructor	INTEGER,
       Days		VARCHAR(5),
       Hours		VARCHAR(11),
       DNO		INTEGER,
       FOREIGN KEY(Instructor) REFERENCES Faculty(FacID),
       FOREIGN KEY(DNO) REFERENCES Department(DNO)
)

CREATE TABLE Minor_in (
       StuID 	      INTEGER,
       DNO		INTEGER,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(DNO) REFERENCES Department(DNO)
)

CREATE TABLE Enrolled_in (
       StuID 		 INTEGER,
       CID		VARCHAR(7),
       Grade		VARCHAR(2),
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(CID) REFERENCES Course(CID),
       FOREIGN KEY(Grade) REFERENCES Gradeconversion(lettergrade)
)

CREATE TABLE Gradeconversion (
       lettergrade	     VARCHAR(2) PRIMARY KEY,
       gradepoint	     FLOAT
)

### Question
What are the names of courses with 1 credit?

### SQL
SELECT CName FROM COURSE WHERE Credits  =  1