SQL for the Fractured : A Script to set up all the data needed for TinyU!

--------------------------------------------------------
--
-- TinyU : A script that creates a tiny set of tables
--         simple enough to be understood but complex
--         enough to be interesting.
--         NOTE : SQL is written for an Oracle Database
--                and should work on 11g - 12c
--                (If you remove the "PURGE", it should 
--                 work on MOST Oracle databases!)
--------------------------------------------------------
--
DROP TABLE EXAMPLE_COURSES     CASCADE CONSTRAINTS PURGE;
DROP TABLE EXAMPLE_STUDENTS    CASCADE CONSTRAINTS PURGE;
DROP TABLE EXAMPLE_SECTIONS    CASCADE CONSTRAINTS PURGE;
DROP TABLE EXAMPLE_ENROLLMENTS CASCADE CONSTRAINTS PURGE;
DROP SEQUENCE EXAMPLE_STUDENTS_SEQ;
DROP SEQUENCE EXAMPLE_COURSES_SEQ;

CREATE TABLE EXAMPLE_COURSES
  (
    COURSE_ID NUMBER,
    FACULTY   VARCHAR2(30),
    COURSE    NUMBER,
    TITLE     VARCHAR2(30),
    CREDIT    NUMBER,
    CONSTRAINT EXAMPLE_COURSES_PK PRIMARY KEY (COURSE_ID) ENABLE
  );
--
CREATE SEQUENCE EXAMPLE_COURSES_SEQ NOCACHE;
--
CREATE OR REPLACE TRIGGER bi_EXAMPLE_COURSES before
  INSERT ON EXAMPLE_COURSES FOR EACH row BEGIN IF :new.COURSE_ID IS NULL THEN
  SELECT EXAMPLE_COURSES_SEQ.nextval INTO :new.COURSE_ID FROM sys.dual;
END IF;
END;
/
--
ALTER TRIGGER bi_EXAMPLE_COURSES ENABLE;
--
CREATE TABLE EXAMPLE_STUDENTS
  (
    STUDENT_ID     NUMBER,
    STUDENT_NAME   VARCHAR2(30),
    MAJOR          VARCHAR2(30),
    CLASSIFICATION VARCHAR2(30),
    ADVISOR        VARCHAR2(30),
    CONSTRAINT EXAMPLE_STUDENTS_PK PRIMARY KEY (STUDENT_ID) ENABLE
  );
--
CREATE SEQUENCE EXAMPLE_STUDENTS_SEQ NOCACHE;
--
CREATE OR REPLACE TRIGGER bi_EXAMPLE_STUDENTS before
  INSERT ON EXAMPLE_STUDENTS FOR EACH row BEGIN IF :new.STUDENT_ID IS NULL THEN
  SELECT EXAMPLE_STUDENTS_SEQ.nextval INTO :new.STUDENT_ID FROM sys.dual;
END IF;
END;
/
--
ALTER TRIGGER bi_EXAMPLE_STUDENTS ENABLE;
--
CREATE TABLE EXAMPLE_SECTIONS
  (
    SECTION    NUMBER,
    CAMPUS     VARCHAR2(30),
    ROOM       VARCHAR2(30),
    START_DATE DATE,
    END_DATE   VARCHAR2(30),
    COURSE_ID  NUMBER,
    CONSTRAINT EXAMPLE_SECTIONS_PK PRIMARY KEY (COURSE_ID, SECTION) ENABLE
  );
--
CREATE TABLE EXAMPLE_ENROLLMENTS
  (
    STUDENT_ID NUMBER,
    COURSE_ID  NUMBER,
    SECTION    NUMBER,
    GRADE      NUMBER,
    CONSTRAINT EXAMPLE_ENROLLMENTS_PK PRIMARY KEY (STUDENT_ID, COURSE_ID, SECTION) ENABLE
  );
