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
Find the name of the department that offers the largest number of credits of all classes.
assistant
SELECT T3.dept_name FROM course AS T1 JOIN CLASS AS T2 ON T1.crs_code = T2.crs_code JOIN department AS T3 ON T1.dept_code = T3.dept_code GROUP BY T1.dept_code ORDER BY sum(T1.crs_credit) DESC LIMIT 1
human
What is the name of the department that has the largest number of students enrolled?
assistant
SELECT T4.dept_name FROM CLASS AS T1 JOIN enroll AS T2 ON T1.class_code = T2.class_code JOIN course AS T3 ON T1.crs_code = T3.crs_code JOIN department AS T4 ON T3.dept_code = T4.dept_code GROUP BY T3.dept_code ORDER BY count(*) DESC LIMIT 1
human
Find the number of departments in each school.
assistant
SELECT count(DISTINCT dept_name) , school_code FROM department GROUP BY school_code
human
Find the number of students for each department.
assistant
SELECT count(*) , dept_code FROM student GROUP BY dept_code
human
Find the name and address of the department that has the highest number of students.
assistant
SELECT T2.dept_name , T2.dept_address FROM student AS T1 JOIN department AS T2 ON T1.dept_code = T2.dept_code GROUP BY T1.dept_code ORDER BY count(*) DESC LIMIT 1
human
Find the name, address, number of students in the departments that have the top 3 highest number of students.
assistant
SELECT T2.dept_name , T2.dept_address , COUNT( * ) FROM student AS T1 JOIN department AS T2 ON T1.dept_code = T2.dept_code GROUP BY T1.dept_code ORDER BY COUNT( * ) DESC LIMIT 3 ;