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 all team names.
assistant
SELECT Name FROM Team
human
Show the season, the player, and the name of the country that player belongs to.
assistant
SELECT T2.Season , T2.Player , T1.Country_name FROM country AS T1 JOIN match_season AS T2 ON T1.Country_id = T2.Country
human
How many countries are there in total?
assistant
SELECT count(*) FROM country
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 country name and capital of all countries.
assistant
SELECT Country_name , Capital FROM country ;