

drop table DEPT_LOCATIONS;
create table DEPT_LOCATIONS (
  DNUMBER integer, 
  DLOCATION varchar(50));

insert into DEPT_LOCATIONS values 
(1, 'Houston');
insert into DEPT_LOCATIONS values 
(4, 'Stafford');
insert into DEPT_LOCATIONS values 
(5, 'Bellaire');
insert into DEPT_LOCATIONS values 
(5, 'Sugarland');
insert into DEPT_LOCATIONS values 
(5, 'Houston');
insert into DEPT_LOCATIONS values 
(6, 'Atlanta');
insert into DEPT_LOCATIONS values 
(6, 'Sacramento');
insert into DEPT_LOCATIONS values 
(7, 'Milwaukee');
insert into DEPT_LOCATIONS values 
(8, 'Chicago');
insert into DEPT_LOCATIONS values 
(8, 'Dallas');
insert into DEPT_LOCATIONS values 
(8, 'Philadephia');
insert into DEPT_LOCATIONS values 
(8, 'Seattle');
insert into DEPT_LOCATIONS values 
(8, 'Miami');

drop table WORKS_ON;
create table WORKS_ON (
  ESSN varchar(50), 
  PNO integer, 
  HOURS decimal(10,2));

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

drop table DEPENDENT;
create table DEPENDENT (
  ESSN varchar(50), 
  DEPENDENT_NAME varchar(50), 
  SEX varchar(50), 
  BDATE varchar(50), 
  RELATIONSHIP varchar(50));

insert into DEPENDENT values 
('333445555', 'Alice', 'F', '05-APR-1976', 'Daughter');
insert into DEPENDENT values 
('333445555', 'Theodore', 'M', '25-OCT-1973', 'Son');
insert into DEPENDENT values 
('333445555', 'Joy', 'F', '03-MAY-1948', 'Spouse');
insert into DEPENDENT values 
('987654321', 'Abner', 'M', '29-FEB-1932', 'Spouse');
insert into DEPENDENT values 
('123456789', 'Michael', 'M', '01-JAN-1978', 'Son');
insert into DEPENDENT values 
('123456789', 'Alice', 'F', '31-DEC-1978', 'Daughter');
insert into DEPENDENT values 
('123456789', 'Elizabeth', 'F', '05-MAY-1957', 'Spouse');
insert into DEPENDENT values 
('444444400', 'Johnny', 'M', '04-APR-1997', 'Son');
insert into DEPENDENT values 
('444444400', 'Tommy', 'M', '07-JUN-1999', 'Son');
insert into DEPENDENT values 
('444444401', 'Chris', 'M', '19-APR-1969', 'Spouse');
insert into DEPENDENT values 
('444444402', 'Alec', 'M', '14-FEB-1964', 'Spouse');

drop table PROJECTS;
create table PROJECTS (
  PNAME varchar(50), 
  PNUMBER integer, 
  PLOCATION varchar(50), 
  DNUM integer);

insert into PROJECTS values 
('ProductX', 1, 'Bellaire', 5);
insert into PROJECTS values 
('ProductY', 2, 'Sugarland', 5);
insert into PROJECTS values 
('ProductZ', 3, 'Houston', 5);
insert into PROJECTS values 
('Computerization', 10, 'Stafford', 4);
insert into PROJECTS values 
('Reorganization', 20, 'Houston', 1);
insert into PROJECTS values 
('Newbenefits', 30, 'Stafford', 4);
insert into PROJECTS values 
('OperatingSystems', 61, 'Jacksonville', 6);
insert into PROJECTS values 
('DatabaseSystems', 62, 'Birmingham', 6);
insert into PROJECTS values 
('Middleware', 63, 'Jackson', 6);
insert into PROJECTS values 
('InkjetPrinters', 91, 'Phoenix', 7);
insert into PROJECTS values 
('LaserPrinters', 92, 'LasVegas', 7);

drop table DEPARTMENT;
create table DEPARTMENT (
  DNAME varchar(50), 
  DNUMBER integer, 
  MGRSSN varchar(50), 
  MGRSTARTDATE varchar(50));

insert into DEPARTMENT values 
('Research', 5, '333445555', '22-MAY-1978');
insert into DEPARTMENT values 
('Administration', 4, '987654321', '01-JAN-1985');
insert into DEPARTMENT values 
('Headquarters', 1, '888665555', '19-JUN-1971');
insert into DEPARTMENT values 
('Software', 6, '111111100', '15-MAY-1999');
insert into DEPARTMENT values 
('Hardware', 7, '444444400', '15-MAY-1998');
insert into DEPARTMENT values 
('Sales', 8, '555555500', '01-JAN-1997');

