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 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
What are the hosts of competitions whose theme is not 'Aliens'?
assistant
SELECT Hosts FROM farm_competition WHERE Theme != 'Aliens'
human
Show the official names of the cities that have hosted more than one competition.
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 HAVING COUNT(*) > 1
human
Show the years and the official names of the host cities of competitions.
assistant
SELECT T2.Year , T1.Official_Name FROM city AS T1 JOIN farm_competition AS T2 ON T1.City_ID = T2.Host_city_ID
human
Show the status of the city that has hosted the greatest number of competitions.
assistant
SELECT T1.Status 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
human
Please show the themes of competitions with host cities having populations larger than 1000.
assistant
SELECT T2.Theme FROM city AS T1 JOIN farm_competition AS T2 ON T1.City_ID = T2.Host_city_ID WHERE T1.Population > 1000 ;