CREATE TABLE CLASS (
    CLASS_CODE TEXT PRIMARY KEY,
    CRS_CODE TEXT,
    CLASS_SECTION TEXT,
    CLASS_TIME TEXT,
    CLASS_ROOM TEXT,
    PROF_NUM NUMBER,
    FOREIGN KEY ("PROF_NUM") REFERENCES EMPLOYEE("EMP_NUM"),
    FOREIGN KEY ("CRS_CODE") REFERENCES COURSE("CRS_CODE")
);
CREATE TABLE COURSE (
    CRS_CODE TEXT PRIMARY KEY,
    DEPT_CODE TEXT,
    CRS_DESCRIPTION TEXT,
    CRS_CREDIT NUMBER,
    FOREIGN KEY ("DEPT_CODE") REFERENCES DEPARTMENT("DEPT_CODE")
);
CREATE TABLE DEPARTMENT (
    DEPT_CODE TEXT PRIMARY KEY,
    DEPT_NAME TEXT,
    SCHOOL_CODE TEXT,
    EMP_NUM NUMBER,
    DEPT_ADDRESS TEXT,
    DEPT_EXTENSION TEXT,
    FOREIGN KEY ("EMP_NUM") REFERENCES EMPLOYEE("EMP_NUM")
);
CREATE TABLE EMPLOYEE (
    EMP_NUM NUMBER PRIMARY KEY,
    EMP_LNAME TEXT,
    EMP_FNAME TEXT,
    EMP_INITIAL TEXT,
    EMP_JOBCODE TEXT,
    EMP_HIREDATE TIME,
    EMP_DOB TIME
);
CREATE TABLE ENROLL (
    CLASS_CODE TEXT,
    STU_NUM NUMBER,
    ENROLL_GRADE TEXT,
    FOREIGN KEY ("STU_NUM") REFERENCES STUDENT("STU_NUM"),
    FOREIGN KEY ("CLASS_CODE") REFERENCES CLASS("CLASS_CODE")
);
CREATE TABLE PROFESSOR (
    EMP_NUM NUMBER,
    DEPT_CODE TEXT,
    PROF_OFFICE TEXT,
    PROF_EXTENSION TEXT,
    PROF_HIGH_DEGREE TEXT,
    FOREIGN KEY ("DEPT_CODE") REFERENCES DEPARTMENT("DEPT_CODE"),
    FOREIGN KEY ("EMP_NUM") REFERENCES EMPLOYEE("EMP_NUM")
);
CREATE TABLE STUDENT (
    STU_NUM NUMBER PRIMARY KEY,
    STU_LNAME TEXT,
    STU_FNAME TEXT,
    STU_INIT TEXT,
    STU_DOB TIME,
    STU_HRS NUMBER,
    STU_CLASS TEXT,
    STU_GPA NUMBER,
    STU_TRANSFER NUMBER,
    DEPT_CODE TEXT,
    STU_PHONE TEXT,
    PROF_NUM NUMBER,
    FOREIGN KEY ("DEPT_CODE") REFERENCES DEPARTMENT("DEPT_CODE")
);

Question: How many classes exist for each school?
SQL: SELECT count(*) ,  T3.school_code FROM CLASS AS T1 JOIN course AS T2 ON T1.crs_code  =  T2.crs_code JOIN department AS T3 ON T2.dept_code  =  T3.dept_code GROUP BY T3.school_code

Question: What is the number of professors for different school?
SQL: SELECT count(*) ,  T1.school_code FROM department AS T1 JOIN professor AS T2 ON T1.dept_code  =  T2.dept_code GROUP BY T1.school_code

Question: How many different professors are there for the different schools?
SQL: SELECT count(*) ,  T1.school_code FROM department AS T1 JOIN professor AS T2 ON T1.dept_code  =  T2.dept_code GROUP BY T1.school_code

Question: Find the count and code of the job has most employees.
SQL: SELECT emp_jobcode ,  count(*) FROM employee GROUP BY emp_jobcode ORDER BY count(*) DESC LIMIT 1

Question: What is the count and code of the job with the most employee?
SQL: SELECT emp_jobcode ,  count(*) FROM employee GROUP BY emp_jobcode ORDER BY count(*) DESC LIMIT 1

Question: Which school has the smallest amount of professors?
SQL: SELECT T1.school_code FROM department AS T1 JOIN professor AS T2 ON T1.dept_code  =  T2.dept_code GROUP BY T1.school_code ORDER BY count(*) LIMIT 1

