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 census ranking of cities whose status are not 'Village'.
assistant
SELECT Census_Ranking FROM city WHERE Status != 'Village'
human
How many different statuses do cities have?
assistant
SELECT count(DISTINCT Status) FROM city
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
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
What are the official names of cities, ordered descending by population?
assistant
SELECT Official_Name FROM city ORDER BY Population DESC ;