lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
Return the city with the customer type code "Good Credit Rating" that had the fewest customers.

### SQL
SELECT town_city FROM customers WHERE customer_type_code  =  "Good Credit Rating" GROUP BY town_city ORDER BY count(*) LIMIT 1
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
List the name of all products along with the number of complaints that they have received.

### SQL
SELECT t1.product_name ,  count(*) FROM products AS t1 JOIN complaints AS t2 ON t1.product_id  =  t2.product_id GROUP BY t1.product_name
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
What are all the different product names, and how many complains has each received?

### SQL
SELECT t1.product_name ,  count(*) FROM products AS t1 JOIN complaints AS t2 ON t1.product_id  =  t2.product_id GROUP BY t1.product_name
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
Find the emails of customers who has filed a complaints of the product with the most complaints.

### SQL
SELECT t1.email_address FROM customers AS t1 JOIN complaints AS t2 ON t1.customer_id  =  t2.customer_id GROUP BY t1.customer_id ORDER BY count(*) LIMIT 1
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
What are the emails of customers who have filed complaints on the product which has had the greatest number of complaints?

### SQL
SELECT t1.email_address FROM customers AS t1 JOIN complaints AS t2 ON t1.customer_id  =  t2.customer_id GROUP BY t1.customer_id ORDER BY count(*) LIMIT 1
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
Which products has been complained by the customer who has filed least amount of complaints?

### SQL
SELECT DISTINCT t1.product_name FROM products AS t1 JOIN complaints AS t2 ON t1.product_id  =  t2.product_id JOIN customers AS t3 GROUP BY t3.customer_id ORDER BY count(*) LIMIT 1
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
Return the names of products that have had complaints filed by the customer who has filed the fewest complaints.

### SQL
SELECT DISTINCT t1.product_name FROM products AS t1 JOIN complaints AS t2 ON t1.product_id  =  t2.product_id JOIN customers AS t3 GROUP BY t3.customer_id ORDER BY count(*) LIMIT 1
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
What is the phone number of the customer who has filed the most recent complaint?

### SQL
SELECT t1.phone_number FROM customers AS t1 JOIN complaints AS t2 ON t1.customer_id  =  t2.customer_id ORDER BY t2.date_complaint_raised DESC LIMIT 1
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
Return the phone number of the customer who filed the complaint that was raised most recently.

### SQL
SELECT t1.phone_number FROM customers AS t1 JOIN complaints AS t2 ON t1.customer_id  =  t2.customer_id ORDER BY t2.date_complaint_raised DESC LIMIT 1
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
Find the email and phone number of the customers who have never filed a complaint before.

### SQL
SELECT email_address ,  phone_number FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM complaints)
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
What are the emails and phone numbers of custoemrs who have never filed a complaint?

### SQL
SELECT email_address ,  phone_number FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM complaints)
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
Find the phone number of all the customers and staff.

### SQL
SELECT phone_number FROM customers UNION SELECT phone_number FROM staff
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
What are the phone numbers of all customers and all staff members?

### SQL
SELECT phone_number FROM customers UNION SELECT phone_number FROM staff
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
What is the description of the product named "Chocolate"?

### SQL
SELECT product_description FROM products WHERE product_name  =  "Chocolate"
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
Return the description of the product called "Chocolate".

### SQL
SELECT product_description FROM products WHERE product_name  =  "Chocolate"
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
Find the name and category of the most expensive product.

### SQL
SELECT product_name ,  product_category_code FROM products ORDER BY product_price DESC LIMIT 1
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
What is the name and category code of the product with the highest price?

### SQL
SELECT product_name ,  product_category_code FROM products ORDER BY product_price DESC LIMIT 1
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
Find the prices of products which has never received a single complaint.

### SQL
SELECT product_price FROM products WHERE product_id NOT IN (SELECT product_id FROM complaints)
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
What are the prices of products that have never gotten a complaint?

### SQL
SELECT product_price FROM products WHERE product_id NOT IN (SELECT product_id FROM complaints)
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
What is the average price of the products for each category?

### SQL
SELECT avg(product_price) ,  product_category_code FROM products GROUP BY product_category_code
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
Return the average price of products that have each category code.

### SQL
SELECT avg(product_price) ,  product_category_code FROM products GROUP BY product_category_code
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
Find the last name of the staff member who processed the complaint of the cheapest product.

### SQL
SELECT t1.last_name FROM staff AS t1 JOIN complaints AS t2 ON t1.staff_id  =  t2.staff_id JOIN products AS t3 ON t2.product_id  =  t3.product_id ORDER BY t3.product_price LIMIT 1
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
What is the last name of the staff member in charge of the complaint on the product with the lowest price?

### SQL
SELECT t1.last_name FROM staff AS t1 JOIN complaints AS t2 ON t1.staff_id  =  t2.staff_id JOIN products AS t3 ON t2.product_id  =  t3.product_id ORDER BY t3.product_price LIMIT 1
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
Which complaint status has more than 3 records on file?

### SQL
SELECT complaint_status_code FROM complaints GROUP BY complaint_status_code HAVING count(*)  >  3
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
Return complaint status codes have more than 3 corresponding complaints?

### SQL
SELECT complaint_status_code FROM complaints GROUP BY complaint_status_code HAVING count(*)  >  3
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
Find the last name of the staff whose email address contains "wrau".

### SQL
SELECT last_name FROM staff WHERE email_address LIKE "%wrau%"
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
What are the last names of staff with email addressed containing the substring "wrau"?

### SQL
SELECT last_name FROM staff WHERE email_address LIKE "%wrau%"
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
How many customers are there in the customer type with the most customers?

### SQL
SELECT count(*) FROM customers GROUP BY customer_type_code ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
Count the number of customers that have the customer type that is most common.

### SQL
SELECT count(*) FROM customers GROUP BY customer_type_code ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
What is the last name of the staff who has handled the first ever complaint?

### SQL
SELECT t1.last_name FROM staff AS t1 JOIN complaints AS t2 ON t1.staff_id  =  t2.staff_id ORDER BY t2.date_complaint_raised LIMIT 1
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
Return the last name of the staff member who handled the complaint with the earliest date raised.

### SQL
SELECT t1.last_name FROM staff AS t1 JOIN complaints AS t2 ON t1.staff_id  =  t2.staff_id ORDER BY t2.date_complaint_raised LIMIT 1