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 players and the years played.
assistant
SELECT Player , Years_Played FROM player
human
Show the country names and the corresponding number of players.
assistant
SELECT Country_name , COUNT(*) FROM country AS T1 JOIN match_season AS T2 ON T1.Country_id = T2.Country GROUP BY T1.Country_name
human
Show the players and years played for players from team 'Columbus Crew'.
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'
human
Show the positions of the players from the team with name 'Ryley Goldner'.
assistant
SELECT T1.Position FROM match_season AS T1 JOIN team AS T2 ON T1.Team = T2.Team_id WHERE T2.Name = 'Ryley Goldner'
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 all team names.
assistant
SELECT Name FROM Team ;