human
Schema:
class (CLASS_CODE, CRS_CODE, CLASS_SECTION, CLASS_TIME, CLASS_ROOM, PROF_NUM)
course (CRS_CODE, DEPT_CODE, CRS_DESCRIPTION, CRS_CREDIT)
department (DEPT_CODE, DEPT_NAME, SCHOOL_CODE, EMP_NUM, DEPT_ADDRESS, DEPT_EXTENSION)
employee (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_JOBCODE, EMP_HIREDATE, EMP_DOB)
enroll (CLASS_CODE, STU_NUM, ENROLL_GRADE)
professor (EMP_NUM, DEPT_CODE, PROF_OFFICE, PROF_EXTENSION, PROF_HIGH_DEGREE)
student (STU_NUM, STU_LNAME, STU_FNAME, STU_INIT, STU_DOB, STU_HRS, STU_CLASS, STU_GPA, STU_TRANSFER, DEPT_CODE, STU_PHONE, PROF_NUM)
Please answer with SQL.
assistant
Sure, I understand.
human
What is the average gpa of the students enrolled in the course with code ACCT-211?
assistant
SELECT avg(T2.stu_gpa) FROM enroll AS T1 JOIN student AS T2 ON T1.stu_num = T2.stu_num JOIN CLASS AS T3 ON T1.class_code = T3.class_code WHERE T3.crs_code = 'ACCT-211'
human
What is the first, last name, gpa of the youngest one among students whose GPA is above 3?
assistant
SELECT stu_fname , stu_lname , stu_gpa FROM student WHERE stu_gpa > 3 ORDER BY stu_dob DESC LIMIT 1
human
What is the first name, gpa and phone number of the top 5 students with highest gpa?
assistant
SELECT stu_gpa , stu_phone , stu_fname FROM student ORDER BY stu_gpa DESC LIMIT 5
human
What is the department name of the students with lowest gpa belongs to?
assistant
SELECT T2.dept_name FROM student AS T1 JOIN department AS T2 ON T1.dept_code = T2.dept_code ORDER BY stu_gpa LIMIT 1
human
What is the name and the average gpa of department whose students have the highest average gpa?
assistant
SELECT T2.dept_name , avg(T1.stu_gpa) FROM student AS T1 JOIN department AS T2 ON T1.dept_code = T2.dept_code GROUP BY T1.dept_code ORDER BY avg(T1.stu_gpa) DESC LIMIT 1
human
What is the highest, lowest, and average student GPA for every department?
assistant
SELECT MAX( stu_gpa ) , avg ( stu_gpa ) , MIN( stu_gpa ) , dept_code FROM student GROUP BY dept_code ;