human
Schema:
country (Country_id, Country_name, Capital, Official_native_language)
team (Team_id, Name)
match_season (Season, Player, Position, Country, Team, Draft_Pick_Number, Draft_Class, College)
player (Player_ID, Player, Years_Played, Total_WL, Singles_WL, Doubles_WL, Team)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the country names and the corresponding number of players.
assistant
SELECT Country_name , COUNT(*) FROM country AS T1 JOIN match_season AS T2 ON T1.Country_id = T2.Country GROUP BY T1.Country_name
human
Show all distinct positions of matches.
assistant
SELECT DISTINCT POSITION FROM match_season
human
Show the top 3 most common colleges of players in match seasons.
assistant
SELECT College FROM match_season GROUP BY College ORDER BY count(*) DESC LIMIT 3
human
Show the most common position of players in match seasons.
assistant
SELECT POSITION FROM match_season GROUP BY POSITION ORDER BY count(*) DESC LIMIT 1
human
What are the names of teams that do no have match season record?
assistant
SELECT Name FROM team WHERE Team_id NOT IN (SELECT Team FROM match_season)
human
How many distinct teams are involved in match seasons?
assistant
SELECT COUNT( DISTINCT Team ) FROM match_season ;