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 student first and last names and grade points of all enrollments.
assistant
SELECT T3.Fname , T3.LName , T2.gradepoint FROM ENROLLED_IN AS T1 JOIN GRADECONVERSION AS T2 JOIN STUDENT AS T3 ON T1.Grade = T2.lettergrade AND T1.StuID = T3.StuID
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 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
Find the first names of students whose first names contain letter 'a'.
assistant
SELECT DISTINCT Fname FROM STUDENT WHERE Fname LIKE '%a%'
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 last name of female (sex is F) students in the descending order of age.
assistant
SELECT LName FROM STUDENT WHERE Sex = "F" ORDER BY Age DESC ;