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 distinct position of players from college UCLA or Duke.
assistant
SELECT DISTINCT POSITION FROM match_season WHERE College = 'UCLA' OR College = 'Duke'
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 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 name of colleges that have at least two players.
assistant
SELECT College FROM match_season GROUP BY College HAVING count(*) >= 2
human
Return all players sorted by college in ascending alphabetical order.
assistant
SELECT player FROM match_season ORDER BY College ASC
human
Return the colleges that have players who play the Midfielder position, as well as players who play the Defender position.
assistant
SELECT College FROM match_season WHERE POSITION = "Midfielder" INTERSECT SELECT College FROM match_season WHERE POSITION = "Defender" ;