Database: riding_club

CREATE TABLE IF NOT EXISTS "player" (
"Player_ID" int,
"Sponsor_name" text,
"Player_name" text,
"Gender" text,
"Residence" text,
"Occupation" text,
"Votes" int,
"Rank" text,
PRIMARY KEY ("Player_ID")
);
CREATE TABLE IF NOT EXISTS "club" (
"Club_ID" int,
"Club_name" text,
"Region" text,
"Start_year" int,
PRIMARY KEY ("Club_ID")
);
CREATE TABLE IF NOT EXISTS "coach" (
"Coach_ID" int,
"Coach_name" text,
"Gender" text,
"Club_ID" int,
"Rank" int,
PRIMARY KEY ("Coach_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
);
CREATE TABLE IF NOT EXISTS "player_coach" (
"Player_ID" int,
"Coach_ID" int,
"Starting_year" int,
PRIMARY KEY ("Player_ID","Coach_ID"),
FOREIGN KEY (`Player_ID`) REFERENCES `player`(`Player_ID`),
FOREIGN KEY (`Coach_ID`) REFERENCES `coach`(`Coach_ID`)
);
CREATE TABLE IF NOT EXISTS "match_result" (
"Rank" int,
"Club_ID" int,
"Gold" int,
"Big_Silver" int,
"Small_Silver" int,
"Bronze" int,
"Points" int,
PRIMARY KEY ("Rank","Club_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
);

Question: How many players are there?
SQL: SELECT count(*) FROM player

Question: List the names of players in ascending order of votes.
SQL: SELECT Player_name FROM player ORDER BY Votes ASC

Question: What are the gender and occupation of players?
SQL: SELECT Gender ,  Occupation FROM player

Question: List the name and residence for players whose occupation is not "Researcher".
SQL: SELECT Player_name ,  residence FROM player WHERE Occupation != "Researcher"

Question: Show the names of sponsors of players whose residence is either "Brandon" or "Birtle".
SQL: SELECT Sponsor_name FROM player WHERE Residence  =  "Brandon" OR Residence  =  "Birtle"

Question: What is the name of the player with the largest number of votes?
SQL: SELECT Player_name FROM player ORDER BY Votes DESC LIMIT 1

Question: Show different occupations along with the number of players in each occupation.
SQL: SELECT Occupation ,  COUNT(*) FROM player GROUP BY Occupation

Question: Please show the most common occupation of players.
SQL: SELECT Occupation FROM player GROUP BY Occupation ORDER BY COUNT(*) DESC LIMIT 1

Question: Show the residences that have at least two players.
SQL: SELECT Residence FROM player GROUP BY Residence HAVING COUNT(*)  >=  2

Question: Show the names of players and names of their coaches.
SQL: 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

Question: Show the names of players coached by the rank 1 coach.
SQL: SELECT T3.Player_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 WHERE T2.Rank  =  1

Question: Show the names and genders of players with a coach starting after 2011.
SQL: 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

Question: Show the names of players and names of their coaches in descending order of the votes of players.
SQL: 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

Question: List the names of players that do not have coaches.
SQL: SELECT Player_name FROM player WHERE Player_ID NOT IN (SELECT Player_ID FROM player_coach)

Question: Show the residences that have both a player of gender "M" and a player of gender "F".
SQL: SELECT Residence FROM player WHERE gender  =  "M" INTERSECT SELECT Residence FROM player WHERE gender  =  "F"

Question: How many coaches does each club has? List the club id, name and the number of coaches.
SQL: SELECT T1.club_id ,  T1.club_name, count(*) FROM club AS T1 JOIN coach AS T2 ON T1.club_id  =  T2.club_id GROUP BY T1.club_id

Question: How many gold medals has the club with the most coaches won?
SQL: SELECT T1.club_id ,  T1.gold FROM match_result AS T1 JOIN coach AS T2 ON T1.club_id  =  T2.club_id GROUP BY T1.club_id ORDER BY count(*) DESC LIMIT 1