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 all the ranks and the number of male and female faculty for each rank.
assistant
SELECT rank , sex , count(*) FROM Faculty GROUP BY rank , sex
human
Which rank has the smallest number of faculty members?
assistant
SELECT rank FROM Faculty GROUP BY rank ORDER BY count(*) ASC LIMIT 1
human
What ranks do we have for faculty?
assistant
SELECT DISTINCT rank FROM Faculty
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
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 are the rank, first name, and last name of the faculty members?
assistant
SELECT rank , Fname , Lname FROM Faculty ;