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
How many different statuses do cities have?
assistant
SELECT count(DISTINCT Status) FROM city
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
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
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
List official names of cities in descending order of population.
assistant
SELECT Official_Name FROM city ORDER BY Population DESC ;