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: 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"