
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 players and the years played.
SELECT Player ,  Years_Played FROM player
[{'Player': 'Cho Soong-Jae (630)', 'Years_Played': '1 (2011)'}, {'Player': 'Chung Hong (717)', 'Years_Played': '1 (2011)'}, {'Player': 'Im Kyu-tae (492)', 'Years_Played': '8 (2003–2005, 2007–2011)'}, {'Player': 'Jeong Suk-Young (793)', 'Years_Played': '2 (2010–2011)'}, {'Player': 'Kim Hyun-Joon (908)', 'Years_Played': '2 (2010–2011)'}, {'Player': 'Kim Young-Jun (474)', 'Years_Played': '4 (2003–2004, 2010–2011)'}, {'Player': 'Lim Yong-Kyu (288)', 'Years_Played': '3 (2009–2011)'}, {'Player': 'Seol Jae-Min (none)', 'Years_Played': '2 (2010-2011)'}, {'Player': 'An Jae-Sung', 'Years_Played': '3 (2005, 2007–2008)'}, {'Player': 'Bae Nam-Ju', 'Years_Played': '2 (1988, 1990)'}]