target-benchmark/spider-queries huggingface dataset:
Database: phone_1 (tables: chip_model)
Question: Find the details for all chip models.
SQL: SELECT * FROM chip_model
Database: phone_1 (tables: chip_model)
Question: How many models do not have the wifi function?
SQL: SELECT count(*) FROM chip_model WHERE wifi  =  'No'
Database: phone_1 (tables: chip_model)
Question: Count the number of chip model that do not have wifi.
SQL: SELECT count(*) FROM chip_model WHERE wifi  =  'No'
Database: phone_1 (tables: chip_model)
Question: List all the model names sorted by their launch year.
SQL: SELECT model_name FROM chip_model ORDER BY launch_year
Database: phone_1 (tables: chip_model, phone)
Question: Find the average ram mib size of the chip models that are never used by any phone.
SQL: SELECT avg(RAM_MiB) FROM chip_model WHERE model_name NOT IN (SELECT chip_model FROM phone)
Database: phone_1 (tables: chip_model, phone)
Question: Find the names of the chip models that are not used by any phone with full accreditation type.
SQL: SELECT model_name FROM chip_model EXCEPT SELECT chip_model FROM phone WHERE Accreditation_type  =  'Full'
Database: phone_1 (tables: screen_mode, phone)
Question: Find the pixels of the screen modes that are used by both phones with full accreditation types and phones with Provisional accreditation types.
SQL: SELECT t1.pixels FROM screen_mode AS t1 JOIN phone AS t2 ON t1.Graphics_mode  =  t2.screen_mode WHERE t2.Accreditation_type  =  'Provisional' INTERSECT SELECT t1.pixels FROM screen_mode AS t1 JOIN phone AS t2 ON t1.Graphics_mode  =  t2.screen_mode WHERE t2.Accreditation_type  =  'Full'
Database: match_season (tables: country)
Question: How many countries are there in total?
SQL: SELECT count(*) FROM country
Database: match_season (tables: country)
Question: Count the number of countries.
SQL: SELECT count(*) FROM country
Database: match_season (tables: country)
Question: Show the country name and capital of all countries.
SQL: SELECT Country_name ,  Capital FROM country
Database: match_season (tables: country)
Question: What are the names and capitals of each country?
SQL: SELECT Country_name ,  Capital FROM country
Database: match_season (tables: country)
Question: Show all official native languages that contain the word "English".
SQL: SELECT Official_native_language FROM country WHERE Official_native_language LIKE "%English%"
Database: match_season (tables: country)
Question: What are the official native languages that contain the string "English".
SQL: SELECT Official_native_language FROM country WHERE Official_native_language LIKE "%English%"
Database: match_season (tables: match_season)
Question: Show all distinct positions of matches.
SQL: SELECT DISTINCT POSITION FROM match_season
Database: match_season (tables: match_season)
Question: What are the different positions for match season?
SQL: SELECT DISTINCT POSITION FROM match_season
Database: match_season (tables: match_season)
Question: Show the players from college UCLA.
SQL: SELECT Player FROM match_season WHERE College  =  "UCLA"
Database: match_season (tables: match_season)
Question: Who are the players from UCLA?
SQL: SELECT Player FROM match_season WHERE College  =  "UCLA"
Database: match_season (tables: match_season)
Question: Show the distinct position of players from college UCLA or Duke.
SQL: SELECT DISTINCT POSITION FROM match_season WHERE College  =  "UCLA" OR College  =  "Duke"
Database: match_season (tables: match_season)
Question: What are the different positions of players from UCLA or Duke colleges?
SQL: SELECT DISTINCT POSITION FROM match_season WHERE College  =  "UCLA" OR College  =  "Duke"
Database: match_season (tables: match_season)
Question: Show the draft pick numbers and draft classes of players whose positions are defenders.
SQL: SELECT Draft_Pick_Number ,  Draft_Class FROM match_season WHERE POSITION  =  "Defender"
Database: match_season (tables: match_season)
Question: What are the draft pick numbers and draft classes for players who play the Defender position?
SQL: SELECT Draft_Pick_Number ,  Draft_Class FROM match_season WHERE POSITION  =  "Defender"
Database: match_season (tables: match_season)
Question: How many distinct teams are involved in match seasons?
SQL: SELECT count(DISTINCT Team) FROM match_season
Database: match_season (tables: match_season)
Question: Count the number of different teams involved in match season.
SQL: SELECT count(DISTINCT Team) FROM match_season
Database: match_season (tables: player)
Question: Show the players and the years played.
SQL: SELECT Player ,  Years_Played FROM player
Database: match_season (tables: player)
Question: Who are the different players and how many years has each played?
SQL: SELECT Player ,  Years_Played FROM player
Database: match_season (tables: team)
Question: Show all team names.
SQL: SELECT Name FROM Team
Database: match_season (tables: team)
Question: What are the names of all teams?
SQL: SELECT Name FROM Team
Database: match_season (tables: match_season, country)
Question: Show the season, the player, and the name of the country that player belongs to.
SQL: SELECT T2.Season ,  T2.Player ,  T1.Country_name FROM country AS T1 JOIN match_season AS T2 ON T1.Country_id  =  T2.Country
Database: match_season (tables: match_season, country)
Question: For each player, what are their name, season, and country that they belong to?
SQL: SELECT T2.Season ,  T2.Player ,  T1.Country_name FROM country AS T1 JOIN match_season AS T2 ON T1.Country_id  =  T2.Country
Database: match_season (tables: match_season, country)
Question: Which players are from Indonesia?
SQL: SELECT T2.Player FROM country AS T1 JOIN match_season AS T2 ON T1.Country_id  =  T2.Country WHERE T1.Country_name  =  "Indonesia"
Database: match_season (tables: match_season, country)
Question: Who are the players from Indonesia?
SQL: SELECT T2.Player FROM country AS T1 JOIN match_season AS T2 ON T1.Country_id  =  T2.Country WHERE T1.Country_name  =  "Indonesia"
Database: match_season (tables: match_season, country)
Question: What are the distinct positions of the players from a country whose capital is Dublin?
SQL: SELECT DISTINCT T2.Position FROM country AS T1 JOIN match_season AS T2 ON T1.Country_id  =  T2.Country WHERE T1.Capital  =  "Dublin"
Database: match_season (tables: match_season, country)
Question: Give the different positions of players who play for the country with the capital Dublin.
SQL: SELECT DISTINCT T2.Position FROM country AS T1 JOIN match_season AS T2 ON T1.Country_id  =  T2.Country WHERE T1.Capital  =  "Dublin"
Database: match_season (tables: match_season, country)
Question: What are the official languages of the countries of players from Maryland or Duke college?
SQL: SELECT T1.Official_native_language FROM country AS T1 JOIN match_season AS T2 ON T1.Country_id  =  T2.Country WHERE T2.College  =  "Maryland" OR T2.College  =  "Duke"
Database: match_season (tables: match_season, country)
Question: Return the official native languages of countries who have players from Maryland or Duke colleges.
SQL: SELECT T1.Official_native_language FROM country AS T1 JOIN match_season AS T2 ON T1.Country_id  =  T2.Country WHERE T2.College  =  "Maryland" OR T2.College  =  "Duke"
Database: match_season (tables: match_season, country)
Question: How many distinct official languages are there among countries of players whose positions are defenders.
SQL: SELECT count(DISTINCT T1.Official_native_language) FROM country AS T1 JOIN match_season AS T2 ON T1.Country_id  =  T2.Country WHERE T2.Position  =  "Defender"
Database: match_season (tables: match_season, country)
Question: Count the number of different official languages corresponding to countries that players who play Defender are from.
SQL: SELECT count(DISTINCT T1.Official_native_language) FROM country AS T1 JOIN match_season AS T2 ON T1.Country_id  =  T2.Country WHERE T2.Position  =  "Defender"
Database: match_season (tables: match_season, team)
Question: Show the season, the player, and the name of the team that players belong to.
SQL: SELECT T1.Season ,  T1.Player ,  T2.Name FROM match_season AS T1 JOIN team AS T2 ON T1.Team  =  T2.Team_id
Database: match_season (tables: match_season, team)
Question: Who are the different players, what season do they play in, and what is the name of the team they are on?
SQL: SELECT T1.Season ,  T1.Player ,  T2.Name FROM match_season AS T1 JOIN team AS T2 ON T1.Team  =  T2.Team_id
Database: match_season (tables: match_season, team)
Question: Show the positions of the players from the team with name "Ryley Goldner".
SQL: SELECT T1.Position FROM match_season AS T1 JOIN team AS T2 ON T1.Team  =  T2.Team_id WHERE T2.Name  =  "Ryley Goldner"
Database: match_season (tables: match_season, team)
Question: Return the positions of players on the team Ryley Goldner.
SQL: SELECT T1.Position FROM match_season AS T1 JOIN team AS T2 ON T1.Team  =  T2.Team_id WHERE T2.Name  =  "Ryley Goldner"
Database: match_season (tables: match_season, team)
Question: How many distinct colleges are associated with players from the team with name "Columbus Crew".
SQL: SELECT count(DISTINCT T1.College) FROM match_season AS T1 JOIN team AS T2 ON T1.Team  =  T2.Team_id WHERE T2.Name  =  "Columbus Crew"
Database: match_season (tables: match_season, team)
Question: Count the number of different colleges that players who play for Columbus Crew are from.
SQL: SELECT count(DISTINCT T1.College) FROM match_season AS T1 JOIN team AS T2 ON T1.Team  =  T2.Team_id WHERE T2.Name  =  "Columbus Crew"
Database: match_season (tables: team, player)
Question: Show the players and years played for players from team "Columbus Crew".
SQL: SELECT T1.Player , T1.Years_Played FROM player AS T1 JOIN team AS T2 ON T1.Team  =  T2.Team_id WHERE T2.Name  =  "Columbus Crew"
Database: match_season (tables: team, player)
Question: What are the players who played for Columbus Crew, and how many years did each play for?
SQL: SELECT T1.Player , T1.Years_Played FROM player AS T1 JOIN team AS T2 ON T1.Team  =  T2.Team_id WHERE T2.Name  =  "Columbus Crew"
Database: match_season (tables: match_season)
Question: Show the position of players and the corresponding number of players.
SQL: SELECT POSITION ,  COUNT(*) FROM match_season GROUP BY POSITION
Database: match_season (tables: match_season)
Question: How many players played each position?
SQL: SELECT POSITION ,  COUNT(*) FROM match_season GROUP BY POSITION
Database: match_season (tables: match_season, country)
Question: Show the country names and the corresponding number of players.
SQL: SELECT Country_name ,  COUNT(*) FROM country AS T1 JOIN match_season AS T2 ON T1.Country_id  =  T2.Country GROUP BY T1.Country_name
Database: match_season (tables: match_season, country)
Question: How many players are from each country?
SQL: SELECT Country_name ,  COUNT(*) FROM country AS T1 JOIN match_season AS T2 ON T1.Country_id  =  T2.Country GROUP BY T1.Country_name
Database: match_season (tables: match_season)
Question: Return all players sorted by college in ascending alphabetical order.
SQL: SELECT player FROM match_season ORDER BY College ASC
Database: match_season (tables: match_season)
Question: What are all the players who played in match season, sorted by college in ascending alphabetical order?
SQL: SELECT player FROM match_season ORDER BY College ASC
Database: match_season (tables: match_season)
Question: Show the most common position of players in match seasons.
SQL: SELECT POSITION FROM match_season GROUP BY POSITION ORDER BY count(*) DESC LIMIT 1
Database: match_season (tables: match_season)
Question: What is the position that is most common among players in match seasons?
SQL: SELECT POSITION FROM match_season GROUP BY POSITION ORDER BY count(*) DESC LIMIT 1
Database: match_season (tables: match_season)
Question: Show the top 3 most common colleges of players in match seasons.
SQL: SELECT College FROM match_season GROUP BY College ORDER BY count(*) DESC LIMIT 3
Database: match_season (tables: match_season)
Question: What are the three colleges from which the most players are from?
SQL: SELECT College FROM match_season GROUP BY College ORDER BY count(*) DESC LIMIT 3
Database: match_season (tables: match_season)
Question: Show the name of colleges that have at least two players.
SQL: SELECT College FROM match_season GROUP BY College HAVING count(*)  >=  2
Database: match_season (tables: match_season)
Question: What are the names of all colleges that have two or more players?
SQL: SELECT College FROM match_season GROUP BY College HAVING count(*)  >=  2
Database: match_season (tables: match_season)
Question: Show the name of colleges that have at least two players in descending alphabetical order.
SQL: SELECT College FROM match_season GROUP BY College HAVING count(*)  >=  2 ORDER BY College DESC
Database: match_season (tables: match_season)
Question: What are the names of colleges that have two or more players, listed in descending alphabetical order?
SQL: SELECT College FROM match_season GROUP BY College HAVING count(*)  >=  2 ORDER BY College DESC
Database: match_season (tables: match_season, team)
Question: What are the names of teams that do no have match season record?
SQL: SELECT Name FROM team WHERE Team_id NOT IN (SELECT Team FROM match_season)
Database: match_season (tables: match_season, team)
Question: Return the names of teams that have no match season record.
SQL: SELECT Name FROM team WHERE Team_id NOT IN (SELECT Team FROM match_season)
Database: match_season (tables: match_season, country)
Question: What are the names of countries that have both players with position forward and players with position defender?
SQL: SELECT T1.Country_name FROM country AS T1 JOIN match_season AS T2 ON T1.Country_id  =  T2.Country WHERE T2.Position  =  "Forward" INTERSECT SELECT T1.Country_name FROM country AS T1 JOIN match_season AS T2 ON T1.Country_id  =  T2.Country WHERE T2.Position  =  "Defender"
Database: match_season (tables: match_season, country)
Question: Return the names of countries that have players that play the Forward position, as well as players who play the Defender position.
SQL: SELECT T1.Country_name FROM country AS T1 JOIN match_season AS T2 ON T1.Country_id  =  T2.Country WHERE T2.Position  =  "Forward" INTERSECT SELECT T1.Country_name FROM country AS T1 JOIN match_season AS T2 ON T1.Country_id  =  T2.Country WHERE T2.Position  =  "Defender"
Database: match_season (tables: match_season)
Question: Which college have both players with position midfielder and players with position defender?
SQL: SELECT College FROM match_season WHERE POSITION  =  "Midfielder" INTERSECT SELECT College FROM match_season WHERE POSITION  =  "Defender"
Database: match_season (tables: match_season)
Question: Return the colleges that have players who play the Midfielder position, as well as players who play the Defender position.
SQL: SELECT College FROM match_season WHERE POSITION  =  "Midfielder" INTERSECT SELECT College FROM match_season WHERE POSITION  =  "Defender"
Database: climbing (tables: climber)
Question: How many climbers are there?
SQL: SELECT count(*) FROM climber
Database: climbing (tables: climber)
Question: Count the number of climbers.
SQL: SELECT count(*) FROM climber
Database: climbing (tables: climber)
Question: List the names of climbers in descending order of points.
SQL: SELECT Name FROM climber ORDER BY Points DESC
Database: climbing (tables: climber)
Question: What are the names of the climbers, ordered by points descending?
SQL: SELECT Name FROM climber ORDER BY Points DESC
Database: climbing (tables: climber)
Question: List the names of climbers whose country is not Switzerland.
SQL: SELECT Name FROM climber WHERE Country != "Switzerland"
Database: climbing (tables: climber)
Question: What are the names of climbers who are not from the country of Switzerland?
SQL: SELECT Name FROM climber WHERE Country != "Switzerland"
Database: climbing (tables: climber)
Question: What is the maximum point for climbers whose country is United Kingdom?
SQL: SELECT max(Points) FROM climber WHERE Country  =  "United Kingdom"
Database: climbing (tables: climber)
Question: Return the maximum number of points for climbers from the United Kingdom.
SQL: SELECT max(Points) FROM climber WHERE Country  =  "United Kingdom"
Database: climbing (tables: climber)
Question: How many distinct countries are the climbers from?
SQL: SELECT COUNT(DISTINCT Country) FROM climber
Database: climbing (tables: climber)
Question: Count the number of different countries that climbers are from.
SQL: SELECT COUNT(DISTINCT Country) FROM climber
Database: climbing (tables: mountain)
Question: What are the names of mountains in ascending alphabetical order?
SQL: SELECT Name FROM mountain ORDER BY Name ASC
Database: climbing (tables: mountain)
Question: Give the names of mountains in alphabetical order.
SQL: SELECT Name FROM mountain ORDER BY Name ASC
Database: climbing (tables: mountain)
Question: What are the countries of mountains with height bigger than 5000?
SQL: SELECT Country FROM mountain WHERE Height  >  5000
Database: climbing (tables: mountain)
Question: Return the countries of the mountains that have a height larger than 5000.
SQL: SELECT Country FROM mountain WHERE Height  >  5000
Database: climbing (tables: mountain)
Question: What is the name of the highest mountain?
SQL: SELECT Name FROM mountain ORDER BY Height DESC LIMIT 1
Database: climbing (tables: mountain)
Question: Return the name of the mountain with the greatest height.
SQL: SELECT Name FROM mountain ORDER BY Height DESC LIMIT 1
Database: climbing (tables: mountain)
Question: List the distinct ranges of the mountains with the top 3 prominence.
SQL: SELECT DISTINCT Range FROM mountain ORDER BY Prominence DESC LIMIT 3
Database: climbing (tables: mountain)
Question: What are the different ranges of the 3 mountains with the highest prominence?
SQL: SELECT DISTINCT Range FROM mountain ORDER BY Prominence DESC LIMIT 3
Database: climbing (tables: mountain, climber)
Question: Show names of climbers and the names of mountains they climb.
SQL: SELECT T1.Name ,  T2.Name FROM climber AS T1 JOIN mountain AS T2 ON T1.Mountain_ID  =  T2.Mountain_ID
Database: climbing (tables: mountain, climber)
Question: What are the names of climbers and the corresponding names of mountains that they climb?
SQL: SELECT T1.Name ,  T2.Name FROM climber AS T1 JOIN mountain AS T2 ON T1.Mountain_ID  =  T2.Mountain_ID
Database: climbing (tables: mountain, climber)
Question: Show the names of climbers and the heights of mountains they climb.
SQL: SELECT T1.Name ,  T2.Height FROM climber AS T1 JOIN mountain AS T2 ON T1.Mountain_ID  =  T2.Mountain_ID
Database: climbing (tables: mountain, climber)
Question: What are the names of climbers and the corresponding heights of the mountains that they climb?
SQL: SELECT T1.Name ,  T2.Height FROM climber AS T1 JOIN mountain AS T2 ON T1.Mountain_ID  =  T2.Mountain_ID
Database: climbing (tables: mountain, climber)
Question: Show the height of the mountain climbed by the climber with the maximum points.
SQL: SELECT T2.Height FROM climber AS T1 JOIN mountain AS T2 ON T1.Mountain_ID  =  T2.Mountain_ID ORDER BY T1.Points DESC LIMIT 1
Database: climbing (tables: mountain, climber)
Question: What is the height of the mountain climbined by the climbing who had the most points?
SQL: SELECT T2.Height FROM climber AS T1 JOIN mountain AS T2 ON T1.Mountain_ID  =  T2.Mountain_ID ORDER BY T1.Points DESC LIMIT 1
Database: climbing (tables: mountain, climber)
Question: Show the distinct names of mountains climbed by climbers from country "West Germany".
SQL: SELECT DISTINCT T2.Name FROM climber AS T1 JOIN mountain AS T2 ON T1.Mountain_ID  =  T2.Mountain_ID WHERE T1.Country  =  "West Germany"
Database: climbing (tables: mountain, climber)
Question: What are the different names of mountains ascended by climbers from the country of West Germany?
SQL: SELECT DISTINCT T2.Name FROM climber AS T1 JOIN mountain AS T2 ON T1.Mountain_ID  =  T2.Mountain_ID WHERE T1.Country  =  "West Germany"
Database: climbing (tables: mountain, climber)
Question: Show the times used by climbers to climb mountains in Country Uganda.
SQL: SELECT T1.Time FROM climber AS T1 JOIN mountain AS T2 ON T1.Mountain_ID  =  T2.Mountain_ID WHERE T2.Country  =  "Uganda"
Database: climbing (tables: mountain, climber)
Question: What are the times used by climbers who climbed mountains in the country of Uganda?
SQL: SELECT T1.Time FROM climber AS T1 JOIN mountain AS T2 ON T1.Mountain_ID  =  T2.Mountain_ID WHERE T2.Country  =  "Uganda"
Database: climbing (tables: climber)
Question: Please show the countries and the number of climbers from each country.
SQL: SELECT Country ,  COUNT(*) FROM climber GROUP BY Country
Database: climbing (tables: climber)
Question: How many climbers are from each country?
SQL: SELECT Country ,  COUNT(*) FROM climber GROUP BY Country
Database: climbing (tables: mountain)
Question: List the countries that have more than one mountain.
SQL: SELECT Country FROM mountain GROUP BY Country HAVING COUNT(*)  >  1
Database: climbing (tables: mountain)
Question: Which countries have more than one mountain?
SQL: SELECT Country FROM mountain GROUP BY Country HAVING COUNT(*)  >  1
Database: climbing (tables: mountain, climber)
Question: List the names of mountains that do not have any climber.
SQL: SELECT Name FROM mountain WHERE Mountain_ID NOT IN (SELECT Mountain_ID FROM climber)
Database: climbing (tables: mountain, climber)
Question: What are the names of countains that no climber has climbed?
SQL: SELECT Name FROM mountain WHERE Mountain_ID NOT IN (SELECT Mountain_ID FROM climber)
Database: climbing (tables: mountain)
Question: Show the countries that have mountains with height more than 5600 stories and mountains with height less than 5200.
SQL: SELECT Country FROM mountain WHERE Height  >  5600 INTERSECT SELECT Country FROM mountain WHERE Height  <  5200
Database: climbing (tables: mountain)
Question: What are the countries that have both mountains that are higher than 5600 and lower than 5200?
SQL: SELECT Country FROM mountain WHERE Height  >  5600 INTERSECT SELECT Country FROM mountain WHERE Height  <  5200
Database: climbing (tables: mountain)
Question: Show the range that has the most number of mountains.
SQL: SELECT Range FROM mountain GROUP BY Range ORDER BY COUNT(*) DESC LIMIT 1
Database: climbing (tables: mountain)
Question: Which range contains the most mountains?
SQL: SELECT Range FROM mountain GROUP BY Range ORDER BY COUNT(*) DESC LIMIT 1
Database: climbing (tables: mountain)
Question: Show the names of mountains with height more than 5000 or prominence more than 1000.
SQL: SELECT Name FROM mountain WHERE Height  >  5000 OR Prominence  >  1000
Database: climbing (tables: mountain)
Question: What are the names of mountains that have a height of over 5000 or a prominence of over 1000?
SQL: SELECT Name FROM mountain WHERE Height  >  5000 OR Prominence  >  1000
Database: body_builder (tables: body_builder)
Question: How many body builders are there?
SQL: SELECT count(*) FROM body_builder
Database: body_builder (tables: body_builder)
Question: List the total scores of body builders in ascending order.
SQL: SELECT Total FROM body_builder ORDER BY Total ASC
Database: body_builder (tables: body_builder)
Question: List the snatch score and clean jerk score of body builders in ascending order of snatch score.
SQL: SELECT Snatch ,  Clean_Jerk FROM body_builder ORDER BY Snatch ASC
Database: body_builder (tables: body_builder)
Question: What is the average snatch score of body builders?
SQL: SELECT avg(Snatch) FROM body_builder
Database: body_builder (tables: body_builder)
Question: What are the clean and jerk score of the body builder with the highest total score?
SQL: SELECT Clean_Jerk FROM body_builder ORDER BY Total DESC LIMIT 1
Database: body_builder (tables: people)
Question: What are the birthdays of people in ascending order of height?
SQL: SELECT Birth_Date FROM People ORDER BY Height ASC
Database: body_builder (tables: people, body_builder)
Question: What are the names of body builders?
SQL: SELECT T2.Name FROM body_builder AS T1 JOIN people AS T2 ON T1.People_ID  =  T2.People_ID
Database: body_builder (tables: people, body_builder)
Question: What are the names of body builders whose total score is higher than 300?
SQL: SELECT T2.Name FROM body_builder AS T1 JOIN people AS T2 ON T1.People_ID  =  T2.People_ID WHERE T1.Total  >  300