lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE `Problem_Category_Codes` (
`problem_category_code` VARCHAR(20) PRIMARY KEY,
`problem_category_description` VARCHAR(80)
)

CREATE TABLE `Problem_Log` (
`problem_log_id` INTEGER PRIMARY KEY,
`assigned_to_staff_id` INTEGER NOT NULL,
`problem_id` INTEGER NOT NULL,
`problem_category_code` VARCHAR(20) NOT NULL,
`problem_status_code` VARCHAR(20) NOT NULL,
`log_entry_date` DATETIME,
`log_entry_description` VARCHAR(255),
`log_entry_fix` VARCHAR(255),
`other_log_details` VARCHAR(255),
FOREIGN KEY (`problem_category_code` ) REFERENCES `Problem_Category_Codes`(`problem_category_code` ),FOREIGN KEY (`assigned_to_staff_id` ) REFERENCES `Staff`(`staff_id` ),FOREIGN KEY (`problem_id` ) REFERENCES `Problems`(`problem_id` ),FOREIGN KEY (`problem_status_code` ) REFERENCES `Problem_Status_Codes`(`problem_status_code` )
)

CREATE TABLE `Problem_Status_Codes` (
`problem_status_code` VARCHAR(20) PRIMARY KEY,
`problem_status_description` VARCHAR(80)
)

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

CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`staff_first_name` VARCHAR(80),
`staff_last_name` VARCHAR(80),
`other_staff_details` VARCHAR(255)
)

CREATE TABLE `Problems` (
`problem_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`closure_authorised_by_staff_id` INTEGER NOT NULL,
`reported_by_staff_id` INTEGER NOT NULL,
`date_problem_reported` DATETIME NOT NULL,
`date_problem_closed` DATETIME,
`problem_description` VARCHAR(255),
`other_problem_details` VARCHAR(255),
FOREIGN KEY (`closure_authorised_by_staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Product`(`product_id` ),
FOREIGN KEY (`reported_by_staff_id` ) REFERENCES `Staff`(`staff_id` )
)

### Question
What are the id of problems reported by the staff named Dameon Frami or Jolie Weber?

### SQL
SELECT product_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE T2.staff_first_name = "Dameon" AND T2.staff_last_name = "Frami" UNION SELECT product_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE T2.staff_first_name = "Jolie" AND T2.staff_last_name = "Weber"
### Database Schema
CREATE TABLE `Problem_Category_Codes` (
`problem_category_code` VARCHAR(20) PRIMARY KEY,
`problem_category_description` VARCHAR(80)
)

CREATE TABLE `Problem_Log` (
`problem_log_id` INTEGER PRIMARY KEY,
`assigned_to_staff_id` INTEGER NOT NULL,
`problem_id` INTEGER NOT NULL,
`problem_category_code` VARCHAR(20) NOT NULL,
`problem_status_code` VARCHAR(20) NOT NULL,
`log_entry_date` DATETIME,
`log_entry_description` VARCHAR(255),
`log_entry_fix` VARCHAR(255),
`other_log_details` VARCHAR(255),
FOREIGN KEY (`problem_category_code` ) REFERENCES `Problem_Category_Codes`(`problem_category_code` ),FOREIGN KEY (`assigned_to_staff_id` ) REFERENCES `Staff`(`staff_id` ),FOREIGN KEY (`problem_id` ) REFERENCES `Problems`(`problem_id` ),FOREIGN KEY (`problem_status_code` ) REFERENCES `Problem_Status_Codes`(`problem_status_code` )
)

CREATE TABLE `Problem_Status_Codes` (
`problem_status_code` VARCHAR(20) PRIMARY KEY,
`problem_status_description` VARCHAR(80)
)

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

CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`staff_first_name` VARCHAR(80),
`staff_last_name` VARCHAR(80),
`other_staff_details` VARCHAR(255)
)

CREATE TABLE `Problems` (
`problem_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`closure_authorised_by_staff_id` INTEGER NOT NULL,
`reported_by_staff_id` INTEGER NOT NULL,
`date_problem_reported` DATETIME NOT NULL,
`date_problem_closed` DATETIME,
`problem_description` VARCHAR(255),
`other_problem_details` VARCHAR(255),
FOREIGN KEY (`closure_authorised_by_staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Product`(`product_id` ),
FOREIGN KEY (`reported_by_staff_id` ) REFERENCES `Staff`(`staff_id` )
)

### Question
Which problems were reported by the staff named Dameon Frami or Jolie Weber? Give me the ids of the problems.

### SQL
SELECT product_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE T2.staff_first_name = "Dameon" AND T2.staff_last_name = "Frami" UNION SELECT product_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE T2.staff_first_name = "Jolie" AND T2.staff_last_name = "Weber"
### Database Schema
CREATE TABLE `Problem_Category_Codes` (
`problem_category_code` VARCHAR(20) PRIMARY KEY,
`problem_category_description` VARCHAR(80)
)

CREATE TABLE `Problem_Log` (
`problem_log_id` INTEGER PRIMARY KEY,
`assigned_to_staff_id` INTEGER NOT NULL,
`problem_id` INTEGER NOT NULL,
`problem_category_code` VARCHAR(20) NOT NULL,
`problem_status_code` VARCHAR(20) NOT NULL,
`log_entry_date` DATETIME,
`log_entry_description` VARCHAR(255),
`log_entry_fix` VARCHAR(255),
`other_log_details` VARCHAR(255),
FOREIGN KEY (`problem_category_code` ) REFERENCES `Problem_Category_Codes`(`problem_category_code` ),FOREIGN KEY (`assigned_to_staff_id` ) REFERENCES `Staff`(`staff_id` ),FOREIGN KEY (`problem_id` ) REFERENCES `Problems`(`problem_id` ),FOREIGN KEY (`problem_status_code` ) REFERENCES `Problem_Status_Codes`(`problem_status_code` )
)

CREATE TABLE `Problem_Status_Codes` (
`problem_status_code` VARCHAR(20) PRIMARY KEY,
`problem_status_description` VARCHAR(80)
)

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

CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`staff_first_name` VARCHAR(80),
`staff_last_name` VARCHAR(80),
`other_staff_details` VARCHAR(255)
)

