human
Schema:
Activity (actid, activity_name)
Participates_in (stuid, actid)
Faculty_Participates_in (FacID, actid)
Student (StuID, LName, Fname, Age, Sex, Major, Advisor, city_code)
Faculty (FacID, Lname, Fname, Rank, Sex, Phone, Room, Building)
Please answer with SQL.
assistant
Sure, I understand.
human
What activities do we have?
assistant
SELECT activity_name FROM Activity
human
Show the first and last name of all the faculty members who participated in some activity, together with the number of activities they participated in.
assistant
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
human
What is the first and last name of the student participating in the most activities?
assistant
SELECT T1.fname , T1.lname FROM Student AS T1 JOIN Participates_in AS T2 ON T1.StuID = T2.StuID GROUP BY T1.StuID ORDER BY count(*) DESC LIMIT 1
human
Show all the activity names and the number of faculty involved in each activity.
assistant
SELECT T1.activity_name , count(*) FROM Activity AS T1 JOIN Faculty_participates_in AS T2 ON T1.actID = T2.actID GROUP BY T1.actID
human
How many activities does Mark Giuliano participate in?
assistant
SELECT count(*) FROM Faculty AS T1 JOIN Faculty_participates_in AS T2 ON T1.facID = T2.facID WHERE T1.fname = 'Mark' AND T1.lname = 'Giuliano'
human
Show the names of all the activities Mark Giuliano participates in.
assistant
SELECT T3.activity_name FROM Faculty AS T1 JOIN Faculty_participates_in AS T2 ON T1.facID = T2.facID JOIN Activity AS T3 ON T3.actid = T2.actid WHERE T1.fname = "Mark" AND T1.lname = "Giuliano" ;