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 max and min grade point for all letter grade.
assistant
SELECT max(gradepoint) , min(gradepoint) FROM GRADECONVERSION
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 average grade point of student whose last name is Smith.
assistant
SELECT avg(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 WHERE T3.LName = 'Smith'
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
human
Find the number of students that have at least one grade 'B'.
assistant
SELECT COUNT( DISTINCT StuID ) FROM ENROLLED_IN WHERE Grade = "B" ;