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 all team names.
assistant
SELECT Name FROM Team
human
Show the season, the player, and the name of the team that players belong to.
assistant
SELECT T1.Season , T1.Player , T2.Name FROM match_season AS T1 JOIN team AS T2 ON T1.Team = T2.Team_id
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 and the years played.
assistant
SELECT Player , Years_Played FROM player
human
How many distinct colleges are associated with players from the team with name 'Columbus Crew'.
assistant
SELECT count(DISTINCT T1.College) FROM match_season AS T1 JOIN team AS T2 ON T1.Team = T2.Team_id WHERE T2.Name = 'Columbus Crew'
human
What are the players who played for Columbus Crew, and how many years did each play for?
assistant
SELECT T1.Player , T1.Years_Played FROM player AS T1 JOIN team AS T2 ON T1.Team = T2.Team_id WHERE T2.Name = "Columbus Crew" ;