
SET ECHO OFF;

--Order changed to reverse creation order, to better handle cascade, etc...
--Copied to end/ commented out due to way program was requested to be made
--Would it be better for later practice with this if these are at the begining?

--6
--DROP TABLE dependent CASCADE CONSTRAINTS;

--5
--DROP TABLE works_on CASCADE CONSTRAINTS;

--4
--DROP TABLE  project CASCADE CONSTRAINTS;

--3
--DROP TABLE dept_locations CASCADE CONSTRAINTS;

--2
--DROP TABLE department CASCADE CONSTRAINTS;

--1
--DROP TABLE employee CASCADE CONSTRAINTS;

--These CREATEs happened to be in a right order... I'm betting this was just luck
--From Employee, and Department: another table should be created?
--These tables, but not [the data/queries] was given to me
--The attribe, and Table names where changed to match the names, and format of names on page 72
--I probably would have chosen different length for Fname, Lname, ... type for Ssn, ...

--1
CREATE TABLE EMPLOYEE (
  Fname    varchar2(15) not null, 
  Minit    varchar2(1),
  Lname    varchar2(15) not null,
  Ssn      char(9),
  Bdate    date,
  Address  varchar2(50),
  Sex      char,
  Salary   number(10,2),
  Super_ssn char(9), --who created this?(not me) it didn't match the table on page 72
  Dno      number(4),
  primary key (Ssn),
  foreign key (Super_ssn) references employee(Ssn) -- I'm not sure how to properly deal with this for the first employee sets -- there should be another table added...
  --foreign key (dno) references department(dnumber)
);

--2
--foreign key constraint removed... see comments in the next section...
CREATE TABLE DEPARTMENT (
  Dname        varchar2(25) not null,
  Dnumber      number(4),
  Mgr_ssn       char(9) not null, 
  Mgr_start_date date, --changed to match what the book calls this column
  primary key (Dnumber),
  unique (Dname),
  foreign key (Mgr_ssn) references employee(Ssn)
);


--If these tables where designed differently ALTER TABLE might not be necessary.
--And there wouldn't be a need to run an UPDATE on new employee managers of new departments.
ALTER TABLE employee ADD (
  foreign key (Dno) references department(Dnumber)
);


--3
CREATE TABLE DEPT_LOCATIONS (
  Dnumber   number(4),
  Dlocation varchar2(15), 
  primary key (Dnumber,Dlocation),
  foreign key (Dnumber) references department(Dnumber)
);

--4 -- project is a BAD table name, it's a keyword... come on... who designed this?
CREATE TABLE PROJECT (
  Pname      varchar2(25) not null,
  Pnumber    number(4),
  Plocation  varchar2(15),
  Dnum       number(4) not null,
  primary key (Pnumber),
  unique (Pname),
  foreign key (Dnum) references department(Dnumber)
);

--5
CREATE TABLE WORKS_ON (
  Essn   char(9),
  Pno    number(4),
  Hours  number(4,1),
  primary key (Essn,Pno),
  foreign key (Essn) references employee(Ssn),
  foreign key (Pno) references project(Pnumber)
);

--6
CREATE TABLE DEPENDENT (
  Essn           char(9),
  Dependent_name varchar2(15),
  Sex            char,
  Bdate          date,
  Relationship   varchar2(8),
  primary key (Essn,Dependent_name),
  foreign key (Essn) references employee(Ssn)
);

--COMMIT; --SUICIDE; --haha j/k 

--This too has to be put in the right order... 
--Stupid employee/department design... arg...
--At least the table in the book uses a nice DATE format!

--Data from table on page 72

