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 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
human
What are the names of countries that have both players with position forward and players with position defender?
assistant
SELECT T1.Country_name FROM country AS T1 JOIN match_season AS T2 ON T1.Country_id = T2.Country WHERE T2.Position = 'Forward' INTERSECT SELECT T1.Country_name FROM country AS T1 JOIN match_season AS T2 ON T1.Country_id = T2.Country WHERE T2.Position = 'Defender'
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
How many distinct teams are involved in match seasons?
assistant
SELECT count(DISTINCT Team) FROM match_season
human
What are the names of teams that do no have match season record?
assistant
SELECT Name FROM team WHERE Team_id NOT IN ( SELECT Team FROM match_season ) ;