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 is the course description and number of credits for QM-261?

### SQL
SELECT crs_credit ,  crs_description FROM course WHERE crs_code  =  'QM-261'
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

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

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

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

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

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

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

### Question
Find the number of departments in each school.

### SQL
SELECT count(DISTINCT dept_name) ,  school_code FROM department GROUP BY school_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
How many departments are in each school?

### SQL
SELECT count(DISTINCT dept_name) ,  school_code FROM department GROUP BY school_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 number of different departments in each school whose number of different departments is less than 5.

### SQL
SELECT count(DISTINCT dept_name) ,  school_code FROM department GROUP BY school_code HAVING count(DISTINCT dept_name)  <  5
### 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 in each school that has less than 5 apartments?

### SQL
SELECT count(DISTINCT dept_name) ,  school_code FROM department GROUP BY school_code HAVING count(DISTINCT dept_name)  <  5
### 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 each course has?

### SQL
SELECT count(*) ,  crs_code FROM CLASS GROUP BY crs_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
How many sections does each course have?

### SQL
SELECT count(*) ,  crs_code FROM CLASS GROUP BY crs_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 total credit does each department offer?

### SQL
SELECT sum(crs_credit) ,  dept_code FROM course 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
How many credits does the department offer?

### SQL
SELECT sum(crs_credit) ,  dept_code FROM course 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 number of classes offered for all class rooms that held at least 2 classes.

### SQL
SELECT count(*) ,  class_room FROM CLASS GROUP BY class_room HAVING count(*)  >=  2
### 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
For each classroom with at least 2 classes, how many classes are offered?

### SQL
SELECT count(*) ,  class_room FROM CLASS GROUP BY class_room HAVING count(*)  >=  2
### 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 number of classes in each department.

### SQL
SELECT count(*) ,  dept_code FROM CLASS AS T1 JOIN course AS T2 ON T1.crs_code  =  T2.crs_code GROUP BY dept_code
### 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 classes are held in each department?

### SQL
SELECT count(*) ,  dept_code FROM CLASS AS T1 JOIN course AS T2 ON T1.crs_code  =  T2.crs_code GROUP BY dept_code
### 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 number of classes in each school.

### SQL
SELECT count(*) ,  T3.school_code FROM CLASS AS T1 JOIN course AS T2 ON T1.crs_code  =  T2.crs_code JOIN department AS T3 ON T2.dept_code  =  T3.dept_code GROUP BY T3.school_code
### 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 classes exist for each school?

### SQL
SELECT count(*) ,  T3.school_code FROM CLASS AS T1 JOIN course AS T2 ON T1.crs_code  =  T2.crs_code JOIN department AS T3 ON T2.dept_code  =  T3.dept_code GROUP BY T3.school_code
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

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

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

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

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

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

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

### Question
What is the number of professors for different school?

### SQL
SELECT count(*) ,  T1.school_code FROM department AS T1 JOIN professor AS T2 ON T1.dept_code  =  T2.dept_code GROUP BY T1.school_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
How many different professors are there for the different schools?

### SQL
SELECT count(*) ,  T1.school_code FROM department AS T1 JOIN professor AS T2 ON T1.dept_code  =  T2.dept_code GROUP BY T1.school_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 count and code of the job has most employees.

### SQL
SELECT emp_jobcode ,  count(*) FROM employee GROUP BY emp_jobcode 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 count and code of the job with the most employee?

### SQL
SELECT emp_jobcode ,  count(*) FROM employee GROUP BY emp_jobcode 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
Which school has the smallest amount of professors?

### SQL
SELECT T1.school_code FROM department AS T1 JOIN professor AS T2 ON T1.dept_code  =  T2.dept_code GROUP BY T1.school_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
Which school has the fewest professors?

### SQL
SELECT T1.school_code FROM department AS T1 JOIN professor AS T2 ON T1.dept_code  =  T2.dept_code GROUP BY T1.school_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
Find the number of professors with a Ph.D. degree in each department.

### SQL
SELECT count(*) ,  dept_code FROM professor WHERE prof_high_degree  =  'Ph.D.' 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
How many professors have a Ph.D. in each department?

### SQL
SELECT count(*) ,  dept_code FROM professor WHERE prof_high_degree  =  'Ph.D.' 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 number of students for each department.

### SQL
SELECT count(*) ,  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
How many students are in each department?

### SQL
SELECT count(*) ,  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 total number of hours have done for all students in each department.

### SQL
SELECT sum(stu_hrs) ,  dept_code FROM student GROUP BY dept_code