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
How many distinct complaint type codes are there in the database?

### SQL
SELECT count(DISTINCT complaint_type_code) 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
Count the number of different complaint type codes.

### SQL
SELECT count(DISTINCT complaint_type_code) 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 address line 1 and 2 of the customer with email "vbogisich@example.org".

### SQL
SELECT address_line_1 ,  address_line_2 FROM customers WHERE email_address  =  "vbogisich@example.org"
### 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 lines 1 and 2 of the addressed of the customer with the email "vbogisich@example.org"?

### SQL
SELECT address_line_1 ,  address_line_2 FROM customers WHERE email_address  =  "vbogisich@example.org"
### 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 number of complaints with Product Failure type for each complaint status.

### SQL
SELECT complaint_status_code ,  count(*) FROM complaints WHERE complaint_type_code  =  "Product Failure" GROUP BY complaint_status_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
Of complaints with the type code "Product Failure", how many had each different status code?

### SQL
SELECT complaint_status_code ,  count(*) FROM complaints WHERE complaint_type_code  =  "Product Failure" GROUP BY complaint_status_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
What is first names of the top 5 staff who have handled the greatest number of complaints?

### SQL
SELECT t1.first_name FROM staff AS t1 JOIN complaints AS t2 ON t1.staff_id  =  t2.staff_id GROUP BY t2.staff_id ORDER BY count(*) LIMIT 5
### 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 first names of the 5 staff members who have handled the most complaints.

### SQL
SELECT t1.first_name FROM staff AS t1 JOIN complaints AS t2 ON t1.staff_id  =  t2.staff_id GROUP BY t2.staff_id ORDER BY count(*) LIMIT 5
### 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 state has the most customers?

### SQL
SELECT state FROM customers GROUP BY state 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
Give the state that has the most customers.

### SQL
SELECT state FROM customers GROUP BY state ORDER BY count(*) LIMIT 1
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
How many submissions are there?

### SQL
SELECT count(*) FROM submission
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
Count the number of submissions.

### SQL
SELECT count(*) FROM submission
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
List the authors of submissions in ascending order of scores.

### SQL
SELECT Author FROM submission ORDER BY Scores ASC
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
Find the author for each submission and list them in ascending order of submission score.

### SQL
SELECT Author FROM submission ORDER BY Scores ASC
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
What are the authors of submissions and their colleges?

### SQL
SELECT Author ,  College FROM submission
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
For each submission, show the author and their affiliated college.

### SQL
SELECT Author ,  College FROM submission
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
Show the names of authors from college "Florida" or "Temple"

### SQL
SELECT Author FROM submission WHERE College  =  "Florida" OR College  =  "Temple"
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
Which authors with submissions are from college "Florida" or "Temple"?

### SQL
SELECT Author FROM submission WHERE College  =  "Florida" OR College  =  "Temple"
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
What is the average score of submissions?

### SQL
SELECT avg(Scores) FROM submission
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
Compute the average score of submissions.

### SQL
SELECT avg(Scores) FROM submission
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
What is the author of the submission with the highest score?

### SQL
SELECT Author FROM submission ORDER BY Scores DESC LIMIT 1
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
Find the author who achieved the highest score in a submission.

### SQL
SELECT Author FROM submission ORDER BY Scores DESC LIMIT 1
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
Show different colleges along with the number of authors of submission from each college.

### SQL
SELECT College ,  COUNT(*) FROM submission GROUP BY College
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
For each college, return the college name and the count of authors with submissions from that college.

### SQL
SELECT College ,  COUNT(*) FROM submission GROUP BY College
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
Show the most common college of authors of submissions.

### SQL
SELECT College FROM submission GROUP BY College ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
Which college has the most authors with submissions?

### SQL
SELECT College FROM submission GROUP BY College ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
Show the colleges that have both authors with submission score larger than 90 and authors with submission score smaller than 80.

### SQL
SELECT College FROM submission WHERE Scores  >  90 INTERSECT SELECT College FROM submission WHERE Scores  <  80
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
Which colleges have both authors with submission score above 90 and authors with submission score below 80?

### SQL
SELECT College FROM submission WHERE Scores  >  90 INTERSECT SELECT College FROM submission WHERE Scores  <  80
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
Show the authors of submissions and the acceptance results of their submissions.

### SQL
SELECT T2.Author ,  T1.Result FROM acceptance AS T1 JOIN submission AS T2 ON T1.Submission_ID  =  T2.Submission_ID
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
For each submission, find its author and acceptance result.