CREATE TABLE `Problems` (
`problem_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`closure_authorised_by_staff_id` INTEGER NOT NULL,
`reported_by_staff_id` INTEGER NOT NULL,
`date_problem_reported` DATETIME NOT NULL,
`date_problem_closed` DATETIME,
`problem_description` VARCHAR(255),
`other_problem_details` VARCHAR(255),
FOREIGN KEY (`closure_authorised_by_staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Product`(`product_id` ),
FOREIGN KEY (`reported_by_staff_id` ) REFERENCES `Staff`(`staff_id` )
)

### Question
What are the product ids for the problems reported by Christop Berge with closure authorised by Ashley Medhurst?

### SQL
SELECT product_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE T2.staff_first_name = "Christop" AND T2.staff_last_name = "Berge" INTERSECT SELECT product_id FROM problems AS T1 JOIN staff AS T2 ON T1.closure_authorised_by_staff_id = T2.staff_id WHERE T2.staff_first_name = "Ashley" AND T2.staff_last_name = "Medhurst"
### Database Schema
CREATE TABLE `Problem_Category_Codes` (
`problem_category_code` VARCHAR(20) PRIMARY KEY,
`problem_category_description` VARCHAR(80)
)

CREATE TABLE `Problem_Log` (
`problem_log_id` INTEGER PRIMARY KEY,
`assigned_to_staff_id` INTEGER NOT NULL,
`problem_id` INTEGER NOT NULL,
`problem_category_code` VARCHAR(20) NOT NULL,
`problem_status_code` VARCHAR(20) NOT NULL,
`log_entry_date` DATETIME,
`log_entry_description` VARCHAR(255),
`log_entry_fix` VARCHAR(255),
`other_log_details` VARCHAR(255),
FOREIGN KEY (`problem_category_code` ) REFERENCES `Problem_Category_Codes`(`problem_category_code` ),FOREIGN KEY (`assigned_to_staff_id` ) REFERENCES `Staff`(`staff_id` ),FOREIGN KEY (`problem_id` ) REFERENCES `Problems`(`problem_id` ),FOREIGN KEY (`problem_status_code` ) REFERENCES `Problem_Status_Codes`(`problem_status_code` )
)

CREATE TABLE `Problem_Status_Codes` (
`problem_status_code` VARCHAR(20) PRIMARY KEY,
`problem_status_description` VARCHAR(80)
)

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

CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`staff_first_name` VARCHAR(80),
`staff_last_name` VARCHAR(80),
`other_staff_details` VARCHAR(255)
)

CREATE TABLE `Problems` (
`problem_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`closure_authorised_by_staff_id` INTEGER NOT NULL,
`reported_by_staff_id` INTEGER NOT NULL,
`date_problem_reported` DATETIME NOT NULL,
`date_problem_closed` DATETIME,
`problem_description` VARCHAR(255),
`other_problem_details` VARCHAR(255),
FOREIGN KEY (`closure_authorised_by_staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Product`(`product_id` ),
FOREIGN KEY (`reported_by_staff_id` ) REFERENCES `Staff`(`staff_id` )
)

### Question
For which product was there a problem reported by Christop Berge, with closure authorised by Ashley Medhurst? Return the product ids.

### SQL
SELECT product_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE T2.staff_first_name = "Christop" AND T2.staff_last_name = "Berge" INTERSECT SELECT product_id FROM problems AS T1 JOIN staff AS T2 ON T1.closure_authorised_by_staff_id = T2.staff_id WHERE T2.staff_first_name = "Ashley" AND T2.staff_last_name = "Medhurst"
### Database Schema
CREATE TABLE `Problem_Category_Codes` (
`problem_category_code` VARCHAR(20) PRIMARY KEY,
`problem_category_description` VARCHAR(80)
)

CREATE TABLE `Problem_Log` (
`problem_log_id` INTEGER PRIMARY KEY,
`assigned_to_staff_id` INTEGER NOT NULL,
`problem_id` INTEGER NOT NULL,
`problem_category_code` VARCHAR(20) NOT NULL,
`problem_status_code` VARCHAR(20) NOT NULL,
`log_entry_date` DATETIME,
`log_entry_description` VARCHAR(255),
`log_entry_fix` VARCHAR(255),
`other_log_details` VARCHAR(255),
FOREIGN KEY (`problem_category_code` ) REFERENCES `Problem_Category_Codes`(`problem_category_code` ),FOREIGN KEY (`assigned_to_staff_id` ) REFERENCES `Staff`(`staff_id` ),FOREIGN KEY (`problem_id` ) REFERENCES `Problems`(`problem_id` ),FOREIGN KEY (`problem_status_code` ) REFERENCES `Problem_Status_Codes`(`problem_status_code` )
)

CREATE TABLE `Problem_Status_Codes` (
`problem_status_code` VARCHAR(20) PRIMARY KEY,
`problem_status_description` VARCHAR(80)
)

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

CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`staff_first_name` VARCHAR(80),
`staff_last_name` VARCHAR(80),
`other_staff_details` VARCHAR(255)
)

