--classes.sql
--sql script to create and populate dataset
--include all tables from classes demo
--Andy Harris, 05/00

--turn on dbms.output
set serveroutput on

DROP TABLE in_skills;
DROP TABLE in_current;
DROP TABLE student_taken;
DROP TABLE student_current;

DROP TABLE section;
DROP TABLE instructor;
DROP TABLE course;
DROP TABLE room;
DROP TABLE student;

DROP VIEW InstructorCourses;

CREATE TABLE instructor(
  ID number(3) primary key,
  LName varChar2(10),
  FName varchar2(10),
  Office varChar2(10),
  Email varchar2(25)
);

CREATE TABLE course(
  CourseNum varchar2(4) primary key,
  Name varchar(20),
  LabNeeded varchar(20)
);

CREATE TABLE room(
  RoomNum varchar2(6) primary key,
  EquipType varchar2(20),
  NumSeats number(3)
);

CREATE TABLE student(
  SID varchar2(11) primary key,
  LName varchar2(15),
  FName varchar2(15)
);

CREATE TABLE section(
  SectionID varchar2(4) primary key,
  Course varchar2(4) references course,
  Room varchar2(7) references room,
  Time varchar2(12),
  Days varchar2(5),
  Instructor number(3) references instructor
);

--relationship tables--
CREATE TABLE in_skills(
  id number(4) primary key,
  ins_id number(3) references instructor,
  courseNum varchar2(4) references course
);

CREATE TABLE in_current(
  id number(4) primary key,
  ins_id number(3) references instructor,
  sectionID varchar2(4) references section
);

CREATE TABLE student_taken(
  stID number(4) primary key,
  SID varchar2(11) references student,
  courseNum varchar2(4) references course
);

CREATE TABLE student_current(
  scID number(4) primary key,
  SID varchar2(11) references student,
  sectionID varchar2(4) references section
);

--Populate the database


--add instructors
INSERT INTO instructor VALUES(1, 'Harris', 'Andy', 'SL280', 'aharris@cs.iupui.edu');
INSERT INTO instructor VALUES(2, 'Roberts', 'Michele', 'SL280', 'mroberts@cs.iupui.edu');
INSERT INTO instructor VALUES(3, 'Molnar', 'Bob', 'SL279', 'rmolnar@cs.iupui.edu');


--add rooms
INSERT INTO room values('SL247', 'Pentium NT', 33);
INSERT INTO room values('SL251', 'Solaris', 25);
INSERT INTO room values('BS2003', 'Display', 60);

--add courses
INSERT INTO course VALUES ('N241', 'Web Design', 'Windows or Unix');
INSERT INTO course VALUES ('N345', 'Java', 'Windows or Unix');
INSERT INTO course VALUES ('N311', 'Adv. Database', 'Windows or Unix');
INSERT INTO course VALUES ('N331', 'Visual Basic', 'Windows');

--add students
INSERT INTO student VALUES('111-11-1111', 'Anderson', 'George');
INSERT INTO student VALUES('222-22-2222', 'Bonner', 'Felicia');
INSERT INTO student VALUES('333-33-3333', 'Conner', 'Christine');
INSERT INTO student VALUES('444-44-4444', 'Edgerin', 'Ellis');
INSERT INTO student VALUES('555-55-5555', 'Francis', 'Alicia');
INSERT INTO student VALUES('666-66-6666', 'Anderson', 'Steven');

--add sections
INSERT INTO section VALUES ('V362', 'N345', 'BS2003', '1:00', 'T, R', 1);
INSERT INTO section VALUES ('V353', 'N311', 'SL247', '9:00', 'T, R', 1);
INSERT INTO section VALUES ('V350', 'N241', 'SL247', '11:00', 'M, W', 3);
INSERT INTO section VALUES ('V356', 'N331', 'SL247', '5:45', 'M, W', 2);

--add instructor skills
INSERT INTO in_skills VALUES (0, 1, 'N241');
INSERT INTO in_skills VALUES (1, 1, 'N345');
INSERT INTO in_skills VALUES (2, 1, 'N311');
INSERT INTO in_skills VALUES (3, 2, 'N241');
INSERT INTO in_skills VALUES (4, 3, 'N241');
INSERT INTO in_skills VALUES (5, 1, 'N331');
INSERT INTO in_skills VALUES (6, 2, 'N331');

--add instructor current courses
INSERT INTO in_current VALUES(0, 1, 'V353');
INSERT INTO in_current VALUES(1, 1, 'V362');
INSERT INTO in_current VALUES(2, 2, 'V356');
INSERT INTO in_current VALUES(3, 3, 'V350');

--Generate some queries

SELECT * FROM section;
--SELECT * FROM instructor;
--SELECT * FROM room;
--SELECT * FROM student;
--SELECT * FROM course;

--SELECT * FROM in_skills;

--SELECT section.course, instructor.FName, instructor.LName 
--FROM section, instructor
--WHERE section.instructor = instructor.id;

--SELECT * FROM in_current;


CREATE view InstructorCourses AS
SELECT FName, LName, Course.Name
FROM instructor, course, in_skills
WHERE in_skills.ins_id = instructor.ID
AND in_skills.courseNum = course.CourseNum;

select * from InstructorCourses;