Database schema:

CREATE TABLE "AwardsCoaches" (
	"coachID" TEXT -- example: ['patrile01c', 'irvindi01c'],
	award TEXT -- example: ['Second Team All-Star', 'First Team All-Star'],
	year INTEGER -- example: ['1930', '1931'],
	"lgID" TEXT -- example: ['NHL', 'WHA'],
	note TEXT, 
	CONSTRAINT fk_awardscoaches_coaches FOREIGN KEY("coachID") REFERENCES "Coaches" ("coachID")
)


CREATE TABLE "AwardsMisc" (
	name TEXT NOT NULL -- example: ['1960 U.S. Olympic Hockey Team', "1998 U.S. Olympic Women's Hockey Team"],
	"ID" TEXT -- example: ['arboual01', 'delveal01'],
	award TEXT -- example: ['Patrick'],
	year INTEGER -- example: ['2001', '1998'],
	"lgID" TEXT -- example: ['NHL'],
	note TEXT -- example: ['posthumous'],
	PRIMARY KEY (name)
)


CREATE TABLE "AwardsPlayers" (
	"playerID" TEXT NOT NULL -- example: ['alfreda01', 'abelsi01'],
	award TEXT NOT NULL -- example: ['Hart', 'First Team All-Star'],
	year INTEGER NOT NULL -- example: ['1948', '1949'],
	"lgID" TEXT -- example: ['NHL', 'WHA'],
	note TEXT -- example: ['shared', 'tie'],
	pos TEXT -- example: ['LW', 'C'],
	PRIMARY KEY ("playerID", award, year), 
	CONSTRAINT fk_awardsplayers_master FOREIGN KEY("playerID") REFERENCES "Master" ("playerID")
)


CREATE TABLE "Coaches" (
	"coachID" TEXT NOT NULL -- example: ['abelsi01c', 'adamsja01c'],
	year INTEGER NOT NULL -- example: ['1952', '1953'],
	"tmID" TEXT NOT NULL -- example: ['DET', 'CHI'],
	"lgID" TEXT -- example: ['NHL', 'WHA'],
	stint INTEGER NOT NULL -- example: ['2', '1'],
	notes TEXT -- example: ['interim', 'co-coach with Barry Smith'],
	g INTEGER -- example: ['33', '70'],
	w INTEGER -- example: ['27', '12'],
	l INTEGER -- example: ['51', '28'],
	t INTEGER -- example: ['15', '7'],
	postg TEXT -- example: ['7', '4'],
	postw TEXT -- example: ['0', '3'],
	postl TEXT -- example: ['5', '4'],
	postt TEXT -- example: ['0', '1'],
	PRIMARY KEY ("coachID", year, "tmID", stint), 
	CONSTRAINT fk_coaches_teams FOREIGN KEY(year, "tmID") REFERENCES "Teams" (year, "tmID")
)


CREATE TABLE "CombinedShutouts" (
	year INTEGER -- example: ['1941', '1929'],
	month INTEGER -- example: ['2', '3'],
	date INTEGER -- example: ['15', '14'],
	"tmID" TEXT -- example: ['MTL', 'TOR'],
	"oppID" TEXT -- example: ['BOS', 'NYA'],
	"R/P" TEXT -- example: ['R', 'P'],
	"IDgoalie1" TEXT -- example: ['chabolo01', 'bibeapa01'],
	"IDgoalie2" TEXT -- example: ['grantbe01', 'gardibe01'],
	CONSTRAINT fk_combinedshutouts_master FOREIGN KEY("IDgoalie1") REFERENCES "Master" ("playerID"),
	CONSTRAINT fk_combinedshutouts_master FOREIGN KEY("IDgoalie2") REFERENCES "Master" ("playerID")
)


