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 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
What is the name of the activity with the most students?
assistant
SELECT T1.activity_name FROM Activity AS T1 JOIN Participates_in AS T2 ON T1.actID = T2.actID GROUP BY T1.actID ORDER BY count(*) DESC LIMIT 1
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 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
What are the ids of the students who are under 20 years old and are involved in at least one activity.
assistant
SELECT StuID FROM Participates_in INTERSECT SELECT StuID FROM Student WHERE age < 20 ;