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 rank, first name, and last name for all the faculty.
assistant
SELECT rank , Fname , Lname FROM Faculty
human
Show the first and last name of all the faculty members who participated in some activity, together with the number of activities they participated in.
assistant
SELECT T1.fname , T1.lname , count(*) , T1.FacID FROM Faculty AS T1 JOIN Faculty_participates_in AS T2 ON T1.facID = T2.facID GROUP BY T1.FacID
human
What is the first and last name of the student participating in the most activities?
assistant
SELECT T1.fname , T1.lname FROM Student AS T1 JOIN Participates_in AS T2 ON T1.StuID = T2.StuID GROUP BY T1.StuID ORDER BY count(*) DESC LIMIT 1
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
What is the name of the activity that has the most faculty members involved in?
assistant
SELECT T1.activity_name FROM Activity AS T1 JOIN Faculty_participates_in AS T2 ON T1.actID = T2.actID GROUP BY T1.actID ORDER BY count(*) DESC LIMIT 1
human
What is the first and last name of the faculty participating in the most activities?
assistant
SELECT T1.fname , T1.lname FROM Faculty AS T1 JOIN Faculty_participates_in AS T2 ON T1.facID = T2.facID GROUP BY T1.FacID ORDER BY COUNT( * ) DESC LIMIT 1 ;