SET DEFINE OFF;
SET SCAN OFF;
DROP TABLE department cascade constraints;

PURGE RECYCLEBIN;
DROP TABLE dependent cascade constraints;

PURGE RECYCLEBIN;
DROP TABLE dept_locations cascade constraints;

PURGE RECYCLEBIN;
DROP TABLE employee cascade constraints;

PURGE RECYCLEBIN;
DROP TABLE project cascade constraints;

PURGE RECYCLEBIN;
DROP TABLE works_on cascade constraints;

PURGE RECYCLEBIN;
CREATE TABLE department(
	dname	varchar2(15),
	dnumber	integer,
	mgr_ssn	char(9),
	mgr_start_date	date);

INSERT INTO department(dname,dnumber,mgr_ssn,mgr_start_date) VALUES ('Research','5','333445555',TO_DATE('1978-05-22','YYYY-MM-DD'));
COMMIT;
INSERT INTO department(dname,dnumber,mgr_ssn,mgr_start_date) VALUES ('Administration','4','987654321',TO_DATE('1985-01-01','YYYY-MM-DD'));
INSERT INTO department(dname,dnumber,mgr_ssn,mgr_start_date) VALUES ('Headquarters','1','888665555',TO_DATE('1971-06-19','YYYY-MM-DD'));
INSERT INTO department(dname,dnumber,mgr_ssn,mgr_start_date) VALUES ('Software','6','111111100',TO_DATE('1999-05-15','YYYY-MM-DD'));
INSERT INTO department(dname,dnumber,mgr_ssn,mgr_start_date) VALUES ('Hardware','7','444444400',TO_DATE('1998-05-15','YYYY-MM-DD'));
INSERT INTO department(dname,dnumber,mgr_ssn,mgr_start_date) VALUES ('Sales','8','555555500',TO_DATE('1997-01-01','YYYY-MM-DD'));


CREATE TABLE dependent(
	essn	char(9),
	dependent_name	varchar2(15),
	sex	char(1),
	bdate	date,
	relationship	varchar2(8));

INSERT INTO dependent(essn,dependent_name,sex,bdate,relationship) VALUES ('333445555','Alice','F',TO_DATE('1976-04-05','YYYY-MM-DD'),'Daughter');
COMMIT;
INSERT INTO dependent(essn,dependent_name,sex,bdate,relationship) VALUES ('333445555','Theodore','M',TO_DATE('1973-10-25','YYYY-MM-DD'),'Son');
INSERT INTO dependent(essn,dependent_name,sex,bdate,relationship) VALUES ('333445555','Joy','F',TO_DATE('1948-05-03','YYYY-MM-DD'),'Spouse');
INSERT INTO dependent(essn,dependent_name,sex,bdate,relationship) VALUES ('987654321','Abner','M',TO_DATE('1932-02-29','YYYY-MM-DD'),'Spouse');
INSERT INTO dependent(essn,dependent_name,sex,bdate,relationship) VALUES ('123456789','Michael','M',TO_DATE('1978-01-01','YYYY-MM-DD'),'Son');
INSERT INTO dependent(essn,dependent_name,sex,bdate,relationship) VALUES ('123456789','Alice','F',TO_DATE('1978-12-31','YYYY-MM-DD'),'Daughter');
INSERT INTO dependent(essn,dependent_name,sex,bdate,relationship) VALUES ('444444400','Johnny','M',TO_DATE('1997-04-04','YYYY-MM-DD'),'Son');
INSERT INTO dependent(essn,dependent_name,sex,bdate,relationship) VALUES ('444444400','Tommy','M',TO_DATE('1999-06-07','YYYY-MM-DD'),'Son');
INSERT INTO dependent(essn,dependent_name,sex,bdate,relationship) VALUES ('444444401','Chris','M',TO_DATE('1969-04-19','YYYY-MM-DD'),'Spouse');
INSERT INTO dependent(essn,dependent_name,sex,bdate,relationship) VALUES ('444444402','Sam','M',TO_DATE('1964-02-14','YYYY-MM-DD'),'Spouse');
INSERT INTO dependent(essn,dependent_name,sex,bdate,relationship) VALUES ('123456789','Elizabeth','F',TO_DATE('1973-12-10','YYYY-MM-DD'),'Spouse');
COMMIT;