CREATE TABLE `Problems` (
`problem_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`closure_authorised_by_staff_id` INTEGER NOT NULL,
`reported_by_staff_id` INTEGER NOT NULL,
`date_problem_reported` DATETIME NOT NULL,
`date_problem_closed` DATETIME,
`problem_description` VARCHAR(255),
`other_problem_details` VARCHAR(255),
FOREIGN KEY (`closure_authorised_by_staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Product`(`product_id` ),
FOREIGN KEY (`reported_by_staff_id` ) REFERENCES `Staff`(`staff_id` )
)

### Question
What are the ids of the problems reported before the date of any problem reported by Lysanne Turcotte?

### SQL
SELECT T1.problem_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE date_problem_reported  <  ( SELECT min(date_problem_reported) FROM problems AS T3 JOIN staff AS T4 ON T3.reported_by_staff_id = T4.staff_id WHERE T4.staff_first_name = "Lysanne" AND T4.staff_last_name = "Turcotte" )
### Database Schema
CREATE TABLE `Problem_Category_Codes` (
`problem_category_code` VARCHAR(20) PRIMARY KEY,
`problem_category_description` VARCHAR(80)
)

CREATE TABLE `Problem_Log` (
`problem_log_id` INTEGER PRIMARY KEY,
`assigned_to_staff_id` INTEGER NOT NULL,
`problem_id` INTEGER NOT NULL,
`problem_category_code` VARCHAR(20) NOT NULL,
`problem_status_code` VARCHAR(20) NOT NULL,
`log_entry_date` DATETIME,
`log_entry_description` VARCHAR(255),
`log_entry_fix` VARCHAR(255),
`other_log_details` VARCHAR(255),
FOREIGN KEY (`problem_category_code` ) REFERENCES `Problem_Category_Codes`(`problem_category_code` ),FOREIGN KEY (`assigned_to_staff_id` ) REFERENCES `Staff`(`staff_id` ),FOREIGN KEY (`problem_id` ) REFERENCES `Problems`(`problem_id` ),FOREIGN KEY (`problem_status_code` ) REFERENCES `Problem_Status_Codes`(`problem_status_code` )
)

CREATE TABLE `Problem_Status_Codes` (
`problem_status_code` VARCHAR(20) PRIMARY KEY,
`problem_status_description` VARCHAR(80)
)

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

CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`staff_first_name` VARCHAR(80),
`staff_last_name` VARCHAR(80),
`other_staff_details` VARCHAR(255)
)

CREATE TABLE `Problems` (
`problem_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`closure_authorised_by_staff_id` INTEGER NOT NULL,
`reported_by_staff_id` INTEGER NOT NULL,
`date_problem_reported` DATETIME NOT NULL,
`date_problem_closed` DATETIME,
`problem_description` VARCHAR(255),
`other_problem_details` VARCHAR(255),
FOREIGN KEY (`closure_authorised_by_staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Product`(`product_id` ),
FOREIGN KEY (`reported_by_staff_id` ) REFERENCES `Staff`(`staff_id` )
)

### Question
Which problems were reported before the date of any problem reported by the staff Lysanne Turcotte? Give me the ids of the problems.

### SQL
SELECT T1.problem_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE date_problem_reported  <  ( SELECT min(date_problem_reported) FROM problems AS T3 JOIN staff AS T4 ON T3.reported_by_staff_id = T4.staff_id WHERE T4.staff_first_name = "Lysanne" AND T4.staff_last_name = "Turcotte" )
### Database Schema
CREATE TABLE `Problem_Category_Codes` (
`problem_category_code` VARCHAR(20) PRIMARY KEY,
`problem_category_description` VARCHAR(80)
)

CREATE TABLE `Problem_Log` (
`problem_log_id` INTEGER PRIMARY KEY,
`assigned_to_staff_id` INTEGER NOT NULL,
`problem_id` INTEGER NOT NULL,
`problem_category_code` VARCHAR(20) NOT NULL,
`problem_status_code` VARCHAR(20) NOT NULL,
`log_entry_date` DATETIME,
`log_entry_description` VARCHAR(255),
`log_entry_fix` VARCHAR(255),
`other_log_details` VARCHAR(255),
FOREIGN KEY (`problem_category_code` ) REFERENCES `Problem_Category_Codes`(`problem_category_code` ),FOREIGN KEY (`assigned_to_staff_id` ) REFERENCES `Staff`(`staff_id` ),FOREIGN KEY (`problem_id` ) REFERENCES `Problems`(`problem_id` ),FOREIGN KEY (`problem_status_code` ) REFERENCES `Problem_Status_Codes`(`problem_status_code` )
)

CREATE TABLE `Problem_Status_Codes` (
`problem_status_code` VARCHAR(20) PRIMARY KEY,
`problem_status_description` VARCHAR(80)
)

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

CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`staff_first_name` VARCHAR(80),
`staff_last_name` VARCHAR(80),
`other_staff_details` VARCHAR(255)
)

