human
Schema:
Student (StuID, LName, Fname, Age, Sex, Major, Advisor, city_code)
Faculty (FacID, Lname, Fname, Rank, Sex, Phone, Room, Building)
Department (DNO, Division, DName, Room, Building, DPhone)
Member_of (FacID, DNO, Appt_Type)
Course (CID, CName, Credits, Instructor, Days, Hours, DNO)
Minor_in (StuID, DNO)
Enrolled_in (StuID, CID, Grade)
Gradeconversion (lettergrade, gradepoint)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the rank of the faculty that the fewest faculties belong to.
assistant
SELECT Rank FROM FACULTY GROUP BY Rank ORDER BY count(*) ASC LIMIT 1
human
Find the full names of faculties who are members of department with department number 520.
assistant
SELECT T1.Fname , T1.Lname FROM FACULTY AS T1 JOIN MEMBER_OF AS T2 ON T1.FacID = T2.FacID WHERE T2.DNO = 520
human
Find the name of the department that has no students minored in?
assistant
SELECT DName FROM DEPARTMENT EXCEPT SELECT T1.DName FROM DEPARTMENT AS T1 JOIN MINOR_IN AS T2 ON T1.DNO = T2.DNO
human
What is the number of departments in Division 'AS'?
assistant
SELECT count(*) FROM DEPARTMENT WHERE Division = 'AS'
human
Find the name of the department that has the biggest number of students minored in?
assistant
SELECT T1.DName FROM DEPARTMENT AS T1 JOIN MINOR_IN AS T2 ON T1.DNO = T2.DNO GROUP BY T2.DNO ORDER BY count(*) DESC LIMIT 1
human
What is the name of the department with the fewest members?
assistant
SELECT T1.DName FROM DEPARTMENT AS T1 JOIN MEMBER_OF AS T2 ON T1.DNO = T2.DNO GROUP BY T2.DNO ORDER BY COUNT( * ) ASC LIMIT 1 ;