ADV database
/*Script to make database for Chapter 3 Lab*/
/* drop tables if they are already in the database
THIS IS WHERE YOU MIGHT WANT TO MAKE A NEW WORKSPACE...
IF YOU ALREADY HAVE THESE TABLES THEY WILL BE DROPPED AND REPLACED*/
/*WARNING::::::::::::::::::::::::::::::::::::::::::::::::::::
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
IF YOU CHOSE NOT TO MAKE A NEW WORKSPACE THESE COMMANDS WILL
COMPLETELEY DROP ALL TABLES *AND* CONSTRAINTS WHICH MEANS ANY
OTHER DATABASE DESIGN WILL BE TOTALLY MESSED UP
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
If you don't have a table with this name already in the database
you might get this error
ORA-00942: table or view does not exist
it is not a true error since there wasn't a table to drop you're
ready to go!'*/
DROP TABLE Department CASCADE CONSTRAINTS;
DROP TABLE Course CASCADE CONSTRAINTS;
DROP TABLE Person CASCADE CONSTRAINTS;
DROP TABLE Grades CASCADE CONSTRAINTS;
DROP TABLE Course_Instructor CASCADE CONSTRAINTS;
/*Create tables and assign primary and foerign keys*/
CREATE TABLE person(
PersonID char(5),
Last_Name varchar(20),
First_Name varchar(20),
Hire_Date date,
Enrollment_Date date,
primary key(PersonID));
CREATE TABLE Department(
DepartmentID char(5),
Name varchar(20),
Budget numeric(8,2),
Administrator varchar(50),
primary key(DepartmentID));
CREATE TABLE Course(
CourseID char(5),
title varchar(20),
credits char(1),
location varchar(50),
days char(3),
classtime varchar(50),
DepartmentID char(5),
primary key(CourseID),
foreign key(DepartmentID) references Department);
CREATE TABLE Grades(
EnrollmentID char(5),
CourseID char(5),
StudentID char(5),
Grade char(1),
primary key(EnrollmentID),
foreign key(CourseID) references Course,
foreign key(StudentID) references Person);
CREATE TABLE Course_Instructor(
CourseID char(5),
PersonID char(5),
foreign key(CourseID) references Course,
foreign key(PersonID) references Person);
/*Insert student values into table person*/
INSERT INTO Person values('1', 'Amberson', 'Christopher','' ,'1-1-2016');
INSERT INTO Person values('2', 'Anderson', 'Nancy','' ,'8-1-2002');
INSERT INTO Person values('3', 'Bennedict', 'Sally','' ,'7-3-1986');
INSERT INTO Person values('4', 'Bobson', 'David','' ,'8-1-2016');
INSERT INTO Person values('5', 'Kennedy', 'Mark','' ,'1-1-2017');
INSERT INTO Person values('6', 'Cadernict', 'Jonah','' ,'12-1-2016');
INSERT INTO Person values('7', 'Donaldson', 'Cedric','' ,'9-1-2016');
INSERT INTO Person values('8', 'Erickson', 'Cedric','' ,'10-1-2005');
INSERT INTO Person values('9', 'Smith', 'Tyler','' ,'8-15-2007');
INSERT INTO Person values('10', 'Lopez', 'James','' ,'9-1-1995');
INSERT INTO Person values('11', 'Smith', 'Robert','' ,'6-1-1987');
INSERT INTO Person values('12', 'Anderson', 'Aiden','' ,'7-1-1998');
INSERT INTO Person values('13', 'Jones', 'Iliana','' ,'8-17-2011');
INSERT INTO Person values('14', 'Zendrick', 'Martha','' ,'9-17-2009');
INSERT INTO Person values('15', 'McDonald', 'Esther','' ,'11-11-2008');
INSERT INTO Person values('16', 'McDonald', 'Bethany','' ,'12-1-1995');
INSERT INTO Person values('17', 'Cerrendale', 'Ali','' ,'12-11-1989');
INSERT INTO Person values('18', 'Thompson', 'Shauna','' ,'12-12-2006');
INSERT INTO Person values('19', 'Paulson', 'Sherry','' ,'1-1-2016');
INSERT INTO Person values('20', 'Donaldson', 'Rosalind','' ,'1-1-2016');
/*Imserting Employee data into the person table...enrollment date is null*/
INSERT INTO Person values('21', 'Jones', 'Christopher','1-1-2016' ,'');
INSERT INTO Person values('22', 'Joshson', 'Nancy','8-1-2002' ,'');
INSERT INTO Person values('23', 'Bamter', 'Sally','7-3-1986' ,'');
INSERT INTO Person values('24', 'Pepin', 'David','8-1-2016' ,'');
INSERT INTO Person values('25', 'Kontra', 'Mark','1-1-2017' ,'');
INSERT INTO Person values('26', 'Phillipson', 'Jonah','12-1-2016' ,'');
INSERT INTO Person values('27', 'Ronaldson', 'Cedric','9-1-2016' ,'');
INSERT INTO Person values('28', 'Johnson', 'Cedric','10-1-2005' ,'');
INSERT INTO Person values('29', 'Elliot', 'Tyler','8-15-2007' ,'');
INSERT INTO Person values('30', 'Lopez', 'James','9-1-1995' ,'');
INSERT INTO Person values('31', 'Smith', 'Robert','6-1-1987' ,'');
INSERT INTO Person values('32', 'Jameson', 'Aiden','7-1-1998' ,'');
INSERT INTO Person values('33', 'Patron', 'Iliana','8-17-2011' ,'');
INSERT INTO Person values('34', 'Gannon', 'Martha','9-17-2009' ,'');
INSERT INTO Person values('35', 'McDonald', 'Esther','11-11-2008' ,'');
INSERT INTO Person values('36', 'McDonald', 'Bethany','12-1-1995' ,'');
INSERT INTO Person values('37', 'Cerrado', 'Ali','12-11-1989' ,'');
INSERT INTO Person values('38', 'Tammison', 'Shauna','12-12-2006' ,'');
INSERT INTO Person values('39', 'Jackson', 'Sherry','1-1-2016' ,'');
INSERT INTO Person values('40', 'Donaldson', 'Rosalind','1-1-2016' ,'');
/*Inserting people whoa re both into the database*/
INSERT INTO Person values('41', 'Smith', 'Robert','6-1-1987' ,'6-1-1987');
INSERT INTO Person values('42', 'Anderson', 'Aiden','7-1-1988' ,'7-1-1998');
INSERT INTO Person values('43', 'Jones', 'Iliana','8-1-2009' ,'8-17-2011');
INSERT INTO Person values('44', 'Zendrick', 'Martha','10-11-2005' ,'9-17-2009');
INSERT INTO Person values('45', 'McDonald', 'Esther','8-1-1985' ,'11-11-2008');
INSERT INTO Person values('46', 'McDonald', 'Bethany','1-1-2016' ,'12-1-1995');
INSERT INTO Person values('47', 'Cerrendale', 'Ali','9-8-2011' ,'12-11-1989');
INSERT INTO Person values('48', 'Thompson', 'Shauna','8-9-2001' ,'12-12-2006');
INSERT INTO Person values('49', 'Paulson', 'Sherry','1-1-2012' ,'1-1-2016');
INSERT INTO Person values('50', 'Donaldson', 'Rosalind','1-1-2016' ,'1-1-2016');
/*Insert data into Grades*/
INSERT INTO Department values('1','IT', '999999.00', 'Sandra');
INSERT INTO Department values('2','History', '333333.00', 'Gerrald');
INSERT INTO Department values('3','Computer Science', '999999.00', 'Sandra');
INSERT INTO Department values('4','English', '330000.00', 'Bob');
INSERT INTO Department values('5','Psychology', '110000.00', 'Helen');
/*Insert IT department data into Course*/
INSERT INTO Course values('1','Introduction to IT', '3', 'AB224', 'MW', '10AM', '1');
INSERT INTO Course values('2','World of IT', '3', 'AB224', 'MW', '8AM', '1');
INSERT INTO Course values('3','HCI', '3', 'AB224', 'MW', '9:30AM', '1');
INSERT INTO Course values('4','HCI', '3', 'AB224', 'TR', '10AM', '1');
INSERT INTO Course values('5','Java', '3', 'AB220', 'MW', '12:30PM', '1');
INSERT INTO Course values('6','DBMS', '3', 'AB224', 'MW', '12:30PM', '1');
INSERT INTO Course values('7','Advanced Database', '3', 'AB224', 'R', '6PM', '1');
INSERT INTO Course values('8','Security', '3', 'AB224', 'MW', '10AM', '1');
INSERT INTO Course values('9','Independent Study', '3', '', '', '', '1');
INSERT INTO Course values('10','Awesome Networking', '3', 'AB224', 'TR', '8AM', '1');
INSERT INTO Course values('11','Distributed Systems', '3', 'AB224', 'MW', '8AM', '1');
INSERT INTO Course values('12','Javascript', '3', 'AB224', 'MW', '2PM', '1');
INSERT INTO Course values('13','DBMS', '3', 'AB224', 'M', '6PM', '1');
INSERT INTO Course values('14','', '3', 'AB224', 'MW', '10AM', '1');
INSERT INTO Course values('15','Networking II', '3', 'AB224', 'TR', '3:30PM', '1');
INSERT INTO Course values('16','HTML', '3', 'AB220', 'MW', '9:30AM', '1');
INSERT INTO Course values('17','API Programming', '3', 'AB224', 'MW', '10AM', '1');
INSERT INTO Course values('18','Networking Protocols', '3', 'AB224', 'F', '8AM', '1');
INSERT INTO Course values('19','TCP/IP', '3', 'AB224', 'TR', '12:30PM', '1');
INSERT INTO Course values('20','Capstone', '3', '', '', '', '1');
/*Insert History department data into Course*/
INSERT INTO Course values('21','Intro to History', '3', 'AB112', 'MW', '10AM', '2');
INSERT INTO Course values('22','World War I', '3', 'AB113', 'MW', '8AM', '2');
INSERT INTO Course values('23','World War II', '3', 'AB112', 'MW', '9:30AM', '2');
INSERT INTO Course values('24','Chinese History', '3', 'AB323', 'TR', '10AM', '2');
INSERT INTO Course values('25','American History', '3', 'AB344', 'MW', '12:30PM', '2');
INSERT INTO Course values('26','African History', '3', 'AB343', 'MW', '12:30PM', '2');
INSERT INTO Course values('27','Alaskan History', '3', 'AB224', 'R', '6PM', '2');
INSERT INTO Course values('28','Russian History', '3', 'AB332', 'MW', '10AM', '2');
INSERT INTO Course values('29','Independent Study', '3', '', '', '', '2');
INSERT INTO Course values('30','Indian History', '3', 'AB224', 'TR', '8AM', '2');
/*Insert Computer Science department data into Course*/
INSERT INTO Course values('31','Intro to CS', '3', 'SC112', 'MW', '10AM', '3');
INSERT INTO Course values('32','Java', '3', 'SC113', 'MW', '8AM', '3');
INSERT INTO Course values('33','C++', '3', 'SC112', 'MW', '9:30AM', '3');
INSERT INTO Course values('34','Assembly', '3', 'SC323', 'TR', '10AM', '3');
INSERT INTO Course values('35','Data Structures', '3', 'SC344', 'MW', '12:30PM', '3');
INSERT INTO Course values('36','Algorithms', '3', 'SC343', 'MW', '12:30PM', '3');
INSERT INTO Course values('37','Client Server', '3', 'SC224', 'R', '6PM', '3');
INSERT INTO Course values('38','Efficiency', '3', 'SC332', 'MW', '10AM', '3');
INSERT INTO Course values('39','Independent Study', '3', '', '', '', '3');
INSERT INTO Course values('40','PLC', '3', 'SC224', 'TR', '8AM', '3');
/*Insert English department data into course*/
INSERT INTO Course values('41','Structure', '3', 'AB212', 'MW', '10AM', '4');
INSERT INTO Course values('42','Writing I', '3', 'AB213', 'MW', '8AM', '4');
INSERT INTO Course values('43','Writing II', '3', 'AB211', 'MW', '9:30AM', '4');
INSERT INTO Course values('44','Poetry', '3', 'AB323', 'TR', '10AM', '4');
INSERT INTO Course values('45','Early Lit', '3', 'AB144', 'MW', '12:30PM', '4');
INSERT INTO Course values('46','World Lit', '3', 'AB331', 'MW', '12:30PM', '4');
INSERT INTO Course values('47','Scifi Writing', '3', 'AB124', 'R', '6PM', '4');
INSERT INTO Course values('48','Novels', '3', 'SC332', 'MW', '10AM', '4');
INSERT INTO Course values('49','Independent Study', '3', '', '', '', '4');
INSERT INTO Course values('50','Technical Writing', '3', 'AB224', 'TR', '8AM', '4');
/*Insert Psychology department data into course*/
INSERT INTO Course values('51','Intro to Psych', '3', 'SC101', 'MW', '10AM', '5');
INSERT INTO Course values('52','Psych II', '3', 'SC101', 'MW', '8AM', '5');
INSERT INTO Course values('53','Behavior', '3', 'SC101', 'MW', '9:30AM', '5');
INSERT INTO Course values('54','Cognition', '3', 'SC101', 'TR', '10AM', '5');
INSERT INTO Course values('55','Therapy', '3', 'SC101', 'MW', '12:30PM', '5');
/*Insert data into grades*/
INSERT INTO Grades values('1', '1', '1', 'A');
INSERT INTO Grades values('2', '1', '2', 'B');
INSERT INTO Grades values('3', '1', '3', 'C');
INSERT INTO Grades values('4', '1', '4', 'F');
INSERT INTO Grades values('5', '1', '5', 'B');
INSERT INTO Grades values('6', '1', '6', 'A');
INSERT INTO Grades values('7', '1', '7', 'B');
INSERT INTO Grades values('8', '1', '8', 'A');
INSERT INTO Grades values('9', '2', '1', 'A');
INSERT INTO Grades values('10', '2', '49', 'A');
INSERT INTO Grades values('11', '2', '12', 'B');
INSERT INTO Grades values('12', '2', '13', 'C');
INSERT INTO Grades values('13', '3', '14', 'F');
INSERT INTO Grades values('14', '3', '13', 'B');
INSERT INTO Grades values('15', '3', '15', 'C');
INSERT INTO Grades values('16', '3', '16', 'F');
INSERT INTO Grades values('17', '4', '17', 'A');
INSERT INTO Grades values('18', '5', '18', 'B');
INSERT INTO Grades values('19', '6', '19', 'C');
INSERT INTO Grades values('20', '6', '20', 'F');
INSERT INTO Grades values('21', '55', '1', 'B');
INSERT INTO Grades values('22', '48', '21', 'W');
INSERT INTO Grades values('23', '13', '22', 'I');
INSERT INTO Grades values('24', '22', '21', 'A');
INSERT INTO Grades values('25', '23', '23', 'B');
/*Insert values into course instructor*/
INSERT INTO course_instructor values(1,21);
INSERT INTO course_instructor values(2,22);
INSERT INTO course_instructor values(3,23);
INSERT INTO course_instructor values(4,24);
INSERT INTO course_instructor values(5,25);
INSERT INTO course_instructor values(6,26);
INSERT INTO course_instructor values(7,27);
INSERT INTO course_instructor values(8,28);
INSERT INTO course_instructor values(9,29);
INSERT INTO course_instructor values(10,30);
INSERT INTO course_instructor values(11,31);
INSERT INTO course_instructor values(12,32);
INSERT INTO course_instructor values(13,33);
INSERT INTO course_instructor values(14,34);
INSERT INTO course_instructor values(15,35);
INSERT INTO course_instructor values(16,36);