Question: Which school has the fewest professors?
SQL: SELECT T1.school_code FROM department AS T1 JOIN professor AS T2 ON T1.dept_code  =  T2.dept_code GROUP BY T1.school_code ORDER BY count(*) LIMIT 1

Question: Find the number of professors with a Ph.D. degree in each department.
SQL: SELECT count(*) ,  dept_code FROM professor WHERE prof_high_degree  =  'Ph.D.' GROUP BY dept_code

Question: How many professors have a Ph.D. in each department?
SQL: SELECT count(*) ,  dept_code FROM professor WHERE prof_high_degree  =  'Ph.D.' GROUP BY dept_code

Question: Find the number of students for each department.
SQL: SELECT count(*) ,  dept_code FROM student GROUP BY dept_code

Question: How many students are in each department?
SQL: SELECT count(*) ,  dept_code FROM student GROUP BY dept_code

Question: Find the total number of hours have done for all students in each department.
SQL: SELECT sum(stu_hrs) ,  dept_code FROM student GROUP BY dept_code

Question: How many hours do the students spend studying in each department?
SQL: SELECT sum(stu_hrs) ,  dept_code FROM student GROUP BY dept_code

Question: Find the max, average, and minimum gpa of all students in each department.
SQL: SELECT max(stu_gpa) ,  avg(stu_gpa) ,  min(stu_gpa) ,  dept_code FROM student GROUP BY dept_code

Question: What is the highest, lowest, and average student GPA for every department?
SQL: SELECT max(stu_gpa) ,  avg(stu_gpa) ,  min(stu_gpa) ,  dept_code FROM student GROUP BY dept_code

Question: What is the name and the average gpa of department whose students have the highest average gpa?
SQL: 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

Question: Which department has the highest average student GPA, and what is the average gpa?
SQL: 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

Question: how many schools exist in total?
SQL: SELECT count(DISTINCT school_code) FROM department

Question: How many schools are there in the department?
SQL: SELECT count(DISTINCT school_code) FROM department

Question: How many different classes are there?
SQL: SELECT count(DISTINCT class_code) FROM CLASS

Question: How many unique classes are offered?
SQL: SELECT count(DISTINCT class_code) FROM CLASS

Question: How many courses are offered?
SQL: SELECT count(DISTINCT crs_code) FROM CLASS

Question: What are the number of different course codes?
SQL: SELECT count(DISTINCT crs_code) FROM CLASS

Question: How many departments does the college has?
SQL: SELECT count(DISTINCT dept_name) FROM department

Question: How many different departments are there?
SQL: SELECT count(DISTINCT dept_name) FROM department

Question: How many courses are offered by the Computer Info. Systems department?
SQL: SELECT count(*) FROM department AS T1 JOIN course AS T2 ON T1.dept_code  =  T2.dept_code WHERE dept_name  =  "Computer Info. Systems"

Question: How many courses does the department of Computer Information Systmes offer?
SQL: SELECT count(*) FROM department AS T1 JOIN course AS T2 ON T1.dept_code  =  T2.dept_code WHERE dept_name  =  "Computer Info. Systems"

Question: How many sections does course ACCT-211 has?
SQL: SELECT count(DISTINCT class_section) FROM CLASS WHERE crs_code  =  'ACCT-211'

Question: What is the number of different class sections offered in the course ACCT-211?
SQL: SELECT count(DISTINCT class_section) FROM CLASS WHERE crs_code  =  'ACCT-211'

Question: Find the total credits of all classes offered by each department.
SQL: SELECT sum(T1.crs_credit) ,  T1.dept_code FROM course AS T1 JOIN CLASS AS T2 ON T1.crs_code  =  T2.crs_code GROUP BY T1.dept_code

Question: What are the total number of credits offered by each department?
SQL: SELECT sum(T1.crs_credit) ,  T1.dept_code FROM course AS T1 JOIN CLASS AS T2 ON T1.crs_code  =  T2.crs_code GROUP BY T1.dept_code

Question: Find the name of the department that offers the largest number of credits of all classes.
SQL: 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

Question: Which department offers the most credits all together?
SQL: 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

Question: How many students enrolled in class ACCT-211?
SQL: SELECT count(*) FROM CLASS AS T1 JOIN enroll AS T2 ON T1.class_code  =  T2.class_code WHERE T1.crs_code  =  'ACCT-211'

Question: What are the total number of students enrolled in ACCT-211?
SQL: SELECT count(*) FROM CLASS AS T1 JOIN enroll AS T2 ON T1.class_code  =  T2.class_code WHERE T1.crs_code  =  'ACCT-211'