Database: city_record

CREATE TABLE "city" (
"City_ID" int,
"City" text,
"Hanzi" text,
"Hanyu_Pinyin" text,
"Regional_Population" int,
"GDP" real,
PRIMARY KEY ("City_ID")
);
CREATE TABLE "match" (
"Match_ID" int,
"Date" text,
"Venue" text,
"Score" text,
"Result" text,
"Competition" text,
PRIMARY KEY ("Match_ID")
);
CREATE TABLE "temperature" (
"City_ID" int,
"Jan" real,
"Feb" real,
"Mar" real,
"Apr" real,
"Jun" real,
"Jul" real,
"Aug" real,
"Sep" real,
"Oct" real,
"Nov" real,
"Dec" real,
PRIMARY KEY ("City_ID"),
FOREIGN KEY (`City_ID`) REFERENCES `city`(`City_ID`)
);
CREATE TABLE "hosting_city" (
  "Year" int,
  "Match_ID" int,
  "Host_City" text,
  PRIMARY KEY ("Year"),
  FOREIGN KEY (`Host_City`) REFERENCES `city`(`City_ID`),
  FOREIGN KEY (`Match_ID`) REFERENCES `match`(`Match_ID`)
);

Question: What is id of the city that hosted events in the most recent year?
SQL: SELECT host_city FROM hosting_city ORDER BY YEAR DESC LIMIT 1

Question: Find the city that hosted some events in the most recent year. What is the id of this city?
SQL: SELECT host_city FROM hosting_city ORDER BY YEAR DESC LIMIT 1

Question: Find the match ids of the cities that hosted competition "1994 FIFA World Cup qualification"?
SQL: SELECT match_id FROM MATCH WHERE competition = "1994 FIFA World Cup qualification"

Question: What is the match id of the competition called "1994 FIFA World Cup qualification"?
SQL: SELECT match_id FROM MATCH WHERE competition = "1994 FIFA World Cup qualification"

Question: Find the cities which were once a host city after 2010?
SQL: SELECT T1.city FROM city AS T1 JOIN hosting_city AS T2 ON T1.city_id = T2.host_city WHERE T2.year  >  2010

Question: Which cities served as a host city after 2010?
SQL: SELECT T1.city FROM city AS T1 JOIN hosting_city AS T2 ON T1.city_id = T2.host_city WHERE T2.year  >  2010

Question: Which city has hosted the most events?
SQL: 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

Question: Find the city that hosted the most events.
SQL: 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

Question: What is the venue of the competition "1994 FIFA World Cup qualification" hosted by "Nanjing ( Jiangsu )"?
SQL: 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"

Question: Find the venue of the competition "1994 FIFA World Cup qualification" which was hosted by "Nanjing ( Jiangsu )".
SQL: 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"

Question: Give me the temperature of Shanghai in January.
SQL: SELECT T2.Jan FROM city AS T1 JOIN temperature AS T2 ON T1.city_id = T2.city_id WHERE T1.city = "Shanghai"

Question: What is the temperature of "Shanghai" city in January?
SQL: SELECT T2.Jan FROM city AS T1 JOIN temperature AS T2 ON T1.city_id = T2.city_id WHERE T1.city = "Shanghai"

Question: What is the host year of city "Taizhou ( Zhejiang )"?
SQL: SELECT T2.year FROM city AS T1 JOIN hosting_city AS T2 ON T1.city_id  =  T2.host_city WHERE T1.city  =  "Taizhou ( Zhejiang )"

Question: IN which year did city "Taizhou ( Zhejiang )" serve as a host city?
SQL: SELECT T2.year FROM city AS T1 JOIN hosting_city AS T2 ON T1.city_id  =  T2.host_city WHERE T1.city  =  "Taizhou ( Zhejiang )"

Question: Which three cities have the largest regional population?
SQL: SELECT city FROM city ORDER BY regional_population DESC LIMIT 3

Question: What are the three largest cities in terms of regional population?
SQL: SELECT city FROM city ORDER BY regional_population DESC LIMIT 3

Question: Which city has the lowest GDP? Please list the city name and its GDP.
SQL: SELECT city ,  GDP FROM city ORDER BY GDP LIMIT 1

