
CREATE TABLE club (
	"Club_ID" INTEGER, 
	"Club_name" TEXT, 
	"Region" TEXT, 
	"Start_year" INTEGER, 
	PRIMARY KEY ("Club_ID")
)


CREATE TABLE coach (
	"Coach_ID" INTEGER, 
	"Coach_name" TEXT, 
	"Gender" TEXT, 
	"Club_ID" INTEGER, 
	"Rank" INTEGER, 
	PRIMARY KEY ("Coach_ID"), 
	FOREIGN KEY("Club_ID") REFERENCES club ("Club_ID")
)


CREATE TABLE match_result (
	"Rank" INTEGER, 
	"Club_ID" INTEGER, 
	"Gold" INTEGER, 
	"Big_Silver" INTEGER, 
	"Small_Silver" INTEGER, 
	"Bronze" INTEGER, 
	"Points" INTEGER, 
	PRIMARY KEY ("Rank", "Club_ID"), 
	FOREIGN KEY("Club_ID") REFERENCES club ("Club_ID")
)


CREATE TABLE player (
	"Player_ID" INTEGER, 
	"Sponsor_name" TEXT, 
	"Player_name" TEXT, 
	"Gender" TEXT, 
	"Residence" TEXT, 
	"Occupation" TEXT, 
	"Votes" INTEGER, 
	"Rank" TEXT, 
	PRIMARY KEY ("Player_ID")
)


CREATE TABLE player_coach (
	"Player_ID" INTEGER, 
	"Coach_ID" INTEGER, 
	"Starting_year" INTEGER, 
	PRIMARY KEY ("Player_ID", "Coach_ID"), 
	FOREIGN KEY("Coach_ID") REFERENCES coach ("Coach_ID"), 
	FOREIGN KEY("Player_ID") REFERENCES player ("Player_ID")
)

CREATE TABLE club (
	"Club_ID" INTEGER, 
	"Club_name" TEXT, 
	"Region" TEXT, 
	"Start_year" INTEGER, 
	PRIMARY KEY ("Club_ID")
)

/*
2 rows from club table:
Club_ID	Club_name	Region	Start_year
1	AIK	USA	2009
2	BK Häcken	UK	1998
*/


CREATE TABLE coach (
	"Coach_ID" INTEGER, 
	"Coach_name" TEXT, 
	"Gender" TEXT, 
	"Club_ID" INTEGER, 
	"Rank" INTEGER, 
	PRIMARY KEY ("Coach_ID"), 
	FOREIGN KEY("Club_ID") REFERENCES club ("Club_ID")
)

/*
2 rows from coach table:
Coach_ID	Coach_name	Gender	Club_ID	Rank
1	Jameson Tomas	M	1	1
2	Joe Fabbri	F	1	2
*/


CREATE TABLE match_result (
	"Rank" INTEGER, 
	"Club_ID" INTEGER, 
	"Gold" INTEGER, 
	"Big_Silver" INTEGER, 
	"Small_Silver" INTEGER, 
	"Bronze" INTEGER, 
	"Points" INTEGER, 
	PRIMARY KEY ("Rank", "Club_ID"), 
	FOREIGN KEY("Club_ID") REFERENCES club ("Club_ID")
)

/*
2 rows from match_result table:
Rank	Club_ID	Gold	Big_Silver	Small_Silver	Bronze	Points
1	1	20	14	9	8	168
2	2	13	11	16	9	139
*/


CREATE TABLE player (
	"Player_ID" INTEGER, 
	"Sponsor_name" TEXT, 
	"Player_name" TEXT, 
	"Gender" TEXT, 
	"Residence" TEXT, 
	"Occupation" TEXT, 
	"Votes" INTEGER, 
	"Rank" TEXT, 
	PRIMARY KEY ("Player_ID")
)

/*
2 rows from player table:
Player_ID	Sponsor_name	Player_name	Gender	Residence	Occupation	Votes	Rank
1	Brandon—Souris	Jean Luc Bouché	M	Brandon	Locomotive Engineer	6055	2nd
2	Charleswood—St. James—Assiniboia	Fiona Shiells	F	Winnipeg	Ministerial Assistant	7190	3rd
*/


CREATE TABLE player_coach (
	"Player_ID" INTEGER, 
	"Coach_ID" INTEGER, 
	"Starting_year" INTEGER, 
	PRIMARY KEY ("Player_ID", "Coach_ID"), 
	FOREIGN KEY("Coach_ID") REFERENCES coach ("Coach_ID"), 
	FOREIGN KEY("Player_ID") REFERENCES player ("Player_ID")
)

/*
2 rows from player_coach table:
Player_ID	Coach_ID	Starting_year
1	1	2010
2	1	2011
*/
What is the name of the player with the largest number of votes?
SELECT Player_name FROM player ORDER BY Votes DESC LIMIT 1
[{'Player_name': 'Jim Maloway'}]