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
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
Find the number of matches in different competitions.
assistant
SELECT count(*) , Competition FROM MATCH GROUP BY Competition
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
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
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
Find the match ids of the cities that hosted competition '1994 FIFA World Cup qualification'?
assistant
SELECT match_id FROM MATCH WHERE competition = "1994 FIFA World Cup qualification" ;