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 position of players and the corresponding number of players.
assistant
SELECT POSITION , COUNT(*) FROM match_season GROUP BY POSITION
human
How many distinct official languages are there among countries of players whose positions are defenders.
assistant
SELECT count(DISTINCT T1.Official_native_language) FROM country AS T1 JOIN match_season AS T2 ON T1.Country_id = T2.Country WHERE T2.Position = 'Defender'
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
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
human
Count the number of countries.
assistant
SELECT COUNT( * ) FROM country ;