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
How many courses are there in total?
assistant
SELECT count(*) FROM COURSE
human
List the course name of courses sorted by credits.
assistant
SELECT CName FROM COURSE ORDER BY Credits
human
Which courses are taught on days MTW?
assistant
SELECT CName FROM COURSE WHERE Days = 'MTW'
human
Find the first name and last name of the instructor of course that has course name
assistant
SELECT T2.Fname , T2.Lname FROM COURSE AS T1 JOIN FACULTY AS T2 ON T1.Instructor = T2.FacID WHERE T1.CName = 'COMPUTER LITERACY'
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
Give the building that the instructor who teaches the greatest number of courses lives 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 ;