### SQL
SELECT T2.Author ,  T1.Result FROM acceptance AS T1 JOIN submission AS T2 ON T1.Submission_ID  =  T2.Submission_ID
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
Show the result of the submission with the highest score.

### SQL
SELECT T1.Result FROM acceptance AS T1 JOIN submission AS T2 ON T1.Submission_ID  =  T2.Submission_ID ORDER BY T2.Scores DESC LIMIT 1
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
Which submission received the highest score in acceptance result. Show me the result.

### SQL
SELECT T1.Result FROM acceptance AS T1 JOIN submission AS T2 ON T1.Submission_ID  =  T2.Submission_ID ORDER BY T2.Scores DESC LIMIT 1
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
Show each author and the number of workshops they submitted to.

### SQL
SELECT T2.Author ,  COUNT(DISTINCT T1.workshop_id) FROM acceptance AS T1 JOIN submission AS T2 ON T1.Submission_ID  =  T2.Submission_ID GROUP BY T2.Author
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
How many workshops did each author submit to? Return the author name and the number of workshops.

### SQL
SELECT T2.Author ,  COUNT(DISTINCT T1.workshop_id) FROM acceptance AS T1 JOIN submission AS T2 ON T1.Submission_ID  =  T2.Submission_ID GROUP BY T2.Author
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
Show the authors who have submissions to more than one workshop.

### SQL
SELECT T2.Author FROM acceptance AS T1 JOIN submission AS T2 ON T1.Submission_ID  =  T2.Submission_ID GROUP BY T2.Author HAVING COUNT(DISTINCT T1.workshop_id)  >  1
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
Which authors have submitted to more than one workshop?

### SQL
SELECT T2.Author FROM acceptance AS T1 JOIN submission AS T2 ON T1.Submission_ID  =  T2.Submission_ID GROUP BY T2.Author HAVING COUNT(DISTINCT T1.workshop_id)  >  1
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
Show the date and venue of each workshop in ascending alphabetical order of the venue.

### SQL
SELECT Date ,  Venue FROM workshop ORDER BY Venue
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
Sort the each workshop in alphabetical order of the venue. Return the date and venue of each workshop.

### SQL
SELECT Date ,  Venue FROM workshop ORDER BY Venue
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
List the authors who do not have submission to any workshop.

### SQL
SELECT Author FROM submission WHERE Submission_ID NOT IN (SELECT Submission_ID FROM acceptance)
### Database Schema
CREATE TABLE "workshop" (
"Workshop_ID" int,
"Date" text,
"Venue" text,
"Name" text,
PRIMARY KEY ("Workshop_ID")
)

CREATE TABLE "submission" (
"Submission_ID" int,
"Scores" real,
"Author" text,
"College" text,
PRIMARY KEY ("Submission_ID")
)

CREATE TABLE "Acceptance" (
"Submission_ID" int,
"Workshop_ID" int,
"Result" text,
PRIMARY KEY ("Submission_ID","Workshop_ID"),
FOREIGN KEY ("Submission_ID") REFERENCES `submission`("Submission_ID"),
FOREIGN KEY ("Workshop_ID") REFERENCES `workshop`("Workshop_ID")
)

### Question
Which authors did not submit to any workshop?

### SQL
SELECT Author FROM submission WHERE Submission_ID NOT IN (SELECT Submission_ID FROM acceptance)
### Database Schema
CREATE TABLE `Investors` (
`investor_id` INTEGER PRIMARY KEY,
`Investor_details` VARCHAR(255)
)

CREATE TABLE `Lots` (
`lot_id` INTEGER PRIMARY KEY,
`investor_id` INTEGER NOT NULL,
`lot_details` VARCHAR(255),
FOREIGN KEY (`investor_id` ) REFERENCES `Investors`(`investor_id` )
)

CREATE TABLE `Ref_Transaction_Types` (
`transaction_type_code` VARCHAR(10) PRIMARY KEY,
`transaction_type_description` VARCHAR(80) NOT NULL
)

CREATE TABLE `Transactions` (
`transaction_id` INTEGER PRIMARY KEY,
`investor_id` INTEGER NOT NULL,
`transaction_type_code` VARCHAR(10) NOT NULL,
`date_of_transaction` DATETIME,
`amount_of_transaction` DECIMAL(19,4),
`share_count` VARCHAR(40),
`other_details` VARCHAR(255),
FOREIGN KEY (`investor_id` ) REFERENCES `Investors`(`investor_id` ),FOREIGN KEY (`transaction_type_code` ) REFERENCES `Ref_Transaction_Types`(`transaction_type_code` )
)

