CREATE TABLE CLASS (
    CLASS_CODE TEXT PRIMARY KEY,
    CRS_CODE TEXT,
    CLASS_SECTION TEXT,
    CLASS_TIME TEXT,
    CLASS_ROOM TEXT,
    PROF_NUM NUMBER,
    FOREIGN KEY ("PROF_NUM") REFERENCES EMPLOYEE("EMP_NUM"),
    FOREIGN KEY ("CRS_CODE") REFERENCES COURSE("CRS_CODE")
);
CREATE TABLE COURSE (
    CRS_CODE TEXT PRIMARY KEY,
    DEPT_CODE TEXT,
    CRS_DESCRIPTION TEXT,
    CRS_CREDIT NUMBER,
    FOREIGN KEY ("DEPT_CODE") REFERENCES DEPARTMENT("DEPT_CODE")
);
CREATE TABLE DEPARTMENT (
    DEPT_CODE TEXT PRIMARY KEY,
    DEPT_NAME TEXT,
    SCHOOL_CODE TEXT,
    EMP_NUM NUMBER,
    DEPT_ADDRESS TEXT,
    DEPT_EXTENSION TEXT,
    FOREIGN KEY ("EMP_NUM") REFERENCES EMPLOYEE("EMP_NUM")
);
CREATE TABLE EMPLOYEE (
    EMP_NUM NUMBER PRIMARY KEY,
    EMP_LNAME TEXT,
    EMP_FNAME TEXT,
    EMP_INITIAL TEXT,
    EMP_JOBCODE TEXT,
    EMP_HIREDATE TIME,
    EMP_DOB TIME
);
CREATE TABLE ENROLL (
    CLASS_CODE TEXT,
    STU_NUM NUMBER,
    ENROLL_GRADE TEXT,
    FOREIGN KEY ("STU_NUM") REFERENCES STUDENT("STU_NUM"),
    FOREIGN KEY ("CLASS_CODE") REFERENCES CLASS("CLASS_CODE")
);
CREATE TABLE PROFESSOR (
    EMP_NUM NUMBER,
    DEPT_CODE TEXT,
    PROF_OFFICE TEXT,
    PROF_EXTENSION TEXT,
    PROF_HIGH_DEGREE TEXT,
    FOREIGN KEY ("DEPT_CODE") REFERENCES DEPARTMENT("DEPT_CODE"),
    FOREIGN KEY ("EMP_NUM") REFERENCES EMPLOYEE("EMP_NUM")
);
CREATE TABLE STUDENT (
    STU_NUM NUMBER PRIMARY KEY,
    STU_LNAME TEXT,
    STU_FNAME TEXT,
    STU_INIT TEXT,
    STU_DOB TIME,
    STU_HRS NUMBER,
    STU_CLASS TEXT,
    STU_GPA NUMBER,
    STU_TRANSFER NUMBER,
    DEPT_CODE TEXT,
    STU_PHONE TEXT,
    PROF_NUM NUMBER,
    FOREIGN KEY ("DEPT_CODE") REFERENCES DEPARTMENT("DEPT_CODE")
);

Question: What are the first names, office locations of all lecturers who have taught some course?
SQL: SELECT T2.emp_fname ,  T4.prof_office ,  T3.crs_description FROM CLASS AS T1 JOIN employee AS T2 ON T1.prof_num  =  T2.emp_num JOIN course AS T3 ON T1.crs_code  =  T3.crs_code JOIN professor AS T4 ON T2.emp_num  =  T4.emp_num

Question: Find the first names and offices of all instructors who have taught some course and the course description and the department name.
SQL: SELECT T2.emp_fname ,  T4.prof_office ,  T3.crs_description ,  T5.dept_name FROM CLASS AS T1 JOIN employee AS T2 ON T1.prof_num  =  T2.emp_num JOIN course AS T3 ON T1.crs_code  =  T3.crs_code JOIN professor AS T4 ON T2.emp_num  =  T4.emp_num JOIN department AS T5 ON T4.dept_code  =  T5.dept_code

Question: What are the first names, office locations, and departments of all instructors, and also what are the descriptions of the courses they teach?
SQL: SELECT T2.emp_fname ,  T4.prof_office ,  T3.crs_description ,  T5.dept_name FROM CLASS AS T1 JOIN employee AS T2 ON T1.prof_num  =  T2.emp_num JOIN course AS T3 ON T1.crs_code  =  T3.crs_code JOIN professor AS T4 ON T2.emp_num  =  T4.emp_num JOIN department AS T5 ON T4.dept_code  =  T5.dept_code

