sql PL

4-7-276 SQL.sql

/*Read in the count (into a scalar variable) of the number of students registered in section 44172 of CS 276 
If the count of students is greater than 15
output the title of the class
the number of students enrolled
A message saying a waitlist has been started
If the count of students is less than 7, 
output the title of the class
the number of students enrolled
a message saying the class needs to be canceled
If the count is greater than or equal to 7 and less than or equal to 15 
output the title of the class 
the number of students enrolled 
a message saying the class can continue
*/

drop table teacher cascade constraints;
drop table student cascade constraints;
drop table student_type cascade constraints;
drop table class cascade constraints;
drop table subject cascade constraints;
drop table term cascade constraints;
drop table section cascade constraints;
drop table enrollment cascade constraints;
drop table department cascade constraints;
drop table trainroute cascade constraints;
drop table trainstop cascade constraints;
drop table trainstation cascade constraints;
drop table freshman cascade constraints;
drop table sophomore cascade constraints;
drop table junior cascade constraints;
drop table senior cascade constraints;
drop table other_level cascade constraints;
drop table house_sale cascade constraints;
drop table agent cascade constraints;

create table charges
(charge_id    varchar(10)  PRIMARY KEY,
customer_id   varchar(10), 
order_id      varchar(10),
last_payment_date   date, 
current_balance   decimal(10,2));

insert into charges values ('1','1','1',DATEADD(DAY, -30, GETDATE()), 50.00 );
insert into charges values ('2','2','2',DATEADD(DAY, -40, GETDATE()), 60.00 );
insert into charges values ('3','3','3',DATEADD(DAY, -50, GETDATE()), 70.00 );
insert into charges values ('4','4','4',DATEADD(DAY, -60, GETDATE()), 80.00 );
insert into charges values ('5','5','5',DATEADD(DAY, -70, GETDATE()), 90.00 );
insert into charges values ('6','6','6',DATEADD(DAY, -80, GETDATE()), 100.00 );
insert into charges values ('7','7','7',DATEADD(DAY, -90, GETDATE()), 120.00 );
insert into charges values ('8','8','8',DATEADD(DAY, -100, GETDATE()), 130.00 );
insert into charges values ('9','9','9',DATEADD(DAY, -120, GETDATE()), 140.00 );
insert into charges values ('10','10','10',DATEADD(DAY, -130, GETDATE()), 150.00 );

create table house_sale
(sale_id        number  PRIMARY KEY,
fk_house_id        number  NOT NULL,
fk_owner_id        number  NOT NULL,
fk_agent_id     number  NOT NULL,
sale_price      number(15,5));

create table agent
(agent_id       number  PRIMARY KEY,
agent_first_name    varchar2(25)    NOT NULL,
agent_last_name     varchar2(25)    NOT NULL,
agent_level         varchar2(1));

alter table house_sale 
add constraint fk_agent_id
foreign key (fk_agent_id)
references agent(agent_id);

create table trainroute
(routenumber_id   number,
route_name  varchar2(50),
route_cost  decimal(8,2));

create table trainstop
(stopnumber_id  number,
routenumber_id  number,
stationnumber_id number,
arrive_time date,
depart_time date);

create table trainstation
(stationnumber_id number,
station_name  varchar2(50),
station_city  varchar2(50));

CREATE TABLE TEACHER
(TEACHER_ID   VARCHAR2(10) PRIMARY KEY,
TEACHER_FIRST_NAME   VARCHAR2(20)  NOT NULL,
TEACHER_LAST_NAME	VARCHAR2(30) NOT NULL,
SALARY			DECIMAL(8,2) NOT NULL,
FK_DEPARTMENT_ID		VARCHAR2(4));

CREATE TABLE DEPARTMENT
(DEPARTMENT_ID		VARCHAR2(4) PRIMARY KEY,
DEPARTMENT_NAME		VARCHAR2(30) NOT NULL);

CREATE TABLE STUDENT
(STUDENT_ID    varchar2(10) PRIMARY KEY,
STUDENT_FIRST_NAME   VARCHAR2(20)   NOT NULL,
STUDENT_LAST_NAME	VARCHAR2(20)	NOT NULL,
FK_STUDENT_TYPE_ID	VARCHAR2(1)	NOT NULL);

