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
How many hours do the students spend studying in each department?

### SQL
SELECT sum(stu_hrs) ,  dept_code FROM student GROUP BY dept_code
### 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 max, average, and minimum gpa of all students in each department.

### SQL
SELECT max(stu_gpa) ,  avg(stu_gpa) ,  min(stu_gpa) ,  dept_code FROM student GROUP BY dept_code
### 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 highest, lowest, and average student GPA for every department?

### SQL
SELECT max(stu_gpa) ,  avg(stu_gpa) ,  min(stu_gpa) ,  dept_code FROM student GROUP BY dept_code
### 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 and the average gpa of department whose students have the highest average gpa?

### SQL
SELECT T2.dept_name ,  avg(T1.stu_gpa) FROM student AS T1 JOIN department AS T2 ON T1.dept_code  =  T2.dept_code GROUP BY T1.dept_code ORDER BY avg(T1.stu_gpa) 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
Which department has the highest average student GPA, and what is the average gpa?

### SQL
SELECT T2.dept_name ,  avg(T1.stu_gpa) FROM student AS T1 JOIN department AS T2 ON T1.dept_code  =  T2.dept_code GROUP BY T1.dept_code ORDER BY avg(T1.stu_gpa) 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
how many schools exist in total?

### SQL
SELECT count(DISTINCT school_code) FROM department
### 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 schools are there in the department?

### SQL
SELECT count(DISTINCT school_code) FROM department
### 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 different classes are there?

### SQL
SELECT count(DISTINCT class_code) FROM CLASS
### 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 unique classes are offered?

### SQL
SELECT count(DISTINCT class_code) FROM CLASS
### 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 courses are offered?

### SQL
SELECT count(DISTINCT crs_code) FROM CLASS
### 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 number of different course codes?

### SQL
SELECT count(DISTINCT crs_code) FROM CLASS
### 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 departments does the college has?

### SQL
SELECT count(DISTINCT dept_name) FROM department
### 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 different departments are there?

### SQL
SELECT count(DISTINCT dept_name) FROM department
### 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 courses are offered by the Computer Info. Systems department?

### SQL
SELECT count(*) FROM department AS T1 JOIN course AS T2 ON T1.dept_code  =  T2.dept_code WHERE 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
How many courses does the department of Computer Information Systmes offer?

### SQL
SELECT count(*) FROM department AS T1 JOIN course AS T2 ON T1.dept_code  =  T2.dept_code WHERE 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
How many sections does course ACCT-211 has?

### SQL
SELECT count(DISTINCT class_section) FROM CLASS WHERE crs_code  =  'ACCT-211'
### 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 different class sections offered in the course ACCT-211?

### SQL
SELECT count(DISTINCT class_section) FROM CLASS WHERE crs_code  =  'ACCT-211'
### 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 total credits of all classes offered by each department.

### SQL
SELECT sum(T1.crs_credit) ,  T1.dept_code FROM course AS T1 JOIN CLASS AS T2 ON T1.crs_code  =  T2.crs_code GROUP BY T1.dept_code
### 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 total number of credits offered by each department?

### SQL
SELECT sum(T1.crs_credit) ,  T1.dept_code FROM course AS T1 JOIN CLASS AS T2 ON T1.crs_code  =  T2.crs_code GROUP BY T1.dept_code
### 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 name of the department that offers the largest number of credits of all classes.

### SQL
SELECT T3.dept_name FROM course AS T1 JOIN CLASS AS T2 ON T1.crs_code  =  T2.crs_code JOIN department AS T3 ON T1.dept_code  =  T3.dept_code GROUP BY T1.dept_code ORDER BY sum(T1.crs_credit) 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
Which department offers the most credits all together?

### SQL
SELECT T3.dept_name FROM course AS T1 JOIN CLASS AS T2 ON T1.crs_code  =  T2.crs_code JOIN department AS T3 ON T1.dept_code  =  T3.dept_code GROUP BY T1.dept_code ORDER BY sum(T1.crs_credit) 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
How many students enrolled in class ACCT-211?

### SQL
SELECT count(*) FROM CLASS AS T1 JOIN enroll AS T2 ON T1.class_code  =  T2.class_code WHERE T1.crs_code  =  'ACCT-211'
### 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 total number of students enrolled in ACCT-211?

### SQL
SELECT count(*) FROM CLASS AS T1 JOIN enroll AS T2 ON T1.class_code  =  T2.class_code WHERE T1.crs_code  =  'ACCT-211'
### 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 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'
### 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 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'
### 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 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'