CREATE TABLE "Goalies" (
	"playerID" TEXT NOT NULL -- example: ['abrahch01', 'abbotge01'],
	year INTEGER NOT NULL -- example: ['1943', '1974'],
	stint INTEGER NOT NULL -- example: ['2', '1'],
	"tmID" TEXT -- example: ['BOS', 'NEW'],
	"lgID" TEXT -- example: ['NHL', 'WHA'],
	"GP" TEXT -- example: ['1', '16'],
	"Min" TEXT -- example: ['870', '60'],
	"W" TEXT -- example: ['0', '8'],
	"L" TEXT -- example: ['1', '6'],
	"T/OL" TEXT -- example: ['0', '1'],
	"ENG" TEXT -- example: ['0', '1'],
	"SHO" TEXT -- example: ['0', '1'],
	"GA" TEXT -- example: ['7', '47'],
	"SA" TEXT -- example: ['1221', '504'],
	"PostGP" TEXT -- example: ['2', '1'],
	"PostMin" TEXT -- example: ['1', '90'],
	"PostW" TEXT -- example: ['0', '6'],
	"PostL" TEXT -- example: ['0', '1'],
	"PostT" TEXT -- example: ['0', '1'],
	"PostENG" TEXT -- example: ['0', '1'],
	"PostSHO" TEXT -- example: ['0', '1'],
	"PostGA" TEXT -- example: ['0', '5'],
	"PostSA" TEXT -- example: ['0', '51'],
	PRIMARY KEY ("playerID", year, stint), 
	CONSTRAINT fk_goalies_teams FOREIGN KEY(year, "tmID") REFERENCES "Teams" (year, "tmID"),
	CONSTRAINT fk_goalies_master FOREIGN KEY("playerID") REFERENCES "Master" ("playerID")
)


CREATE TABLE "GoaliesSC" (
	"playerID" TEXT NOT NULL -- example: ['benedcl01', 'clancki01'],
	year INTEGER NOT NULL -- example: ['1914', '1919'],
	"tmID" TEXT -- example: ['OT1', 'OTS'],
	"lgID" TEXT -- example: ['NHL', 'NHA'],
	"GP" INTEGER -- example: ['5', '3'],
	"Min" INTEGER -- example: ['300', '180'],
	"W" INTEGER -- example: ['0', '3'],
	"L" INTEGER -- example: ['2', '3'],
	"T" INTEGER -- example: ['0', '1'],
	"SHO" INTEGER -- example: ['0', '1'],
	"GA" INTEGER -- example: ['26', '11'],
	PRIMARY KEY ("playerID", year), 
	CONSTRAINT fk_goaliessc_teams FOREIGN KEY(year, "tmID") REFERENCES "Teams" (year, "tmID"),
	CONSTRAINT fk_goaliessc_master FOREIGN KEY("playerID") REFERENCES "Master" ("playerID")
)


CREATE TABLE "GoaliesShootout" (
	"playerID" TEXT -- example: ['aebisda01', 'andercr01'],
	year INTEGER -- example: ['2005', '2006'],
	stint INTEGER -- example: ['2', '1'],
	"tmID" TEXT -- example: ['COL', 'MTL'],
	"W" INTEGER -- example: ['2', '0'],
	"L" INTEGER -- example: ['2', '1'],
	"SA" INTEGER -- example: ['10', '18'],
	"GA" INTEGER -- example: ['2', '6'],
	CONSTRAINT fk_goaliesshootout_teams FOREIGN KEY(year, "tmID") REFERENCES "Teams" (year, "tmID"),
	CONSTRAINT fk_goaliesshootout_master FOREIGN KEY("playerID") REFERENCES "Master" ("playerID")
)


CREATE TABLE "HOF" (
	year INTEGER -- example: ['1969', '1960'],
	"hofID" TEXT NOT NULL -- example: ['abelsi01h', 'adamsch01h'],
	name TEXT -- example: ['Sid Abel', 'Charles Adams'],
	category TEXT -- example: ['Player', 'Builder'],
	PRIMARY KEY ("hofID")
)


