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: Find the first name of student who is taking classes from accounting and Computer Info. Systems departments
SQL: SELECT T1.stu_fname 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 JOIN department AS T5 ON T5.dept_code  =  T4.dept_code WHERE T5.dept_name  =  'Accounting' INTERSECT SELECT T1.stu_fname 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 JOIN department AS T5 ON T5.dept_code  =  T4.dept_code WHERE T5.dept_name  =  'Computer Info. Systems'

Question: What are the first names of all students taking accoutning and Computer Information Systems classes?
SQL: SELECT T1.stu_fname 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 JOIN department AS T5 ON T5.dept_code  =  T4.dept_code WHERE T5.dept_name  =  'Accounting' INTERSECT SELECT T1.stu_fname 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 JOIN department AS T5 ON T5.dept_code  =  T4.dept_code WHERE T5.dept_name  =  'Computer Info. Systems'

Question: What is the average gpa of the students enrolled in the course with code ACCT-211?
SQL: SELECT avg(T2.stu_gpa) FROM enroll AS T1 JOIN student AS T2 ON T1.stu_num  =  T2.stu_num JOIN CLASS AS T3 ON T1.class_code  =  T3.class_code WHERE T3.crs_code  =  'ACCT-211'

Question: What is the average GPA of students taking ACCT-211?
SQL: SELECT avg(T2.stu_gpa) FROM enroll AS T1 JOIN student AS T2 ON T1.stu_num  =  T2.stu_num JOIN CLASS AS T3 ON T1.class_code  =  T3.class_code WHERE T3.crs_code  =  'ACCT-211'

Question: What is the first name, gpa and phone number of the top 5 students with highest gpa?
SQL: SELECT stu_gpa ,  stu_phone ,  stu_fname FROM student ORDER BY stu_gpa DESC LIMIT 5

Question: What is the first name, GPA, and phone number of the students with the top 5 GPAs?
SQL: SELECT stu_gpa ,  stu_phone ,  stu_fname FROM student ORDER BY stu_gpa DESC LIMIT 5

Question: What is the department name of the students with lowest gpa belongs to?
SQL: SELECT T2.dept_name FROM student AS T1 JOIN department AS T2 ON T1.dept_code  =  T2.dept_code ORDER BY stu_gpa LIMIT 1

Question: What is the name of the department with the student that has the lowest GPA?
SQL: SELECT T2.dept_name FROM student AS T1 JOIN department AS T2 ON T1.dept_code  =  T2.dept_code ORDER BY stu_gpa LIMIT 1

Question: Find the first name and gpa of the students whose gpa is lower than the average gpa of all students.
SQL: SELECT stu_fname ,  stu_gpa FROM student WHERE stu_gpa  <  (SELECT avg(stu_gpa) FROM student)

Question: What is the first name and GPA of every student that has a GPA lower than average?
SQL: SELECT stu_fname ,  stu_gpa FROM student WHERE stu_gpa  <  (SELECT avg(stu_gpa) FROM student)

Question: Find the name and address of the department that has the highest number of students.
SQL: SELECT T2.dept_name ,  T2.dept_address FROM student AS T1 JOIN department AS T2 ON T1.dept_code  =  T2.dept_code GROUP BY T1.dept_code ORDER BY count(*) DESC LIMIT 1

Question: What is the name and address of the department with the most students?
SQL: SELECT T2.dept_name ,  T2.dept_address FROM student AS T1 JOIN department AS T2 ON T1.dept_code  =  T2.dept_code GROUP BY T1.dept_code ORDER BY count(*) DESC LIMIT 1

Question: Find the name, address, number of students in the departments that have the top 3 highest number of students.
SQL: SELECT T2.dept_name ,  T2.dept_address ,  count(*) FROM student AS T1 JOIN department AS T2 ON T1.dept_code  =  T2.dept_code GROUP BY T1.dept_code ORDER BY count(*) DESC LIMIT 3

Question: What is the name, address, and number of students in the departments that have the 3 most students?
SQL: SELECT T2.dept_name ,  T2.dept_address ,  count(*) FROM student AS T1 JOIN department AS T2 ON T1.dept_code  =  T2.dept_code GROUP BY T1.dept_code ORDER BY count(*) DESC LIMIT 3

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

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

Question: Find the first names of all instructors who have taught some course and the course code.
SQL: SELECT T2.emp_fname ,  T1.crs_code FROM CLASS AS T1 JOIN employee AS T2 ON T1.prof_num  =  T2.emp_num

Question: What are the first names of all teachers who have taught a course and the corresponding course codes?
SQL: SELECT T2.emp_fname ,  T1.crs_code FROM CLASS AS T1 JOIN employee AS T2 ON T1.prof_num  =  T2.emp_num

Question: Find the first names of all instructors who have taught some course and the course description.
SQL: SELECT T2.emp_fname ,  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

Question: What are the first names of all teachers who have taught a course and the corresponding descriptions?
SQL: SELECT T2.emp_fname ,  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

Question: Find the first names and offices of all instructors who have taught some course and also find the course description.
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