human
Schema:
player (Player_ID, Sponsor_name, Player_name, Gender, Residence, Occupation, Votes, Rank)
club (Club_ID, Club_name, Region, Start_year)
coach (Coach_ID, Coach_name, Gender, Club_ID, Rank)
player_coach (Player_ID, Coach_ID, Starting_year)
match_result (Rank, Club_ID, Gold, Big_Silver, Small_Silver, Bronze, Points)
Please answer with SQL.
assistant
Sure, I understand.
human
How many players are there?
assistant
SELECT count(*) FROM player
human
Show the names and genders of players with a coach starting after 2011.
assistant
SELECT T3.Player_name , T3.gender FROM player_coach AS T1 JOIN coach AS T2 ON T1.Coach_ID = T2.Coach_ID JOIN player AS T3 ON T1.Player_ID = T3.Player_ID WHERE T1.Starting_year > 2011
human
List the name and residence for players whose occupation is not 'Researcher'.
assistant
SELECT Player_name , residence FROM player WHERE Occupation != 'Researcher'
human
Show different occupations along with the number of players in each occupation.
assistant
SELECT Occupation , COUNT(*) FROM player GROUP BY Occupation
human
Please show the most common occupation of players.
assistant
SELECT Occupation FROM player GROUP BY Occupation ORDER BY COUNT(*) DESC LIMIT 1
human
What are the gender and occupation of players?
assistant
SELECT Gender , Occupation FROM player ;