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 is the first name of each student enrolled in class ACCT-211?
SQL: SELECT T3.stu_fname FROM CLASS AS T1 JOIN enroll AS T2 ON T1.class_code  =  T2.class_code JOIN student AS T3 ON T2.stu_num  =  T3.stu_num WHERE T1.crs_code  =  'ACCT-211'

Question: What are the first names of all students in course ACCT-211?
SQL: SELECT T3.stu_fname FROM CLASS AS T1 JOIN enroll AS T2 ON T1.class_code  =  T2.class_code JOIN student AS T3 ON T2.stu_num  =  T3.stu_num WHERE T1.crs_code  =  'ACCT-211'

Question: What is the first name of students enrolled in class ACCT-211 and got grade C?
SQL: SELECT T3.stu_fname FROM CLASS AS T1 JOIN enroll AS T2 ON T1.class_code  =  T2.class_code JOIN student AS T3 ON T2.stu_num  =  T3.stu_num WHERE T1.crs_code  =  'ACCT-211' AND T2.enroll_grade  =  'C'

Question: What are the first names of all students who took ACCT-211 and received a C?
SQL: SELECT T3.stu_fname FROM CLASS AS T1 JOIN enroll AS T2 ON T1.class_code  =  T2.class_code JOIN student AS T3 ON T2.stu_num  =  T3.stu_num WHERE T1.crs_code  =  'ACCT-211' AND T2.enroll_grade  =  'C'

Question: Find the total number of employees.
SQL: SELECT count(*) FROM employee

Question: How many employees are there all together?
SQL: SELECT count(*) FROM employee

Question: How many professors do have a Ph.D. degree?
SQL: SELECT count(*) FROM professor WHERE prof_high_degree  =  'Ph.D.'

Question: What is the total number of professors with a Ph.D. ?
SQL: SELECT count(*) FROM professor WHERE prof_high_degree  =  'Ph.D.'

Question: How many students are enrolled in the class taught by some professor from the accounting department?
SQL: SELECT count(*) FROM CLASS AS T1 JOIN enroll AS T2 ON T1.class_code  =  T2.class_code JOIN course AS T3 ON T1.crs_code  =  T3.crs_code JOIN department AS T4 ON T3.dept_code  =  T4.dept_code WHERE T4.dept_name  =  'Accounting'

Question: How many students are enrolled in some classes that are taught by an accounting professor?
SQL: SELECT count(*) FROM CLASS AS T1 JOIN enroll AS T2 ON T1.class_code  =  T2.class_code JOIN course AS T3 ON T1.crs_code  =  T3.crs_code JOIN department AS T4 ON T3.dept_code  =  T4.dept_code WHERE T4.dept_name  =  'Accounting'

Question: What is the name of the department that has the largest number of students enrolled?
SQL: SELECT T4.dept_name FROM CLASS AS T1 JOIN enroll AS T2 ON T1.class_code  =  T2.class_code JOIN course AS T3 ON T1.crs_code  =  T3.crs_code JOIN department AS T4 ON T3.dept_code  =  T4.dept_code GROUP BY T3.dept_code ORDER BY count(*) DESC LIMIT 1

Question: What is the name of the department with the most students enrolled?
SQL: SELECT T4.dept_name FROM CLASS AS T1 JOIN enroll AS T2 ON T1.class_code  =  T2.class_code JOIN course AS T3 ON T1.crs_code  =  T3.crs_code JOIN department AS T4 ON T3.dept_code  =  T4.dept_code GROUP BY T3.dept_code ORDER BY count(*) DESC LIMIT 1

Question: list names of all departments ordered by their names.
SQL: SELECT dept_name FROM department ORDER BY dept_name

Question: What are the names of all departments in alphabetical order?
SQL: SELECT dept_name FROM department ORDER BY dept_name

Question: List the codes of all courses that take place in room KLR209.
SQL: SELECT class_code FROM CLASS WHERE class_room  =  'KLR209'

Question: What are the codes of all the courses that are located in room KLR209?
SQL: SELECT class_code FROM CLASS WHERE class_room  =  'KLR209'

Question: List the first name of all employees with job code PROF ordered by their date of birth.
SQL: SELECT emp_fname FROM employee WHERE emp_jobcode  =  'PROF' ORDER BY emp_dob

Question: What are the first names of all employees that are professors ordered by date of birth?
SQL: SELECT emp_fname FROM employee WHERE emp_jobcode  =  'PROF' ORDER BY emp_dob

Question: Find the first names and offices of all professors sorted by alphabetical order of their first name.
SQL: 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

Question: What are the first names and office locations for all professors sorted alphabetically by first name?
SQL: 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

Question: What is the first and last name of the oldest employee?
SQL: SELECT emp_fname ,  emp_lname FROM employee ORDER BY emp_dob LIMIT 1

Question: What are the first and last names of the employee with the earliest date of birth?
SQL: SELECT emp_fname ,  emp_lname FROM employee ORDER BY emp_dob LIMIT 1

Question: What is the first, last name, gpa of the youngest one among students whose GPA is above 3?
SQL: SELECT stu_fname ,  stu_lname ,  stu_gpa FROM student WHERE stu_gpa  >  3 ORDER BY stu_dob DESC LIMIT 1

Question: What is the first and last name of the youngest student with a GPA above 3, and what is their GPA?
SQL: SELECT stu_fname ,  stu_lname ,  stu_gpa FROM student WHERE stu_gpa  >  3 ORDER BY stu_dob DESC LIMIT 1

Question: What is the first name of students who got grade C in any class?
SQL: SELECT DISTINCT stu_fname FROM student AS T1 JOIN enroll AS T2 ON T1.stu_num  =  T2.stu_num WHERE enroll_grade  =  'C'

Question: What are the first names of all students who got a grade C in a class?
SQL: SELECT DISTINCT stu_fname FROM student AS T1 JOIN enroll AS T2 ON T1.stu_num  =  T2.stu_num WHERE enroll_grade  =  'C'

Question: What is the name of department where has the smallest number of professors?
SQL: SELECT T2.dept_name FROM professor AS T1 JOIN department AS T2 ON T1.dept_code  =  T2.dept_code GROUP BY T1.dept_code ORDER BY count(*) LIMIT 1

Question: What is the name of the department with the fewest professors?
SQL: SELECT T2.dept_name FROM professor AS T1 JOIN department AS T2 ON T1.dept_code  =  T2.dept_code GROUP BY T1.dept_code ORDER BY count(*) LIMIT 1

Question: What is the name of department where has the largest number of professors with a Ph.D. degree?
SQL: SELECT T2.dept_name ,  T1.dept_code FROM professor AS T1 JOIN department AS T2 ON T1.dept_code  =  T2.dept_code WHERE T1.prof_high_degree  =  'Ph.D.' GROUP BY T1.dept_code ORDER BY count(*) DESC LIMIT 1