CREATE TABLE `Sales` (
`sales_transaction_id` INTEGER PRIMARY KEY,
`sales_details` VARCHAR(255),
FOREIGN KEY (`sales_transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

CREATE TABLE `Purchases` (
`purchase_transaction_id` INTEGER NOT NULL,
`purchase_details` VARCHAR(255) NOT NULL,
FOREIGN KEY (`purchase_transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

CREATE TABLE `Transactions_Lots` (
`transaction_id` INTEGER NOT NULL,
`lot_id` INTEGER NOT NULL,
FOREIGN KEY (`lot_id` ) REFERENCES `Lots`(`lot_id` ),
FOREIGN KEY (`transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

### Question
Find the number of investors in total.

### SQL
SELECT count(*) FROM INVESTORS
### Database Schema
CREATE TABLE `Investors` (
`investor_id` INTEGER PRIMARY KEY,
`Investor_details` VARCHAR(255)
)

CREATE TABLE `Lots` (
`lot_id` INTEGER PRIMARY KEY,
`investor_id` INTEGER NOT NULL,
`lot_details` VARCHAR(255),
FOREIGN KEY (`investor_id` ) REFERENCES `Investors`(`investor_id` )
)

CREATE TABLE `Ref_Transaction_Types` (
`transaction_type_code` VARCHAR(10) PRIMARY KEY,
`transaction_type_description` VARCHAR(80) NOT NULL
)

CREATE TABLE `Transactions` (
`transaction_id` INTEGER PRIMARY KEY,
`investor_id` INTEGER NOT NULL,
`transaction_type_code` VARCHAR(10) NOT NULL,
`date_of_transaction` DATETIME,
`amount_of_transaction` DECIMAL(19,4),
`share_count` VARCHAR(40),
`other_details` VARCHAR(255),
FOREIGN KEY (`investor_id` ) REFERENCES `Investors`(`investor_id` ),FOREIGN KEY (`transaction_type_code` ) REFERENCES `Ref_Transaction_Types`(`transaction_type_code` )
)

CREATE TABLE `Sales` (
`sales_transaction_id` INTEGER PRIMARY KEY,
`sales_details` VARCHAR(255),
FOREIGN KEY (`sales_transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

CREATE TABLE `Purchases` (
`purchase_transaction_id` INTEGER NOT NULL,
`purchase_details` VARCHAR(255) NOT NULL,
FOREIGN KEY (`purchase_transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

CREATE TABLE `Transactions_Lots` (
`transaction_id` INTEGER NOT NULL,
`lot_id` INTEGER NOT NULL,
FOREIGN KEY (`lot_id` ) REFERENCES `Lots`(`lot_id` ),
FOREIGN KEY (`transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

### Question
Show all investor details.

### SQL
SELECT Investor_details FROM INVESTORS
### Database Schema
CREATE TABLE `Investors` (
`investor_id` INTEGER PRIMARY KEY,
`Investor_details` VARCHAR(255)
)

CREATE TABLE `Lots` (
`lot_id` INTEGER PRIMARY KEY,
`investor_id` INTEGER NOT NULL,
`lot_details` VARCHAR(255),
FOREIGN KEY (`investor_id` ) REFERENCES `Investors`(`investor_id` )
)

CREATE TABLE `Ref_Transaction_Types` (
`transaction_type_code` VARCHAR(10) PRIMARY KEY,
`transaction_type_description` VARCHAR(80) NOT NULL
)

CREATE TABLE `Transactions` (
`transaction_id` INTEGER PRIMARY KEY,
`investor_id` INTEGER NOT NULL,
`transaction_type_code` VARCHAR(10) NOT NULL,
`date_of_transaction` DATETIME,
`amount_of_transaction` DECIMAL(19,4),
`share_count` VARCHAR(40),
`other_details` VARCHAR(255),
FOREIGN KEY (`investor_id` ) REFERENCES `Investors`(`investor_id` ),FOREIGN KEY (`transaction_type_code` ) REFERENCES `Ref_Transaction_Types`(`transaction_type_code` )
)

CREATE TABLE `Sales` (
`sales_transaction_id` INTEGER PRIMARY KEY,
`sales_details` VARCHAR(255),
FOREIGN KEY (`sales_transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

CREATE TABLE `Purchases` (
`purchase_transaction_id` INTEGER NOT NULL,
`purchase_details` VARCHAR(255) NOT NULL,
FOREIGN KEY (`purchase_transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

CREATE TABLE `Transactions_Lots` (
`transaction_id` INTEGER NOT NULL,
`lot_id` INTEGER NOT NULL,
FOREIGN KEY (`lot_id` ) REFERENCES `Lots`(`lot_id` ),
FOREIGN KEY (`transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

### Question
Show all distinct lot details.

### SQL
SELECT DISTINCT lot_details FROM LOTS
### Database Schema
CREATE TABLE `Investors` (
`investor_id` INTEGER PRIMARY KEY,
`Investor_details` VARCHAR(255)
)

CREATE TABLE `Lots` (
`lot_id` INTEGER PRIMARY KEY,
`investor_id` INTEGER NOT NULL,
`lot_details` VARCHAR(255),
FOREIGN KEY (`investor_id` ) REFERENCES `Investors`(`investor_id` )
)

CREATE TABLE `Ref_Transaction_Types` (
`transaction_type_code` VARCHAR(10) PRIMARY KEY,
`transaction_type_description` VARCHAR(80) NOT NULL
)

CREATE TABLE `Transactions` (
`transaction_id` INTEGER PRIMARY KEY,
`investor_id` INTEGER NOT NULL,
`transaction_type_code` VARCHAR(10) NOT NULL,
`date_of_transaction` DATETIME,
`amount_of_transaction` DECIMAL(19,4),
`share_count` VARCHAR(40),
`other_details` VARCHAR(255),
FOREIGN KEY (`investor_id` ) REFERENCES `Investors`(`investor_id` ),FOREIGN KEY (`transaction_type_code` ) REFERENCES `Ref_Transaction_Types`(`transaction_type_code` )
)

CREATE TABLE `Sales` (
`sales_transaction_id` INTEGER PRIMARY KEY,
`sales_details` VARCHAR(255),
FOREIGN KEY (`sales_transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

CREATE TABLE `Purchases` (
`purchase_transaction_id` INTEGER NOT NULL,
`purchase_details` VARCHAR(255) NOT NULL,
FOREIGN KEY (`purchase_transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

CREATE TABLE `Transactions_Lots` (
`transaction_id` INTEGER NOT NULL,
`lot_id` INTEGER NOT NULL,
FOREIGN KEY (`lot_id` ) REFERENCES `Lots`(`lot_id` ),
FOREIGN KEY (`transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

### Question
Show the maximum amount of transaction.

### SQL
SELECT max(amount_of_transaction) FROM TRANSACTIONS
### Database Schema
CREATE TABLE `Investors` (
`investor_id` INTEGER PRIMARY KEY,
`Investor_details` VARCHAR(255)
)

CREATE TABLE `Lots` (
`lot_id` INTEGER PRIMARY KEY,
`investor_id` INTEGER NOT NULL,
`lot_details` VARCHAR(255),
FOREIGN KEY (`investor_id` ) REFERENCES `Investors`(`investor_id` )
)

CREATE TABLE `Ref_Transaction_Types` (
`transaction_type_code` VARCHAR(10) PRIMARY KEY,
`transaction_type_description` VARCHAR(80) NOT NULL
)

CREATE TABLE `Transactions` (
`transaction_id` INTEGER PRIMARY KEY,
`investor_id` INTEGER NOT NULL,
`transaction_type_code` VARCHAR(10) NOT NULL,
`date_of_transaction` DATETIME,
`amount_of_transaction` DECIMAL(19,4),
`share_count` VARCHAR(40),
`other_details` VARCHAR(255),
FOREIGN KEY (`investor_id` ) REFERENCES `Investors`(`investor_id` ),FOREIGN KEY (`transaction_type_code` ) REFERENCES `Ref_Transaction_Types`(`transaction_type_code` )
)

CREATE TABLE `Sales` (
`sales_transaction_id` INTEGER PRIMARY KEY,
`sales_details` VARCHAR(255),
FOREIGN KEY (`sales_transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

CREATE TABLE `Purchases` (
`purchase_transaction_id` INTEGER NOT NULL,
`purchase_details` VARCHAR(255) NOT NULL,
FOREIGN KEY (`purchase_transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

CREATE TABLE `Transactions_Lots` (
`transaction_id` INTEGER NOT NULL,
`lot_id` INTEGER NOT NULL,
FOREIGN KEY (`lot_id` ) REFERENCES `Lots`(`lot_id` ),
FOREIGN KEY (`transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

### Question
Show all date and share count of transactions.

### SQL
SELECT date_of_transaction ,  share_count FROM TRANSACTIONS
### Database Schema
CREATE TABLE `Investors` (
`investor_id` INTEGER PRIMARY KEY,
`Investor_details` VARCHAR(255)
)

CREATE TABLE `Lots` (
`lot_id` INTEGER PRIMARY KEY,
`investor_id` INTEGER NOT NULL,
`lot_details` VARCHAR(255),
FOREIGN KEY (`investor_id` ) REFERENCES `Investors`(`investor_id` )
)

CREATE TABLE `Ref_Transaction_Types` (
`transaction_type_code` VARCHAR(10) PRIMARY KEY,
`transaction_type_description` VARCHAR(80) NOT NULL
)

CREATE TABLE `Transactions` (
`transaction_id` INTEGER PRIMARY KEY,
`investor_id` INTEGER NOT NULL,
`transaction_type_code` VARCHAR(10) NOT NULL,
`date_of_transaction` DATETIME,
`amount_of_transaction` DECIMAL(19,4),
`share_count` VARCHAR(40),
`other_details` VARCHAR(255),
FOREIGN KEY (`investor_id` ) REFERENCES `Investors`(`investor_id` ),FOREIGN KEY (`transaction_type_code` ) REFERENCES `Ref_Transaction_Types`(`transaction_type_code` )
)

CREATE TABLE `Sales` (
`sales_transaction_id` INTEGER PRIMARY KEY,
`sales_details` VARCHAR(255),
FOREIGN KEY (`sales_transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

CREATE TABLE `Purchases` (
`purchase_transaction_id` INTEGER NOT NULL,
`purchase_details` VARCHAR(255) NOT NULL,
FOREIGN KEY (`purchase_transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

CREATE TABLE `Transactions_Lots` (
`transaction_id` INTEGER NOT NULL,
`lot_id` INTEGER NOT NULL,
FOREIGN KEY (`lot_id` ) REFERENCES `Lots`(`lot_id` ),
FOREIGN KEY (`transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

### Question
What is the total share of transactions?

### SQL
SELECT sum(share_count) FROM TRANSACTIONS
### Database Schema
CREATE TABLE `Investors` (
`investor_id` INTEGER PRIMARY KEY,
`Investor_details` VARCHAR(255)
)

CREATE TABLE `Lots` (
`lot_id` INTEGER PRIMARY KEY,
`investor_id` INTEGER NOT NULL,
`lot_details` VARCHAR(255),
FOREIGN KEY (`investor_id` ) REFERENCES `Investors`(`investor_id` )
)

CREATE TABLE `Ref_Transaction_Types` (
`transaction_type_code` VARCHAR(10) PRIMARY KEY,
`transaction_type_description` VARCHAR(80) NOT NULL
)

CREATE TABLE `Transactions` (
`transaction_id` INTEGER PRIMARY KEY,
`investor_id` INTEGER NOT NULL,
`transaction_type_code` VARCHAR(10) NOT NULL,
`date_of_transaction` DATETIME,
`amount_of_transaction` DECIMAL(19,4),
`share_count` VARCHAR(40),
`other_details` VARCHAR(255),
FOREIGN KEY (`investor_id` ) REFERENCES `Investors`(`investor_id` ),FOREIGN KEY (`transaction_type_code` ) REFERENCES `Ref_Transaction_Types`(`transaction_type_code` )
)

CREATE TABLE `Sales` (
`sales_transaction_id` INTEGER PRIMARY KEY,
`sales_details` VARCHAR(255),
FOREIGN KEY (`sales_transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

CREATE TABLE `Purchases` (
`purchase_transaction_id` INTEGER NOT NULL,
`purchase_details` VARCHAR(255) NOT NULL,
FOREIGN KEY (`purchase_transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

CREATE TABLE `Transactions_Lots` (
`transaction_id` INTEGER NOT NULL,
`lot_id` INTEGER NOT NULL,
FOREIGN KEY (`lot_id` ) REFERENCES `Lots`(`lot_id` ),
FOREIGN KEY (`transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

### Question
Show all transaction ids with transaction code 'PUR'.

### SQL
SELECT transaction_id FROM TRANSACTIONS WHERE transaction_type_code  =  'PUR'