CREATE TABLE "Master" (
	"playerID" TEXT -- example: ['abbeybr01', 'aaltoan01'],
	"coachID" TEXT -- example: ['abelsi01c', 'adamsja01c'],
	"hofID" TEXT -- example: ['abelsi01h', 'adamsch01h'],
	"firstName" TEXT -- example: ['Bruce', 'Antti'],
	"lastName" TEXT NOT NULL -- example: ['Aalto', 'Abbey'],
	"nameNote" TEXT -- example: ['also listed as Bourdginon', 'also known as Bellehumeur'],
	"nameGiven" TEXT -- example: ['Bruce', 'Antti'],
	"nameNick" TEXT -- example: ['Taffy', 'Preacher'],
	height TEXT -- example: ['67', '73'],
	weight TEXT -- example: ['210', '185'],
	"shootCatch" TEXT -- example: ['R', 'L'],
	"legendsID" TEXT -- example: ['18411', '14862'],
	"ihdbID" TEXT -- example: ['11918', '5928'],
	"hrefID" TEXT -- example: ['abbeybr01', 'aaltoan01'],
	"firstNHL" TEXT -- example: ['1997', '1943'],
	"lastNHL" TEXT -- example: ['1943', '2000'],
	"firstWHA" TEXT -- example: ['1975', '1976'],
	"lastWHA" TEXT -- example: ['1975', '1977'],
	pos TEXT -- example: ['D', 'C'],
	"birthYear" TEXT -- example: ['1975', '1951'],
	"birthMon" TEXT -- example: ['8', '3'],
	"birthDay" TEXT -- example: ['18', '4'],
	"birthCountry" TEXT -- example: ['Finland', 'Canada'],
	"birthState" TEXT -- example: ['MB', 'ON'],
	"birthCity" TEXT -- example: ['Lappeenranta', 'Toronto'],
	"deathYear" TEXT -- example: ['1964', '2000'],
	"deathMon" TEXT -- example: ['2', '8'],
	"deathDay" TEXT -- example: ['7', '1'],
	"deathCountry" TEXT -- example: ['USA', 'Canada'],
	"deathState" TEXT -- example: ['AB', 'MI'],
	"deathCity" TEXT -- example: ['Sault Ste. Marie', 'Farmington'],
	CONSTRAINT fk_master_coaches FOREIGN KEY("coachID") REFERENCES "Coaches" ("coachID")
)


CREATE TABLE "Scoring" (
	"playerID" TEXT -- example: ['abbeybr01', 'aaltoan01'],
	year INTEGER -- example: ['1997', '1998'],
	stint INTEGER -- example: ['2', '1'],
	"tmID" TEXT -- example: ['CIN', 'ANA'],
	"lgID" TEXT -- example: ['NHL', 'WHA'],
	pos TEXT -- example: ['D', 'C'],
	"GP" INTEGER -- example: ['73', '3'],
	"G" INTEGER -- example: ['0', '3'],
	"A" INTEGER -- example: ['0', '5'],
	"Pts" INTEGER -- example: ['0', '8'],
	"PIM" INTEGER -- example: ['0', '24'],
	"+/-" TEXT -- example: ['-1', '-12'],
	"PPG" TEXT -- example: ['0', '2'],
	"PPA" TEXT -- example: ['0', '1'],
	"SHG" TEXT -- example: ['0', '2'],
	"SHA" TEXT -- example: ['0', '1'],
	"GWG" TEXT -- example: ['0', '1'],
	"GTG" TEXT -- example: ['0', '1'],
	"SOG" TEXT -- example: ['1', '61'],
	"PostGP" TEXT -- example: ['10', '4'],
	"PostG" TEXT -- example: ['0', '2'],
	"PostA" TEXT -- example: ['0', '1'],
	"PostPts" TEXT -- example: ['0', '3'],
	"PostPIM" TEXT -- example: ['2', '0'],
	"Post+/-" TEXT -- example: ['0', '2'],
	"PostPPG" TEXT -- example: ['0', '1'],
	"PostPPA" TEXT -- example: ['0', '1'],
	"PostSHG" TEXT -- example: ['0', '1'],
	"PostSHA" TEXT -- example: ['0', '1'],
	"PostGWG" TEXT -- example: ['0', '1'],
	"PostSOG" TEXT -- example: ['0', '11'],
	CONSTRAINT fk_scoring_teams FOREIGN KEY(year, "tmID") REFERENCES "Teams" (year, "tmID"),
	CONSTRAINT fk_scoring_master FOREIGN KEY("playerID") REFERENCES "Master" ("playerID")
)


