
CREATE TABLE country (
	"Country_id" INTEGER, 
	"Country_name" TEXT, 
	"Capital" TEXT, 
	"Official_native_language" TEXT, 
	PRIMARY KEY ("Country_id")
)


CREATE TABLE match_season (
	"Season" REAL, 
	"Player" TEXT, 
	"Position" TEXT, 
	"Country" INTEGER, 
	"Team" INTEGER, 
	"Draft_Pick_Number" INTEGER, 
	"Draft_Class" TEXT, 
	"College" TEXT, 
	PRIMARY KEY ("Season"), 
	FOREIGN KEY("Team") REFERENCES team ("Team_id"), 
	FOREIGN KEY("Country") REFERENCES country ("Country_id")
)


CREATE TABLE player (
	"Player_ID" INTEGER, 
	"Player" TEXT, 
	"Years_Played" TEXT, 
	"Total_WL" TEXT, 
	"Singles_WL" TEXT, 
	"Doubles_WL" TEXT, 
	"Team" INTEGER, 
	PRIMARY KEY ("Player_ID"), 
	FOREIGN KEY("Team") REFERENCES team ("Team_id")
)


CREATE TABLE team (
	"Team_id" INTEGER, 
	"Name" TEXT, 
	PRIMARY KEY ("Team_id")
)

CREATE TABLE country (
	"Country_id" INTEGER, 
	"Country_name" TEXT, 
	"Capital" TEXT, 
	"Official_native_language" TEXT, 
	PRIMARY KEY ("Country_id")
)

/*
2 rows from country table:
Country_id	Country_name	Capital	Official_native_language
1	Indonesia	Jakarta	Bahasa Indonesia
2	Iran	Tehran	Persian ( Arabic script )
*/


CREATE TABLE match_season (
	"Season" REAL, 
	"Player" TEXT, 
	"Position" TEXT, 
	"Country" INTEGER, 
	"Team" INTEGER, 
	"Draft_Pick_Number" INTEGER, 
	"Draft_Class" TEXT, 
	"College" TEXT, 
	PRIMARY KEY ("Season"), 
	FOREIGN KEY("Team") REFERENCES team ("Team_id"), 
	FOREIGN KEY("Country") REFERENCES country ("Country_id")
)

/*
2 rows from match_season table:
Season	Player	Position	Country	Team	Draft_Pick_Number	Draft_Class	College
1996.0	Steve Ralston Category:Articles with hCards	Midfielder	6	1	18	1996 MLS College Draft	Florida International
1997.0	Mike Duhaney Category:Articles with hCards	Defender	6	2	87	1996 MLS Inaugural Player Draft	UNLV
*/


CREATE TABLE player (
	"Player_ID" INTEGER, 
	"Player" TEXT, 
	"Years_Played" TEXT, 
	"Total_WL" TEXT, 
	"Singles_WL" TEXT, 
	"Doubles_WL" TEXT, 
	"Team" INTEGER, 
	PRIMARY KEY ("Player_ID"), 
	FOREIGN KEY("Team") REFERENCES team ("Team_id")
)

/*
2 rows from player table:
Player_ID	Player	Years_Played	Total_WL	Singles_WL	Doubles_WL	Team
1	Cho Soong-Jae (630)	1 (2011)	2–0	1–0	1–0	1
2	Chung Hong (717)	1 (2011)	0–0	0–0	0–0	1
*/


CREATE TABLE team (
	"Team_id" INTEGER, 
	"Name" TEXT, 
	PRIMARY KEY ("Team_id")
)

/*
2 rows from team table:
Team_id	Name
1	Columbus Crew
2	Evalyn Feil
*/
Show the season, the player, and the name of the team that players belong to.
SELECT T1.Season ,  T1.Player ,  T2.Name FROM match_season AS T1 JOIN team AS T2 ON T1.Team  =  T2.Team_id
[{'Season': 1996.0, 'Player': 'Steve Ralston Category:Articles with hCards', 'Name': 'Columbus Crew'}, {'Season': 1997.0, 'Player': 'Mike Duhaney Category:Articles with hCards', 'Name': 'Evalyn Feil'}, {'Season': 1998.0, 'Player': 'Ben Olsen Category:Articles with hCards', 'Name': 'Anais VonRueden'}, {'Season': 1999.0, 'Player': 'Jay Heaps Category:Articles with hCards', 'Name': 'Miami Fusion'}, {'Season': 2000.0, 'Player': 'Carlos Bocanegra Category:Articles with hCards', 'Name': 'Enrique Osinski'}, {'Season': 2001.0, 'Player': 'Rodrigo Faria Category:Articles with hCards', 'Name': 'Enrique Osinski'}, {'Season': 2002.0, 'Player': 'Kyle Martino Category:Articles with hCards', 'Name': 'Anais VonRueden'}, {'Season': 2003.0, 'Player': 'Damani Ralph Category:Articles with hCards', 'Name': 'Evalyn Feil'}, {'Season': 2004.0, 'Player': 'Clint Dempsey Category:Articles with hCards', 'Name': 'Anais VonRueden'}, {'Season': 2005.0, 'Player': 'Michael Parkhurst Category:Articles with hCards', 'Name': 'Miami Fusion'}, {'Season': 2006.0, 'Player': 'Jonathan Bornstein Category:Articles with hCards', 'Name': 'D.C. United'}, {'Season': 2007.0, 'Player': 'Maurice Edu Category:Articles with hCards', 'Name': 'Ryley Goldner'}, {'Season': 2008.0, 'Player': 'Sean Franklin Category:Articles with hCards', 'Name': 'Enrique Osinski'}, {'Season': 2009.0, 'Player': 'Omar Gonzalez Category:Articles with hCards', 'Name': 'Enrique Osinski'}, {'Season': 2010.0, 'Player': 'Andy Najar Category:Articles with hCards', 'Name': 'Enrique Osinski'}, {'Season': 2011.0, 'Player': 'C. J. Sapong Category:Articles with hCards', 'Name': 'Anais VonRueden'}]