human
Schema:
Student (StuID, LName, Fname, Age, Sex, Major, Advisor, city_code)
Faculty (FacID, Lname, Fname, Rank, Sex, Phone, Room, Building)
Department (DNO, Division, DName, Room, Building, DPhone)
Member_of (FacID, DNO, Appt_Type)
Course (CID, CName, Credits, Instructor, Days, Hours, DNO)
Minor_in (StuID, DNO)
Enrolled_in (StuID, CID, Grade)
Gradeconversion (lettergrade, gradepoint)
Please answer with SQL.
assistant
Sure, I understand.
human
What are the first and last names of the instructors who teach the top 3 number of courses?
assistant
SELECT T2.Fname , T2.Lname FROM COURSE AS T1 JOIN FACULTY AS T2 ON T1.Instructor = T2.FacID GROUP BY T1.Instructor ORDER BY count(*) DESC LIMIT 3
human
Find the names of departments that are either in division AS or in division EN and in Building NEB.
assistant
SELECT DName FROM DEPARTMENT WHERE Division = 'AS' UNION SELECT DName FROM DEPARTMENT WHERE Division = 'EN' AND Building = 'NEB'
human
Find the first names and last names of male (sex is M) faculties who live in building NEB.
assistant
SELECT Fname , Lname FROM FACULTY WHERE sex = 'M' AND Building = 'NEB'
human
Which building does the instructor who teaches the most number of courses live in?
assistant
SELECT T2.Building FROM COURSE AS T1 JOIN FACULTY AS T2 ON T1.Instructor = T2.FacID GROUP BY T1.Instructor ORDER BY count(*) DESC LIMIT 1
human
What are the phones of departments in Room 268?
assistant
SELECT DPhone FROM DEPARTMENT WHERE Room = 268
human
What are the rooms for members of the faculty who are professors and who live in building NEB?
assistant
SELECT Room FROM FACULTY WHERE Rank = "Professor" AND Building = "NEB" ;