CREATE TABLE "ScoringSC" (
	"playerID" TEXT -- example: ['adamsbi01', 'adamsja01'],
	year INTEGER -- example: ['1920', '1921'],
	"tmID" TEXT -- example: ['CAT', 'VML'],
	"lgID" TEXT -- example: ['WCHL', 'PCHA'],
	pos TEXT -- example: ['R', 'C'],
	"GP" INTEGER -- example: ['5', '4'],
	"G" INTEGER -- example: ['0', '2'],
	"A" INTEGER -- example: ['0', '1'],
	"Pts" INTEGER -- example: ['0', '3'],
	"PIM" INTEGER -- example: ['0', '6'],
	CONSTRAINT fk_scoringsc_teams FOREIGN KEY(year, "tmID") REFERENCES "Teams" (year, "tmID"),
	CONSTRAINT fk_scoringsc_master FOREIGN KEY("playerID") REFERENCES "Master" ("playerID")
)


CREATE TABLE "ScoringShootout" (
	"playerID" TEXT -- example: ['adamske01', 'afanadm01'],
	year INTEGER -- example: ['2005', '2006'],
	stint INTEGER -- example: ['2', '1'],
	"tmID" TEXT -- example: ['PHO', 'TBL'],
	"S" INTEGER -- example: ['2', '1'],
	"G" INTEGER -- example: ['0', '1'],
	"GDG" INTEGER -- example: ['0', '1'],
	CONSTRAINT fk_scoringshootout_teams FOREIGN KEY(year, "tmID") REFERENCES "Teams" (year, "tmID"),
	CONSTRAINT fk_scoringshootout_master FOREIGN KEY("playerID") REFERENCES "Master" ("playerID")
)


CREATE TABLE "ScoringSup" (
	"playerID" TEXT -- example: ['adamsgr01', 'actonke01'],
	year INTEGER -- example: ['1988', '1989'],
	"PPA" TEXT -- example: ['5', '1'],
	"SHA" TEXT -- example: ['2', '1'],
	CONSTRAINT fk_scoringsup_master FOREIGN KEY("playerID") REFERENCES "Master" ("playerID")
)


CREATE TABLE "SeriesPost" (
	year INTEGER -- example: ['1913', '1912'],
	round TEXT -- example: ['SCF', 'F'],
	series TEXT -- example: ['A', 'B'],
	"tmIDWinner" TEXT -- example: ['VA1', 'TBS'],
	"lgIDWinner" TEXT -- example: ['NHA', 'PCHA'],
	"tmIDLoser" TEXT -- example: ['QU1', 'MOC'],
	"lgIDLoser" TEXT -- example: ['PCHA', 'NHA'],
	"W" INTEGER -- example: ['2', '1'],
	"L" INTEGER -- example: ['0', '1'],
	"T" INTEGER -- example: ['0', '1'],
	"GoalsWinner" INTEGER -- example: ['6', '16'],
	"GoalsLoser" INTEGER -- example: ['2', '12'],
	note TEXT -- example: ['EX', 'TG'],
	CONSTRAINT fk_seriespost_teams FOREIGN KEY(year, "tmIDWinner") REFERENCES "Teams" (year, "tmID"),
	CONSTRAINT fk_seriespost_teams FOREIGN KEY(year, "tmIDLoser") REFERENCES "Teams" (year, "tmID")
)


