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
Yes, hi, I'd like to know how many professors are in the history department?
assistant
SELECT COUNT( * ) FROM professor WHERE dept_code = 'HIST';
human
Do both of them have doctorates?
assistant
SELECT COUNT( * ) FROM professor WHERE dept_code = 'HIST' AND prof_high_degree = 'Ph.D.';
human
How many departments are there total?
assistant
SELECT COUNT( distinct dept_code ) FROM professor;
human
Is English one of them?
assistant
SELECT distinct dept_code FROM professor WHERE dept_code = 'ENG';
human
Do any of the English professors have a PhD?
assistant
SELECT COUNT( * ) FROM professor WHERE dept_code = 'ENG' AND prof_high_degree = 'Ph.D.';
human
Can you tell me where the history Doctorate's office is?
assistant
SELECT prof_office FROM professor WHERE dept_code = 'HIST' AND prof_high_degree = 'Ph.D.';
human
What is the total number professors with doctorates employed by the 11 departments?
assistant
SELECT COUNT( * ) FROM professor WHERE prof_high_degree = 'Ph.D.';
human
Of those 18, how many doctorates are employed by the accounting department?
assistant
SELECT COUNT( * ) FROM professor WHERE prof_high_degree = "Ph.D." AND dept_code = "ACCT" ;