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
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
human
What are the GDP and population of the city that already served as a host more than once?
assistant
SELECT t1.gdp , t1.Regional_Population FROM city AS T1 JOIN hosting_city AS T2 ON T1.city_id = T2.host_city GROUP BY t2.Host_City HAVING count(*) > 1
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 the venue of the competition '1994 FIFA World Cup qualification' hosted by 'Nanjing ( Jiangsu )'?
assistant
SELECT T3.venue FROM city AS T1 JOIN hosting_city AS T2 ON T1.city_id = T2.host_city JOIN MATCH AS T3 ON T2.match_id = T3.match_id WHERE T1.city = 'Nanjing ( Jiangsu )' AND T3.competition = '1994 FIFA World Cup qualification'
human
IN which year did city 'Taizhou ( Zhejiang )' serve as a host city?
assistant
SELECT T2.year FROM city AS T1 JOIN hosting_city AS T2 ON T1.city_id = T2.host_city WHERE T1.city = "Taizhou ( Zhejiang )" ;