CREATE TABLE `Problems` (
`problem_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`closure_authorised_by_staff_id` INTEGER NOT NULL,
`reported_by_staff_id` INTEGER NOT NULL,
`date_problem_reported` DATETIME NOT NULL,
`date_problem_closed` DATETIME,
`problem_description` VARCHAR(255),
`other_problem_details` VARCHAR(255),
FOREIGN KEY (`closure_authorised_by_staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Product`(`product_id` ),
FOREIGN KEY (`reported_by_staff_id` ) REFERENCES `Staff`(`staff_id` )
)

### Question
What are the ids of the problems reported after the date of any problems reported by Rylan Homenick?

### SQL
SELECT T1.problem_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE date_problem_reported  >  ( SELECT max(date_problem_reported) FROM problems AS T3 JOIN staff AS T4 ON T3.reported_by_staff_id = T4.staff_id WHERE T4.staff_first_name = "Rylan" AND T4.staff_last_name = "Homenick" )
### Database Schema
CREATE TABLE `Problem_Category_Codes` (
`problem_category_code` VARCHAR(20) PRIMARY KEY,
`problem_category_description` VARCHAR(80)
)

CREATE TABLE `Problem_Log` (
`problem_log_id` INTEGER PRIMARY KEY,
`assigned_to_staff_id` INTEGER NOT NULL,
`problem_id` INTEGER NOT NULL,
`problem_category_code` VARCHAR(20) NOT NULL,
`problem_status_code` VARCHAR(20) NOT NULL,
`log_entry_date` DATETIME,
`log_entry_description` VARCHAR(255),
`log_entry_fix` VARCHAR(255),
`other_log_details` VARCHAR(255),
FOREIGN KEY (`problem_category_code` ) REFERENCES `Problem_Category_Codes`(`problem_category_code` ),FOREIGN KEY (`assigned_to_staff_id` ) REFERENCES `Staff`(`staff_id` ),FOREIGN KEY (`problem_id` ) REFERENCES `Problems`(`problem_id` ),FOREIGN KEY (`problem_status_code` ) REFERENCES `Problem_Status_Codes`(`problem_status_code` )
)

CREATE TABLE `Problem_Status_Codes` (
`problem_status_code` VARCHAR(20) PRIMARY KEY,
`problem_status_description` VARCHAR(80)
)

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

CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`staff_first_name` VARCHAR(80),
`staff_last_name` VARCHAR(80),
`other_staff_details` VARCHAR(255)
)

CREATE TABLE `Problems` (
`problem_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`closure_authorised_by_staff_id` INTEGER NOT NULL,
`reported_by_staff_id` INTEGER NOT NULL,
`date_problem_reported` DATETIME NOT NULL,
`date_problem_closed` DATETIME,
`problem_description` VARCHAR(255),
`other_problem_details` VARCHAR(255),
FOREIGN KEY (`closure_authorised_by_staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Product`(`product_id` ),
FOREIGN KEY (`reported_by_staff_id` ) REFERENCES `Staff`(`staff_id` )
)

### Question
Find the ids of the problems reported after the date of any problems reported by the staff Rylan Homenick.

### SQL
SELECT T1.problem_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE date_problem_reported  >  ( SELECT max(date_problem_reported) FROM problems AS T3 JOIN staff AS T4 ON T3.reported_by_staff_id = T4.staff_id WHERE T4.staff_first_name = "Rylan" AND T4.staff_last_name = "Homenick" )
### Database Schema
CREATE TABLE `Problem_Category_Codes` (
`problem_category_code` VARCHAR(20) PRIMARY KEY,
`problem_category_description` VARCHAR(80)
)

CREATE TABLE `Problem_Log` (
`problem_log_id` INTEGER PRIMARY KEY,
`assigned_to_staff_id` INTEGER NOT NULL,
`problem_id` INTEGER NOT NULL,
`problem_category_code` VARCHAR(20) NOT NULL,
`problem_status_code` VARCHAR(20) NOT NULL,
`log_entry_date` DATETIME,
`log_entry_description` VARCHAR(255),
`log_entry_fix` VARCHAR(255),
`other_log_details` VARCHAR(255),
FOREIGN KEY (`problem_category_code` ) REFERENCES `Problem_Category_Codes`(`problem_category_code` ),FOREIGN KEY (`assigned_to_staff_id` ) REFERENCES `Staff`(`staff_id` ),FOREIGN KEY (`problem_id` ) REFERENCES `Problems`(`problem_id` ),FOREIGN KEY (`problem_status_code` ) REFERENCES `Problem_Status_Codes`(`problem_status_code` )
)

CREATE TABLE `Problem_Status_Codes` (
`problem_status_code` VARCHAR(20) PRIMARY KEY,
`problem_status_description` VARCHAR(80)
)

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

CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`staff_first_name` VARCHAR(80),
`staff_last_name` VARCHAR(80),
`other_staff_details` VARCHAR(255)
)

