lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE "product" (
"product_id" int,
"product" text,
"dimensions" text,
"dpi" real,
"pages_per_minute_color" real,
"max_page_size" text,
"interface" text,
PRIMARY KEY ("product_id")
)

CREATE TABLE "store" (
"Store_ID" int,
"Store_Name" text,
"Type" text,
"Area_size" real,
"Number_of_product_category" real,
"Ranking" int,
PRIMARY KEY ("Store_ID")
)

CREATE TABLE "district" (
"District_ID" int,
"District_name" text,
"Headquartered_City" text,
"City_Population" real,
"City_Area" real,
PRIMARY KEY ("District_ID")
)

CREATE TABLE "store_product" (
"Store_ID" int,
"Product_ID" int,
PRIMARY KEY ("Store_ID","Product_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`Product_ID`) REFERENCES `product`(`Product_ID`)
)

CREATE TABLE "store_district" (
"Store_ID" int,
"District_ID" int,
PRIMARY KEY ("Store_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`District_ID`) REFERENCES `district`(`District_ID`)
)

### Question
What is the city with the most number of flagship stores?

### SQL
SELECT t3.headquartered_city FROM store AS t1 JOIN store_district AS t2 ON t1.store_id  =  t2.store_id JOIN district AS t3 ON t2.district_id  =  t3.district_id GROUP BY t3.headquartered_city ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "product" (
"product_id" int,
"product" text,
"dimensions" text,
"dpi" real,
"pages_per_minute_color" real,
"max_page_size" text,
"interface" text,
PRIMARY KEY ("product_id")
)

CREATE TABLE "store" (
"Store_ID" int,
"Store_Name" text,
"Type" text,
"Area_size" real,
"Number_of_product_category" real,
"Ranking" int,
PRIMARY KEY ("Store_ID")
)

CREATE TABLE "district" (
"District_ID" int,
"District_name" text,
"Headquartered_City" text,
"City_Population" real,
"City_Area" real,
PRIMARY KEY ("District_ID")
)

CREATE TABLE "store_product" (
"Store_ID" int,
"Product_ID" int,
PRIMARY KEY ("Store_ID","Product_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`Product_ID`) REFERENCES `product`(`Product_ID`)
)

CREATE TABLE "store_district" (
"Store_ID" int,
"District_ID" int,
PRIMARY KEY ("Store_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`District_ID`) REFERENCES `district`(`District_ID`)
)

### Question
What is the average pages per minute color?

### SQL
SELECT avg(pages_per_minute_color) FROM product
### Database Schema
CREATE TABLE "product" (
"product_id" int,
"product" text,
"dimensions" text,
"dpi" real,
"pages_per_minute_color" real,
"max_page_size" text,
"interface" text,
PRIMARY KEY ("product_id")
)

CREATE TABLE "store" (
"Store_ID" int,
"Store_Name" text,
"Type" text,
"Area_size" real,
"Number_of_product_category" real,
"Ranking" int,
PRIMARY KEY ("Store_ID")
)

CREATE TABLE "district" (
"District_ID" int,
"District_name" text,
"Headquartered_City" text,
"City_Population" real,
"City_Area" real,
PRIMARY KEY ("District_ID")
)

CREATE TABLE "store_product" (
"Store_ID" int,
"Product_ID" int,
PRIMARY KEY ("Store_ID","Product_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`Product_ID`) REFERENCES `product`(`Product_ID`)
)

CREATE TABLE "store_district" (
"Store_ID" int,
"District_ID" int,
PRIMARY KEY ("Store_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`District_ID`) REFERENCES `district`(`District_ID`)
)

### Question
What is the average number of pages per minute color?

### SQL
SELECT avg(pages_per_minute_color) FROM product
### Database Schema
CREATE TABLE "product" (
"product_id" int,
"product" text,
"dimensions" text,
"dpi" real,
"pages_per_minute_color" real,
"max_page_size" text,
"interface" text,
PRIMARY KEY ("product_id")
)

CREATE TABLE "store" (
"Store_ID" int,
"Store_Name" text,
"Type" text,
"Area_size" real,
"Number_of_product_category" real,
"Ranking" int,
PRIMARY KEY ("Store_ID")
)

CREATE TABLE "district" (
"District_ID" int,
"District_name" text,
"Headquartered_City" text,
"City_Population" real,
"City_Area" real,
PRIMARY KEY ("District_ID")
)

CREATE TABLE "store_product" (
"Store_ID" int,
"Product_ID" int,
PRIMARY KEY ("Store_ID","Product_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`Product_ID`) REFERENCES `product`(`Product_ID`)
)

CREATE TABLE "store_district" (
"Store_ID" int,
"District_ID" int,
PRIMARY KEY ("Store_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`District_ID`) REFERENCES `district`(`District_ID`)
)

### Question
What products are available at store named "Miramichi"?

### SQL
SELECT t1.product FROM product AS t1 JOIN store_product AS t2 ON t1.product_id  =  t2.product_id JOIN store AS t3 ON t2.store_id  =  t3.store_id WHERE t3.store_name  =  "Miramichi"
### Database Schema
CREATE TABLE "product" (
"product_id" int,
"product" text,
"dimensions" text,
"dpi" real,
"pages_per_minute_color" real,
"max_page_size" text,
"interface" text,
PRIMARY KEY ("product_id")
)

CREATE TABLE "store" (
"Store_ID" int,
"Store_Name" text,
"Type" text,
"Area_size" real,
"Number_of_product_category" real,
"Ranking" int,
PRIMARY KEY ("Store_ID")
)

CREATE TABLE "district" (
"District_ID" int,
"District_name" text,
"Headquartered_City" text,
"City_Population" real,
"City_Area" real,
PRIMARY KEY ("District_ID")
)

CREATE TABLE "store_product" (
"Store_ID" int,
"Product_ID" int,
PRIMARY KEY ("Store_ID","Product_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`Product_ID`) REFERENCES `product`(`Product_ID`)
)

CREATE TABLE "store_district" (
"Store_ID" int,
"District_ID" int,
PRIMARY KEY ("Store_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`District_ID`) REFERENCES `district`(`District_ID`)
)

### Question
What products are sold at the store named Miramichi?

### SQL
SELECT t1.product FROM product AS t1 JOIN store_product AS t2 ON t1.product_id  =  t2.product_id JOIN store AS t3 ON t2.store_id  =  t3.store_id WHERE t3.store_name  =  "Miramichi"
### Database Schema
CREATE TABLE "product" (
"product_id" int,
"product" text,
"dimensions" text,
"dpi" real,
"pages_per_minute_color" real,
"max_page_size" text,
"interface" text,
PRIMARY KEY ("product_id")
)

CREATE TABLE "store" (
"Store_ID" int,
"Store_Name" text,
"Type" text,
"Area_size" real,
"Number_of_product_category" real,
"Ranking" int,
PRIMARY KEY ("Store_ID")
)

CREATE TABLE "district" (
"District_ID" int,
"District_name" text,
"Headquartered_City" text,
"City_Population" real,
"City_Area" real,
PRIMARY KEY ("District_ID")
)

CREATE TABLE "store_product" (
"Store_ID" int,
"Product_ID" int,
PRIMARY KEY ("Store_ID","Product_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`Product_ID`) REFERENCES `product`(`Product_ID`)
)

CREATE TABLE "store_district" (
"Store_ID" int,
"District_ID" int,
PRIMARY KEY ("Store_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`District_ID`) REFERENCES `district`(`District_ID`)
)

### Question
Find products with max page size as "A4" and pages per minute color smaller than 5.

### SQL
SELECT product FROM product WHERE max_page_size  =  "A4" AND pages_per_minute_color  <  5
### Database Schema
CREATE TABLE "product" (
"product_id" int,
"product" text,
"dimensions" text,
"dpi" real,
"pages_per_minute_color" real,
"max_page_size" text,
"interface" text,
PRIMARY KEY ("product_id")
)

CREATE TABLE "store" (
"Store_ID" int,
"Store_Name" text,
"Type" text,
"Area_size" real,
"Number_of_product_category" real,
"Ranking" int,
PRIMARY KEY ("Store_ID")
)

CREATE TABLE "district" (
"District_ID" int,
"District_name" text,
"Headquartered_City" text,
"City_Population" real,
"City_Area" real,
PRIMARY KEY ("District_ID")
)

CREATE TABLE "store_product" (
"Store_ID" int,
"Product_ID" int,
PRIMARY KEY ("Store_ID","Product_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`Product_ID`) REFERENCES `product`(`Product_ID`)
)

CREATE TABLE "store_district" (
"Store_ID" int,
"District_ID" int,
PRIMARY KEY ("Store_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`District_ID`) REFERENCES `district`(`District_ID`)
)

### Question
What are the products with the maximum page size A4 that also have a pages per minute color smaller than 5?

### SQL
SELECT product FROM product WHERE max_page_size  =  "A4" AND pages_per_minute_color  <  5
### Database Schema
CREATE TABLE "product" (
"product_id" int,
"product" text,
"dimensions" text,
"dpi" real,
"pages_per_minute_color" real,
"max_page_size" text,
"interface" text,
PRIMARY KEY ("product_id")
)

CREATE TABLE "store" (
"Store_ID" int,
"Store_Name" text,
"Type" text,
"Area_size" real,
"Number_of_product_category" real,
"Ranking" int,
PRIMARY KEY ("Store_ID")
)

CREATE TABLE "district" (
"District_ID" int,
"District_name" text,
"Headquartered_City" text,
"City_Population" real,
"City_Area" real,
PRIMARY KEY ("District_ID")
)

CREATE TABLE "store_product" (
"Store_ID" int,
"Product_ID" int,
PRIMARY KEY ("Store_ID","Product_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`Product_ID`) REFERENCES `product`(`Product_ID`)
)

CREATE TABLE "store_district" (
"Store_ID" int,
"District_ID" int,
PRIMARY KEY ("Store_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`District_ID`) REFERENCES `district`(`District_ID`)
)

### Question
Find products with max page size as "A4" or pages per minute color smaller than 5.

### SQL
SELECT product FROM product WHERE max_page_size  =  "A4" OR pages_per_minute_color  <  5
### Database Schema
CREATE TABLE "product" (
"product_id" int,
"product" text,
"dimensions" text,
"dpi" real,
"pages_per_minute_color" real,
"max_page_size" text,
"interface" text,
PRIMARY KEY ("product_id")
)

CREATE TABLE "store" (
"Store_ID" int,
"Store_Name" text,
"Type" text,
"Area_size" real,
"Number_of_product_category" real,
"Ranking" int,
PRIMARY KEY ("Store_ID")
)

CREATE TABLE "district" (
"District_ID" int,
"District_name" text,
"Headquartered_City" text,
"City_Population" real,
"City_Area" real,
PRIMARY KEY ("District_ID")
)

CREATE TABLE "store_product" (
"Store_ID" int,
"Product_ID" int,
PRIMARY KEY ("Store_ID","Product_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`Product_ID`) REFERENCES `product`(`Product_ID`)
)

CREATE TABLE "store_district" (
"Store_ID" int,
"District_ID" int,
PRIMARY KEY ("Store_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`District_ID`) REFERENCES `district`(`District_ID`)
)

### Question
What are the products with the maximum page size eqal to A4 or a pages per minute color less than 5?

### SQL
SELECT product FROM product WHERE max_page_size  =  "A4" OR pages_per_minute_color  <  5
### Database Schema
CREATE TABLE "product" (
"product_id" int,
"product" text,
"dimensions" text,
"dpi" real,
"pages_per_minute_color" real,
"max_page_size" text,
"interface" text,
PRIMARY KEY ("product_id")
)

CREATE TABLE "store" (
"Store_ID" int,
"Store_Name" text,
"Type" text,
"Area_size" real,
"Number_of_product_category" real,
"Ranking" int,
PRIMARY KEY ("Store_ID")
)

CREATE TABLE "district" (
"District_ID" int,
"District_name" text,
"Headquartered_City" text,
"City_Population" real,
"City_Area" real,
PRIMARY KEY ("District_ID")
)

CREATE TABLE "store_product" (
"Store_ID" int,
"Product_ID" int,
PRIMARY KEY ("Store_ID","Product_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`Product_ID`) REFERENCES `product`(`Product_ID`)
)

CREATE TABLE "store_district" (
"Store_ID" int,
"District_ID" int,
PRIMARY KEY ("Store_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`District_ID`) REFERENCES `district`(`District_ID`)
)

### Question
Find all the product whose name contains the word "Scanner".

### SQL
SELECT product FROM product WHERE product LIKE "%Scanner%"
### Database Schema
CREATE TABLE "product" (
"product_id" int,
"product" text,
"dimensions" text,
"dpi" real,
"pages_per_minute_color" real,
"max_page_size" text,
"interface" text,
PRIMARY KEY ("product_id")
)

CREATE TABLE "store" (
"Store_ID" int,
"Store_Name" text,
"Type" text,
"Area_size" real,
"Number_of_product_category" real,
"Ranking" int,
PRIMARY KEY ("Store_ID")
)

CREATE TABLE "district" (
"District_ID" int,
"District_name" text,
"Headquartered_City" text,
"City_Population" real,
"City_Area" real,
PRIMARY KEY ("District_ID")
)

CREATE TABLE "store_product" (
"Store_ID" int,
"Product_ID" int,
PRIMARY KEY ("Store_ID","Product_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`Product_ID`) REFERENCES `product`(`Product_ID`)
)

CREATE TABLE "store_district" (
"Store_ID" int,
"District_ID" int,
PRIMARY KEY ("Store_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`District_ID`) REFERENCES `district`(`District_ID`)
)

### Question
What are all of the products whose name includes the substring "Scanner"?

### SQL
SELECT product FROM product WHERE product LIKE "%Scanner%"
### Database Schema
CREATE TABLE "product" (
"product_id" int,
"product" text,
"dimensions" text,
"dpi" real,
"pages_per_minute_color" real,
"max_page_size" text,
"interface" text,
PRIMARY KEY ("product_id")
)

CREATE TABLE "store" (
"Store_ID" int,
"Store_Name" text,
"Type" text,
"Area_size" real,
"Number_of_product_category" real,
"Ranking" int,
PRIMARY KEY ("Store_ID")
)

CREATE TABLE "district" (
"District_ID" int,
"District_name" text,
"Headquartered_City" text,
"City_Population" real,
"City_Area" real,
PRIMARY KEY ("District_ID")
)

CREATE TABLE "store_product" (
"Store_ID" int,
"Product_ID" int,
PRIMARY KEY ("Store_ID","Product_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`Product_ID`) REFERENCES `product`(`Product_ID`)
)

CREATE TABLE "store_district" (
"Store_ID" int,
"District_ID" int,
PRIMARY KEY ("Store_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`District_ID`) REFERENCES `district`(`District_ID`)
)

### Question
Find the most prominent max page size among all the products.

### SQL
SELECT max_page_size FROM product GROUP BY max_page_size ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "product" (
"product_id" int,
"product" text,
"dimensions" text,
"dpi" real,
"pages_per_minute_color" real,
"max_page_size" text,
"interface" text,
PRIMARY KEY ("product_id")
)

CREATE TABLE "store" (
"Store_ID" int,
"Store_Name" text,
"Type" text,
"Area_size" real,
"Number_of_product_category" real,
"Ranking" int,
PRIMARY KEY ("Store_ID")
)

CREATE TABLE "district" (
"District_ID" int,
"District_name" text,
"Headquartered_City" text,
"City_Population" real,
"City_Area" real,
PRIMARY KEY ("District_ID")
)

CREATE TABLE "store_product" (
"Store_ID" int,
"Product_ID" int,
PRIMARY KEY ("Store_ID","Product_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`Product_ID`) REFERENCES `product`(`Product_ID`)
)

CREATE TABLE "store_district" (
"Store_ID" int,
"District_ID" int,
PRIMARY KEY ("Store_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`District_ID`) REFERENCES `district`(`District_ID`)
)

### Question
What is the most common maximum page size?

### SQL
SELECT max_page_size FROM product GROUP BY max_page_size ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "product" (
"product_id" int,
"product" text,
"dimensions" text,
"dpi" real,
"pages_per_minute_color" real,
"max_page_size" text,
"interface" text,
PRIMARY KEY ("product_id")
)

CREATE TABLE "store" (
"Store_ID" int,
"Store_Name" text,
"Type" text,
"Area_size" real,
"Number_of_product_category" real,
"Ranking" int,
PRIMARY KEY ("Store_ID")
)

CREATE TABLE "district" (
"District_ID" int,
"District_name" text,
"Headquartered_City" text,
"City_Population" real,
"City_Area" real,
PRIMARY KEY ("District_ID")
)

CREATE TABLE "store_product" (
"Store_ID" int,
"Product_ID" int,
PRIMARY KEY ("Store_ID","Product_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`Product_ID`) REFERENCES `product`(`Product_ID`)
)

CREATE TABLE "store_district" (
"Store_ID" int,
"District_ID" int,
PRIMARY KEY ("Store_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`District_ID`) REFERENCES `district`(`District_ID`)
)

### Question
Find the name of the products that are not using the most frequently-used max page size.

### SQL
SELECT product FROM product WHERE product != (SELECT max_page_size FROM product GROUP BY max_page_size ORDER BY count(*) DESC LIMIT 1)
### Database Schema
CREATE TABLE "product" (
"product_id" int,
"product" text,
"dimensions" text,
"dpi" real,
"pages_per_minute_color" real,
"max_page_size" text,
"interface" text,
PRIMARY KEY ("product_id")
)

CREATE TABLE "store" (
"Store_ID" int,
"Store_Name" text,
"Type" text,
"Area_size" real,
"Number_of_product_category" real,
"Ranking" int,
PRIMARY KEY ("Store_ID")
)

CREATE TABLE "district" (
"District_ID" int,
"District_name" text,
"Headquartered_City" text,
"City_Population" real,
"City_Area" real,
PRIMARY KEY ("District_ID")
)

CREATE TABLE "store_product" (
"Store_ID" int,
"Product_ID" int,
PRIMARY KEY ("Store_ID","Product_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`Product_ID`) REFERENCES `product`(`Product_ID`)
)

CREATE TABLE "store_district" (
"Store_ID" int,
"District_ID" int,
PRIMARY KEY ("Store_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`District_ID`) REFERENCES `district`(`District_ID`)
)

### Question
What are the names of all products that are not the most frequently-used maximum page size?

### SQL
SELECT product FROM product WHERE product != (SELECT max_page_size FROM product GROUP BY max_page_size ORDER BY count(*) DESC LIMIT 1)
### Database Schema
CREATE TABLE "product" (
"product_id" int,
"product" text,
"dimensions" text,
"dpi" real,
"pages_per_minute_color" real,
"max_page_size" text,
"interface" text,
PRIMARY KEY ("product_id")
)

CREATE TABLE "store" (
"Store_ID" int,
"Store_Name" text,
"Type" text,
"Area_size" real,
"Number_of_product_category" real,
"Ranking" int,
PRIMARY KEY ("Store_ID")
)

CREATE TABLE "district" (
"District_ID" int,
"District_name" text,
"Headquartered_City" text,
"City_Population" real,
"City_Area" real,
PRIMARY KEY ("District_ID")
)

CREATE TABLE "store_product" (
"Store_ID" int,
"Product_ID" int,
PRIMARY KEY ("Store_ID","Product_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`Product_ID`) REFERENCES `product`(`Product_ID`)
)

CREATE TABLE "store_district" (
"Store_ID" int,
"District_ID" int,
PRIMARY KEY ("Store_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`District_ID`) REFERENCES `district`(`District_ID`)
)

### Question
Find the total population of the districts where the area is bigger than the average city area.

### SQL
SELECT sum(city_population) FROM district WHERE city_area  >  (SELECT avg(city_area) FROM district)
### Database Schema
CREATE TABLE "product" (
"product_id" int,
"product" text,
"dimensions" text,
"dpi" real,
"pages_per_minute_color" real,
"max_page_size" text,
"interface" text,
PRIMARY KEY ("product_id")
)

CREATE TABLE "store" (
"Store_ID" int,
"Store_Name" text,
"Type" text,
"Area_size" real,
"Number_of_product_category" real,
"Ranking" int,
PRIMARY KEY ("Store_ID")
)

CREATE TABLE "district" (
"District_ID" int,
"District_name" text,
"Headquartered_City" text,
"City_Population" real,
"City_Area" real,
PRIMARY KEY ("District_ID")
)

CREATE TABLE "store_product" (
"Store_ID" int,
"Product_ID" int,
PRIMARY KEY ("Store_ID","Product_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`Product_ID`) REFERENCES `product`(`Product_ID`)
)

CREATE TABLE "store_district" (
"Store_ID" int,
"District_ID" int,
PRIMARY KEY ("Store_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`District_ID`) REFERENCES `district`(`District_ID`)
)

### Question
What is the total population for all the districts that have an area larger tahn the average city area?

### SQL
SELECT sum(city_population) FROM district WHERE city_area  >  (SELECT avg(city_area) FROM district)
### Database Schema
CREATE TABLE "product" (
"product_id" int,
"product" text,
"dimensions" text,
"dpi" real,
"pages_per_minute_color" real,
"max_page_size" text,
"interface" text,
PRIMARY KEY ("product_id")
)

CREATE TABLE "store" (
"Store_ID" int,
"Store_Name" text,
"Type" text,
"Area_size" real,
"Number_of_product_category" real,
"Ranking" int,
PRIMARY KEY ("Store_ID")
)

CREATE TABLE "district" (
"District_ID" int,
"District_name" text,
"Headquartered_City" text,
"City_Population" real,
"City_Area" real,
PRIMARY KEY ("District_ID")
)

CREATE TABLE "store_product" (
"Store_ID" int,
"Product_ID" int,
PRIMARY KEY ("Store_ID","Product_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`Product_ID`) REFERENCES `product`(`Product_ID`)
)

CREATE TABLE "store_district" (
"Store_ID" int,
"District_ID" int,
PRIMARY KEY ("Store_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`District_ID`) REFERENCES `district`(`District_ID`)
)

### Question
Find the names of districts where have both city mall and village store type stores.

### SQL
SELECT t3.District_name FROM store AS t1 JOIN store_district AS t2 ON t1.store_id  =  t2.store_id JOIN district AS t3 ON t2.district_id  =  t3.district_id WHERE t1.Type  =  "City Mall" INTERSECT SELECT t3.District_name FROM store AS t1 JOIN store_district AS t2 ON t1.store_id  =  t2.store_id JOIN district AS t3 ON t2.district_id  =  t3.district_id WHERE t1.Type  =  "Village Store"
### Database Schema
CREATE TABLE "product" (
"product_id" int,
"product" text,
"dimensions" text,
"dpi" real,
"pages_per_minute_color" real,
"max_page_size" text,
"interface" text,
PRIMARY KEY ("product_id")
)

CREATE TABLE "store" (
"Store_ID" int,
"Store_Name" text,
"Type" text,
"Area_size" real,
"Number_of_product_category" real,
"Ranking" int,
PRIMARY KEY ("Store_ID")
)

CREATE TABLE "district" (
"District_ID" int,
"District_name" text,
"Headquartered_City" text,
"City_Population" real,
"City_Area" real,
PRIMARY KEY ("District_ID")
)

CREATE TABLE "store_product" (
"Store_ID" int,
"Product_ID" int,
PRIMARY KEY ("Store_ID","Product_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`Product_ID`) REFERENCES `product`(`Product_ID`)
)

CREATE TABLE "store_district" (
"Store_ID" int,
"District_ID" int,
PRIMARY KEY ("Store_ID"),
FOREIGN KEY (`Store_ID`) REFERENCES `store`(`Store_ID`),
FOREIGN KEY (`District_ID`) REFERENCES `district`(`District_ID`)
)

### Question
What are the names of the districts that have both mall and village store style shops?

### SQL
SELECT t3.District_name FROM store AS t1 JOIN store_district AS t2 ON t1.store_id  =  t2.store_id JOIN district AS t3 ON t2.district_id  =  t3.district_id WHERE t1.Type  =  "City Mall" INTERSECT SELECT t3.District_name FROM store AS t1 JOIN store_district AS t2 ON t1.store_id  =  t2.store_id JOIN district AS t3 ON t2.district_id  =  t3.district_id WHERE t1.Type  =  "Village Store"
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What is the total enrollment number of all colleges?

### SQL
SELECT sum(enr) FROM College
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
How many students are enrolled in college?

### SQL
SELECT sum(enr) FROM College
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What is the average enrollment number?

### SQL
SELECT avg(enr) FROM College
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
How many students, on average, does each college have enrolled?

### SQL
SELECT avg(enr) FROM College
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
How many colleges in total?

### SQL
SELECT count(*) FROM College
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
How many different colleges are there?

### SQL
SELECT count(*) FROM College
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
How many players have more than 1000 hours of training?

### SQL
SELECT count(*) FROM Player WHERE HS  >  1000
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
How many different players trained for more than 1000 hours?

### SQL
SELECT count(*) FROM Player WHERE HS  >  1000
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
How many colleges has more than 15000 students?

### SQL
SELECT count(*) FROM College WHERE enr  >  15000
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What is the number of colleges with a student population greater than 15000?

### SQL
SELECT count(*) FROM College WHERE enr  >  15000
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What is the average training hours of all players?

### SQL
SELECT avg(HS) FROM Player
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
How many hours do the players train on average?

### SQL
SELECT avg(HS) FROM Player
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Find the name and training hours of players whose hours are below 1500.

### SQL
SELECT pName ,  HS FROM Player WHERE HS  <  1500
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What are the names and number of hours spent training for each player who trains for less than 1500 hours?

### SQL
SELECT pName ,  HS FROM Player WHERE HS  <  1500
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
How many different colleges do attend the tryout test?

### SQL
SELECT count(DISTINCT cName) FROM tryout
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
How many different colleges were represented at tryouts?

### SQL
SELECT count(DISTINCT cName) FROM tryout
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What are the unique types of player positions in the tryout?

### SQL
SELECT count(DISTINCT pPos) FROM tryout
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What are the different types of player positions?

### SQL
SELECT count(DISTINCT pPos) FROM tryout
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
How many students got accepted after the tryout?

### SQL
SELECT count(*) FROM tryout WHERE decision  =  'yes'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
How many students received a yes from tryouts?

### SQL
SELECT count(*) FROM tryout WHERE decision  =  'yes'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
How many students whose are playing the role of goalie?

### SQL
SELECT count(*) FROM tryout WHERE pPos  =  'goalie'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What is the number of students playing as a goalie?

### SQL
SELECT count(*) FROM tryout WHERE pPos  =  'goalie'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Find the max, average and min training hours of all players.

### SQL
SELECT avg(HS) ,  max(HS) ,  min(HS) FROM Player
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What is the average, maximum, and minimum for the number of hours spent training?

### SQL
SELECT avg(HS) ,  max(HS) ,  min(HS) FROM Player
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What is average enrollment of colleges in the state FL?

### SQL
SELECT avg(enr) FROM College WHERE state  =  'FL'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What is average number of students enrolled in Florida colleges?

### SQL
SELECT avg(enr) FROM College WHERE state  =  'FL'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What are the names of players whose training hours is between 500 and 1500?

### SQL
SELECT pName FROM Player WHERE HS BETWEEN 500 AND 1500
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What are the names of players who train between 500 and 1500 hours?

### SQL
SELECT pName FROM Player WHERE HS BETWEEN 500 AND 1500
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Find the players whose names contain letter 'a'.

### SQL
SELECT DISTINCT pName FROM Player WHERE pName LIKE '%a%'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Who are the players that have names containing the letter a?

### SQL
SELECT DISTINCT pName FROM Player WHERE pName LIKE '%a%'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Find the name, enrollment of the colleges whose size is bigger than 10000 and location is in state LA.

### SQL
SELECT cName ,  enr FROM College WHERE enr  >  10000 AND state = "LA"
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What are the names and enrollment numbers for colleges that have more than 10000 enrolled and are located in Louisiana?

### SQL
SELECT cName ,  enr FROM College WHERE enr  >  10000 AND state = "LA"
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
List all information about college sorted by enrollment number in the ascending order.

### SQL
SELECT * FROM College ORDER BY enr
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What information do you have on colleges sorted by increasing enrollment numbers?

### SQL
SELECT * FROM College ORDER BY enr
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
List the name of the colleges whose enrollment is greater 18000 sorted by the college's name.

### SQL
SELECT cName FROM College WHERE enr  >  18000 ORDER BY cName
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What is the name of every college in alphabetical order that has more than 18000 students enrolled?

### SQL
SELECT cName FROM College WHERE enr  >  18000 ORDER BY cName