CREATE TABLE FRESHMAN
(STUDENT_ID    varchar2(10) PRIMARY KEY,
STUDENT_FIRST_NAME   VARCHAR2(20)   NOT NULL,
STUDENT_LAST_NAME	VARCHAR2(20)	NOT NULL);

CREATE TABLE SOPHOMORE
(STUDENT_ID    varchar2(10) PRIMARY KEY,
STUDENT_FIRST_NAME   VARCHAR2(20)   NOT NULL,
STUDENT_LAST_NAME	VARCHAR2(20)	NOT NULL);

CREATE TABLE JUNIOR
(STUDENT_ID    varchar2(10) PRIMARY KEY,
STUDENT_FIRST_NAME   VARCHAR2(20)   NOT NULL,
STUDENT_LAST_NAME	VARCHAR2(20)	NOT NULL);

CREATE TABLE SENIOR
(STUDENT_ID    varchar2(10) PRIMARY KEY,
STUDENT_FIRST_NAME   VARCHAR2(20)   NOT NULL,
STUDENT_LAST_NAME	VARCHAR2(20)	NOT NULL);

CREATE TABLE OTHER_LEVEL
(STUDENT_ID    varchar2(10) PRIMARY KEY,
STUDENT_FIRST_NAME   VARCHAR2(20)   NOT NULL,
STUDENT_LAST_NAME	VARCHAR2(20)	NOT NULL);

CREATE TABLE STUDENT_TYPE
(STUDENT_TYPE_ID	VARCHAR2(1)	PRIMARY KEY,
STUDENT_TYPE_DESC	VARCHAR2(20)	NOT NULL);

CREATE TABLE CLASS
(CLASS_NUMBER		VARCHAR2(8)	PRIMARY KEY,
CLASS_NAME		VARCHAR2(30)	NOT NULL,
FK_SUBJECT_ID		VARCHAR2(1)	NOT NULL);

CREATE TABLE SUBJECT
(SUBJECT_ID		VARCHAR2(10)	PRIMARY KEY,
SUBJECT_NAME		VARCHAR2(20)	NOT NULL);

CREATE TABLE TERM
(TERM_ID		VARCHAR2(8)	PRIMARY KEY,
TERM_DESC		VARCHAR2(30)	NOT NULL,
TERM_START_DATE		DATE		NOT NULL,
TERM_END_DATE		DATE		NOT NULL);

CREATE TABLE SECTION
(SECTION_ID		NUMBER		PRIMARY KEY,
AVAILABLE_ENROLLMENT	NUMBER,
LOCATION		VARCHAR2(8)	NOT NULL,
FK_TEACHER_ID		VARCHAR2(10),
FK_CLASS_NUMBER		VARCHAR2(8)	NOT NULL,
FK_TERM_ID		VARCHAR2(8)	NOT NULL);

CREATE TABLE ENROLLMENT
(ENROLLMENT_ID		VARCHAR2(6)	PRIMARY KEY,
FK_STUDENT_ID		VARCHAR2(10)	NOT NULL,
FK_SECTION_ID		NUMBER		NOT NULL,
GRADE			VARCHAR2(4));

alter table teacher
add constraint fk_department
foreign key (fk_department_id)
references department(department_id);

alter table student
add constraint fk_student_type
foreign key (fk_student_type_id)
references student_type(student_type_id);

alter table class
add constraint fk_subject
foreign key (fk_subject_id)
references subject(subject_id);

alter table section
add constraint fk_teacher
foreign key (fk_teacher_id)
references teacher (teacher_id);

alter table section
add constraint fk_class
foreign key (fk_class_number)
references class(class_number);

alter table enrollment
add constraint fk_student
foreign key (fk_student_id)
references student(student_id);

alter table enrollment
add constraint fk_section
foreign key (fk_section_id)
references section(section_id);

alter table section
add constraint fk_term
foreign key (fk_term_id)
references term(term_id);

---- Inserts

INSERT INTO DEPARTMENT VALUES
('BUS','BUSINESS');

INSERT INTO DEPARTMENT VALUES
('CSI','COMPUTER SCIENCE');

INSERT INTO DEPARTMENT VALUES
('MTH','MATH');

INSERT INTO DEPARTMENT VALUES
('ENG','ENGLISH');

insert into agent
values
(200,'Ernest','Hemingway','L');

insert into agent
values
(201,'Ayn','Rand','M');

