
CREATE TABLE "Activity" (
	actid INTEGER, 
	activity_name VARCHAR(25), 
	PRIMARY KEY (actid)
)


CREATE TABLE "Faculty" (
	"FacID" INTEGER, 
	"Lname" VARCHAR(15), 
	"Fname" VARCHAR(15), 
	"Rank" VARCHAR(15), 
	"Sex" VARCHAR(1), 
	"Phone" INTEGER, 
	"Room" VARCHAR(5), 
	"Building" VARCHAR(13), 
	PRIMARY KEY ("FacID")
)


CREATE TABLE "Faculty_Participates_in" (
	"FacID" INTEGER, 
	actid INTEGER, 
	FOREIGN KEY("FacID") REFERENCES "Faculty" ("FacID"), 
	FOREIGN KEY(actid) REFERENCES "Activity" (actid)
)


CREATE TABLE "Participates_in" (
	stuid INTEGER, 
	actid INTEGER, 
	FOREIGN KEY(stuid) REFERENCES "Student" ("StuID"), 
	FOREIGN KEY(actid) REFERENCES "Activity" (actid)
)


CREATE TABLE "Student" (
	"StuID" INTEGER, 
	"LName" VARCHAR(12), 
	"Fname" VARCHAR(12), 
	"Age" INTEGER, 
	"Sex" VARCHAR(1), 
	"Major" INTEGER, 
	"Advisor" INTEGER, 
	city_code VARCHAR(3), 
	PRIMARY KEY ("StuID")
)

CREATE TABLE "Activity" (
	actid INTEGER, 
	activity_name VARCHAR(25), 
	PRIMARY KEY (actid)
)

/*
2 rows from Activity table:
actid	activity_name
770	Mountain Climbing
771	Canoeing
*/


CREATE TABLE "Faculty" (
	"FacID" INTEGER, 
	"Lname" VARCHAR(15), 
	"Fname" VARCHAR(15), 
	"Rank" VARCHAR(15), 
	"Sex" VARCHAR(1), 
	"Phone" INTEGER, 
	"Room" VARCHAR(5), 
	"Building" VARCHAR(13), 
	PRIMARY KEY ("FacID")
)

/*
2 rows from Faculty table:
FacID	Lname	Fname	Rank	Sex	Phone	Room	Building
1082	Giuliano	Mark	Instructor	M	2424	224	NEB
1121	Goodrich	Michael	Professor	M	3593	219	NEB
*/


CREATE TABLE "Faculty_Participates_in" (
	"FacID" INTEGER, 
	actid INTEGER, 
	FOREIGN KEY("FacID") REFERENCES "Faculty" ("FacID"), 
	FOREIGN KEY(actid) REFERENCES "Activity" (actid)
)

/*
2 rows from Faculty_Participates_in table:
FacID	actid
1082	784
1082	785
*/


CREATE TABLE "Participates_in" (
	stuid INTEGER, 
	actid INTEGER, 
	FOREIGN KEY(stuid) REFERENCES "Student" ("StuID"), 
	FOREIGN KEY(actid) REFERENCES "Activity" (actid)
)

/*
2 rows from Participates_in table:
stuid	actid
1001	770
1001	771
*/


CREATE TABLE "Student" (
	"StuID" INTEGER, 
	"LName" VARCHAR(12), 
	"Fname" VARCHAR(12), 
	"Age" INTEGER, 
	"Sex" VARCHAR(1), 
	"Major" INTEGER, 
	"Advisor" INTEGER, 
	city_code VARCHAR(3), 
	PRIMARY KEY ("StuID")
)

/*
2 rows from Student table:
StuID	LName	Fname	Age	Sex	Major	Advisor	city_code
1001	Smith	Linda	18	F	600	1121	BAL
1002	Kim	Tracy	19	F	600	7712	HKG
*/
Which students participate in both Canoeing and Kayaking as their activities? Tell me their student ids.
SELECT T1.stuid FROM participates_in AS T1 JOIN activity AS T2 ON T2.actid  =  T2.actid WHERE T2.activity_name  =  'Canoeing' INTERSECT SELECT T1.stuid FROM participates_in AS T1 JOIN activity AS T2 ON T2.actid  =  T2.actid WHERE T2.activity_name  =  'Kayaking'
[{'stuid': 1001}, {'stuid': 1002}, {'stuid': 1003}, {'stuid': 1004}, {'stuid': 1005}, {'stuid': 1006}, {'stuid': 1007}, {'stuid': 1008}, {'stuid': 1009}, {'stuid': 1010}, {'stuid': 1011}, {'stuid': 1012}, {'stuid': 1014}, {'stuid': 1015}, {'stuid': 1016}, {'stuid': 1017}, {'stuid': 1018}, {'stuid': 1019}, {'stuid': 1020}, {'stuid': 1021}, {'stuid': 1022}, {'stuid': 1023}, {'stuid': 1024}, {'stuid': 1025}, {'stuid': 1028}, {'stuid': 1029}, {'stuid': 1030}, {'stuid': 1033}, {'stuid': 1034}, {'stuid': 1035}]