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
How many professors who has a either Ph.D. or MA degree?
assistant
SELECT count(*) FROM professor WHERE prof_high_degree = 'Ph.D.' OR prof_high_degree = 'MA'
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 professors in accounting department.
assistant
SELECT count(*) FROM professor AS T1 JOIN department AS T2 ON T1.dept_code = T2.dept_code WHERE DEPT_NAME = 'Accounting'
human
What is the name of department where has the largest number of professors with a Ph.D. degree?
assistant
SELECT T2.dept_name , T1.dept_code FROM professor AS T1 JOIN department AS T2 ON T1.dept_code = T2.dept_code WHERE T1.prof_high_degree = 'Ph.D.' GROUP BY T1.dept_code ORDER BY count(*) DESC LIMIT 1
human
How many professors do have a Ph.D. degree?
assistant
SELECT count(*) FROM professor WHERE prof_high_degree = 'Ph.D.'
human
Find the number of professors with a Ph.D. degree in each department.
assistant
SELECT COUNT( * ) , dept_code FROM professor WHERE prof_high_degree = "Ph.D." GROUP BY dept_code ;