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 phone, room, and building for the faculty named Jerry Prince.
assistant
SELECT phone , room , building FROM Faculty WHERE Fname = 'Jerry' AND Lname = 'Prince'
human
Show the rank, first name, and last name for all the faculty.
assistant
SELECT rank , Fname , Lname FROM Faculty
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 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
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 ;