CREATE TABLE `Problems` (
`problem_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`closure_authorised_by_staff_id` INTEGER NOT NULL,
`reported_by_staff_id` INTEGER NOT NULL,
`date_problem_reported` DATETIME NOT NULL,
`date_problem_closed` DATETIME,
`problem_description` VARCHAR(255),
`other_problem_details` VARCHAR(255),
FOREIGN KEY (`closure_authorised_by_staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Product`(`product_id` ),
FOREIGN KEY (`reported_by_staff_id` ) REFERENCES `Staff`(`staff_id` )
)

### Question
Find the top 3 products which have the largest number of problems?

### SQL
SELECT T2.product_name FROM problems AS T1 JOIN product AS T2 ON T1.product_id = T2.product_id GROUP BY T2.product_name ORDER BY count(*) DESC LIMIT 3
### Database Schema
CREATE TABLE `Problem_Category_Codes` (
`problem_category_code` VARCHAR(20) PRIMARY KEY,
`problem_category_description` VARCHAR(80)
)

CREATE TABLE `Problem_Log` (
`problem_log_id` INTEGER PRIMARY KEY,
`assigned_to_staff_id` INTEGER NOT NULL,
`problem_id` INTEGER NOT NULL,
`problem_category_code` VARCHAR(20) NOT NULL,
`problem_status_code` VARCHAR(20) NOT NULL,
`log_entry_date` DATETIME,
`log_entry_description` VARCHAR(255),
`log_entry_fix` VARCHAR(255),
`other_log_details` VARCHAR(255),
FOREIGN KEY (`problem_category_code` ) REFERENCES `Problem_Category_Codes`(`problem_category_code` ),FOREIGN KEY (`assigned_to_staff_id` ) REFERENCES `Staff`(`staff_id` ),FOREIGN KEY (`problem_id` ) REFERENCES `Problems`(`problem_id` ),FOREIGN KEY (`problem_status_code` ) REFERENCES `Problem_Status_Codes`(`problem_status_code` )
)

CREATE TABLE `Problem_Status_Codes` (
`problem_status_code` VARCHAR(20) PRIMARY KEY,
`problem_status_description` VARCHAR(80)
)

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

CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`staff_first_name` VARCHAR(80),
`staff_last_name` VARCHAR(80),
`other_staff_details` VARCHAR(255)
)

CREATE TABLE `Problems` (
`problem_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`closure_authorised_by_staff_id` INTEGER NOT NULL,
`reported_by_staff_id` INTEGER NOT NULL,
`date_problem_reported` DATETIME NOT NULL,
`date_problem_closed` DATETIME,
`problem_description` VARCHAR(255),
`other_problem_details` VARCHAR(255),
FOREIGN KEY (`closure_authorised_by_staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Product`(`product_id` ),
FOREIGN KEY (`reported_by_staff_id` ) REFERENCES `Staff`(`staff_id` )
)

### Question
What are the three products that have the most problems?s

### SQL
SELECT T2.product_name FROM problems AS T1 JOIN product AS T2 ON T1.product_id = T2.product_id GROUP BY T2.product_name ORDER BY count(*) DESC LIMIT 3
### Database Schema
CREATE TABLE `Problem_Category_Codes` (
`problem_category_code` VARCHAR(20) PRIMARY KEY,
`problem_category_description` VARCHAR(80)
)

CREATE TABLE `Problem_Log` (
`problem_log_id` INTEGER PRIMARY KEY,
`assigned_to_staff_id` INTEGER NOT NULL,
`problem_id` INTEGER NOT NULL,
`problem_category_code` VARCHAR(20) NOT NULL,
`problem_status_code` VARCHAR(20) NOT NULL,
`log_entry_date` DATETIME,
`log_entry_description` VARCHAR(255),
`log_entry_fix` VARCHAR(255),
`other_log_details` VARCHAR(255),
FOREIGN KEY (`problem_category_code` ) REFERENCES `Problem_Category_Codes`(`problem_category_code` ),FOREIGN KEY (`assigned_to_staff_id` ) REFERENCES `Staff`(`staff_id` ),FOREIGN KEY (`problem_id` ) REFERENCES `Problems`(`problem_id` ),FOREIGN KEY (`problem_status_code` ) REFERENCES `Problem_Status_Codes`(`problem_status_code` )
)

CREATE TABLE `Problem_Status_Codes` (
`problem_status_code` VARCHAR(20) PRIMARY KEY,
`problem_status_description` VARCHAR(80)
)

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

CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`staff_first_name` VARCHAR(80),
`staff_last_name` VARCHAR(80),
`other_staff_details` VARCHAR(255)
)

