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 classes are professor whose last name is Graztevski has?
assistant
SELECT count(*) FROM employee AS T1 JOIN CLASS AS T2 ON T1.EMP_NUM = T2.PROF_NUM WHERE T1.EMP_LNAME = 'Graztevski'
human
What are the first names and date of birth of professors teaching course ACCT-211?
assistant
SELECT DISTINCT T1.EMP_FNAME , T1.EMP_DOB FROM employee AS T1 JOIN CLASS AS T2 ON T1.EMP_NUM = T2.PROF_NUM WHERE CRS_CODE = 'ACCT-211'
human
Find the first names of students who took exactly one class.
assistant
SELECT T1.stu_fname FROM student AS T1 JOIN enroll AS T2 ON T1.stu_num = T2.stu_num GROUP BY T2.stu_num HAVING count(*) = 1
human
Find the first names of professors who are teaching more than one class.
assistant
SELECT T2.emp_fname FROM CLASS AS T1 JOIN employee AS T2 ON T1.prof_num = T2.emp_num GROUP BY T1.prof_num HAVING count(*) > 1
human
What is the first names of the professors from the history department who do not teach a class.
assistant
SELECT T1.emp_fname FROM employee AS T1 JOIN professor AS T2 ON T1.emp_num = T2.emp_num JOIN department AS T3 ON T2.dept_code = T3.dept_code WHERE T3.dept_name = 'History' EXCEPT SELECT T4.emp_fname FROM employee AS T4 JOIN CLASS AS T5 ON T4.emp_num = T5.prof_num
human
What are the first names of the professors who do not teach a class.
assistant
SELECT emp_fname FROM employee WHERE emp_jobcode = "PROF" EXCEPT SELECT T1.emp_fname FROM employee AS T1 JOIN CLASS AS T2 ON T1.emp_num = T2.prof_num ;