Question: What is the city with the smallest GDP? Return the city and its GDP.
SQL: SELECT city ,  GDP FROM city ORDER BY GDP LIMIT 1

Question: Which city has the highest temperature in February?
SQL: SELECT T1.city FROM city AS T1 JOIN temperature AS T2 ON T1.city_id  =  T2.city_id ORDER BY T2.Feb DESC LIMIT 1

Question: In February, which city marks the highest temperature?
SQL: SELECT T1.city FROM city AS T1 JOIN temperature AS T2 ON T1.city_id  =  T2.city_id ORDER BY T2.Feb DESC LIMIT 1

Question: Give me a list of cities whose temperature in March is lower than that in July or higher than that in Oct?
SQL: SELECT T1.city FROM city AS T1 JOIN temperature AS T2 ON T1.city_id  =  T2.city_id WHERE T2.Mar  <  T2.Jul OR T2.Mar  >  T2.Oct

Question: Which cities' temperature in March is lower than that in July or higher than that in Oct?
SQL: SELECT T1.city FROM city AS T1 JOIN temperature AS T2 ON T1.city_id  =  T2.city_id WHERE T2.Mar  <  T2.Jul OR T2.Mar  >  T2.Oct

Question: Give me a list of cities whose temperature in Mar is lower than that in July and which have also served as host cities?
SQL: 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

Question: Which cities have lower temperature in March than in July and have been once host cities?
SQL: 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

Question: Give me a list of cities whose temperature in Mar is lower than that in Dec and which have never been host cities.
SQL: SELECT T1.city FROM city AS T1 JOIN temperature AS T2 ON T1.city_id  =  T2.city_id WHERE T2.Mar  <  T2.Dec EXCEPT SELECT T3.city FROM city AS T3 JOIN hosting_city AS T4 ON T3.city_id  =  T4.host_city

Question: Which cities have lower temperature in March than in Dec and have never served as host cities?
SQL: SELECT T1.city FROM city AS T1 JOIN temperature AS T2 ON T1.city_id  =  T2.city_id WHERE T2.Mar  <  T2.Dec EXCEPT SELECT T3.city FROM city AS T3 JOIN hosting_city AS T4 ON T3.city_id  =  T4.host_city

Question: Give me a list of cities whose temperature in Feb is higher than that in Jun or cities that were once host cities?
SQL: 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

Question: Which cities have higher temperature in Feb than in Jun or have once served as host cities?
SQL: 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

Question: Please give me a list of cities whose regional population is over 10000000.
SQL: SELECT city FROM city WHERE regional_population  >  10000000

Question: Which cities have regional population above 10000000?
SQL: SELECT city FROM city WHERE regional_population  >  10000000

Question: Please give me a list of cities whose regional population is over 8000000 or under 5000000.
SQL: SELECT city FROM city WHERE regional_population  >  10000000 UNION SELECT city FROM city WHERE regional_population  <  5000000

Question: Which cities have regional population above 8000000 or below 5000000?
SQL: SELECT city FROM city WHERE regional_population  >  10000000 UNION SELECT city FROM city WHERE regional_population  <  5000000

Question: Find the number of matches in different competitions.
SQL: SELECT count(*) ,  Competition FROM MATCH GROUP BY Competition

Question: For each competition, count the number of matches.
SQL: SELECT count(*) ,  Competition FROM MATCH GROUP BY Competition

Question: List venues of all matches in the order of their dates starting from the most recent one.
SQL: SELECT venue FROM MATCH ORDER BY date DESC

Question: What are the venues of all the matches? Sort them in the descending order of match date.
SQL: SELECT venue FROM MATCH ORDER BY date DESC

Question: what is the GDP of the city with the largest population.
SQL: SELECT gdp FROM city ORDER BY Regional_Population DESC LIMIT 1

Question: Find the GDP of the city with the largest regional population.
SQL: SELECT gdp FROM city ORDER BY Regional_Population DESC LIMIT 1

Question: What are the GDP and population of the city that already served as a host more than once?
SQL: 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

Question: Which cities have served as host cities more than once? Return me their GDP and population.
SQL: 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