CREATE TABLE `Problems` (
`problem_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`closure_authorised_by_staff_id` INTEGER NOT NULL,
`reported_by_staff_id` INTEGER NOT NULL,
`date_problem_reported` DATETIME NOT NULL,
`date_problem_closed` DATETIME,
`problem_description` VARCHAR(255),
`other_problem_details` VARCHAR(255),
FOREIGN KEY (`closure_authorised_by_staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Product`(`product_id` ),
FOREIGN KEY (`reported_by_staff_id` ) REFERENCES `Staff`(`staff_id` )
)

### Question
List the ids of the problems from the product "voluptatem" that are reported after 1995?

### SQL
SELECT T1.problem_id FROM problems AS T1 JOIN product AS T2 ON T1.product_id = T2.product_id WHERE T2.product_name = "voluptatem" AND T1.date_problem_reported > "1995"
### Database Schema
CREATE TABLE `Problem_Category_Codes` (
`problem_category_code` VARCHAR(20) PRIMARY KEY,
`problem_category_description` VARCHAR(80)
)

CREATE TABLE `Problem_Log` (
`problem_log_id` INTEGER PRIMARY KEY,
`assigned_to_staff_id` INTEGER NOT NULL,
`problem_id` INTEGER NOT NULL,
`problem_category_code` VARCHAR(20) NOT NULL,
`problem_status_code` VARCHAR(20) NOT NULL,
`log_entry_date` DATETIME,
`log_entry_description` VARCHAR(255),
`log_entry_fix` VARCHAR(255),
`other_log_details` VARCHAR(255),
FOREIGN KEY (`problem_category_code` ) REFERENCES `Problem_Category_Codes`(`problem_category_code` ),FOREIGN KEY (`assigned_to_staff_id` ) REFERENCES `Staff`(`staff_id` ),FOREIGN KEY (`problem_id` ) REFERENCES `Problems`(`problem_id` ),FOREIGN KEY (`problem_status_code` ) REFERENCES `Problem_Status_Codes`(`problem_status_code` )
)

CREATE TABLE `Problem_Status_Codes` (
`problem_status_code` VARCHAR(20) PRIMARY KEY,
`problem_status_description` VARCHAR(80)
)

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

CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`staff_first_name` VARCHAR(80),
`staff_last_name` VARCHAR(80),
`other_staff_details` VARCHAR(255)
)

CREATE TABLE `Problems` (
`problem_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`closure_authorised_by_staff_id` INTEGER NOT NULL,
`reported_by_staff_id` INTEGER NOT NULL,
`date_problem_reported` DATETIME NOT NULL,
`date_problem_closed` DATETIME,
`problem_description` VARCHAR(255),
`other_problem_details` VARCHAR(255),
FOREIGN KEY (`closure_authorised_by_staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Product`(`product_id` ),
FOREIGN KEY (`reported_by_staff_id` ) REFERENCES `Staff`(`staff_id` )
)

### Question
What are the ids of the problems that are from the product "voluptatem" and are reported after 1995?

### SQL
SELECT T1.problem_id FROM problems AS T1 JOIN product AS T2 ON T1.product_id = T2.product_id WHERE T2.product_name = "voluptatem" AND T1.date_problem_reported > "1995"
### Database Schema
CREATE TABLE `Problem_Category_Codes` (
`problem_category_code` VARCHAR(20) PRIMARY KEY,
`problem_category_description` VARCHAR(80)
)

CREATE TABLE `Problem_Log` (
`problem_log_id` INTEGER PRIMARY KEY,
`assigned_to_staff_id` INTEGER NOT NULL,
`problem_id` INTEGER NOT NULL,
`problem_category_code` VARCHAR(20) NOT NULL,
`problem_status_code` VARCHAR(20) NOT NULL,
`log_entry_date` DATETIME,
`log_entry_description` VARCHAR(255),
`log_entry_fix` VARCHAR(255),
`other_log_details` VARCHAR(255),
FOREIGN KEY (`problem_category_code` ) REFERENCES `Problem_Category_Codes`(`problem_category_code` ),FOREIGN KEY (`assigned_to_staff_id` ) REFERENCES `Staff`(`staff_id` ),FOREIGN KEY (`problem_id` ) REFERENCES `Problems`(`problem_id` ),FOREIGN KEY (`problem_status_code` ) REFERENCES `Problem_Status_Codes`(`problem_status_code` )
)

CREATE TABLE `Problem_Status_Codes` (
`problem_status_code` VARCHAR(20) PRIMARY KEY,
`problem_status_description` VARCHAR(80)
)

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

CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`staff_first_name` VARCHAR(80),
`staff_last_name` VARCHAR(80),
`other_staff_details` VARCHAR(255)
)

