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 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
What are the official languages of the countries of players from Maryland or Duke college?
assistant
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'
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 players from college UCLA.
assistant
SELECT Player FROM match_season WHERE College = 'UCLA'
human
Which college have both players with position midfielder and players with position defender?
assistant
SELECT College FROM match_season WHERE POSITION = 'Midfielder' INTERSECT SELECT College FROM match_season WHERE POSITION = 'Defender'
human
What are the different positions of players from UCLA or Duke colleges?
assistant
SELECT DISTINCT POSITION FROM match_season WHERE College = "UCLA" OR College = "Duke" ;