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 rooms of faculties with rank professor who live in building NEB.
assistant
SELECT Room FROM FACULTY WHERE Rank = 'Professor' AND Building = 'NEB'
human
Find the first name of students not enrolled in any course.
assistant
SELECT Fname FROM STUDENT WHERE StuID NOT IN (SELECT StuID FROM ENROLLED_IN)
human
List the course name of courses sorted by credits.
assistant
SELECT CName FROM COURSE ORDER BY Credits
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
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'
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" ;