--
ALTER TABLE EXAMPLE_SECTIONS
ADD   CONSTRAINTS EXAMPLE_SECTIONS_FK
FOREIGN KEY (course_id)
REFERENCES EXAMPLE_COURSES (COURSE_ID);
--
ALTER TABLE EXAMPLE_ENROLLMENTS
ADD   CONSTRAINTS EXAMPLE_ENROLLMENTS_FK
FOREIGN KEY (course_id, section)
REFERENCES EXAMPLE_SECTIONS (COURSE_ID, SECTION);
--
ALTER TABLE EXAMPLE_ENROLLMENTS
ADD   CONSTRAINTS EXAMPLE_ENROLLMENTS_FK2
FOREIGN KEY (student_id)
REFERENCES EXAMPLE_STUDENTS (student_id);
--
---------------------------------------------
-- Data Load
---------------------------------------------
--
Insert into EXAMPLE_COURSES (COURSE_ID,FACULTY,COURSE,TITLE,CREDIT) values (1079,'CCIS',2701,'Database Design & SQL',4);
Insert into EXAMPLE_COURSES (COURSE_ID,FACULTY,COURSE,TITLE,CREDIT) values (1080,'CCIS',2801,'Systems Analysis',4);
--
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (1,'Nicholas J','.NET Prgrmr','Freshman','Grant');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (25,'Jacob A','Programmer','Sophomore','Mary');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (2,'Paul D','.NET Prgrmr','Freshman','Steve');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (3,'Lindsay M','Web Program','Freshman','Dave');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (4,'Michael T','.NET Prgrmr','Freshman','Grant');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (28,'Eric D','.NET Prgrmr','Sophomore','Mary');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (29,'Derek N','Programmer','Freshman','Steve');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (5,'Jack','General Ed','Special','Dave');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (30,'Ravi M','.NET Prgrmr','Sophomore','Grant');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (31,'Suguna','Bus Analyst','Sophomore','Mary');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (6,'Gregory S','Web Program','Sophomore','Steve');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (7,'Ryan J','Web Program','Sophomore','Dave');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (32,'Pamela S','Pre Dent Ast','Freshman','Grant');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (8,'Edward T','Network Adm','Sophomore','Mary');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (9,'Caitlin A','MS DB Spec','Freshman','Steve');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (10,'Andrew R','Work Adm Ast','Special','Dave');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (11,'James L','MS DB Spec','Prev Degree','Grant');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (33,'Joshua R','Network Adm','Freshman','Mary');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (12,'Mark D','Java/Open','Freshman','Steve');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (13,'Ian J','General Ed','Special','Dave');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (14,'Lorenzo E','.NET Prgrmr','Prev Degree','Grant');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (34,'Yuliya','Bus Analyst','Freshman','Mary');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (35,'Karen M','Programmer','Freshman','Steve');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (15,'Timothy J','Web Program','Freshman','Dave');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (16,'Mai N','.NET Prgrmr','Prev Degree','Grant');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (17,'Jeffrey A','Work Adm Ast','Freshman','Mary');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (18,'Damion H','.NET Prgrmr','Sophomore','Steve');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (37,'Jeff M','Programmer','Prev Degree','Dave');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (19,'Wendy L','.NET Prgrmr','Freshman','Grant');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (20,'Ryan','Service Desk','Freshman','Mary');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (39,'Andreas','Network Adm','Sophomore','Steve');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (21,'Daniel','Programmer','Prev Degree','Dave');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (22,'W T','General Ed','Special','Grant');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (23,'Chantel','MS DB Spec','Prev Degree','Mary');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (40,'Divya','Web Program','Prev Degree','Steve');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (24,'James F','Programmer','Freshman','Dave');
Insert into EXAMPLE_STUDENTS (STUDENT_ID,STUDENT_NAME,MAJOR,CLASSIFICATION,ADVISOR) values (41,'Steve D','.NET Prgrmr','Prev Degree','Grant');
--
INSERT INTO EXAMPLE_SECTIONS (SECTION, CAMPUS, ROOM, START_DATE, END_DATE, COURSE_ID) VALUES (40,'Eden Prairie','H257',to_date('1/16/2014','mm/dd/yyyy'),to_date('5/15/2014','mm/dd/yyyy'),1079);
INSERT INTO EXAMPLE_SECTIONS (SECTION, CAMPUS, ROOM, START_DATE, END_DATE, COURSE_ID) VALUES (40,'Eden Prairie','H255',to_date('1/13/2014','mm/dd/yyyy'),to_date('5/19/2014','mm/dd/yyyy'),1080);
--
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (1,1079,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (25,1080,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (2,1079,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (2,1080,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (3,1079,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (4,1079,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (4,1080,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (28,1080,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (29,1080,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (5,1079,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (30,1080,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (31,1080,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (6,1079,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (7,1079,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (32,1080,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (8,1079,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (9,1079,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (10,1079,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (11,1079,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (33,1080,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (12,1079,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (13,1079,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (14,1079,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (34,1080,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (35,1080,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (15,1079,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (16,1079,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (17,1079,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (18,1079,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (20,1080,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (37,1080,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (19,1079,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (20,1079,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (18,1080,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (39,1080,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (21,1079,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (22,1079,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (23,1079,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (40,1080,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (24,1079,40,4);
Insert into EXAMPLE_ENROLLMENTS (STUDENT_ID,COURSE_ID,SECTION,GRADE) values (41,1080,40,4);
--
COMMIT;
​