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 the faculty members who don't advise any student.
assistant
SELECT FacID FROM Faculty EXCEPT SELECT advisor FROM Student
human
Find the ids of the students who participate in Canoeing and Kayaking.
assistant
SELECT T1.stuid FROM participates_in AS T1 JOIN activity AS T2 ON T2.actid = T2.actid WHERE T2.activity_name = 'Canoeing' INTERSECT SELECT T1.stuid FROM participates_in AS T1 JOIN activity AS T2 ON T2.actid = T2.actid WHERE T2.activity_name = 'Kayaking'
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 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 the students who don't participate in any activity.
assistant
SELECT StuID FROM Student EXCEPT SELECT StuID FROM Participates_in
human
Show the ids for all the students who participate in an activity and are under 20.
assistant
SELECT StuID FROM Participates_in INTERSECT SELECT StuID FROM Student WHERE age < 20 ;