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 not enrolled in any course.
assistant
SELECT Fname FROM STUDENT WHERE StuID NOT IN (SELECT StuID FROM ENROLLED_IN)
human
Find the number of students that have at least one grade 'B'.
assistant
SELECT COUNT(DISTINCT StuID) FROM ENROLLED_IN WHERE Grade = 'B'
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 first names of students whose first names contain letter 'a'.
assistant
SELECT DISTINCT Fname FROM STUDENT WHERE Fname LIKE '%a%'
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 distinct student first names of all students that have grade point at least 3.8 in one course.
assistant
SELECT DISTINCT T3.Fname FROM ENROLLED_IN AS T1 JOIN GRADECONVERSION AS T2 JOIN STUDENT AS T3 ON T1.Grade = T2.lettergrade AND T1.StuID = T3.StuID WHERE T2.gradepoint >= 3.8 ;