Database: match_season

CREATE TABLE "country" (
    "Country_id" int,
    "Country_name" text,
    "Capital" text,
    "Official_native_language" text,
    PRIMARY KEY ("Country_id")
);
CREATE TABLE `team` (
      `Team_id` int,
      `Name` text,
      PRIMARY KEY (`Team_id`)
) ;
CREATE TABLE "match_season" (
    "Season" real,
    "Player" text,
    "Position" text,
    "Country" int,
    "Team" int,
    "Draft_Pick_Number" int,
    "Draft_Class" text,
    "College" text,
    PRIMARY KEY ("Season"),
    FOREIGN KEY (`Country`) REFERENCES `country`(`Country_id`),
    FOREIGN KEY (`Team`) REFERENCES `team`(`Team_id`)
);
CREATE TABLE "player" (
    "Player_ID" int,
    "Player" text,
    "Years_Played" text,
    "Total_WL" text,
    "Singles_WL" text,
    "Doubles_WL" text,
    "Team" int,
    PRIMARY KEY ("Player_ID"),
    FOREIGN KEY (`Team`) REFERENCES `team`(`Team_id`)
);

Question: How many countries are there in total?
SQL: SELECT count(*) FROM country

Question: Count the number of countries.
SQL: SELECT count(*) FROM country

Question: Show the country name and capital of all countries.
SQL: SELECT Country_name ,  Capital FROM country

Question: What are the names and capitals of each country?
SQL: SELECT Country_name ,  Capital FROM 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%"

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%"

Question: Show all distinct positions of matches.
SQL: SELECT DISTINCT POSITION FROM match_season

Question: What are the different positions for match season?
SQL: SELECT DISTINCT POSITION FROM match_season

Question: Show the players from college UCLA.
SQL: SELECT Player FROM match_season WHERE College  =  "UCLA"

Question: Who are the players from UCLA?
SQL: SELECT Player FROM match_season WHERE College  =  "UCLA"

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"

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"

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"

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"

Question: How many distinct teams are involved in match seasons?
SQL: SELECT count(DISTINCT Team) FROM match_season

Question: Count the number of different teams involved in match season.
SQL: SELECT count(DISTINCT Team) FROM match_season

Question: Show the players and the years played.
SQL: SELECT Player ,  Years_Played FROM player

Question: Who are the different players and how many years has each played?
SQL: SELECT Player ,  Years_Played FROM player

Question: Show all team names.
SQL: SELECT Name FROM Team

Question: What are the names of all teams?
SQL: SELECT Name FROM Team

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

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

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"

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"

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"

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"

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"

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"

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"

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"

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

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

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"

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"

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"

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"

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"

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"

Question: Show the position of players and the corresponding number of players.
SQL: SELECT POSITION ,  COUNT(*) FROM match_season GROUP BY POSITION

Question: How many players played each position?
SQL: SELECT POSITION ,  COUNT(*) FROM match_season GROUP BY POSITION

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

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

Question: Return all players sorted by college in ascending alphabetical order.
SQL: SELECT player FROM match_season ORDER BY College ASC

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

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

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

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

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

Question: Show the name of colleges that have at least two players.
SQL: SELECT College FROM match_season GROUP BY College HAVING count(*)  >=  2

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

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

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

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)

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)

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"

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"

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"

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"