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 ids for all the male faculty.
assistant
SELECT FacID FROM Faculty WHERE Sex = 'M'
human
Show ids for the faculty members who don't advise any student.
assistant
SELECT FacID FROM Faculty EXCEPT SELECT advisor FROM Student
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 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
Show the ids of all the faculty members who participate in an activity and advise a student.
assistant
SELECT FacID FROM Faculty_participates_in INTERSECT SELECT advisor FROM Student
human
Show the ids of students whose advisors are professors.
assistant
SELECT T2.StuID FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID = T2.advisor WHERE T1.rank = "Professor" ;