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
What are the first names of all students who took ACCT-211 and received a C?

### SQL
SELECT T3.stu_fname FROM CLASS AS T1 JOIN enroll AS T2 ON T1.class_code  =  T2.class_code JOIN student AS T3 ON T2.stu_num  =  T3.stu_num WHERE T1.crs_code  =  'ACCT-211' AND T2.enroll_grade  =  'C'
### 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 number of employees.

### SQL
SELECT count(*) FROM employee
### 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 employees are there all together?

### SQL
SELECT count(*) FROM employee
### 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 do have a Ph.D. degree?

### SQL
SELECT count(*) FROM professor WHERE prof_high_degree  =  'Ph.D.'
### 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 total number of professors with a Ph.D. ?

### SQL
SELECT count(*) FROM professor WHERE prof_high_degree  =  'Ph.D.'
### 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 are enrolled in the class taught by some professor from the accounting department?

### SQL
SELECT count(*) FROM CLASS AS T1 JOIN enroll AS T2 ON T1.class_code  =  T2.class_code JOIN course AS T3 ON T1.crs_code  =  T3.crs_code JOIN department AS T4 ON T3.dept_code  =  T4.dept_code WHERE T4.dept_name  =  'Accounting'
### 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 are enrolled in some classes that are taught by an accounting professor?

### SQL
SELECT count(*) FROM CLASS AS T1 JOIN enroll AS T2 ON T1.class_code  =  T2.class_code JOIN course AS T3 ON T1.crs_code  =  T3.crs_code JOIN department AS T4 ON T3.dept_code  =  T4.dept_code WHERE T4.dept_name  =  'Accounting'
### 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 that has the largest number of students enrolled?

### SQL
SELECT T4.dept_name FROM CLASS AS T1 JOIN enroll AS T2 ON T1.class_code  =  T2.class_code JOIN course AS T3 ON T1.crs_code  =  T3.crs_code JOIN department AS T4 ON T3.dept_code  =  T4.dept_code GROUP BY T3.dept_code ORDER BY count(*) DESC LIMIT 1
### 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 with the most students enrolled?

### SQL
SELECT T4.dept_name FROM CLASS AS T1 JOIN enroll AS T2 ON T1.class_code  =  T2.class_code JOIN course AS T3 ON T1.crs_code  =  T3.crs_code JOIN department AS T4 ON T3.dept_code  =  T4.dept_code GROUP BY T3.dept_code ORDER BY count(*) DESC LIMIT 1
### 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
list names of all departments ordered by their names.

### SQL
SELECT dept_name FROM department ORDER BY dept_name
### 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 names of all departments in alphabetical order?

### SQL
SELECT dept_name FROM department ORDER BY dept_name
### 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
List the codes of all courses that take place in room KLR209.

### SQL
SELECT class_code FROM CLASS WHERE class_room  =  'KLR209'
### 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 codes of all the courses that are located in room KLR209?

### SQL
SELECT class_code FROM CLASS WHERE class_room  =  'KLR209'
### 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
List the first name of all employees with job code PROF ordered by their date of birth.

### SQL
SELECT emp_fname FROM employee WHERE emp_jobcode  =  'PROF' ORDER BY emp_dob
### 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 employees that are professors ordered by date of birth?

### SQL
SELECT emp_fname FROM employee WHERE emp_jobcode  =  'PROF' ORDER BY emp_dob
### 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 names and offices of all professors sorted by alphabetical order of their first name.

### SQL
SELECT T2.emp_fname ,  T1.prof_office FROM professor AS T1 JOIN employee AS T2 ON T1.emp_num  =  T2.emp_num ORDER BY T2.emp_fname
### 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 and office locations for all professors sorted alphabetically by first name?

### SQL
SELECT T2.emp_fname ,  T1.prof_office FROM professor AS T1 JOIN employee AS T2 ON T1.emp_num  =  T2.emp_num ORDER BY T2.emp_fname
### 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 and last name of the oldest employee?

### SQL
SELECT emp_fname ,  emp_lname FROM employee ORDER BY emp_dob 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 and last names of the employee with the earliest date of birth?

### SQL
SELECT emp_fname ,  emp_lname FROM employee ORDER BY emp_dob LIMIT 1
### 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, last name, gpa of the youngest one among students whose GPA is above 3?

### SQL
SELECT stu_fname ,  stu_lname ,  stu_gpa FROM student WHERE stu_gpa  >  3 ORDER BY stu_dob DESC LIMIT 1
### 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 and last name of the youngest student with a GPA above 3, and what is their GPA?

### SQL
SELECT stu_fname ,  stu_lname ,  stu_gpa FROM student WHERE stu_gpa  >  3 ORDER BY stu_dob DESC LIMIT 1
### 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 who got grade C in any class?

### SQL
SELECT DISTINCT stu_fname FROM student AS T1 JOIN enroll AS T2 ON T1.stu_num  =  T2.stu_num WHERE enroll_grade  =  'C'
### 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 who got a grade C in a class?

### SQL
SELECT DISTINCT stu_fname FROM student AS T1 JOIN enroll AS T2 ON T1.stu_num  =  T2.stu_num WHERE enroll_grade  =  'C'
### 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 department where has the smallest number of professors?

### SQL
SELECT T2.dept_name FROM professor AS T1 JOIN department AS T2 ON T1.dept_code  =  T2.dept_code GROUP BY T1.dept_code ORDER BY count(*) LIMIT 1
### 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 with the fewest professors?

### SQL
SELECT T2.dept_name FROM professor AS T1 JOIN department AS T2 ON T1.dept_code  =  T2.dept_code GROUP BY T1.dept_code ORDER BY count(*) LIMIT 1
### 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 department where has the largest number of professors with a Ph.D. degree?

### SQL
SELECT T2.dept_name ,  T1.dept_code FROM professor AS T1 JOIN department AS T2 ON T1.dept_code  =  T2.dept_code WHERE T1.prof_high_degree  =  'Ph.D.' GROUP BY T1.dept_code ORDER BY count(*) DESC LIMIT 1