CREATE TABLE dept_locations(
	dnumber	integer,
	dlocation	varchar2(15));

INSERT INTO dept_locations(dnumber,dlocation) VALUES ('1','Houston');
COMMIT;
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','Houston');
INSERT INTO dept_locations(dnumber,dlocation) VALUES ('5','Sugarland');
INSERT INTO dept_locations(dnumber,dlocation) VALUES ('6','Atlanta');
INSERT INTO dept_locations(dnumber,dlocation) VALUES ('6','Sacramento');
INSERT INTO dept_locations(dnumber,dlocation) VALUES ('7','Milwaukee');
INSERT INTO dept_locations(dnumber,dlocation) VALUES ('8','Chicago');
INSERT INTO dept_locations(dnumber,dlocation) VALUES ('8','Dallas');
INSERT INTO dept_locations(dnumber,dlocation) VALUES ('8','Miami');
COMMIT;
INSERT INTO dept_locations(dnumber,dlocation) VALUES ('8','Philadephia');
INSERT INTO dept_locations(dnumber,dlocation) VALUES ('8','Seattle');


CREATE TABLE employee(
	fname	varchar2(15),
	minit	char(1),
	lname	varchar2(15),
	ssn	char(9),
	bdate	date,
	address	varchar2(100),
	sex	char(1),
	salary	numeric,
	super_ssn	char(9),
	dno	integer);

INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Brad','C','Knight','111111103',TO_DATE('1968-02-13','YYYY-MM-DD'),'176 Main St., Atlanta, GA','M','44000.00','111111100','6');
COMMIT;
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Evan','E','Wallis','222222200',TO_DATE('1958-01-16','YYYY-MM-DD'),'134 Pelham, Milwaukee, WI','M','92000.00','null','7');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Josh','U','Zell','222222201',TO_DATE('1954-05-22','YYYY-MM-DD'),'266 McGrady, Milwaukee, WI','M','56000.00','222222200','7');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Jared','D','James','111111100',TO_DATE('1966-10-10','YYYY-MM-DD'),'123 Peachtree, Atlanta, GA','M','85000.00','null','6');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Justin','n','Mark','111111102',TO_DATE('1966-01-12','YYYY-MM-DD'),'2342 May, Atlanta, GA','M','40000.00','111111100','6');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Jon','C','Jones','111111101',TO_DATE('1967-11-14','YYYY-MM-DD'),'111 Allgood, Atlanta, GA','M','45000.00','111111100','6');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Alex','D','Freed','444444400',TO_DATE('1950-10-09','YYYY-MM-DD'),'4333 Pillsbury, Milwaukee, WI','M','89000.00','null','7');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Ahmad','V','Jabbar','987987987',TO_DATE('1959-03-29','YYYY-MM-DD'),'980 Dallas, Houston, TX','M','25000.00','987654321','4');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Joyce','A','English','453453453',TO_DATE('1962-07-31','YYYY-MM-DD'),'5631 Rice, Houston, TX','F','25000.00','333445555','5');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Ramesh','K','Narayan','666884444',TO_DATE('1952-09-15','YYYY-MM-DD'),'971 Fire Oak, Humble, TX','M','38000.00','333445555','5');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Alicia','J','Zelaya','999887777',TO_DATE('1958-07-19','YYYY-MM-DD'),'3321 Castle, Spring, TX','F','25000.00','987654321','4');
COMMIT;
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('John','B','Smith','123456789',TO_DATE('1955-01-09','YYYY-MM-DD'),'731 Fondren, Houston, TX','M','30000.00','333445555','5');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Jennifer','S','Wallace','987654321',TO_DATE('1931-06-20','YYYY-MM-DD'),'291 Berry, Bellaire, TX','F','43000.00','888665555','4');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Franklin','T','Wong','333445555',TO_DATE('1945-12-08','YYYY-MM-DD'),'638 Voss, Houston, TX','M','40000.00','888665555','5');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('James','E','Borg','888665555',TO_DATE('1927-11-10','YYYY-MM-DD'),'450 Stone, Houston, TX','M','55000.00','null','1');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Tom','G','Brand','222222203',TO_DATE('1966-12-16','YYYY-MM-DD'),'112 Third St, Milwaukee, WI','M','62500.00','222222200','7');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Jenny','F','Vos','222222204',TO_DATE('1967-11-11','YYYY-MM-DD'),'263 Mayberry, Milwaukee, WI','F','61000.00','222222201','7');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Chris','A','Carter','222222205',TO_DATE('1960-03-21','YYYY-MM-DD'),'565 Jordan, Milwaukee, WI','F','43000.00','222222201','7');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Kim','C','Grace','333333300',TO_DATE('1970-10-23','YYYY-MM-DD'),'6677 Mills Ave, Sacramento, CA','F','79000.00','null','6');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Jeff','H','Chase','333333301',TO_DATE('1970-01-07','YYYY-MM-DD'),'145 Bradbury, Sacramento, CA','M','44000.00','333333300','6');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Bonnie','S','Bays','444444401',TO_DATE('1956-06-19','YYYY-MM-DD'),'111 Hollow, Milwaukee, WI','F','70000.00','444444400','7');
COMMIT;
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Alec','C','Best','444444402',TO_DATE('1966-06-18','YYYY-MM-DD'),'233 Solid, Milwaukee, WI','M','60000.00','444444400','7');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Sam','S','Snedden','444444403',TO_DATE('1977-07-31','YYYY-MM-DD'),'987 Windy St, Milwaukee, WI','M','48000.00','444444400','7');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Nandita','K','Ball','555555501',TO_DATE('1969-04-16','YYYY-MM-DD'),'222 Howard, Sacramento, CA','M','62000.00','555555500','6');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Bob','B','Bender','666666600',TO_DATE('1968-04-17','YYYY-MM-DD'),'8794 Garfield, Chicago, IL','M','96000.00','null','8');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Andy','C','Vile','222222202',TO_DATE('1944-06-21','YYYY-MM-DD'),'1967 Jordan, Milwaukee, WI','M','53000.00','222222200','7');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Richard','K','Marini','653298653',TO_DATE('1962-12-30','YYYY-MM-DD'),'98 Oak Forest, Katy, TX','M','37000.00','653298653','4');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Robert',null,'Hatcher','980760540',null,null,null,null,null,'2');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('John','C','James','555555500',TO_DATE('1975-06-30','YYYY-MM-DD'),'7676 Bloomington, Sacramento, CA','M','81000.00','null','6');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Carl','E','Reedy','666666611',TO_DATE('1977-06-21','YYYY-MM-DD'),'213 Ball St, Philadelphia, PA','M','32000.00','666666610','8');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Sammy','G','Hall','666666612',TO_DATE('1970-01-11','YYYY-MM-DD'),'433 Main Street, Miami, FL','M','37000.00','666666611','8');
COMMIT;
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Red','A','Bacher','666666613',TO_DATE('1980-05-21','YYYY-MM-DD'),'196 Elm Street, Miami, FL','M','33500.00','666666612','8');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Gerald','D','Small','666666607',TO_DATE('1962-05-15','YYYY-MM-DD'),'122 Ball Street, Dallas, TX','M','29000.00','666666602','8');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Arnold','A','Head','666666608',TO_DATE('1967-05-19','YYYY-MM-DD'),'233 Spring St, Dallas, TX','M','33000.00','666666602','8');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Helga','C','Pataki','666666609',TO_DATE('1969-03-11','YYYY-MM-DD'),'101 Holyoke St, Dallas, TX','F','32000.00','666666602','8');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Naveen','B','Drew','666666610',TO_DATE('1970-05-23','YYYY-MM-DD'),'198 Elm St, Philadelphia, PA','M','34000.00','666666607','8');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Kate','W','King','666666602',TO_DATE('1966-04-16','YYYY-MM-DD'),'1976 Boone Trace, Chicago, IL','F','44000.00','666666600','8');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Lyle','G','Leslie','666666603',TO_DATE('1963-06-09','YYYY-MM-DD'),'417 Hancock Ave, Chicago, IL','M','41000.00','666666601','8');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Billie','J','King','666666604',TO_DATE('1960-01-01','YYYY-MM-DD'),'556 Washington, Chicago, IL','F','38000.00','666666603','8');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Jon','A','Kramer','666666605',TO_DATE('1964-08-22','YYYY-MM-DD'),'1988 Windy Creek, Seattle, WA','M','41500.00','666666603','8');
INSERT INTO employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno) VALUES ('Ray','H','King','666666606',TO_DATE('1949-08-16','YYYY-MM-DD'),'213 Delk Road, Seattle, WA','M','44500.00','666666604','8');
COMMIT;


