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
Show the names of players and names of their coaches in descending order of the votes of players.
assistant
SELECT T3.Player_name , T2.coach_name 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 ORDER BY T3.Votes DESC
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
Show the names of players and names of their coaches.
assistant
SELECT T3.Player_name , T2.coach_name 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
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 the residences that have at least two players.
assistant
SELECT Residence FROM player GROUP BY Residence HAVING COUNT(*) >= 2
human
Show the names of sponsors of players whose residence is either 'Brandon' or 'Birtle'.
assistant
SELECT Sponsor_name FROM player WHERE Residence = "Brandon" OR Residence = "Birtle" ;