lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
What is the denomination of the school the most players belong to?

### SQL
SELECT T2.Denomination FROM player AS T1 JOIN school AS T2 ON T1.School_ID  =  T2.School_ID GROUP BY T1.School_ID ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
Show locations and nicknames of schools.

### SQL
SELECT T1.Location ,  T2.Nickname FROM school AS T1 JOIN school_details AS T2 ON T1.School_ID  =  T2.School_ID
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
What are the location and nickname of each school?

### SQL
SELECT T1.Location ,  T2.Nickname FROM school AS T1 JOIN school_details AS T2 ON T1.School_ID  =  T2.School_ID
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
Please show different denominations and the corresponding number of schools.

### SQL
SELECT Denomination ,  COUNT(*) FROM school GROUP BY Denomination
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
For each denomination, return the denomination and the count of schools with that denomination.

### SQL
SELECT Denomination ,  COUNT(*) FROM school GROUP BY Denomination
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
Please show different denominations and the corresponding number of schools in descending order.

### SQL
SELECT Denomination ,  COUNT(*) FROM school GROUP BY Denomination ORDER BY COUNT(*) DESC
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
Order denominations in descending order of the count of schools with the denomination. Return each denomination with the count of schools.

### SQL
SELECT Denomination ,  COUNT(*) FROM school GROUP BY Denomination ORDER BY COUNT(*) DESC
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
List the school color of the school that has the largest enrollment.

### SQL
SELECT School_Colors FROM school ORDER BY Enrollment DESC LIMIT 1
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
What is the school color of the school with the largest enrollment?

### SQL
SELECT School_Colors FROM school ORDER BY Enrollment DESC LIMIT 1
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
List the locations of schools that do not have any player.

### SQL
SELECT LOCATION FROM school WHERE School_ID NOT IN (SELECT School_ID FROM Player)
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
Which schools do not have any player? Give me the school locations.

### SQL
SELECT LOCATION FROM school WHERE School_ID NOT IN (SELECT School_ID FROM Player)
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
Show the denomination shared by schools founded before 1890 and schools founded after 1900

### SQL
SELECT Denomination FROM school WHERE Founded  <  1890 INTERSECT SELECT Denomination FROM school WHERE Founded  >  1900
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
What are the denominations used by both schools founded before 1890 and schools founded  after 1900?

### SQL
SELECT Denomination FROM school WHERE Founded  <  1890 INTERSECT SELECT Denomination FROM school WHERE Founded  >  1900
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
Show the nicknames of schools that are not in division 1.

### SQL
SELECT Nickname FROM school_details WHERE Division != "Division 1"
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
What are the nicknames of schools whose division is not 1?

### SQL
SELECT Nickname FROM school_details WHERE Division != "Division 1"
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
Show the denomination shared by more than one school.

### SQL
SELECT Denomination FROM school GROUP BY Denomination HAVING COUNT(*)  >  1
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
What are the denomination more than one school have?

### SQL
SELECT Denomination FROM school GROUP BY Denomination HAVING COUNT(*)  >  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 all the distinct district names ordered by city area in descending.

### SQL
SELECT DISTINCT District_name FROM district ORDER BY city_area DESC
### 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 different district names in order of descending city area?

### SQL
SELECT DISTINCT District_name FROM district ORDER BY city_area DESC
### 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 list of page size which have more than 3 product listed

### SQL
SELECT max_page_size FROM product GROUP BY max_page_size HAVING count(*)  >  3
### 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 maximum page size for everything that has more than 3 products listed?

### SQL
SELECT max_page_size FROM product GROUP BY max_page_size HAVING count(*)  >  3
### 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 and population of district with population between 200000 and 2000000

### SQL
SELECT District_name ,  City_Population FROM district WHERE City_Population BETWEEN 200000 AND 2000000
### 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 district names and city populations for all districts that between 200,000 and 2,000,000 residents?

### SQL
SELECT District_name ,  City_Population FROM district WHERE City_Population BETWEEN 200000 AND 2000000
### 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 all districts with city area greater than 10 or population larger than 100000

### SQL
SELECT district_name FROM district WHERE city_area  >  10 OR City_Population  >  100000
### 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 districts with a city area greater than 10 or have more than 100000 people living there?

### SQL
SELECT district_name FROM district WHERE city_area  >  10 OR City_Population  >  100000
### 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
Which district has the largest population?

### SQL
SELECT district_name FROM district ORDER BY city_population 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 name of the district with the most residents?

### SQL
SELECT district_name FROM district ORDER BY city_population 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
Which district has the least area?

### SQL
SELECT district_name FROM district ORDER BY city_area ASC 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 name of the district with the smallest area?

### SQL
SELECT district_name FROM district ORDER BY city_area ASC 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 top 3 districts with the largest area.

### SQL
SELECT sum(city_population) FROM district ORDER BY city_area DESC LIMIT 3
### 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 number of residents for the districts with the 3 largest areas?

### SQL
SELECT sum(city_population) FROM district ORDER BY city_area DESC LIMIT 3
### 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 types of store and number of them.

### SQL
SELECT TYPE ,  count(*) FROM store GROUP BY TYPE
### 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
For each type of store, how many of them are there?

### SQL
SELECT TYPE ,  count(*) FROM store GROUP BY TYPE
### 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 all stores in Khanewal District.

### SQL
SELECT t1.store_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 t3.district_name  =  "Khanewal 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 are the names of all the stores located in Khanewal District?

### SQL
SELECT t1.store_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 t3.district_name  =  "Khanewal 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 all the stores in the district with the most population.

### SQL
SELECT t1.store_name FROM store AS t1 JOIN store_district AS t2 ON t1.store_id  =  t2.store_id WHERE district_id  =  (SELECT district_id FROM district ORDER BY city_population 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 the stores in the largest district by population?

### SQL
SELECT t1.store_name FROM store AS t1 JOIN store_district AS t2 ON t1.store_id  =  t2.store_id WHERE district_id  =  (SELECT district_id FROM district ORDER BY city_population 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
Which city is the headquarter of the store named "Blackville" in?

### 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 WHERE t1.store_name  =  "Blackville"
### 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 city is the headquarter of the store Blackville?

### 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 WHERE t1.store_name  =  "Blackville"
### 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 number of stores in each city.

### SQL
SELECT t3.headquartered_city ,  count(*) 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
### 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
How many stores are headquarted in each city?

### SQL
SELECT t3.headquartered_city ,  count(*) 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
### 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 city with the most number of 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