--1 --Non reordered version
--INSERT INTO EMPLOYEE(Fname ,Minit ,Lname ,Ssn ,Bdate ,Address ,Sex , Salary ,Super_ssn ,Dno) VALUES('John' ,'B' ,'Smith' ,'123456789',TO_DATE( '1965-01-09', 'YYYY-MM-DD' ),'731 Fondren, Houston, TX ','M' ,30000,'333445555',5);
--INSERT INTO EMPLOYEE(Fname ,Minit ,Lname ,Ssn ,Bdate ,Address ,Sex , Salary ,Super_ssn ,Dno) VALUES('Franklin' ,'T' ,'Wong' ,'333445555',TO_DATE( '1955-12-08', 'YYYY-MM-DD' ),'638 Voss, Houston, TX ','M' ,40000,'888665555',5);
--INSERT INTO EMPLOYEE(Fname ,Minit ,Lname ,Ssn ,Bdate ,Address ,Sex , Salary ,Super_ssn ,Dno) VALUES('Alicia' ,'J' ,'Zelaya' ,'999887777',TO_DATE( '1968-01-19', 'YYYY-MM-DD' ),'3321 Castle, Spring, TX ','F' ,25000,'987654321',4);
--INSERT INTO EMPLOYEE(Fname ,Minit ,Lname ,Ssn ,Bdate ,Address ,Sex , Salary ,Super_ssn ,Dno) VALUES('Jennifer' ,'S' ,'Wallace' ,'987654321',TO_DATE( '1941-06-20', 'YYYY-MM-DD' ),'291 Berry, Bellaire, TX ','F' ,43000,'888665555',4);
--INSERT INTO EMPLOYEE(Fname ,Minit ,Lname ,Ssn ,Bdate ,Address ,Sex , Salary ,Super_ssn ,Dno) VALUES('Ramesh' ,'K' ,'Narayan' ,'666884444',TO_DATE( '1962-09-15', 'YYYY-MM-DD' ),'975 Fire Oak, Humble, TX ','M' ,38000,'333445555',5);
--INSERT INTO EMPLOYEE(Fname ,Minit ,Lname ,Ssn ,Bdate ,Address ,Sex , Salary ,Super_ssn ,Dno) VALUES('Joyce' ,'A' ,'English' ,'453453453',TO_DATE( '1972-07-31', 'YYYY-MM-DD' ),'5631 Rice, Houston, TX ','F' ,25000,'333445555',5);
--INSERT INTO EMPLOYEE(Fname ,Minit ,Lname ,Ssn ,Bdate ,Address ,Sex , Salary ,Super_ssn ,Dno) VALUES('Ahmad' ,'V' ,'Jabbar' ,'987987987',TO_DATE( '1969-03-29', 'YYYY-MM-DD' ),'980 Dallas, Houston, TX ','M' ,25000,'987654321',4);
--INSERT INTO EMPLOYEE(Fname ,Minit ,Lname ,Ssn ,Bdate ,Address ,Sex , Salary ,Super_ssn ,Dno) VALUES('James' ,'E' ,'Borg' ,'888665555',TO_DATE( '1937-11-10', 'YYYY-MM-DD' ),'450 Stone, Houston, TX ','M' ,55000,NULL ,1);


--1 and 2 --modified the manager of each department, and reordered so that Super_ssn, Dno... won't cause problems

INSERT INTO EMPLOYEE(Fname ,Minit ,Lname ,Ssn ,Bdate ,Address ,Sex , Salary ,Super_ssn ,Dno) VALUES('James' ,'E' ,'Borg' ,'888665555',TO_DATE( '1937-11-10', 'YYYY-MM-DD' ),'450 Stone, Houston, TX ','M' ,55000,NULL ,NULL);
INSERT INTO DEPARTMENT(Dname ,Dnumber ,Mgr_ssn ,Mgr_start_date) VALUES( 'Headquarters' ,1,'888665555',TO_DATE( '1981-06-19', 'YYYY-MM-DD' ));
UPDATE EMPLOYEE SET Dno=1 WHERE Ssn='888665555';

INSERT INTO EMPLOYEE(Fname ,Minit ,Lname ,Ssn ,Bdate ,Address ,Sex , Salary ,Super_ssn ,Dno) VALUES('Jennifer' ,'S' ,'Wallace' ,'987654321',TO_DATE( '1941-06-20', 'YYYY-MM-DD' ),'291 Berry, Bellaire, TX ','F' ,43000,'888665555',NULL);
INSERT INTO DEPARTMENT(Dname ,Dnumber ,Mgr_ssn ,Mgr_start_date) VALUES( 'Administration' ,4,'987654321',TO_DATE( '1995-01-01', 'YYYY-MM-DD' ));
UPDATE EMPLOYEE SET Dno=4 WHERE Ssn='987654321';

INSERT INTO EMPLOYEE(Fname ,Minit ,Lname ,Ssn ,Bdate ,Address ,Sex , Salary ,Super_ssn ,Dno) VALUES('Franklin' ,'T' ,'Wong' ,'333445555',TO_DATE( '1955-12-08', 'YYYY-MM-DD' ),'638 Voss, Houston, TX ','M' ,40000,'888665555',NULL);
INSERT INTO DEPARTMENT(Dname ,Dnumber ,Mgr_ssn ,Mgr_start_date) VALUES( 'Research' ,5,'333445555',TO_DATE( '1988-05-22', 'YYYY-MM-DD' ));
UPDATE EMPLOYEE SET Dno=5 WHERE Ssn='333445555';
---
INSERT INTO EMPLOYEE(Fname ,Minit ,Lname ,Ssn ,Bdate ,Address ,Sex , Salary ,Super_ssn ,Dno) VALUES('Alicia' ,'J' ,'Zelaya' ,'999887777',TO_DATE( '1968-01-19', 'YYYY-MM-DD' ),'3321 Castle, Spring, TX ','F' ,25000,'987654321',4);
INSERT INTO EMPLOYEE(Fname ,Minit ,Lname ,Ssn ,Bdate ,Address ,Sex , Salary ,Super_ssn ,Dno) VALUES('Ahmad' ,'V' ,'Jabbar' ,'987987987',TO_DATE( '1969-03-29', 'YYYY-MM-DD' ),'980 Dallas, Houston, TX ','M' ,25000,'987654321',4);

