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 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 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
human
Find the first names of professors who are not playing Canoeing or Kayaking.
assistant
SELECT lname FROM faculty WHERE rank = 'Professor' EXCEPT SELECT DISTINCT T1.lname FROM Faculty AS T1 JOIN Faculty_participates_in AS T2 ON T1.facID = T2.facID JOIN activity AS T3 ON T2.actid = T2.actid WHERE T3.activity_name = 'Canoeing' OR T3.activity_name = 'Kayaking'
human
Find the first names of the faculty members who are playing Canoeing or Kayaking.
assistant
SELECT DISTINCT T1.lname FROM Faculty AS T1 JOIN Faculty_participates_in AS T2 ON T1.facID = T2.facID JOIN activity AS T3 ON T2.actid = T2.actid WHERE T3.activity_name = 'Canoeing' OR T3.activity_name = 'Kayaking'
human
Find the first names of the faculty members who participate in Canoeing and Kayaking.
assistant
SELECT T1.lname FROM Faculty AS T1 JOIN Faculty_participates_in AS T2 ON T1.facID = T2.facID JOIN activity AS T3 ON T2.actid = T2.actid WHERE T3.activity_name = 'Canoeing' INTERSECT SELECT T1.lname FROM Faculty AS T1 JOIN Faculty_participates_in AS T2 ON T1.facID = T2.facID JOIN activity AS T3 ON T2.actid = T2.actid WHERE T3.activity_name = 'Kayaking'
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" ;