CREATE TABLE `Problems` (
`problem_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`closure_authorised_by_staff_id` INTEGER NOT NULL,
`reported_by_staff_id` INTEGER NOT NULL,
`date_problem_reported` DATETIME NOT NULL,
`date_problem_closed` DATETIME,
`problem_description` VARCHAR(255),
`other_problem_details` VARCHAR(255),
FOREIGN KEY (`closure_authorised_by_staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Product`(`product_id` ),
FOREIGN KEY (`reported_by_staff_id` ) REFERENCES `Staff`(`staff_id` )
)

### Question
Find the first and last name of the staff members who reported problems from the product "rem" but not "aut"?

### SQL
SELECT T3.staff_first_name ,  T3.staff_last_name FROM problems AS T1 JOIN product AS T2 JOIN staff AS T3 ON T1.product_id = T2.product_id AND T1.reported_by_staff_id = T3.staff_id WHERE T2.product_name = "rem" EXCEPT SELECT T3.staff_first_name ,  T3.staff_last_name FROM problems AS T1 JOIN product AS T2 JOIN staff AS T3 ON T1.product_id = T2.product_id AND T1.reported_by_staff_id = T3.staff_id WHERE T2.product_name = "aut"
### Database Schema
CREATE TABLE `Problem_Category_Codes` (
`problem_category_code` VARCHAR(20) PRIMARY KEY,
`problem_category_description` VARCHAR(80)
)

CREATE TABLE `Problem_Log` (
`problem_log_id` INTEGER PRIMARY KEY,
`assigned_to_staff_id` INTEGER NOT NULL,
`problem_id` INTEGER NOT NULL,
`problem_category_code` VARCHAR(20) NOT NULL,
`problem_status_code` VARCHAR(20) NOT NULL,
`log_entry_date` DATETIME,
`log_entry_description` VARCHAR(255),
`log_entry_fix` VARCHAR(255),
`other_log_details` VARCHAR(255),
FOREIGN KEY (`problem_category_code` ) REFERENCES `Problem_Category_Codes`(`problem_category_code` ),FOREIGN KEY (`assigned_to_staff_id` ) REFERENCES `Staff`(`staff_id` ),FOREIGN KEY (`problem_id` ) REFERENCES `Problems`(`problem_id` ),FOREIGN KEY (`problem_status_code` ) REFERENCES `Problem_Status_Codes`(`problem_status_code` )
)

CREATE TABLE `Problem_Status_Codes` (
`problem_status_code` VARCHAR(20) PRIMARY KEY,
`problem_status_description` VARCHAR(80)
)

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

CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`staff_first_name` VARCHAR(80),
`staff_last_name` VARCHAR(80),
`other_staff_details` VARCHAR(255)
)

CREATE TABLE `Problems` (
`problem_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`closure_authorised_by_staff_id` INTEGER NOT NULL,
`reported_by_staff_id` INTEGER NOT NULL,
`date_problem_reported` DATETIME NOT NULL,
`date_problem_closed` DATETIME,
`problem_description` VARCHAR(255),
`other_problem_details` VARCHAR(255),
FOREIGN KEY (`closure_authorised_by_staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Product`(`product_id` ),
FOREIGN KEY (`reported_by_staff_id` ) REFERENCES `Staff`(`staff_id` )
)

### Question
Which staff members who reported problems from the product "rem" but not "aut"? Give me their first and last names.

### SQL
SELECT T3.staff_first_name ,  T3.staff_last_name FROM problems AS T1 JOIN product AS T2 JOIN staff AS T3 ON T1.product_id = T2.product_id AND T1.reported_by_staff_id = T3.staff_id WHERE T2.product_name = "rem" EXCEPT SELECT T3.staff_first_name ,  T3.staff_last_name FROM problems AS T1 JOIN product AS T2 JOIN staff AS T3 ON T1.product_id = T2.product_id AND T1.reported_by_staff_id = T3.staff_id WHERE T2.product_name = "aut"
### Database Schema
CREATE TABLE `Problem_Category_Codes` (
`problem_category_code` VARCHAR(20) PRIMARY KEY,
`problem_category_description` VARCHAR(80)
)

CREATE TABLE `Problem_Log` (
`problem_log_id` INTEGER PRIMARY KEY,
`assigned_to_staff_id` INTEGER NOT NULL,
`problem_id` INTEGER NOT NULL,
`problem_category_code` VARCHAR(20) NOT NULL,
`problem_status_code` VARCHAR(20) NOT NULL,
`log_entry_date` DATETIME,
`log_entry_description` VARCHAR(255),
`log_entry_fix` VARCHAR(255),
`other_log_details` VARCHAR(255),
FOREIGN KEY (`problem_category_code` ) REFERENCES `Problem_Category_Codes`(`problem_category_code` ),FOREIGN KEY (`assigned_to_staff_id` ) REFERENCES `Staff`(`staff_id` ),FOREIGN KEY (`problem_id` ) REFERENCES `Problems`(`problem_id` ),FOREIGN KEY (`problem_status_code` ) REFERENCES `Problem_Status_Codes`(`problem_status_code` )
)

CREATE TABLE `Problem_Status_Codes` (
`problem_status_code` VARCHAR(20) PRIMARY KEY,
`problem_status_description` VARCHAR(80)
)

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

CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`staff_first_name` VARCHAR(80),
`staff_last_name` VARCHAR(80),
`other_staff_details` VARCHAR(255)
)