INSERT INTO EMPLOYEE(Fname ,Minit ,Lname ,Ssn ,Bdate ,Address ,Sex , Salary ,Super_ssn ,Dno) VALUES('John' ,'B' ,'Smith' ,'123456789',TO_DATE( '1965-01-09', 'YYYY-MM-DD' ),'731 Fondren, Houston, TX ','M' ,30000,'333445555',5);
INSERT INTO EMPLOYEE(Fname ,Minit ,Lname ,Ssn ,Bdate ,Address ,Sex , Salary ,Super_ssn ,Dno) VALUES('Ramesh' ,'K' ,'Narayan' ,'666884444',TO_DATE( '1962-09-15', 'YYYY-MM-DD' ),'975 Fire Oak, Humble, TX ','M' ,38000,'333445555',5);
INSERT INTO EMPLOYEE(Fname ,Minit ,Lname ,Ssn ,Bdate ,Address ,Sex , Salary ,Super_ssn ,Dno) VALUES('Joyce' ,'A' ,'English' ,'453453453',TO_DATE( '1972-07-31', 'YYYY-MM-DD' ),'5631 Rice, Houston, TX ','F' ,25000,'333445555',5);

--2 --reordered this too... wait... assignment says to fill each table with at least 5 entities, but I only have 3 to choose from, and it also says add an employee called Mavis Neff, but that's not in the list of choices either, what's up?
--INSERT INTO DEPARTMENT(Dname ,Dnumber ,Mgr_ssn ,Mgr_start_date) VALUES( 'Headquarters' ,1,'888665555',TO_DATE( '1981-06-19', 'YYYY-MM-DD' ));
--INSERT INTO DEPARTMENT(Dname ,Dnumber ,Mgr_ssn ,Mgr_start_date) VALUES( 'Administration' ,4,'987654321',TO_DATE( '1995-01-01', 'YYYY-MM-DD' ));
--INSERT INTO DEPARTMENT(Dname ,Dnumber ,Mgr_ssn ,Mgr_start_date) VALUES( 'Research' ,5,'333445555',TO_DATE( '1988-05-22', 'YYYY-MM-DD' ));

--3
INSERT INTO DEPT_LOCATIONS(Dnumber,Dlocation) VALUES(1,'Houston');
INSERT INTO DEPT_LOCATIONS(Dnumber,Dlocation) VALUES(4,'Stafford');
INSERT INTO DEPT_LOCATIONS(Dnumber,Dlocation) VALUES(5,'Bellaire');
INSERT INTO DEPT_LOCATIONS(Dnumber,Dlocation) VALUES(5,'Sugarland');
INSERT INTO DEPT_LOCATIONS(Dnumber,Dlocation) VALUES(5,'Houston');

--4
INSERT INTO PROJECT(Pname ,Pnumber ,Plocation ,Dnum) VALUES('ProductX' ,1,'Bellaire' ,5);
INSERT INTO PROJECT(Pname ,Pnumber ,Plocation ,Dnum) VALUES('ProductY' ,2,'Sugarland' ,5);
INSERT INTO PROJECT(Pname ,Pnumber ,Plocation ,Dnum) VALUES('ProductZ' ,3,'Houston' ,5);
INSERT INTO PROJECT(Pname ,Pnumber ,Plocation ,Dnum) VALUES('Computerization' ,10,'Stafford' ,4);
INSERT INTO PROJECT(Pname ,Pnumber ,Plocation ,Dnum) VALUES('Reorganization' ,20,'Houston' ,1);
INSERT INTO PROJECT(Pname ,Pnumber ,Plocation ,Dnum) VALUES('Newbenefits' ,30,'Stafford' ,4);

--5

