lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
Which department has the most professors with a Ph.D.?

### 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
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
What are the first names of the professors who do not teach a class.

### SQL
SELECT emp_fname FROM employee WHERE emp_jobcode  =  'PROF' EXCEPT SELECT T1.emp_fname FROM employee AS T1 JOIN CLASS AS T2 ON T1.emp_num  =  T2.prof_num
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
What are the first names of all professors not teaching any classes?

### SQL
SELECT emp_fname FROM employee WHERE emp_jobcode  =  'PROF' EXCEPT SELECT T1.emp_fname FROM employee AS T1 JOIN CLASS AS T2 ON T1.emp_num  =  T2.prof_num
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
What is the first names of the professors from the history department who do not teach a class.

### SQL
SELECT T1.emp_fname FROM employee AS T1 JOIN professor AS T2 ON T1.emp_num  =  T2.emp_num JOIN department AS T3 ON T2.dept_code  =  T3.dept_code WHERE T3.dept_name  =  'History' EXCEPT SELECT T4.emp_fname FROM employee AS T4 JOIN CLASS AS T5 ON T4.emp_num  =  T5.prof_num
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
What are the first names of all history professors who do not teach?

### SQL
SELECT T1.emp_fname FROM employee AS T1 JOIN professor AS T2 ON T1.emp_num  =  T2.emp_num JOIN department AS T3 ON T2.dept_code  =  T3.dept_code WHERE T3.dept_name  =  'History' EXCEPT SELECT T4.emp_fname FROM employee AS T4 JOIN CLASS AS T5 ON T4.emp_num  =  T5.prof_num
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
What is the last name and office of the professor from the history department?

### SQL
SELECT T1.emp_lname ,  T2.prof_office FROM employee AS T1 JOIN professor AS T2 ON T1.emp_num  =  T2.emp_num JOIN department AS T3 ON T2.dept_code  =  T3.dept_code WHERE T3.dept_name  =  'History'
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
What are the last name and office of all history professors?

### SQL
SELECT T1.emp_lname ,  T2.prof_office FROM employee AS T1 JOIN professor AS T2 ON T1.emp_num  =  T2.emp_num JOIN department AS T3 ON T2.dept_code  =  T3.dept_code WHERE T3.dept_name  =  'History'
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
What is department name and office for the professor whose last name is Heffington?

### SQL
SELECT T3.dept_name  ,  T2.prof_office FROM employee AS T1 JOIN professor AS T2 ON T1.emp_num  =  T2.emp_num JOIN department AS T3 ON T2.dept_code  =  T3.dept_code WHERE T1.emp_lname  =  'Heffington'
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
What is the name of the department and office location for the professor with the last name of Heffington?

### SQL
SELECT T3.dept_name  ,  T2.prof_office FROM employee AS T1 JOIN professor AS T2 ON T1.emp_num  =  T2.emp_num JOIN department AS T3 ON T2.dept_code  =  T3.dept_code WHERE T1.emp_lname  =  'Heffington'
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
Find the last name and hire date of the professor who is in office DRE 102.

### SQL
SELECT T1.emp_lname ,  T1.emp_hiredate FROM employee AS T1 JOIN professor AS T2 ON T1.emp_num  =  T2.emp_num WHERE T2.prof_office  =  'DRE 102'
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
What is the last name of the professor whose office is located in DRE 102, and when were they hired?

### SQL
SELECT T1.emp_lname ,  T1.emp_hiredate FROM employee AS T1 JOIN professor AS T2 ON T1.emp_num  =  T2.emp_num WHERE T2.prof_office  =  'DRE 102'
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
What is the code of the course which the student whose last name is Smithson took?

### SQL
SELECT T1.crs_code 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 WHERE T3.stu_lname  =  'Smithson'
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
What are the course codes for every class that the student with the last name Smithson took?

### SQL
SELECT T1.crs_code 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 WHERE T3.stu_lname  =  'Smithson'
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
What are the description and credit of the course which the student whose last name is Smithson took?

### SQL
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'
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
How many credits is the course that the student with the last name Smithson took, and what is its description?

### SQL
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'
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
How many professors who has a either Ph.D. or MA degree?

### SQL
SELECT count(*) FROM professor WHERE prof_high_degree  =  'Ph.D.' OR prof_high_degree  =  'MA'
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
How many professors attained either Ph.D. or Masters degrees?

### SQL
SELECT count(*) FROM professor WHERE prof_high_degree  =  'Ph.D.' OR prof_high_degree  =  'MA'
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
How many professors who are from either Accounting or Biology department?

### SQL
SELECT count(*) FROM professor AS T1 JOIN department AS T2 ON T1.dept_code  =  T2.dept_code WHERE T2.dept_name  =  'Accounting' OR T2.dept_name  =  'Biology'
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
What is the number of professors who are in the Accounting or Biology departments?

### SQL
SELECT count(*) FROM professor AS T1 JOIN department AS T2 ON T1.dept_code  =  T2.dept_code WHERE T2.dept_name  =  'Accounting' OR T2.dept_name  =  'Biology'
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
Find the first name of the professor who is teaching two courses with code CIS-220 and QM-261.

### SQL
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'
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
What is the first name of the professor who is teaching CIS-220 and QM-261?

### SQL
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'
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
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'
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### 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'
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### 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'