CREATE TABLE "TeamSplits" (
	year INTEGER NOT NULL -- example: ['1910', '1909'],
	"lgID" TEXT -- example: ['PCHA', 'NHA'],
	"tmID" TEXT NOT NULL -- example: ['COB', 'HAI'],
	"hW" INTEGER -- example: ['2', '3'],
	"hL" INTEGER -- example: ['4', '3'],
	"hT" INTEGER -- example: ['0', '1'],
	"hOTL" TEXT -- example: ['2', '3'],
	"rW" INTEGER -- example: ['2', '1'],
	"rL" INTEGER -- example: ['5', '4'],
	"rT" INTEGER -- example: ['0', '1'],
	"rOTL" TEXT -- example: ['5', '1'],
	"SepW" TEXT -- example: ['1'],
	"SepL" TEXT -- example: ['1'],
	"SepT" TEXT, 
	"SepOL" TEXT -- example: ['0'],
	"OctW" TEXT -- example: ['0', '1'],
	"OctL" TEXT -- example: ['0', '1'],
	"OctT" TEXT -- example: ['0', '1'],
	"OctOL" TEXT -- example: ['0', '1'],
	"NovW" TEXT -- example: ['5', '1'],
	"NovL" TEXT -- example: ['1', '4'],
	"NovT" TEXT -- example: ['0', '1'],
	"NovOL" TEXT -- example: ['0', '2'],
	"DecW" TEXT -- example: ['0', '1'],
	"DecL" TEXT -- example: ['0', '1'],
	"DecT" TEXT -- example: ['0', '1'],
	"DecOL" TEXT -- example: ['0', '2'],
	"JanW" INTEGER -- example: ['0', '1'],
	"JanL" INTEGER -- example: ['1', '3'],
	"JanT" INTEGER -- example: ['0', '1'],
	"JanOL" TEXT -- example: ['0', '1'],
	"FebW" INTEGER -- example: ['2', '1'],
	"FebL" INTEGER -- example: ['5', '3'],
	"FebT" INTEGER -- example: ['0', '1'],
	"FebOL" TEXT -- example: ['0', '1'],
	"MarW" TEXT -- example: ['1', '3'],
	"MarL" TEXT -- example: ['0', '4'],
	"MarT" TEXT -- example: ['0', '1'],
	"MarOL" TEXT -- example: ['0', '1'],
	"AprW" TEXT -- example: ['0', '1'],
	"AprL" TEXT -- example: ['0', '2'],
	"AprT" TEXT -- example: ['0', '1'],
	"AprOL" TEXT -- example: ['0', '1'],
	PRIMARY KEY (year, "tmID"), 
	CONSTRAINT fk_teamsplits_teams FOREIGN KEY(year, "tmID") REFERENCES "Teams" (year, "tmID")
)


CREATE TABLE "TeamVsTeam" (
	year INTEGER NOT NULL -- example: ['1910', '1909'],
	"lgID" TEXT -- example: ['PCHA', 'NHA'],
	"tmID" TEXT NOT NULL -- example: ['COB', 'HAI'],
	"oppID" TEXT NOT NULL -- example: ['LES', 'HAI'],
	"W" INTEGER -- example: ['2', '1'],
	"L" INTEGER -- example: ['0', '1'],
	"T" INTEGER -- example: ['0', '1'],
	"OTL" TEXT -- example: ['0', '1'],
	PRIMARY KEY (year, "tmID", "oppID"), 
	CONSTRAINT fk_teamvsteam_teams FOREIGN KEY(year, "tmID") REFERENCES "Teams" (year, "tmID"),
	CONSTRAINT fk_teamvsteam_teams FOREIGN KEY("oppID", year) REFERENCES "Teams" ("tmID", year)
)


CREATE TABLE "Teams" (
	year INTEGER NOT NULL -- example: ['1910', '1909'],
	"lgID" TEXT -- example: ['PCHA', 'NHA'],
	"tmID" TEXT NOT NULL -- example: ['COB', 'HAI'],
	"franchID" TEXT -- example: ['BKN', 'MTL'],
	"confID" TEXT -- example: ['WA', 'CC'],
	"divID" TEXT -- example: ['AM', 'CA'],
	rank INTEGER -- example: ['5', '4'],
	playoff TEXT -- example: ['LCS', 'WCS'],
	"G" INTEGER -- example: ['16', '12'],
	"W" INTEGER -- example: ['2', '4'],
	"L" INTEGER -- example: ['8', '10'],
	"T" INTEGER -- example: ['0', '1'],
	"OTL" TEXT -- example: ['4', '3'],
	"Pts" INTEGER -- example: ['8', '4'],
	"SoW" TEXT -- example: ['5', '3'],
	"SoL" TEXT -- example: ['7', '5'],
	"GF" INTEGER -- example: ['79', '77'],
	"GA" INTEGER -- example: ['104', '83'],
	name TEXT -- example: ['Haileybury Hockey Club', 'Cobalt Silver Kings'],
	"PIM" TEXT -- example: ['336', '27'],
	"BenchMinor" TEXT -- example: ['14', '12'],
	"PPG" TEXT -- example: ['51', '28'],
	"PPC" TEXT -- example: ['279', '220'],
	"SHA" TEXT -- example: ['2', '4'],
	"PKG" TEXT -- example: ['45', '43'],
	"PKC" TEXT -- example: ['242', '301'],
	"SHF" TEXT -- example: ['8', '3'],
	PRIMARY KEY (year, "tmID")
)


