
CREATE TABLE college (
	"College_ID" INTEGER, 
	"Name" TEXT, 
	"Leader_Name" TEXT, 
	"College_Location" TEXT, 
	PRIMARY KEY ("College_ID")
)


CREATE TABLE member (
	"Member_ID" INTEGER, 
	"Name" TEXT, 
	"Country" TEXT, 
	"College_ID" INTEGER, 
	PRIMARY KEY ("Member_ID"), 
	FOREIGN KEY("College_ID") REFERENCES college ("College_ID")
)


CREATE TABLE round (
	"Round_ID" INTEGER, 
	"Member_ID" INTEGER, 
	"Decoration_Theme" TEXT, 
	"Rank_in_Round" INTEGER, 
	PRIMARY KEY ("Member_ID", "Round_ID"), 
	FOREIGN KEY("Member_ID") REFERENCES member ("Member_ID")
)

CREATE TABLE college (
	"College_ID" INTEGER, 
	"Name" TEXT, 
	"Leader_Name" TEXT, 
	"College_Location" TEXT, 
	PRIMARY KEY ("College_ID")
)

/*
2 rows from college table:
College_ID	Name	Leader_Name	College_Location
1	Saskatchewan School	Ousame Tounkara	Ottawa
2	B.C. School	Ryan Thelwell	Minnesota
*/


CREATE TABLE member (
	"Member_ID" INTEGER, 
	"Name" TEXT, 
	"Country" TEXT, 
	"College_ID" INTEGER, 
	PRIMARY KEY ("Member_ID"), 
	FOREIGN KEY("College_ID") REFERENCES college ("College_ID")
)

/*
2 rows from member table:
Member_ID	Name	Country	College_ID
1	Jack Nicklaus	United States	1
2	Billy Casper	United States	1
*/


CREATE TABLE round (
	"Round_ID" INTEGER, 
	"Member_ID" INTEGER, 
	"Decoration_Theme" TEXT, 
	"Rank_in_Round" INTEGER, 
	PRIMARY KEY ("Member_ID", "Round_ID"), 
	FOREIGN KEY("Member_ID") REFERENCES member ("Member_ID")
)

/*
2 rows from round table:
Round_ID	Member_ID	Decoration_Theme	Rank_in_Round
1	1	Walk on the Moon	1
1	2	Soft Dream	2
*/
Show the names of members that have a rank in round higher than 3.
SELECT T1.Name FROM member AS T1 JOIN round AS T2 ON T1.Member_ID  =  T2.Member_ID WHERE T2.Rank_in_Round  >  3
[{'Name': 'Tom Weiskopf'}]