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 number of professors in accounting department.
SQL: SELECT count(*) FROM professor AS T1 JOIN department AS T2 ON T1.dept_code  =  T2.dept_code WHERE DEPT_NAME  =  "Accounting"

Question: How many professors are in the accounting dept?
SQL: SELECT count(*) FROM professor AS T1 JOIN department AS T2 ON T1.dept_code  =  T2.dept_code WHERE DEPT_NAME  =  "Accounting"

Question: How many professors are teaching class with code ACCT-211?
SQL: SELECT count(DISTINCT PROF_NUM) FROM CLASS WHERE CRS_CODE  =  "ACCT-211"

Question: How many professors teach a class with the code ACCT-211?
SQL: SELECT count(DISTINCT PROF_NUM) FROM CLASS WHERE CRS_CODE  =  "ACCT-211"

Question: What is the first and last name of the professor in biology department?
SQL: SELECT T3.EMP_FNAME ,  T3.EMP_LNAME FROM professor AS T1 JOIN department AS T2 ON T1.dept_code  =  T2.dept_code JOIN employee AS T3 ON T1.EMP_NUM  =  T3.EMP_NUM WHERE DEPT_NAME  =  "Biology"

Question: What are the first and last name of all biology professors?
SQL: SELECT T3.EMP_FNAME ,  T3.EMP_LNAME FROM professor AS T1 JOIN department AS T2 ON T1.dept_code  =  T2.dept_code JOIN employee AS T3 ON T1.EMP_NUM  =  T3.EMP_NUM WHERE DEPT_NAME  =  "Biology"

Question: What are the first names and date of birth of professors teaching course ACCT-211?
SQL: SELECT DISTINCT T1.EMP_FNAME ,  T1.EMP_DOB FROM employee AS T1 JOIN CLASS AS T2 ON T1.EMP_NUM  =  T2.PROF_NUM WHERE CRS_CODE  =  "ACCT-211"

Question: What are the first names and birthdates of the professors in charge of ACCT-211?
SQL: SELECT DISTINCT T1.EMP_FNAME ,  T1.EMP_DOB FROM employee AS T1 JOIN CLASS AS T2 ON T1.EMP_NUM  =  T2.PROF_NUM WHERE CRS_CODE  =  "ACCT-211"

Question: How many classes are professor whose last name is Graztevski has?
SQL: SELECT count(*) FROM employee AS T1 JOIN CLASS AS T2 ON T1.EMP_NUM  =  T2.PROF_NUM WHERE T1.EMP_LNAME  =  'Graztevski'

Question: How many classes does the professor whose last name is Graztevski teach?
SQL: SELECT count(*) FROM employee AS T1 JOIN CLASS AS T2 ON T1.EMP_NUM  =  T2.PROF_NUM WHERE T1.EMP_LNAME  =  'Graztevski'

Question: What is the code of the school where the accounting department belongs to?
SQL: SELECT school_code FROM department WHERE dept_name  =  "Accounting"

Question: What is the school code of the accounting department?
SQL: SELECT school_code FROM department WHERE dept_name  =  "Accounting"

Question: How many credits does course CIS-220 have, and what its description?
SQL: SELECT crs_credit ,  crs_description FROM course WHERE crs_code  =  'CIS-220'

Question: What is the description for the CIS-220 and how many credits does it have?
SQL: SELECT crs_credit ,  crs_description FROM course WHERE crs_code  =  'CIS-220'

Question: what is the address of history department?
SQL: SELECT dept_address FROM department WHERE dept_name  =  'History'

Question: Where is the history department?
SQL: SELECT dept_address FROM department WHERE dept_name  =  'History'

Question: How many different locations does the school with code BUS has?
SQL: SELECT count(DISTINCT dept_address) FROM department WHERE school_code  =  'BUS'

Question: What are the different locations of the school with the code BUS?
SQL: SELECT count(DISTINCT dept_address) FROM department WHERE school_code  =  'BUS'

Question: How many different locations does each school have?
SQL: SELECT count(DISTINCT dept_address) ,  school_code FROM department GROUP BY school_code

Question: Count different addresses of each school.
SQL: SELECT count(DISTINCT dept_address) ,  school_code FROM department GROUP BY school_code

Question: Find the description and credit for the course QM-261?
SQL: SELECT crs_credit ,  crs_description FROM course WHERE crs_code  =  'QM-261'

Question: What is the course description and number of credits for QM-261?
SQL: SELECT crs_credit ,  crs_description FROM course WHERE crs_code  =  'QM-261'

Question: Find the number of departments in each school.
SQL: SELECT count(DISTINCT dept_name) ,  school_code FROM department GROUP BY school_code

Question: How many departments are in each school?
SQL: SELECT count(DISTINCT dept_name) ,  school_code FROM department GROUP BY school_code

Question: Find the number of different departments in each school whose number of different departments is less than 5.
SQL: SELECT count(DISTINCT dept_name) ,  school_code FROM department GROUP BY school_code HAVING count(DISTINCT dept_name)  <  5

Question: How many different departments are there in each school that has less than 5 apartments?
SQL: SELECT count(DISTINCT dept_name) ,  school_code FROM department GROUP BY school_code HAVING count(DISTINCT dept_name)  <  5

Question: How many sections does each course has?
SQL: SELECT count(*) ,  crs_code FROM CLASS GROUP BY crs_code

Question: How many sections does each course have?
SQL: SELECT count(*) ,  crs_code FROM CLASS GROUP BY crs_code

Question: What is the total credit does each department offer?
SQL: SELECT sum(crs_credit) ,  dept_code FROM course GROUP BY dept_code

Question: How many credits does the department offer?
SQL: SELECT sum(crs_credit) ,  dept_code FROM course GROUP BY dept_code

Question: Find the number of classes offered for all class rooms that held at least 2 classes.
SQL: SELECT count(*) ,  class_room FROM CLASS GROUP BY class_room HAVING count(*)  >=  2

Question: For each classroom with at least 2 classes, how many classes are offered?
SQL: SELECT count(*) ,  class_room FROM CLASS GROUP BY class_room HAVING count(*)  >=  2

Question: Find the number of classes in each department.
SQL: SELECT count(*) ,  dept_code FROM CLASS AS T1 JOIN course AS T2 ON T1.crs_code  =  T2.crs_code GROUP BY dept_code

Question: How many classes are held in each department?
SQL: SELECT count(*) ,  dept_code FROM CLASS AS T1 JOIN course AS T2 ON T1.crs_code  =  T2.crs_code GROUP BY dept_code

Question: Find the number of classes in each school.
SQL: SELECT count(*) ,  T3.school_code FROM CLASS AS T1 JOIN course AS T2 ON T1.crs_code  =  T2.crs_code JOIN department AS T3 ON T2.dept_code  =  T3.dept_code GROUP BY T3.school_code