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
List official names of cities in descending order of population.
assistant
SELECT Official_Name FROM city ORDER BY Population DESC
human
What are the themes of farm competitions sorted by year in ascending order?
assistant
SELECT Theme FROM farm_competition ORDER BY YEAR ASC
human
What are the maximum and minimum number of cows across all farms.
assistant
SELECT max(Cows) , min(Cows) FROM farm
human
How many farms are there?
assistant
SELECT count(*) FROM farm
human
What is the average number of working horses of farms with more than 5000 total number of horses?
assistant
SELECT avg(Working_Horses) FROM farm WHERE Total_Horses > 5000
human
List the total number of horses on farms in ascending order.
assistant
SELECT Total_Horses FROM farm ORDER BY Total_Horses ASC ;