insert into house_sale
values
(100,100,100,200,150000);

insert into house_sale
values
(101,100,100,201,350000);


insert into teacher
(teacher_id, teacher_first_name, teacher_last_name,SALARY, fk_department_id)
values
('L0123456','PAMELA','FARR',75000,'CSI');
insert into teacher
(teacher_id, teacher_first_name, teacher_last_name,SALARY,fk_department_id)
values
('L0234567','JAMES','JOYCE',70000,'ENG');
insert into teacher
(teacher_id, teacher_first_name, teacher_last_name,SALARY,fk_department_id)
values
('L0345678','KAREN','TAMAKI',65000,'ENG');
insert into teacher
(teacher_id, teacher_first_name, teacher_last_name,SALARY,fk_department_id)
values
('L0456789','WILLIAM','RAY',60000,'CSI');
insert into teacher
(teacher_id, teacher_first_name, teacher_last_name,SALARY,fk_department_id)
values
('L0135791','JOHNATHAN','PRYCE',55000,'BUS');
insert into teacher
(teacher_id, teacher_first_name, teacher_last_name,SALARY,fk_department_id)
values
('L0248635','PATRICIA','LANSBURY',40000,'MTH');
insert into teacher
(teacher_id, teacher_first_name, teacher_last_name,SALARY,fk_department_id)
values
('L0367913','ROBERT','CALDER',50000,'CSI');
insert into teacher
(teacher_id, teacher_first_name, teacher_last_name,SALARY,fk_department_id)
values
('L0567890','JACOB','RIPLEY',45000,NULL);

INSERT INTO STUDENT_TYPE
(STUDENT_TYPE_ID,STUDENT_TYPE_DESC)
VALUES
('F','FRESHMAN');
INSERT INTO STUDENT_TYPE
(STUDENT_TYPE_ID,STUDENT_TYPE_DESC)
VALUES
('S','SOPHOMORE');

INSERT INTO SUBJECT
(SUBJECT_ID, SUBJECT_NAME)
VALUES
('C','COMPUTER SCIENCE');
INSERT INTO SUBJECT
(SUBJECT_ID, SUBJECT_NAME)
VALUES
('B','BUSINESS');
INSERT INTO SUBJECT
(SUBJECT_ID, SUBJECT_NAME)
VALUES
('M','MATH');
INSERT INTO SUBJECT
(SUBJECT_ID, SUBJECT_NAME)
VALUES
('E','ENGLISH');

INSERT INTO TERM
(TERM_ID, TERM_DESC, TERM_START_DATE, TERM_END_DATE)
VALUES
('201020','FALL 2017',CAST ('25-SEP-17' AS DATE), CAST ('09-DEC-17' AS DATE));
INSERT INTO TERM
(TERM_ID, TERM_DESC, TERM_START_DATE, TERM_END_DATE)
VALUES
('201030','WINTER 2018',CAST ('02-JAN-18' AS DATE), CAST ('24-MAR-18' AS DATE));
INSERT INTO TERM
(TERM_ID, TERM_DESC, TERM_START_DATE, TERM_END_DATE)
VALUES
('201040','SPRING 2010',CAST ('29-MAR-10' AS DATE), CAST ('11-JUN-10' AS DATE));
INSERT INTO TERM
(TERM_ID, TERM_DESC, TERM_START_DATE, TERM_END_DATE)
VALUES
('201120','FALL 2010',CAST ('27-SEP-10' AS DATE), CAST ('11-DEC-10' AS DATE));

