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
List the college and the corresponding team name.
assistant
SELECT college , name FROM match_season AS T1 JOIN team AS T2 ON T1.Team = T2.Team_id;
human
How many unique colleges are there for team 'Miami Fusion'?
assistant
SELECT COUNT( DISTINCT College ) FROM match_season AS T1 JOIN team AS T2 ON T1.Team = T2.Team_id WHERE T2.Name = 'Miami Fusion';
human
What about the team '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" ;