human
Schema:
city (City_ID, Official_Name, Status, Area_km_2, Population, Census_Ranking)
farm (Farm_ID, Year, Total_Horses, Working_Horses, Total_Cattle, Oxen, Bulls, Cows, Pigs, Sheep_and_Goats)
farm_competition (Competition_ID, Year, Theme, Host_city_ID, Hosts)
competition_record (Competition_ID, Farm_ID, Rank)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the status shared by cities with population bigger than 1500 and smaller than 500.
assistant
SELECT Status FROM city WHERE Population > 1500 INTERSECT SELECT Status FROM city WHERE Population < 500
human
List official names of cities in descending order of population.
assistant
SELECT Official_Name FROM city ORDER BY Population DESC
human
Please show the different statuses, ordered by the number of cities that have each.
assistant
SELECT Status FROM city GROUP BY Status ORDER BY COUNT(*) ASC
human
List the most common type of Status across cities.
assistant
SELECT Status FROM city GROUP BY Status ORDER BY COUNT(*) DESC LIMIT 1
human
How many different statuses do cities have?
assistant
SELECT count(DISTINCT Status) FROM city
human
What are the statuses and average populations of each city?
assistant
SELECT Status , avg ( Population ) FROM city GROUP BY Status ;