INSERT INTO CLASS
(CLASS_NUMBER, CLASS_NAME, FK_SUBJECT_ID)
VALUES
('CS275','DATABASE DESIGN','C');
INSERT INTO CLASS
(CLASS_NUMBER, CLASS_NAME, FK_SUBJECT_ID)
VALUES
('MT250','CALCULUS','M');
INSERT INTO CLASS
(CLASS_NUMBER, CLASS_NAME, FK_SUBJECT_ID)
VALUES
('CS170','BEGINNING C#','C');
INSERT INTO CLASS
(CLASS_NUMBER, CLASS_NAME, FK_SUBJECT_ID)
VALUES
('CS133P','BEGINNING PYTHON','C');
INSERT INTO CLASS
(CLASS_NUMBER, CLASS_NAME, FK_SUBJECT_ID)
VALUES
('CS276','DATA WAREHOUSING','C');
INSERT INTO CLASS
(CLASS_NUMBER, CLASS_NAME, FK_SUBJECT_ID)
VALUES
('BT165','ACCOUNTING 101','B');
INSERT INTO CLASS
(CLASS_NUMBER, CLASS_NAME, FK_SUBJECT_ID)
VALUES
('CS277','DATABASE ADMINISTRATION 101','C');
INSERT INTO CLASS
(CLASS_NUMBER, CLASS_NAME, FK_SUBJECT_ID)
VALUES
('WR101','BASIC WRITING AND GRAMMAR','E');
INSERT INTO CLASS
(CLASS_NUMBER, CLASS_NAME, FK_SUBJECT_ID)
VALUES
('ENG201','SHAKESPEARE','E');
INSERT INTO CLASS
(CLASS_NUMBER, CLASS_NAME, FK_SUBJECT_ID)
VALUES
('BT265','MANAGERIAL ACCOUNTING','B');
INSERT INTO CLASS
(CLASS_NUMBER, CLASS_NAME, FK_SUBJECT_ID)
VALUES
('MT101','ALGEBRA','M');
INSERT INTO CLASS
(CLASS_NUMBER, CLASS_NAME, FK_SUBJECT_ID)
VALUES
('CS296N','INTERMEDIATE WEB DESIGN','C');

INSERT INTO STUDENT
(STUDENT_ID, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, FK_STUDENT_TYPE_ID)
VALUES
('L1164156','CINDY','TUGENBERG','F');
INSERT INTO STUDENT
(STUDENT_ID, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, FK_STUDENT_TYPE_ID)
VALUES
('L3201551','ANDY','WINTER','S');
INSERT INTO STUDENT
(STUDENT_ID, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, FK_STUDENT_TYPE_ID)
VALUES
('L5957698','BETH','ORASI','F');
INSERT INTO STUDENT
(STUDENT_ID, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, FK_STUDENT_TYPE_ID)
VALUES
('L6696156','SUSAN','BARNESON','F');
INSERT INTO STUDENT
(STUDENT_ID, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, FK_STUDENT_TYPE_ID)
VALUES
('L3267134','LOREN','TOYE','S');
INSERT INTO STUDENT
(STUDENT_ID, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, FK_STUDENT_TYPE_ID)
VALUES
('L3315732','RUSSELL','LETTS','S');
INSERT INTO STUDENT
(STUDENT_ID, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, FK_STUDENT_TYPE_ID)
VALUES
('L5144710','DEREK','GUERIN','F');
INSERT INTO STUDENT
(STUDENT_ID, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, FK_STUDENT_TYPE_ID)
VALUES
('L1577435','COLBY','MEHRA','S');
INSERT INTO STUDENT
(STUDENT_ID, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, FK_STUDENT_TYPE_ID)
VALUES
('L3351513','WILLIAM','BRUECKNER','F');
INSERT INTO STUDENT
(STUDENT_ID, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, FK_STUDENT_TYPE_ID)
VALUES
('L7058685','CHARLES','LAWRENCE','S');
INSERT INTO STUDENT
(STUDENT_ID, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, FK_STUDENT_TYPE_ID)
VALUES
('L6106575','KIMBERLY','ANDERSON','S');
INSERT INTO STUDENT
(STUDENT_ID, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, FK_STUDENT_TYPE_ID)
VALUES
('L5299391','HENRY','GRIEVE','F');
INSERT INTO STUDENT
(STUDENT_ID, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, FK_STUDENT_TYPE_ID)
VALUES
('L4418973','OWEN','SKINNER','F');
INSERT INTO STUDENT
(STUDENT_ID, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, FK_STUDENT_TYPE_ID)
VALUES
('L9433939','KAYLEE','SMITH','S');
INSERT INTO STUDENT
(STUDENT_ID, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, FK_STUDENT_TYPE_ID)
VALUES
('L3464629','ELIZABETH','MANDEL','F');
INSERT INTO STUDENT
(STUDENT_ID, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, FK_STUDENT_TYPE_ID)
VALUES
('L4383178','LYDIA','HALBROOK','S');
INSERT INTO STUDENT
(STUDENT_ID, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, FK_STUDENT_TYPE_ID)
VALUES
('L2434508','FREDA','TREVELYAN','F');
INSERT INTO STUDENT
(STUDENT_ID, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, FK_STUDENT_TYPE_ID)
VALUES
('L3679451','SCHROEDER','DRAYTON','F');
INSERT INTO STUDENT
(STUDENT_ID, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, FK_STUDENT_TYPE_ID)
VALUES
('L9551454','RAYMOND','HANSON','F');
INSERT INTO STUDENT
(STUDENT_ID, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, FK_STUDENT_TYPE_ID)
VALUES
('L2903636','WILSON','BATTLE','S');
INSERT INTO STUDENT
(STUDENT_ID, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, FK_STUDENT_TYPE_ID)
VALUES
('L9171677','ROBERT','FORGIONE','S');
INSERT INTO STUDENT
(STUDENT_ID, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, FK_STUDENT_TYPE_ID)
VALUES
('L8621669','JANE','NEITERMAN','S');
INSERT INTO STUDENT
(STUDENT_ID, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, FK_STUDENT_TYPE_ID)
VALUES
('L1421737','LINUS','VAN PELT','S');
INSERT INTO STUDENT
(STUDENT_ID, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, FK_STUDENT_TYPE_ID)
VALUES
('L0573134','MARK','RICHARDSON','F');
INSERT INTO STUDENT
(STUDENT_ID, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, FK_STUDENT_TYPE_ID)
VALUES
('L9810299','SNOOPY','BROWN','F');

