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 the official name and status of the city with the largest population.
assistant
SELECT Official_Name , Status FROM city ORDER BY Population DESC LIMIT 1
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
Please show the different statuses of cities and the average population of cities with each status.
assistant
SELECT Status , avg(Population) FROM city GROUP BY Status
human
How many different statuses do cities have?
assistant
SELECT count(DISTINCT Status) FROM city
human
What is the most common status across all cities?
assistant
SELECT Status FROM city GROUP BY Status ORDER BY COUNT( * ) DESC LIMIT 1 ;