Database: store_product

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

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

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

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

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

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

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

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

Question: Which district has the largest population?
SQL: SELECT district_name FROM district ORDER BY city_population DESC LIMIT 1

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

Question: Which district has the least area?
SQL: SELECT district_name FROM district ORDER BY city_area ASC LIMIT 1

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

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

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

Question: Find all types of store and number of them.
SQL: SELECT TYPE ,  count(*) FROM store GROUP BY TYPE

Question: For each type of store, how many of them are there?
SQL: SELECT TYPE ,  count(*) FROM store GROUP BY TYPE

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"

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"

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)

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)

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"

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"

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

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

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

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

Question: What is the average pages per minute color?
SQL: SELECT avg(pages_per_minute_color) FROM product

Question: What is the average number of pages per minute color?
SQL: SELECT avg(pages_per_minute_color) FROM product

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"

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"

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

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

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

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

Question: Find all the product whose name contains the word "Scanner".
SQL: SELECT product FROM product WHERE product LIKE "%Scanner%"

Question: What are all of the products whose name includes the substring "Scanner"?
SQL: SELECT product FROM product WHERE product LIKE "%Scanner%"

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

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

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)

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)

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)

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)

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"

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"