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 me the names of all cities
assistant
SELECT official_name FROM city;
human
How many competitions did these cities host?
assistant
SELECT t1.official_name , COUNT( * ) FROM city AS t1 JOIN farm_competition AS t2 ON t1.city_id = t2.host_city_id GROUP BY city_id;
human
Which of these cities hosted the most competitions?
assistant
SELECT T1.official_name FROM city AS T1 JOIN farm_competition AS T2 ON T1.City_ID = T2.Host_city_ID GROUP BY T2.Host_city_ID ORDER BY COUNT( * ) DESC LIMIT 1 ;