CREATE TABLE `Problems` (
`problem_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`closure_authorised_by_staff_id` INTEGER NOT NULL,
`reported_by_staff_id` INTEGER NOT NULL,
`date_problem_reported` DATETIME NOT NULL,
`date_problem_closed` DATETIME,
`problem_description` VARCHAR(255),
`other_problem_details` VARCHAR(255),
FOREIGN KEY (`closure_authorised_by_staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Product`(`product_id` ),
FOREIGN KEY (`reported_by_staff_id` ) REFERENCES `Staff`(`staff_id` )
)

### Question
Find the products which have problems reported by both Lacey Bosco and Kenton Champlin?

### SQL
SELECT T2.product_name FROM problems AS T1 JOIN product AS T2 JOIN staff AS T3 ON T1.product_id = T2.product_id AND T1.reported_by_staff_id = T3.staff_id WHERE T3.staff_first_name = "Lacey" AND T3.staff_last_name = "Bosco" INTERSECT SELECT T2.product_name FROM problems AS T1 JOIN product AS T2 JOIN staff AS T3 ON T1.product_id = T2.product_id AND T1.reported_by_staff_id = T3.staff_id WHERE T3.staff_first_name = "Kenton" AND T3.staff_last_name = "Champlin"
### Database Schema
CREATE TABLE `Problem_Category_Codes` (
`problem_category_code` VARCHAR(20) PRIMARY KEY,
`problem_category_description` VARCHAR(80)
)

CREATE TABLE `Problem_Log` (
`problem_log_id` INTEGER PRIMARY KEY,
`assigned_to_staff_id` INTEGER NOT NULL,
`problem_id` INTEGER NOT NULL,
`problem_category_code` VARCHAR(20) NOT NULL,
`problem_status_code` VARCHAR(20) NOT NULL,
`log_entry_date` DATETIME,
`log_entry_description` VARCHAR(255),
`log_entry_fix` VARCHAR(255),
`other_log_details` VARCHAR(255),
FOREIGN KEY (`problem_category_code` ) REFERENCES `Problem_Category_Codes`(`problem_category_code` ),FOREIGN KEY (`assigned_to_staff_id` ) REFERENCES `Staff`(`staff_id` ),FOREIGN KEY (`problem_id` ) REFERENCES `Problems`(`problem_id` ),FOREIGN KEY (`problem_status_code` ) REFERENCES `Problem_Status_Codes`(`problem_status_code` )
)

CREATE TABLE `Problem_Status_Codes` (
`problem_status_code` VARCHAR(20) PRIMARY KEY,
`problem_status_description` VARCHAR(80)
)

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

CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`staff_first_name` VARCHAR(80),
`staff_last_name` VARCHAR(80),
`other_staff_details` VARCHAR(255)
)

CREATE TABLE `Problems` (
`problem_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`closure_authorised_by_staff_id` INTEGER NOT NULL,
`reported_by_staff_id` INTEGER NOT NULL,
`date_problem_reported` DATETIME NOT NULL,
`date_problem_closed` DATETIME,
`problem_description` VARCHAR(255),
`other_problem_details` VARCHAR(255),
FOREIGN KEY (`closure_authorised_by_staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Product`(`product_id` ),
FOREIGN KEY (`reported_by_staff_id` ) REFERENCES `Staff`(`staff_id` )
)

### Question
Which products have problems reported by both the staff named Lacey Bosco and the staff named Kenton Champlin?

### SQL
SELECT T2.product_name FROM problems AS T1 JOIN product AS T2 JOIN staff AS T3 ON T1.product_id = T2.product_id AND T1.reported_by_staff_id = T3.staff_id WHERE T3.staff_first_name = "Lacey" AND T3.staff_last_name = "Bosco" INTERSECT SELECT T2.product_name FROM problems AS T1 JOIN product AS T2 JOIN staff AS T3 ON T1.product_id = T2.product_id AND T1.reported_by_staff_id = T3.staff_id WHERE T3.staff_first_name = "Kenton" AND T3.staff_last_name = "Champlin"
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
How many branches where have more than average number of memberships are there?

### SQL
SELECT count(*) FROM branch WHERE membership_amount  >  (SELECT avg(membership_amount) FROM branch)
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
What is the number of branches that have more than the average number of memberships?

### SQL
SELECT count(*) FROM branch WHERE membership_amount  >  (SELECT avg(membership_amount) FROM branch)
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
Show name, address road, and city for all branches sorted by open year.

### SQL
SELECT name ,  address_road ,  city FROM branch ORDER BY open_year
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
What are the names, address roads, and cities of the branches ordered by opening year?

### SQL
SELECT name ,  address_road ,  city FROM branch ORDER BY open_year
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
What are names for top three branches with most number of membership?

### SQL
SELECT name FROM branch ORDER BY membership_amount DESC LIMIT 3
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
What are the names for the 3 branches that have the most memberships?

### SQL
SELECT name FROM branch ORDER BY membership_amount DESC LIMIT 3
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
Show all distinct city where branches with at least 100 memberships are located.

### SQL
SELECT DISTINCT city FROM branch WHERE membership_amount  >=  100
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
What are the different cities that have more than 100 memberships?

### SQL
SELECT DISTINCT city FROM branch WHERE membership_amount  >=  100
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
List all open years when at least two shops are opened.

### SQL
SELECT open_year FROM branch GROUP BY open_year HAVING count(*)  >=  2
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
What are the opening years in which at least two shops opened?

### SQL
SELECT open_year FROM branch GROUP BY open_year HAVING count(*)  >=  2
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
Show minimum and maximum amount of memberships for all branches opened in 2011 or located at city London.

### SQL
SELECT min(membership_amount) ,  max(membership_amount) FROM branch WHERE open_year  =  2011 OR city  =  'London'
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
What are the minimum and maximum membership amounts for all branches that either opened in 2011 or are located in London?

### SQL
SELECT min(membership_amount) ,  max(membership_amount) FROM branch WHERE open_year  =  2011 OR city  =  'London'
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
Show the city and the number of branches opened before 2010 for each city.

### SQL
SELECT city ,  count(*) FROM branch WHERE open_year  <  2010 GROUP BY city