human
Schema:
product (product_id, product, dimensions, dpi, pages_per_minute_color, max_page_size, interface)
store (Store_ID, Store_Name, Type, Area_size, Number_of_product_category, Ranking)
district (District_ID, District_name, Headquartered_City, City_Population, City_Area)
store_product (Store_ID, Product_ID)
store_district (Store_ID, District_ID)
Please answer with SQL.
assistant
Sure, I understand.
human
Find all the stores in the district with the most population.
assistant
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)
human
Which district has the least area?
assistant
SELECT district_name FROM district ORDER BY city_area ASC LIMIT 1
human
Which district has the largest population?
assistant
SELECT district_name FROM district ORDER BY city_population DESC LIMIT 1
human
Find the total population of the top 3 districts with the largest area.
assistant
SELECT sum(city_population) FROM district ORDER BY city_area DESC LIMIT 3
human
Find the name all districts with city area greater than 10 or population larger than 100000
assistant
SELECT district_name FROM district WHERE city_area > 10 OR City_Population > 100000
human
Find all the distinct district names ordered by city area in descending.
assistant
SELECT DISTINCT District_name FROM district ORDER BY city_area DESC ;