INSERT INTO WORKS_ON(Essn,Pno,Hours) VALUES('123456789',1,32.5);
INSERT INTO WORKS_ON(Essn,Pno,Hours) VALUES('123456789',2,7.5);
INSERT INTO WORKS_ON(Essn,Pno,Hours) VALUES('666884444',3,40);
INSERT INTO WORKS_ON(Essn,Pno,Hours) VALUES('453453453',1,20);
INSERT INTO WORKS_ON(Essn,Pno,Hours) VALUES('453453453',2,20);
INSERT INTO WORKS_ON(Essn,Pno,Hours) VALUES('333445555',2,10);
INSERT INTO WORKS_ON(Essn,Pno,Hours) VALUES('333445555',3,10);
INSERT INTO WORKS_ON(Essn,Pno,Hours) VALUES('333445555',10,10);
INSERT INTO WORKS_ON(Essn,Pno,Hours) VALUES('333445555',20,10);
INSERT INTO WORKS_ON(Essn,Pno,Hours) VALUES('999887777',30,30);
INSERT INTO WORKS_ON(Essn,Pno,Hours) VALUES('999887777',10,10);
INSERT INTO WORKS_ON(Essn,Pno,Hours) VALUES('987987987',10,35);
INSERT INTO WORKS_ON(Essn,Pno,Hours) VALUES('987987987',30,5);
INSERT INTO WORKS_ON(Essn,Pno,Hours) VALUES('987654321',30,20);
INSERT INTO WORKS_ON(Essn,Pno,Hours) VALUES('987654321',20,15);
INSERT INTO WORKS_ON(Essn,Pno,Hours) VALUES('888665555',20,null);

--6

INSERT INTO DEPENDENT(Essn,Dependent_name,Sex,Bdate,Relationship) VALUES('333445555','Alice','F',TO_DATE( '1986-04-05', 'YYYY-MM-DD' ),'Daughter');
INSERT INTO DEPENDENT(Essn,Dependent_name,Sex,Bdate,Relationship) VALUES('333445555','Theodore','M',TO_DATE( '1983-10-25', 'YYYY-MM-DD' ),'Son');
INSERT INTO DEPENDENT(Essn,Dependent_name,Sex,Bdate,Relationship) VALUES('333445555','Joy','F',TO_DATE( '1958-05-03', 'YYYY-MM-DD' ),'Spouse');
INSERT INTO DEPENDENT(Essn,Dependent_name,Sex,Bdate,Relationship) VALUES('987654321','Abner','M',TO_DATE( '1942-02-28', 'YYYY-MM-DD' ),'Spouse');
INSERT INTO DEPENDENT(Essn,Dependent_name,Sex,Bdate,Relationship) VALUES('123456789','Michael','M',TO_DATE( '1988-01-04', 'YYYY-MM-DD' ),'Son');
INSERT INTO DEPENDENT(Essn,Dependent_name,Sex,Bdate,Relationship) VALUES('123456789','Alice','F',TO_DATE( '1988-12-30', 'YYYY-MM-DD' ),'Daughter');
INSERT INTO DEPENDENT(Essn,Dependent_name,Sex,Bdate,Relationship) VALUES('123456789','Elizabeth','F',TO_DATE( '1967-05-05', 'YYYY-MM-DD' ),'Spouse');

SET ECHO ON;

--Query Section
--List the first and last names of all employees who work for the Research department
SELECT Fname, Lname FROM EMPLOYEE e, DEPARTMENT d WHERE e.Dno=d.Dnumber AND d.Dname='Research';

--List the first and last names of all employees who work on the project called “ProductX”
SELECT Fname, Lname FROM EMPLOYEE e, PROJECT p, Works_on w WHERE w.Essn=e.Ssn AND w.Pno=p.Pnumber AND p.Pname='ProductX';

--List the name of each project and the number of employees who work on each one. --By 'one' I'm assuming you mean in each project
SELECT Pname, COUNT(*) FROM WORKS_ON w JOIN PROJECT p ON w.pno=p.pnumber GROUP BY Pname;

--List the salary and birth date of Mavis Neff
--There isn't a person named Mavis Neff in the table on page 72 so
--This returns 'no rows selected' or '0 rows selected', ...depending upon the software version, etc...
SELECT Salary, Bdate FROM EMPLOYEE e WHERE e.Fname='Mavis' AND e.Lname='Neff';

--Let's try it again with someone who's actually on the table...
--List the salary and birth date of 'Joyce English'
SELECT Salary, Bdate FROM EMPLOYEE e WHERE e.Fname='Joyce' AND e.Lname='English';

--List the number of employees and average salary for the Research department
SELECT COUNT(*), AVG(e.Salary) FROM EMPLOYEE e, DEPARTMENT d WHERE e.Dno=d.Dnumber AND d.Dname='Research'; 

SET ECHO OFF;

--Order changed from file given to reverse creation order, better handle cascade, etc...
--CASCADE CONSTRAINTS isn't necessary for this if the tables are designed nicely designed, and these are in a correct order.

--6
DROP TABLE dependent CASCADE CONSTRAINTS;

--5
DROP TABLE works_on CASCADE CONSTRAINTS;

--4
DROP TABLE project CASCADE CONSTRAINTS;

--3
DROP TABLE dept_locations CASCADE CONSTRAINTS;

--2
DROP TABLE department CASCADE CONSTRAINTS;

--1
DROP TABLE employee CASCADE CONSTRAINTS;

ROLLBACK; -- I'm done here!