CREATE TABLE "TeamsHalf" (
	year INTEGER NOT NULL -- example: ['1916', '1917'],
	"lgID" TEXT -- example: ['NHL', 'NHA'],
	"tmID" TEXT NOT NULL -- example: ['MOW', 'MOC'],
	half INTEGER NOT NULL -- example: ['2', '1'],
	rank INTEGER -- example: ['1', '3'],
	"G" INTEGER -- example: ['10', '4'],
	"W" INTEGER -- example: ['7', '3'],
	"L" INTEGER -- example: ['7', '3'],
	"T" INTEGER -- example: ['0'],
	"GF" INTEGER -- example: ['58', '31'],
	"GA" INTEGER -- example: ['42', '38'],
	PRIMARY KEY (year, "tmID", half), 
	CONSTRAINT fk_teamshalf_teams FOREIGN KEY("tmID", year) REFERENCES "Teams" ("tmID", year)
)


CREATE TABLE "TeamsPost" (
	year INTEGER NOT NULL -- example: ['1913', '1914'],
	"lgID" TEXT -- example: ['NHL', 'NHA'],
	"tmID" TEXT NOT NULL -- example: ['TBS', 'MOC'],
	"G" INTEGER -- example: ['2', '5'],
	"W" INTEGER -- example: ['0', '1'],
	"L" INTEGER -- example: ['0', '1'],
	"T" INTEGER -- example: ['0', '1'],
	"GF" INTEGER -- example: ['2', '6'],
	"GA" INTEGER -- example: ['2', '6'],
	"PIM" TEXT -- example: ['59', '68'],
	"BenchMinor" TEXT -- example: ['0', '6'],
	"PPG" TEXT -- example: ['5', '1'],
	"PPC" TEXT -- example: ['39', '27'],
	"SHA" TEXT -- example: ['0', '1'],
	"PKG" TEXT -- example: ['8', '3'],
	"PKC" TEXT -- example: ['45', '55'],
	"SHF" TEXT -- example: ['0', '1'],
	PRIMARY KEY (year, "tmID"), 
	CONSTRAINT fk_teamspost_teams FOREIGN KEY(year, "tmID") REFERENCES "Teams" (year, "tmID")
)


CREATE TABLE "TeamsSC" (
	year INTEGER NOT NULL -- example: ['1913', '1912'],
	"lgID" TEXT -- example: ['PCHA', 'NHA'],
	"tmID" TEXT NOT NULL -- example: ['VA1', 'QU1'],
	"G" INTEGER -- example: ['5', '3'],
	"W" INTEGER -- example: ['2', '1'],
	"L" INTEGER -- example: ['2', '1'],
	"T" INTEGER -- example: ['0', '1'],
	"GF" INTEGER -- example: ['16', '12'],
	"GA" INTEGER -- example: ['16', '12'],
	"PIM" TEXT -- example: ['24', '20'],
	PRIMARY KEY (year, "tmID"), 
	CONSTRAINT fk_teamssc_teams FOREIGN KEY(year, "tmID") REFERENCES "Teams" (year, "tmID")
)


CREATE TABLE abbrev (
	"Type" TEXT NOT NULL -- example: ['Division', 'Conference'],
	"Code" TEXT NOT NULL -- example: ['CC', 'EC'],
	"Fullname" TEXT -- example: ['Eastern Conference', 'Campbell Conference'],
	PRIMARY KEY ("Type", "Code")
)

Question: For all players who becomes coach after retirement, state the given name of coach and which teams and years did they coach?

SQL:
SELECT DISTINCT T2.nameGiven, T3.name, T3.year FROM Coaches AS T1 INNER JOIN Master AS T2 ON T2.coachID = T1.coachID INNER JOIN Teams AS T3 ON T1.lgID = T3.lgID WHERE T2.playerID IS NOT NULL AND T2.coachID IS NOT NULL