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
How many faculty do we have?
assistant
SELECT count(*) FROM Faculty
human
How many Professors are in building NEB?
assistant
SELECT count(*) FROM Faculty WHERE Rank = 'Professor' AND building = 'NEB'
human
Show all the buildings along with the number of faculty members the buildings have.
assistant
SELECT building , count(*) FROM Faculty GROUP BY building
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
In which buildings are there at least ten professors?
assistant
SELECT building FROM Faculty WHERE rank = "Professor" GROUP BY building HAVING COUNT( * ) >= 10 ;