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
How many distinct teams are involved in match seasons?
assistant
SELECT count(DISTINCT Team) FROM match_season
human
Return all players sorted by college in ascending alphabetical order.
assistant
SELECT player FROM match_season ORDER BY College ASC
human
Show the name of colleges that have at least two players in descending alphabetical order.
assistant
SELECT College FROM match_season GROUP BY College HAVING count(*) >= 2 ORDER BY College DESC
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
Show the name of colleges that have at least two players.
assistant
SELECT College FROM match_season GROUP BY College HAVING count(*) >= 2
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 ;