
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
*/
What is the first and last name of the faculty members who participated in at least one activity? For each of them, also show the number of activities they participated in.
SELECT  T1.fname ,  T1.lname ,  count(*) ,  T1.FacID FROM Faculty AS T1 JOIN Faculty_participates_in AS T2 ON T1.facID  =  T2.facID GROUP BY T1.FacID
[{'Fname': 'Mark', 'Lname': 'Giuliano', 'count(*)': 3, 'FacID': 1082}, {'Fname': 'Michael', 'Lname': 'Goodrich', 'count(*)': 3, 'FacID': 1121}, {'Fname': 'Daniel', 'Lname': 'Naiman', 'count(*)': 3, 'FacID': 1177}, {'Fname': 'Stacey', 'Lname': 'Jones', 'count(*)': 3, 'FacID': 1193}, {'Fname': 'David', 'Lname': 'Yarowsky', 'count(*)': 3, 'FacID': 2192}, {'Fname': 'Alan', 'Lname': 'Goldman', 'count(*)': 2, 'FacID': 2881}, {'Fname': 'Scott', 'Lname': 'Smith', 'count(*)': 3, 'FacID': 3457}, {'Fname': 'Joanne', 'Lname': 'Houlahan', 'count(*)': 2, 'FacID': 4230}, {'Fname': 'Luigi', 'Lname': 'Burzio', 'count(*)': 2, 'FacID': 4432}, {'Fname': 'Robert', 'Lname': 'Frank', 'count(*)': 1, 'FacID': 5718}, {'Fname': 'Louis', 'Lname': 'Beach', 'count(*)': 2, 'FacID': 6112}, {'Fname': 'Cheng', 'Lname': 'Cheng', 'count(*)': 2, 'FacID': 6182}, {'Fname': 'Russell', 'Lname': 'Taylor', 'count(*)': 2, 'FacID': 7723}, {'Fname': 'Ellie', 'Lname': 'Angelopoulou', 'count(*)': 1, 'FacID': 8114}, {'Fname': 'Lawrence', 'Lname': 'Wolff', 'count(*)': 2, 'FacID': 8721}, {'Fname': 'Steven', 'Lname': 'Salzberg', 'count(*)': 2, 'FacID': 8741}, {'Fname': 'Yair', 'Lname': 'Amir', 'count(*)': 3, 'FacID': 8918}, {'Fname': 'Leslie', 'Lname': 'Hall', 'count(*)': 1, 'FacID': 9922}]