INSERT INTO SECTION
(SECTION_ID, AVAILABLE_ENROLLMENT, LOCATION, FK_TEACHER_ID, FK_CLASS_NUMBER, FK_TERM_ID)
VALUES
('24168',20,'4/101','L0456789','CS275','201020');
INSERT INTO SECTION
(SECTION_ID, AVAILABLE_ENROLLMENT, LOCATION, FK_TEACHER_ID, FK_CLASS_NUMBER, FK_TERM_ID)
VALUES
('24169',30,'2/214','L0234567','ENG201','201020');
INSERT INTO SECTION
(SECTION_ID, AVAILABLE_ENROLLMENT, LOCATION, FK_TEACHER_ID, FK_CLASS_NUMBER, FK_TERM_ID)
VALUES
('24170',25,'19/126','L0345678','WR101','201020');
INSERT INTO SECTION
(SECTION_ID, AVAILABLE_ENROLLMENT, LOCATION, FK_TEACHER_ID, FK_CLASS_NUMBER, FK_TERM_ID)
VALUES
('24171',20,'19/128','L0456789','CS170','201020');
INSERT INTO SECTION
(SECTION_ID, AVAILABLE_ENROLLMENT, LOCATION, FK_TEACHER_ID, FK_CLASS_NUMBER, FK_TERM_ID)
VALUES
('24172',30,'4/201','L0367913','CS276','201020');
INSERT INTO SECTION
(SECTION_ID, AVAILABLE_ENROLLMENT, LOCATION, FK_TEACHER_ID, FK_CLASS_NUMBER, FK_TERM_ID)
VALUES
('44172',30,'4/201','L0367913','CS276','201040');
INSERT INTO SECTION
(SECTION_ID, AVAILABLE_ENROLLMENT, LOCATION, FK_TEACHER_ID, FK_CLASS_NUMBER, FK_TERM_ID)
VALUES
('25172',30,'4/201','L0367913','CS276','201120');
INSERT INTO SECTION
(SECTION_ID, AVAILABLE_ENROLLMENT, LOCATION, FK_TEACHER_ID, FK_CLASS_NUMBER, FK_TERM_ID)
VALUES
('34172',30,'4/201','L0367913','CS276','201030');
INSERT INTO SECTION
(SECTION_ID, AVAILABLE_ENROLLMENT, LOCATION, FK_TEACHER_ID, FK_CLASS_NUMBER, FK_TERM_ID)
VALUES
('34173',35,'5/153','L0123456','CS275','201030');
INSERT INTO SECTION
(SECTION_ID, AVAILABLE_ENROLLMENT, LOCATION, FK_TEACHER_ID, FK_CLASS_NUMBER, FK_TERM_ID)
VALUES
('34174',25,'19/120','L0135791','BT165','201030');
INSERT INTO SECTION
(SECTION_ID, AVAILABLE_ENROLLMENT, LOCATION, FK_TEACHER_ID, FK_CLASS_NUMBER, FK_TERM_ID)
VALUES
('34175',20,'19/256','L0456789','CS170','201030');
INSERT INTO SECTION
(SECTION_ID, AVAILABLE_ENROLLMENT, LOCATION, FK_TEACHER_ID, FK_CLASS_NUMBER, FK_TERM_ID)
VALUES
('34176',25,'2/216','L0567890','CS133P','201030');
INSERT INTO SECTION
(SECTION_ID, AVAILABLE_ENROLLMENT, LOCATION, FK_TEACHER_ID, FK_CLASS_NUMBER, FK_TERM_ID)
VALUES
('34177',30,'4/202','L0234567','ENG201','201030');
INSERT INTO SECTION
(SECTION_ID, AVAILABLE_ENROLLMENT, LOCATION, FK_TEACHER_ID, FK_CLASS_NUMBER, FK_TERM_ID)
VALUES
('34178',35,'2/211','L0123456','CS276','201030');
INSERT INTO SECTION
(SECTION_ID, AVAILABLE_ENROLLMENT, LOCATION, FK_TEACHER_ID, FK_CLASS_NUMBER, FK_TERM_ID)
VALUES
('44179',20,'2/214','L0123456','CS277','201040');
INSERT INTO SECTION
(SECTION_ID, AVAILABLE_ENROLLMENT, LOCATION, FK_TEACHER_ID, FK_CLASS_NUMBER, FK_TERM_ID)
VALUES
('44180',30,'4/101','L0567890','CS133P','201040');
INSERT INTO SECTION
(SECTION_ID, AVAILABLE_ENROLLMENT, LOCATION, FK_TEACHER_ID, FK_CLASS_NUMBER, FK_TERM_ID)
VALUES
('44181',25,'19/126','L0135791','BT265','201040');
INSERT INTO SECTION
(SECTION_ID, AVAILABLE_ENROLLMENT, LOCATION, FK_TEACHER_ID, FK_CLASS_NUMBER, FK_TERM_ID)
VALUES
('44182',30,'19/120','L0345678','WR101','201040');
INSERT INTO SECTION
(SECTION_ID, AVAILABLE_ENROLLMENT, LOCATION, FK_TEACHER_ID, FK_CLASS_NUMBER, FK_TERM_ID)
VALUES
('44183',25,'5/152','L0234567','WR101','201040');
INSERT INTO SECTION
(SECTION_ID, AVAILABLE_ENROLLMENT, LOCATION, FK_TEACHER_ID, FK_CLASS_NUMBER, FK_TERM_ID)
VALUES
('44184',35,'19/244','L0567890','CS133P','201040');
INSERT INTO SECTION
(SECTION_ID, AVAILABLE_ENROLLMENT, LOCATION, FK_TEACHER_ID, FK_CLASS_NUMBER, FK_TERM_ID)
VALUES
('44185',20,'6/133','L0234567','ENG201','201120');
INSERT INTO SECTION
(SECTION_ID, AVAILABLE_ENROLLMENT, LOCATION, FK_TEACHER_ID, FK_CLASS_NUMBER, FK_TERM_ID)
VALUES
('44186',25,'7/233','L0123456','CS277','201120');
INSERT INTO SECTION
(SECTION_ID, AVAILABLE_ENROLLMENT, LOCATION, FK_TEACHER_ID, FK_CLASS_NUMBER, FK_TERM_ID)
VALUES
('44187',30,'19/126','L0345678','MT101','201120');


INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR101','L1164156',34176,'A');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR102','L1164156',24170,'A-');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR103','L1164156',24171,'B');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR104','L1164156',34174,'A');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR105','L3201551',24168,'C');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR106','L3201551',34177,'C-');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR107','L3201551',44181,'F');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR109','L5957698',44187,'B+');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR110','L6696156',44186,'A');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR111','L3267134',24169,'B');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR112','L3267134',44182,'A-');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR113','L3267134',44187,'A');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR114','L3267134',44181,'B');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR115','L3315732',44186,'C');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR116','L3315732',24172,'F');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR147','L3315732',34172,'B');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR117','L3315732',24169,'A');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR118','L5144710',34178,'B');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR119','L5144710',44187,'B-');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR120','L3351513',34178,'C-');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR121','L7058685',24168,'B');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR122','L7058685',24169,'B+');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR123','L7058685',24170,'A-');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR124','L7058685',24171,'B');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR125','L6106575',24172,'C');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR126','L6106575',34173,'C+');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR127','L6106575',34174,'A');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR128','L5299391',34175,'B');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR129','L5299391',34176,'B-');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR130','L4418973',34177,'C');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR131','L9433939',34178,'C+');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR132','L9433939',44179,'A');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR133','L9433939',44180,'B+');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR134','L9433939',44181,'B+');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR135','L3464629',44182,'A-');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR136','L3464629',44183,'A-');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR137','L3464629',44184,'F');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR138','L4383178',44185,'A');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR139','L4383178',44186,'B');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR140','L2434508',44187,'C+');


INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR142','L3679451',24168,'C-');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR143','L3679451',24169,'A');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR144','L9551454',24170,'A-');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR145','L9551454',24171,'B');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR146','L2903636',24172,'B+');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR148','L2903636',34172,'A');

INSERT INTO ENROLLMENT
(ENROLLMENT_ID, FK_STUDENT_ID, FK_SECTION_ID, GRADE)
VALUES
('ENR149','L4383178',25172,'B+');

INSERT INTO TrainRoute (routeNumber_ID, route_name, route_cost) Values(1,'Boston-Worcester', 9.25);
INSERT INTO TrainRoute (routeNumber_ID, route_name, route_cost) Values(2,'Worcester-Boston',9.25);
INSERT INTO TrainRoute (routeNumber_ID, route_name, route_cost) Values(3,'Boston-Greenbush', 7.50);
INSERT INTO TrainRoute (routeNumber_ID, route_name, route_cost) Values(4,'Greenbush-Boston', 7.50);
INSERT INTO TrainRoute (routeNumber_ID, route_name, route_cost) Values (5,'Boston-Franklin/Forge Park', 10.00);
INSERT INTO TrainRoute (routeNumber_ID, route_name, route_cost) Values (6,'Franklin/Forge Park-Boston', 10.00);
INSERT INTO TrainStation(stationNumber_ID, station_name, station_city) VALUES (1,'South Station', 'Boston');
INSERT INTO TrainStation(stationNumber_ID, station_name, station_city) VALUES (2,'Worcester', 'Worcester');
INSERT INTO TrainStation(stationNumber_ID, station_name, station_city) VALUES (3,'Forge Park', 'Franklin');
INSERT INTO TrainStation(stationNumber_ID, station_name, station_city) VALUES (4,'Greenbush', 'Scituate');
INSERT INTO TrainStation(stationNumber_ID, station_name, station_city) VALUES (5,'Cohasset', 'Cohasset');
INSERT INTO TrainStation(stationNumber_ID, station_name, station_city) VALUES (6,'Weymouth Landing', 'East Scituate');
INSERT INTO TrainStation(stationNumber_ID, station_name, station_city) VALUES (7,'Norfolk', 'Norfolk');
INSERT INTO TrainStation(stationNumber_ID, station_name, station_city) VALUES (8,'Walpole', 'Walpole');
INSERT INTO TrainStation(stationNumber_ID, station_name, station_city) VALUES (9,'Wellesley Square', 'Wellesley');
INSERT INTO TrainStation(stationNumber_ID, station_name, station_city) VALUES (10,'Wellesley Hills', 'Wellesley');
INSERT INTO TrainStation(stationNumber_ID, station_name, station_city) VALUES (11,'Wellesley Farms', 'Wellesley');
INSERT INTO TrainStation(stationNumber_ID, station_name, station_city) VALUES (12,'Framingham', 'Framingham');

