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
What are the themes of farm competitions sorted by year in ascending order?
assistant
SELECT Theme FROM farm_competition ORDER BY YEAR ASC
human
List the total number of horses on farms in ascending order.
assistant
SELECT Total_Horses FROM farm ORDER BY Total_Horses ASC
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
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 ;