Question: Find names of all students who took some course and the course description.
SQL: 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

Question: What are the names of all students who took a class and the corresponding course descriptions?
SQL: 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

Question: Find names of all students who took some course and got A or C.
SQL: SELECT T1.stu_fname ,  T1.stu_lname FROM student AS T1 JOIN enroll AS T2 ON T1.stu_num  =  T2.stu_num WHERE T2.enroll_grade  =  'C' OR T2.enroll_grade  =  'A'

Question: What are the names of all students taking a course who received an A or C?
SQL: SELECT T1.stu_fname ,  T1.stu_lname FROM student AS T1 JOIN enroll AS T2 ON T1.stu_num  =  T2.stu_num WHERE T2.enroll_grade  =  'C' OR T2.enroll_grade  =  'A'

Question: Find the first names of all professors in the Accounting department who is teaching some course and the class room.
SQL: SELECT T2.emp_fname ,  T1.class_room FROM CLASS AS T1 JOIN employee AS T2 ON T1.prof_num  =  T2.emp_num JOIN professor AS T3 ON T2.emp_num  =  T3.emp_num JOIN department AS T4 ON T4.dept_code  =  T3.dept_code WHERE T4.dept_name  =  'Accounting'

Question: What are the first names of all Accounting professors who teach and what are the classrooms of the courses they teach?
SQL: SELECT T2.emp_fname ,  T1.class_room FROM CLASS AS T1 JOIN employee AS T2 ON T1.prof_num  =  T2.emp_num JOIN professor AS T3 ON T2.emp_num  =  T3.emp_num JOIN department AS T4 ON T4.dept_code  =  T3.dept_code WHERE T4.dept_name  =  'Accounting'

Question: Find the first names and degree of all professors who are teaching some class in Computer Info. Systems department.
SQL: SELECT DISTINCT T2.emp_fname ,  T3.prof_high_degree FROM CLASS AS T1 JOIN employee AS T2 ON T1.prof_num  =  T2.emp_num JOIN professor AS T3 ON T2.emp_num  =  T3.emp_num JOIN department AS T4 ON T4.dept_code  =  T3.dept_code WHERE T4.dept_name  =  'Computer Info. Systems'

Question: What are the different first names and highest degree attained for professors teaching in the Computer Information Systems department?
SQL: SELECT DISTINCT T2.emp_fname ,  T3.prof_high_degree FROM CLASS AS T1 JOIN employee AS T2 ON T1.prof_num  =  T2.emp_num JOIN professor AS T3 ON T2.emp_num  =  T3.emp_num JOIN department AS T4 ON T4.dept_code  =  T3.dept_code WHERE T4.dept_name  =  'Computer Info. Systems'

Question: What is the last name of the student who got a grade A in the class with code 10018.
SQL: SELECT T1.stu_lname FROM student AS T1 JOIN enroll AS T2 ON T1.stu_num  =  T2.stu_num WHERE T2.enroll_grade  =  'A' AND T2.class_code  =  10018

Question: What is the last name of the student who received an A in the class with the code 10018?
SQL: SELECT T1.stu_lname FROM student AS T1 JOIN enroll AS T2 ON T1.stu_num  =  T2.stu_num WHERE T2.enroll_grade  =  'A' AND T2.class_code  =  10018

Question: Find the first name and office of history professor who did not get a Ph.D. degree.
SQL: SELECT T2.emp_fname ,  T1.prof_office FROM professor AS T1 JOIN employee AS T2 ON T1.emp_num  =  T2.emp_num JOIN department AS T3 ON T1.dept_code  =  T3.dept_code WHERE T3.dept_name  =  'History' AND T1.prof_high_degree != 'Ph.D.'

Question: What are the first names and offices of history professors who don't have Ph.D.s?
SQL: SELECT T2.emp_fname ,  T1.prof_office FROM professor AS T1 JOIN employee AS T2 ON T1.emp_num  =  T2.emp_num JOIN department AS T3 ON T1.dept_code  =  T3.dept_code WHERE T3.dept_name  =  'History' AND T1.prof_high_degree != 'Ph.D.'

Question: Find the first names of professors who are teaching more than one class.
SQL: 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

Question: What are the first names of all professors who teach more than one class?
SQL: 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

Question: Find the first names of students who took exactly one class.
SQL: 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

Question: What are the first names of student who only took one course?
SQL: 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

Question: Find the name of department that offers the class whose description has the word "Statistics".
SQL: SELECT T2.dept_name FROM course AS T1 JOIN department AS T2 ON T1.dept_code  =  T2.dept_code WHERE T1.crs_description LIKE '%Statistics%'