CREATE TABLE project(
	pname	varchar2(50),
	pnumber	integer,
	plocation	varchar2(15),
	dnum	integer);

INSERT INTO project(pname,pnumber,plocation,dnum) VALUES ('ProductX','1','Bellaire','5');
COMMIT;
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');
INSERT INTO project(pname,pnumber,plocation,dnum) VALUES ('DatabaseSystems','62','Birmingham','6');
INSERT INTO project(pname,pnumber,plocation,dnum) VALUES ('Middleware','63','Jackson','6');
INSERT INTO project(pname,pnumber,plocation,dnum) VALUES ('InkjetPrinters','91','Phoenix','7');
INSERT INTO project(pname,pnumber,plocation,dnum) VALUES ('LaserPrinters','92','LasVegas','7');
INSERT INTO project(pname,pnumber,plocation,dnum) VALUES ('OperatingSystems','61','Jacksonville','6');
COMMIT;


CREATE TABLE works_on(
	essn	char(9),
	pno	integer,
	hours	numeric);

INSERT INTO works_on(essn,pno,hours) VALUES ('123456789','2','7.5');
COMMIT;
INSERT INTO works_on(essn,pno,hours) VALUES ('666884444','3','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('453453453','1','20.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('453453453','2','20.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('333445555','2','10.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('333445555','3','10.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('333445555','10','10.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('333445555','20','10.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('999887777','30','30.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('999887777','10','10.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('987987987','10','35.0');
COMMIT;
INSERT INTO works_on(essn,pno,hours) VALUES ('987987987','30','5.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('987654321','30','20.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('987654321','20','15.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('888665555','20','0.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('111111100','61','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('111111101','61','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('111111102','61','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('111111103','61','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('222222200','62','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('222222201','62','48.0');
COMMIT;
INSERT INTO works_on(essn,pno,hours) VALUES ('222222202','62','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('222222203','62','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('222222204','62','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('222222205','62','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('333333300','63','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('333333301','63','46.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('444444400','91','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('444444401','91','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('444444402','91','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('444444403','91','40.0');
COMMIT;
INSERT INTO works_on(essn,pno,hours) VALUES ('555555500','92','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('555555501','92','44.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('666666603','91','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('666666604','91','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('666666605','92','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('666666606','91','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('666666607','61','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('666666608','62','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('666666609','63','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('666666610','61','40.0');
COMMIT;
INSERT INTO works_on(essn,pno,hours) VALUES ('666666611','61','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('666666612','61','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('666666613','61','30.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('666666613','62','10.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('666666613','63','10.0');


ALTER TABLE department ADD PRIMARY KEY (dnumber);
ALTER TABLE dependent ADD PRIMARY KEY (essn,dependent_name);
ALTER TABLE dept_locations ADD PRIMARY KEY (dnumber,dlocation);
ALTER TABLE employee ADD PRIMARY KEY (ssn);
ALTER TABLE project ADD PRIMARY KEY (pnumber);
ALTER TABLE works_on ADD PRIMARY KEY (essn,pno);
ALTER TABLE department ADD CONSTRAINT department_mgr_ssn_fkey FOREIGN KEY (mgr_ssn) REFERENCES employee(ssn);
ALTER TABLE dependent ADD CONSTRAINT dependent_essn_fkey FOREIGN KEY (essn) REFERENCES employee(ssn);
ALTER TABLE dept_locations ADD CONSTRAINT dept_locations_dnumber_fkey FOREIGN KEY (dnumber) REFERENCES department(dnumber);
ALTER TABLE project ADD CONSTRAINT project_dnum_fkey FOREIGN KEY (dnum) REFERENCES department(dnumber);
ALTER TABLE works_on ADD CONSTRAINT works_on_essn_fkey FOREIGN KEY (essn) REFERENCES employee(ssn);
ALTER TABLE works_on ADD CONSTRAINT works_on_pno_fkey FOREIGN KEY (pno) REFERENCES project(pnumber);
