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 all the activity names and the number of faculty involved in each activity.
assistant
SELECT T1.activity_name , count(*) FROM Activity AS T1 JOIN Faculty_participates_in AS T2 ON T1.actID = T2.actID GROUP BY T1.actID
human
Which building has most faculty members?
assistant
SELECT building FROM Faculty GROUP BY building 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
Show all the distinct buildings that have faculty rooms.
assistant
SELECT DISTINCT building FROM Faculty
human
Show all the buildings along with the number of faculty members the buildings have.
assistant
SELECT building , COUNT( * ) FROM Faculty GROUP BY building ;