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
For each faculty rank, show the number of faculty members who have it.
assistant
SELECT rank , count(*) FROM Faculty GROUP BY rank
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
Which building has most faculty members?
assistant
SELECT building FROM Faculty GROUP BY building ORDER BY count(*) DESC LIMIT 1
human
How many faculty members does each building have? List the result with the name of the building.
assistant
SELECT building , COUNT( * ) FROM Faculty GROUP BY building ;