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 last name and hire date of the professor who is in office DRE 102.
assistant
SELECT T1.emp_lname , T1.emp_hiredate FROM employee AS T1 JOIN professor AS T2 ON T1.emp_num = T2.emp_num WHERE T2.prof_office = 'DRE 102'
human
list names of all departments ordered by their names.
assistant
SELECT dept_name FROM department ORDER BY dept_name
human
What is the first and last name of the oldest employee?
assistant
SELECT emp_fname , emp_lname FROM employee ORDER BY emp_dob LIMIT 1
human
Find the first names and offices of all professors sorted by alphabetical order of their first name.
assistant
SELECT T2.emp_fname , T1.prof_office FROM professor AS T1 JOIN employee AS T2 ON T1.emp_num = T2.emp_num ORDER BY T2.emp_fname
human
Find the count and code of the job has most employees.
assistant
SELECT emp_jobcode , count(*) FROM employee GROUP BY emp_jobcode ORDER BY count(*) DESC LIMIT 1
human
List the first name of all employees with job code PROF ordered by their date of birth.
assistant
SELECT emp_fname FROM employee WHERE emp_jobcode = "PROF" ORDER BY emp_dob ;