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
What are the description and credit of the course which the student whose last name is Smithson took?
assistant
SELECT T4.crs_description , T4.crs_credit FROM CLASS AS T1 JOIN enroll AS T2 ON T1.class_code = T2.class_code JOIN student AS T3 ON T3.stu_num = T2.stu_num JOIN course AS T4 ON T4.crs_code = T1.crs_code WHERE T3.stu_lname = 'Smithson'
human
How many credits does course CIS-220 have, and what its description?
assistant
SELECT crs_credit , crs_description FROM course WHERE crs_code = 'CIS-220'
human
Find names of all students who took some course and the course description.
assistant
SELECT T1.stu_fname , T1.stu_lname , T4.crs_description FROM student AS T1 JOIN enroll AS T2 ON T1.stu_num = T2.stu_num JOIN CLASS AS T3 ON T2.class_code = T3.class_code JOIN course AS T4 ON T3.crs_code = T4.crs_code
human
How many sections does course ACCT-211 has?
assistant
SELECT count(DISTINCT class_section) FROM CLASS WHERE crs_code = 'ACCT-211'
human
Find the first name of the professor who is teaching two courses with code CIS-220 and QM-261.
assistant
SELECT T1.emp_fname FROM employee AS T1 JOIN CLASS AS T2 ON T1.emp_num = T2.prof_num WHERE crs_code = 'CIS-220' INTERSECT SELECT T1.emp_fname FROM employee AS T1 JOIN CLASS AS T2 ON T1.emp_num = T2.prof_num WHERE crs_code = 'QM-261'
human
Find the description and credit for the course QM-261?
assistant
SELECT crs_credit , crs_description FROM course WHERE crs_code = "QM-261" ;