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
Find the name of the department that has the fewest members.
assistant
SELECT T1.DName FROM DEPARTMENT AS T1 JOIN MEMBER_OF AS T2 ON T1.DNO = T2.DNO GROUP BY T2.DNO ORDER BY count(*) ASC LIMIT 1
human
Find the first names of faculties of rank Professor in alphabetic order.
assistant
SELECT Fname FROM FACULTY WHERE Rank = 'Professor' ORDER BY Fname
human
Find the last names of faculties in building Barton in alphabetic order.
assistant
SELECT Lname FROM FACULTY WHERE Building = 'Barton' ORDER BY Lname
human
Find the full names of faculties who are members of department with department number 520.
assistant
SELECT T1.Fname , T1.Lname FROM FACULTY AS T1 JOIN MEMBER_OF AS T2 ON T1.FacID = T2.FacID WHERE T2.DNO = 520
human
Find the department name and room of the course INTRODUCTION TO COMPUTER SCIENCE.
assistant
SELECT T2.Dname , T2.Room FROM COURSE AS T1 JOIN DEPARTMENT AS T2 ON T1.DNO = T2.DNO WHERE T1.CName = 'INTRODUCTION TO COMPUTER SCIENCE'
human
Find the last names of faculties who are members of computer science department.
assistant
SELECT T2.Lname FROM DEPARTMENT AS T1 JOIN FACULTY AS T2 ON T1.DNO = T3.DNO JOIN MEMBER_OF AS T3 ON T2.FacID = T3.FacID WHERE T1.DName = "Computer Science" ;