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 all the residences?
assistant
SELECT DISTINCT Residence FROM player;
human
How many people live at each of them?
assistant
SELECT Residence , COUNT( * ) FROM player GROUP BY Residence;
human
Now remove the residences with less than 2 people
assistant
SELECT Residence , COUNT( * ) FROM player EXCEPT SELECT Residence , COUNT( * ) FROM player WHERE COUNT( * ) < 2;
human
OK, now list all the people who live in Winnipeg and their gender.
assistant
SELECT Player_name , Gender FROM player WHERE Residence = "Winnipeg" ;