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
Show the faculty id of each faculty member, along with the number of students he or she advises.
assistant
SELECT T1.FacID , count(*) FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID = T2.advisor GROUP BY T1.FacID
human
How many faculty members participate in an activity?
assistant
SELECT count(DISTINCT FacID) FROM Faculty_participates_in
human
Show the ids for all the faculty members who have at least 2 students.
assistant
SELECT T1.FacID FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID = T2.advisor GROUP BY T1.FacID HAVING count(*) >= 2
human
Show the ids of the faculty who don't participate in any activity.
assistant
SELECT FacID FROM Faculty EXCEPT SELECT FacID FROM Faculty_participates_in
human
Show ids for the faculty members who don't advise any student.
assistant
SELECT FacID FROM Faculty EXCEPT SELECT advisor FROM Student
human
What are ids of the faculty members who not only participate in an activity but also advise a student.
assistant
SELECT FacID FROM Faculty_participates_in INTERSECT SELECT advisor FROM Student ;