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
How many faculty do we have?
assistant
SELECT count(*) FROM Faculty
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 the first and last name of the faculty who has the most students?
assistant
SELECT T1.fname , T1.lname FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID = T2.advisor GROUP BY T1.FacID ORDER BY count(*) DESC LIMIT 1
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 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
Which faculty members advise two ore more students? Give me their faculty ids.
assistant
SELECT T1.FacID FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID = T2.advisor GROUP BY T1.FacID HAVING COUNT( * ) >= 2 ;