target-benchmark/spider-queries huggingface dataset:
Database: music_4 (tables: volume)
Question: Return the issue date of the volume that has spent the fewest weeks on top.
SQL: SELECT Issue_Date FROM volume ORDER BY Weeks_on_Top ASC LIMIT 1
Database: music_4 (tables: volume)
Question: How many distinct artists have volumes?
SQL: SELECT COUNT(DISTINCT Artist_ID) FROM volume
Database: music_4 (tables: volume)
Question: Count the number of artists who have had volumes.
SQL: SELECT COUNT(DISTINCT Artist_ID) FROM volume
Database: music_4 (tables: music_festival)
Question: Please show the results of music festivals and the number of music festivals that have had each, ordered by this count.
SQL: SELECT RESULT ,  COUNT(*) FROM music_festival GROUP BY RESULT ORDER BY COUNT(*) DESC
Database: music_4 (tables: music_festival)
Question: How many music festivals have had each kind of result, ordered descending by count?
SQL: SELECT RESULT ,  COUNT(*) FROM music_festival GROUP BY RESULT ORDER BY COUNT(*) DESC
Database: music_4 (tables: volume, artist)
Question: What are the issue dates of volumes associated with the artist aged 23 or younger?
SQL: SELECT Issue_Date FROM artist AS T1 JOIN volume AS T2 ON T1.Artist_ID  =  T2.Artist_ID WHERE T1.age  <=  23
Database: music_4 (tables: volume, artist)
Question: Return the issue dates of volumes by artists who are at most 23 years old?
SQL: SELECT Issue_Date FROM artist AS T1 JOIN volume AS T2 ON T1.Artist_ID  =  T2.Artist_ID WHERE T1.age  <=  23
Database: roller_coaster (tables: roller_coaster)
Question: How many roller coasters are there?
SQL: SELECT count(*) FROM roller_coaster
Database: roller_coaster (tables: roller_coaster)
Question: List the names of roller coasters by ascending order of length.
SQL: SELECT Name FROM roller_coaster ORDER BY LENGTH ASC
Database: roller_coaster (tables: roller_coaster)
Question: What are the lengths and heights of roller coasters?
SQL: SELECT LENGTH ,  Height FROM roller_coaster
Database: roller_coaster (tables: country)
Question: List the names of countries whose language is not "German".
SQL: SELECT Name FROM country WHERE Languages != "German"
Database: roller_coaster (tables: roller_coaster)
Question: Show the statuses of roller coasters longer than 3300 or higher than 100.
SQL: SELECT Status FROM roller_coaster WHERE LENGTH  >  3300 OR Height  >  100
Database: roller_coaster (tables: roller_coaster)
Question: What are the speeds of the longest roller coaster?
SQL: SELECT Speed FROM roller_coaster ORDER BY LENGTH DESC LIMIT 1
Database: roller_coaster (tables: roller_coaster)
Question: What is the average speed of roller coasters?
SQL: SELECT avg(Speed) FROM roller_coaster
Database: roller_coaster (tables: roller_coaster)
Question: Show the different statuses and the numbers of roller coasters for each status.
SQL: SELECT Status ,  COUNT(*) FROM roller_coaster GROUP BY Status
Database: roller_coaster (tables: roller_coaster)
Question: Please show the most common status of roller coasters.
SQL: SELECT Status FROM roller_coaster GROUP BY Status ORDER BY COUNT(*) DESC LIMIT 1
Database: roller_coaster (tables: roller_coaster)
Question: List the status shared by more than two roller coaster.
SQL: SELECT Status FROM roller_coaster GROUP BY Status HAVING COUNT(*)  >  2
Database: roller_coaster (tables: roller_coaster)
Question: Show the park of the roller coaster with the highest speed.
SQL: SELECT Park FROM roller_coaster ORDER BY Speed DESC LIMIT 1
Database: roller_coaster (tables: roller_coaster, country)
Question: Show the names of roller coasters and names of country they are in.
SQL: SELECT T2.Name ,  T1.Name FROM country AS T1 JOIN roller_coaster AS T2 ON T1.Country_ID  =  T2.Country_ID
Database: roller_coaster (tables: roller_coaster, country)
Question: Show the names of countries that have more than one roller coaster.
SQL: SELECT T1.Name FROM country AS T1 JOIN roller_coaster AS T2 ON T1.Country_ID  =  T2.Country_ID GROUP BY T1.Name HAVING COUNT(*)  >  1
Database: roller_coaster (tables: roller_coaster, country)
Question: Show the name and population of the country that has the highest roller coaster.
SQL: SELECT T1.Name ,  T1.population FROM country AS T1 JOIN roller_coaster AS T2 ON T1.Country_ID  =  T2.Country_ID ORDER BY T2.Height DESC LIMIT 1
Database: roller_coaster (tables: roller_coaster, country)
Question: Show the names of countries and the average speed of roller coasters from each country.
SQL: SELECT T1.Name ,  avg(T2.Speed) FROM country AS T1 JOIN roller_coaster AS T2 ON T1.Country_ID  =  T2.Country_ID GROUP BY T1.Name
Database: roller_coaster (tables: roller_coaster, country)
Question: How many countries do not have an roller coaster longer than 3000?
SQL: SELECT count(*) FROM country WHERE country_id NOT IN ( SELECT country_id FROM roller_coaster WHERE LENGTH  >  3000 )
Database: roller_coaster (tables: roller_coaster, country)
Question: What are the country names, area and population which has both roller coasters with speed higher
SQL: SELECT T1.name ,  T1.area ,  T1.population FROM country AS T1 JOIN roller_coaster AS T2 ON T1.Country_ID  =  T2.Country_ID WHERE T2.speed  >  60 INTERSECT SELECT T1.name ,  T1.area ,  T1.population FROM country AS T1 JOIN roller_coaster AS T2 ON T1.Country_ID  =  T2.Country_ID WHERE T2.speed  <  55
Database: ship_1 (tables: captain)
Question: How many different captain ranks are there?
SQL: SELECT count(DISTINCT rank) FROM captain
Database: ship_1 (tables: captain)
Question: Count the number of different ranks of captain.
SQL: SELECT count(DISTINCT rank) FROM captain
Database: ship_1 (tables: captain)
Question: How many captains are in each rank?
SQL: SELECT count(*) ,  rank FROM captain GROUP BY rank
Database: ship_1 (tables: captain)
Question: Count the number of captains that have each rank.
SQL: SELECT count(*) ,  rank FROM captain GROUP BY rank
Database: ship_1 (tables: captain)
Question: How many captains with younger than 50 are in each rank?
SQL: SELECT count(*) ,  rank FROM captain WHERE age  <  50 GROUP BY rank
Database: ship_1 (tables: captain)
Question: Count the number of captains younger than 50 of each rank.
SQL: SELECT count(*) ,  rank FROM captain WHERE age  <  50 GROUP BY rank
Database: ship_1 (tables: captain)
Question: Sort all captain names by their ages from old to young.
SQL: SELECT name FROM captain ORDER BY age DESC
Database: ship_1 (tables: captain)
Question: What are the names of captains, sorted by age descending?
SQL: SELECT name FROM captain ORDER BY age DESC
Database: ship_1 (tables: captain)
Question: Find the name, class and rank of all captains.
SQL: SELECT name ,  CLASS ,  rank FROM captain
Database: ship_1 (tables: captain)
Question: What are the names, classes, and ranks of all captains?
SQL: SELECT name ,  CLASS ,  rank FROM captain
Database: ship_1 (tables: captain)
Question: Which rank is the most common among captains?
SQL: SELECT rank FROM captain GROUP BY rank ORDER BY count(*) DESC LIMIT 1
Database: ship_1 (tables: captain)
Question: Return the rank for which there are the fewest captains.
SQL: SELECT rank FROM captain GROUP BY rank ORDER BY count(*) DESC LIMIT 1
Database: ship_1 (tables: captain)
Question: Which classes have more than two captains?
SQL: SELECT CLASS FROM captain GROUP BY CLASS HAVING count(*)  >  2
Database: ship_1 (tables: captain)
Question: Give the classes that have more than two captains.
SQL: SELECT CLASS FROM captain GROUP BY CLASS HAVING count(*)  >  2
Database: ship_1 (tables: captain)
Question: Find the name of captains whose rank are either Midshipman or Lieutenant.
SQL: SELECT name FROM captain WHERE rank  =  'Midshipman' OR rank  =  'Lieutenant'
Database: ship_1 (tables: captain)
Question: What are the names of captains that have either the rank Midshipman or Lieutenant?
SQL: SELECT name FROM captain WHERE rank  =  'Midshipman' OR rank  =  'Lieutenant'
Database: ship_1 (tables: captain)
Question: What are the average and minimum age of captains in different class?
SQL: SELECT avg(age) ,  min(age) ,  CLASS FROM captain GROUP BY CLASS
Database: ship_1 (tables: captain)
Question: Return the average and minimum age of captains in each class.
SQL: SELECT avg(age) ,  min(age) ,  CLASS FROM captain GROUP BY CLASS
Database: ship_1 (tables: captain)
Question: Find the captain rank that has some captains in both Cutter and Armed schooner classes.
SQL: SELECT rank FROM captain WHERE CLASS  =  'Cutter' INTERSECT SELECT rank FROM captain WHERE CLASS  =  'Armed schooner'
Database: ship_1 (tables: captain)
Question: What are the ranks of captains that are both in the Cutter and Armed schooner classes?
SQL: SELECT rank FROM captain WHERE CLASS  =  'Cutter' INTERSECT SELECT rank FROM captain WHERE CLASS  =  'Armed schooner'
Database: ship_1 (tables: captain)
Question: Find the captain rank that has no captain in Third-rate ship of the line class.
SQL: SELECT rank FROM captain EXCEPT SELECT rank FROM captain WHERE CLASS  =  'Third-rate ship of the line'
Database: ship_1 (tables: captain)
Question: What are the ranks of captains that have no captain that are in the Third-rate ship of the line class?
SQL: SELECT rank FROM captain EXCEPT SELECT rank FROM captain WHERE CLASS  =  'Third-rate ship of the line'
Database: ship_1 (tables: captain)
Question: What is the name of the youngest captain?
SQL: SELECT name FROM captain ORDER BY age LIMIT 1
Database: ship_1 (tables: captain)
Question: Return the name of the youngest captain.
SQL: SELECT name FROM captain ORDER BY age LIMIT 1
Database: ship_1 (tables: ship)
Question: how many ships are there?
SQL: SELECT count(*) FROM ship
Database: ship_1 (tables: ship)
Question: Count the number of ships.
SQL: SELECT count(*) FROM ship
Database: ship_1 (tables: ship)
Question: Find the name, type, and flag of the ship that is built in the most recent year.
SQL: SELECT name ,  TYPE ,  flag FROM ship ORDER BY built_year DESC LIMIT 1
Database: ship_1 (tables: ship)
Question: What is the name, type, and flag of the ship that was built in the most recent year?
SQL: SELECT name ,  TYPE ,  flag FROM ship ORDER BY built_year DESC LIMIT 1
Database: ship_1 (tables: ship)
Question: Group by ships by flag, and return number of ships that have each flag.
SQL: SELECT count(*) ,  flag FROM ship GROUP BY flag
Database: ship_1 (tables: ship)
Question: What are the different ship flags, and how many ships have each?
SQL: SELECT count(*) ,  flag FROM ship GROUP BY flag
Database: ship_1 (tables: ship)
Question: Which flag is most widely used among all ships?
SQL: SELECT flag FROM ship GROUP BY flag ORDER BY count(*) DESC LIMIT 1
Database: ship_1 (tables: ship)
Question: Return the flag that is most common among all ships.
SQL: SELECT flag FROM ship GROUP BY flag ORDER BY count(*) DESC LIMIT 1
Database: ship_1 (tables: ship)
Question: List all ship names in the order of built year and class.
SQL: SELECT name FROM ship ORDER BY built_year ,  CLASS
Database: ship_1 (tables: ship)
Question: What are the names of ships, ordered by year they were built and their class?
SQL: SELECT name FROM ship ORDER BY built_year ,  CLASS
Database: ship_1 (tables: ship)
Question: Find the ship type that are used by both ships with Panama and Malta flags.
SQL: SELECT TYPE FROM ship WHERE flag  =  'Panama' INTERSECT SELECT TYPE FROM ship WHERE flag  =  'Malta'
Database: ship_1 (tables: ship)
Question: What types of ships have both ships that have Panama Flags and Malta flags?
SQL: SELECT TYPE FROM ship WHERE flag  =  'Panama' INTERSECT SELECT TYPE FROM ship WHERE flag  =  'Malta'
Database: ship_1 (tables: ship)
Question: In which year were most of ships built?
SQL: SELECT built_year FROM ship GROUP BY built_year ORDER BY count(*) DESC LIMIT 1
Database: ship_1 (tables: ship)
Question: What is the year in which most ships were built?
SQL: SELECT built_year FROM ship GROUP BY built_year ORDER BY count(*) DESC LIMIT 1
Database: ship_1 (tables: captain, ship)
Question: Find the name of the ships that have more than one captain.
SQL: SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id  =  t2.ship_id GROUP BY t2.ship_id HAVING count(*)  >  1
Database: ship_1 (tables: captain, ship)
Question: What are the names of ships that have more than one captain?
SQL: SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id  =  t2.ship_id GROUP BY t2.ship_id HAVING count(*)  >  1
Database: ship_1 (tables: captain, ship)
Question: what are the names and classes of the ships that do not have any captain yet?
SQL: SELECT name ,  CLASS FROM ship WHERE ship_id NOT IN (SELECT ship_id FROM captain)
Database: ship_1 (tables: captain, ship)
Question: Return the names and classes of ships that do not have a captain?
SQL: SELECT name ,  CLASS FROM ship WHERE ship_id NOT IN (SELECT ship_id FROM captain)
Database: ship_1 (tables: captain, ship)
Question: Find the name of the ship that is steered by the youngest captain.
SQL: SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id  =  t2.ship_id ORDER BY t2.age LIMIT 1
Database: ship_1 (tables: captain, ship)
Question: What is the name of the ship that is commanded by the youngest captain?
SQL: SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id  =  t2.ship_id ORDER BY t2.age LIMIT 1
Database: ship_1 (tables: captain, ship)
Question: Find the name and flag of ships that are not steered by any captain with Midshipman rank.
SQL: SELECT name ,  flag FROM ship WHERE ship_id NOT IN (SELECT ship_id FROM captain WHERE rank  =  'Midshipman')
Database: ship_1 (tables: captain, ship)
Question: What are the names and flags of ships that do not have a captain with the rank of Midshipman?
SQL: SELECT name ,  flag FROM ship WHERE ship_id NOT IN (SELECT ship_id FROM captain WHERE rank  =  'Midshipman')
Database: ship_1 (tables: captain, ship)
Question: Find the name of the ships that are steered by both a captain with Midshipman rank and a captain with Lieutenant rank.
SQL: SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id  =  t2.ship_id WHERE t2.rank  =  'Midshipman' INTERSECT SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id  =  t2.ship_id WHERE t2.rank  =  'Lieutenant'
Database: ship_1 (tables: captain, ship)
Question: What are the names of ships that are commanded by both captains with the rank of Midshipman and captains with the rank of Lieutenant?
SQL: SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id  =  t2.ship_id WHERE t2.rank  =  'Midshipman' INTERSECT SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id  =  t2.ship_id WHERE t2.rank  =  'Lieutenant'
Database: city_record (tables: hosting_city)
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
Database: city_record (tables: hosting_city)
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
Database: city_record (tables: match)
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"
Database: city_record (tables: match)
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"
Database: city_record (tables: city, hosting_city)
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
Database: city_record (tables: city, hosting_city)
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
Database: city_record (tables: city, hosting_city)
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
Database: city_record (tables: city, hosting_city)
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
Database: city_record (tables: match, city, hosting_city)
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"
Database: city_record (tables: match, city, hosting_city)
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"
Database: city_record (tables: city, temperature)
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"
Database: city_record (tables: city, temperature)
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"
Database: city_record (tables: city, hosting_city)
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 )"
Database: city_record (tables: city, hosting_city)
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 )"
Database: city_record (tables: city)
Question: Which three cities have the largest regional population?
SQL: SELECT city FROM city ORDER BY regional_population DESC LIMIT 3
Database: city_record (tables: city)
Question: What are the three largest cities in terms of regional population?
SQL: SELECT city FROM city ORDER BY regional_population DESC LIMIT 3
Database: city_record (tables: city)
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
Database: city_record (tables: city)
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
Database: city_record (tables: city, temperature)
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
Database: city_record (tables: city, temperature)
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
Database: city_record (tables: city, temperature)
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
Database: city_record (tables: city, temperature)
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
Database: city_record (tables: city, temperature, hosting_city)
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
Database: city_record (tables: city, temperature, hosting_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
Database: city_record (tables: city, temperature, hosting_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
Database: city_record (tables: city, temperature, hosting_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
Database: city_record (tables: city, temperature, hosting_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
Database: city_record (tables: city, temperature, hosting_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
Database: city_record (tables: city)
Question: Please give me a list of cities whose regional population is over 10000000.
SQL: SELECT city FROM city WHERE regional_population  >  10000000
Database: city_record (tables: city)
Question: Which cities have regional population above 10000000?
SQL: SELECT city FROM city WHERE regional_population  >  10000000
Database: city_record (tables: city)
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
Database: city_record (tables: city)
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
Database: city_record (tables: match)
Question: Find the number of matches in different competitions.
SQL: SELECT count(*) ,  Competition FROM MATCH GROUP BY Competition
Database: city_record (tables: match)
Question: For each competition, count the number of matches.
SQL: SELECT count(*) ,  Competition FROM MATCH GROUP BY Competition
Database: city_record (tables: match)
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
Database: city_record (tables: match)
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
Database: city_record (tables: city)
Question: what is the GDP of the city with the largest population.
SQL: SELECT gdp FROM city ORDER BY Regional_Population DESC LIMIT 1
Database: city_record (tables: city)
Question: Find the GDP of the city with the largest regional population.
SQL: SELECT gdp FROM city ORDER BY Regional_Population DESC LIMIT 1
Database: city_record (tables: city, hosting_city)
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