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 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
Show all the buildings that have at least 10 professors.
assistant
SELECT building FROM Faculty WHERE rank = 'Professor' GROUP BY building HAVING count(*) >= 10
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
Show all the distinct buildings that have faculty rooms.
assistant
SELECT DISTINCT building FROM Faculty
human
Which building has most faculty members?
assistant
SELECT building FROM Faculty GROUP BY building ORDER BY count(*) DESC LIMIT 1
human
What are the phone, room, and building of the faculty member called Jerry Prince?
assistant
SELECT phone , room , building FROM Faculty WHERE Fname = "Jerry" AND Lname = "Prince" ;