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
What are the residences of the players?
assistant
SELECT Residence FROM player;
human
Which of those residences have both male and female players?
assistant
SELECT Residence FROM player WHERE gender = 'M' INTERSECT SELECT Residence FROM player WHERE gender = 'F';
human
How many male players does Winnipeg have?
assistant
SELECT COUNT( * ) FROM player WHERE gender = 'M' AND residence = 'Winnipeg';
human
How many female players does Winnipeg have?
assistant
SELECT COUNT( * ) FROM player WHERE gender = 'F' AND residence = 'Winnipeg';
human
How many residences are there in total?
assistant
SELECT COUNT( distinct residence ) FROM player;
human
And how many of those six distinct residences have More than one player?
assistant
SELECT COUNT( * ) FROM ( SELECT * FROM player GROUP BY Residence HAVING COUNT( * ) > 1 ) ;