human
Schema:
city (City_ID, City, Hanzi, Hanyu_Pinyin, Regional_Population, GDP)
match (Match_ID, Date, Venue, Score, Result, Competition)
temperature (City_ID, Jan, Feb, Mar, Apr, Jun, Jul, Aug, Sep, Oct, Nov, Dec)
hosting_city (Year, Match_ID, Host_City)
Please answer with SQL.
assistant
Sure, I understand.
human
List venues of all matches in the order of their dates starting from the most recent one.
assistant
SELECT venue FROM MATCH ORDER BY date DESC
human
Give me a list of cities whose temperature in Feb is higher than that in Jun or cities that were once host cities?
assistant
SELECT T1.city FROM city AS T1 JOIN temperature AS T2 ON T1.city_id = T2.city_id WHERE T2.Feb > T2.Jun UNION SELECT T3.city FROM city AS T3 JOIN hosting_city AS T4 ON T3.city_id = T4.host_city
human
Give me a list of cities whose temperature in Mar is lower than that in July and which have also served as host cities?
assistant
SELECT T1.city FROM city AS T1 JOIN temperature AS T2 ON T1.city_id = T2.city_id WHERE T2.Mar < T2.Jul INTERSECT SELECT T3.city FROM city AS T3 JOIN hosting_city AS T4 ON T3.city_id = T4.host_city
human
Find the cities which were once a host city after 2010?
assistant
SELECT T1.city FROM city AS T1 JOIN hosting_city AS T2 ON T1.city_id = T2.host_city WHERE T2.year > 2010
human
What is id of the city that hosted events in the most recent year?
assistant
SELECT host_city FROM hosting_city ORDER BY YEAR DESC LIMIT 1
human
Which city has hosted the most events?
assistant
SELECT T1.city FROM city AS T1 JOIN hosting_city AS T2 ON T1.city_id = T2.host_city GROUP BY T2.host_city ORDER BY COUNT( * ) DESC LIMIT 1 ;