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
Find the official names of cities with population bigger than 1500 or smaller than 500.
assistant
SELECT Official_Name FROM city WHERE Population > 1500 OR Population < 500
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
List official names of cities in descending order of population.
assistant
SELECT Official_Name FROM city ORDER BY Population DESC
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 ;