CREATE TABLE product (
    product_id NUMBER PRIMARY KEY,
    product TEXT,
    dimensions TEXT,
    dpi NUMBER,
    pages_per_minute_color NUMBER,
    max_page_size TEXT,
    interface TEXT
);
CREATE TABLE store (
    Store_ID NUMBER PRIMARY KEY,
    Store_Name TEXT,
    Type TEXT,
    Area_size NUMBER,
    Number_of_product_category NUMBER,
    Ranking NUMBER
);
CREATE TABLE district (
    District_ID NUMBER PRIMARY KEY,
    District_name TEXT,
    Headquartered_City TEXT,
    City_Population NUMBER,
    City_Area NUMBER
);
CREATE TABLE store_product (
    Store_ID NUMBER PRIMARY KEY,
    Product_ID NUMBER,
    FOREIGN KEY ("Store_ID") REFERENCES store("Store_ID")
);
CREATE TABLE store_district (
    Store_ID NUMBER PRIMARY KEY,
    District_ID NUMBER,
    FOREIGN KEY ("District_ID") REFERENCES district("District_ID"),
    FOREIGN KEY ("Store_ID") REFERENCES store("Store_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%"