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
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
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
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
What are the official names of cities that have population over 1500 or less than 500?
assistant
SELECT Official_Name FROM city WHERE Population > 1500 OR Population < 500 ;