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
What ranks do we have for faculty?
assistant
SELECT DISTINCT rank FROM Faculty
human
Show all the faculty ranks and the number of students advised by each rank.
assistant
SELECT T1.rank , count(*) FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID = T2.advisor GROUP BY T1.rank
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
Show the rank, first name, and last name for all the faculty.
assistant
SELECT rank , Fname , Lname FROM Faculty
human
For each faculty rank, show the number of faculty members who have it.
assistant
SELECT rank , count(*) FROM Faculty GROUP BY rank
human
Find the faculty rank that has the least members.
assistant
SELECT rank FROM Faculty GROUP BY rank ORDER BY COUNT( * ) ASC LIMIT 1 ;