INSERT INTO TrainStop (StopNumber_id, routeNumber_Id, stationNumber_ID, arrive_time, depart_time) VALUES
(1,1,1,to_date ('07:30', 'hh:mi AM'),to_date ('07:30', 'hh:mi AM'));
INSERT INTO TrainStop (StopNumber_id, routeNumber_Id, stationNumber_ID, arrive_time, depart_time) VALUES
(2,1,9,to_date ('07:50', 'hh:mi AM'),to_date ('07:52', 'hh:mi AM'));
INSERT INTO TrainStop (StopNumber_id, routeNumber_Id, stationNumber_ID, arrive_time, depart_time) VALUES
(3,1,10,to_date ('07:55', 'hh:mi AM'),to_date ('07:57', 'hh:mi AM'));
INSERT INTO TrainStop (StopNumber_id, routeNumber_Id, stationNumber_ID, arrive_time, depart_time) VALUES
(4,1,11,to_date ('08:05', 'hh:mi AM'),to_date ('08:07', 'hh:mi AM'));
INSERT INTO TrainStop (StopNumber_id, routeNumber_Id, stationNumber_ID, arrive_time, depart_time) VALUES
(5,1,12,to_date ('08:15', 'hh:mi AM'),to_date ('08:19', 'hh:mi AM'));
INSERT INTO TrainStop (StopNumber_id, routeNumber_Id, stationNumber_ID, arrive_time, depart_time) VALUES
(6,1,2,to_date ('08:45', 'hh:mi AM'),to_date ('08:45', 'hh:mi AM'));
INSERT INTO TrainStop (StopNumber_id, routeNumber_Id, stationNumber_ID, arrive_time, depart_time) VALUES
(7,5,1,to_date ('07:30', 'hh:mi AM'),to_date ('07:30', 'hh:mi AM'));
INSERT INTO TrainStop (StopNumber_id, routeNumber_Id, stationNumber_ID, arrive_time, depart_time) VALUES
(8,5,8,to_date ('08:00', 'hh:mi AM'),to_date ('08:05', 'hh:mi AM'));
INSERT INTO TrainStop (StopNumber_id, routeNumber_Id, stationNumber_ID, arrive_time, depart_time) VALUES
(9,5,7,to_date ('08:15', 'hh:mi AM'),to_date ('08:18', 'hh:mi AM'));
INSERT INTO TrainStop (StopNumber_id, routeNumber_Id, stationNumber_ID, arrive_time, depart_time) VALUES
(10,5,3,to_date ('08:30', 'hh:mi AM'),to_date ('08:30', 'hh:mi AM'));

insert into enrollment 
values ('ENR150','L9433939',44172,NULL);

insert into enrollment
values ('ENR151','L6696156',44172,NULL);

insert into enrollment
values ('ENR152','L3351513',44172,NULL);

insert into enrollment 
values ('ENR153','L3315732',44172,NULL);

insert into enrollment
values ('ENR154','L5144710',44172,NULL);

insert into enrollment
values ('ENR155','L6106575',44172,NULL);

update section set fk_term_id = '201120';

commit;


select * from class;
select * from enrollment;
select * from section WHERE FK_CLASS_NUMBER='CS276';
select * from enrollment WHERE FK_SECTION_ID=44172;
select count(*) from enrollment WHERE FK_SECTION_ID=44172;
--
-- Read the count  students registered in section 44172 of cs276
set serveroutput on;
DECLARE
    enrolled_in_276 number(3) :=NULL;
    title_of_class VARCHAR2(60) := NULL;
BEGIN
   -- Get Class Name (title?)
   SELECT CLASS_NAME INTO title_of_class FROM class WHERE CLASS_NUMBER='CS276';
   select count(*) INTO enrolled_in_276 from enrollment WHERE FK_SECTION_ID=44172;
    
    IF enrolled_in_276 > 15
        THEN
            DBMS_OUTPUT.PUT_LINE(title_of_class || ' Student Count:' || enrolled_in_276 || ' A waitlist has been started');
        -- output the title of the class
        -- output the number of students enrolled
        -- a message saying a waitlist has been started
    ELSIF enrolled_in_276 < 7
        THEN
        DBMS_OUTPUT.PUT_LINE(title_of_class || ' Student Count:' || enrolled_in_276 || ' The class needs to be cancelled');
        -- output the title of the class
        -- output the number of students enrolled
        -- a message saying a waitlist has been cancelled
    ELSIF enrolled_in_276 >= 7 AND enrolled_in_276 <= 15
        THEN  DBMS_OUTPUT.PUT_LINE(title_of_class || ' Student Count:' || enrolled_in_276 || ' The class can continue');
    ELSE
        --output the title of the class 
        --the number of students enrolled 
        --a message saying the class can continue
        DBMS_OUTPUT.PUT_LINE(title_of_class || ' Student Count:' || enrolled_in_276 || 'Please consult the dean.');
    END IF;

END;