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 first name of students in the descending order of age.
assistant
SELECT Fname FROM STUDENT ORDER BY Age DESC
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
What are the first names and last names of the students that minor in the department with DNO 140.
assistant
SELECT T2.Fname , T2.Lname FROM MINOR_IN AS T1 JOIN STUDENT AS T2 ON T1.StuID = T2.StuID WHERE T1.DNO = 140
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
Find the name of the department that has the biggest number of students minored in?
assistant
SELECT T1.DName FROM DEPARTMENT AS T1 JOIN MINOR_IN AS T2 ON T1.DNO = T2.DNO GROUP BY T2.DNO ORDER BY count(*) DESC LIMIT 1
human
Find the name of the department that has no students minored in?
assistant
SELECT DName FROM DEPARTMENT EXCEPT SELECT T1.DName FROM DEPARTMENT AS T1 JOIN MINOR_IN AS T2 ON T1.DNO = T2.DNO ;