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

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"