drop table EMPLOYEE;
create table EMPLOYEE (
  FNAME varchar(50), 
  MINIT varchar(50), 
  LNAME varchar(50), 
  SSN varchar(50), 
  BDATE varchar(50), 
  ADDRESS varchar(50), 
  SEX varchar(50), 
  SALARY decimal(10,2), 
  SUPERSSN varchar(50), 
  DNO integer);

insert into EMPLOYEE values 
('James', 'E', 'Borg', '888665555', '10-NOV-27', '450 Stone, Houston, TX', 'M', 55000.0, 'null', 1);
insert into EMPLOYEE values 
('Franklin', 'T', 'Wong', '333445555', '08-DEC-45', '638 Voss, Houston, TX', 'M', 40000.0, '888665555', 5);
insert into EMPLOYEE values 
('Jennifer', 'S', 'Wallace', '987654321', '20-JUN-31', '291 Berry, Bellaire, TX', 'F', 43000.0, '888665555', 4);
insert into EMPLOYEE values 
('John', 'B', 'Smith', '123456789', '09-Jan-55', '731 Fondren, Houston, TX', 'M', 30000.0, '333445555', 5);
insert into EMPLOYEE values 
('Alicia', 'J', 'Zelaya', '999887777', '19-JUL-58', '3321 Castle, Spring, TX', 'F', 25000.0, '987654321', 4);
insert into EMPLOYEE values 
('Ramesh', 'K', 'Narayan', '666884444', '15-SEP-52', '971 Fire Oak, Humble, TX', 'M', 38000.0, '333445555', 5);
insert into EMPLOYEE values 
('Joyce', 'A', 'English', '453453453', '31-JUL-62', '5631 Rice, Houston, TX', 'F', 25000.0, '333445555', 5);
insert into EMPLOYEE values 
('Ahmad', 'V', 'Jabbar', '987987987', '29-MAR-59', '980 Dallas, Houston, TX', 'M', 25000.0, '987654321', 4);
insert into EMPLOYEE values 
('Jared', 'D', 'James', '111111100', '10-OCT-1966', '123 Peachtree, Atlanta, GA', 'M', 85000.0, 'null', 6);
insert into EMPLOYEE values 
('Alex', 'D', 'Freed', '444444400', '09-OCT-1950', '4333 Pillsbury, Milwaukee, WI', 'M', 89000.0, 'null', 7);
insert into EMPLOYEE values 
('John', 'C', 'James', '555555500', '30-JUN-1975', '7676 Bloomington, Sacramento, CA', 'M', 81000.0, 'null', 6);
insert into EMPLOYEE values 
('Jon', 'C', 'Jones', '111111101', '14-NOV-1967', '111 Allgood, Atlanta, GA', 'M', 45000.0, '111111100', 6);
insert into EMPLOYEE values 
('Justin', 'null', 'Mark', '111111102', '12-JAN-1966', '2342 May, Atlanta, GA', 'M', 40000.0, '111111100', 6);
insert into EMPLOYEE values 
('Brad', 'C', 'Knight', '111111103', '13-FEB-1968', '176 Main St., Atlanta, GA', 'M', 44000.0, '111111100', 6);
insert into EMPLOYEE values 
('Evan', 'E', 'Wallis', '222222200', '16-JAN-1958', '134 Pelham, Milwaukee, WI', 'M', 92000.0, 'null', 7);
insert into EMPLOYEE values 
('Josh', 'U', 'Zell', '222222201', '22-MAY-1954', '266 McGrady, Milwaukee, WI', 'M', 56000.0, '222222200', 7);
insert into EMPLOYEE values 
('Andy', 'C', 'Vile', '222222202', '21-JUN-1944', '1967 Jordan, Milwaukee, WI', 'M', 53000.0, '222222200', 7);
insert into EMPLOYEE values 
('Tom', 'G', 'Brand', '222222203', '16-DEC-1966', '112 Third St, Milwaukee, WI', 'M', 62500.0, '222222200', 7);
insert into EMPLOYEE values 
('Jenny', 'F', 'Vos', '222222204', '11-NOV-1967', '263 Mayberry, Milwaukee, WI', 'F', 61000.0, '222222201', 7);
insert into EMPLOYEE values 
('Chris', 'A', 'Carter', '222222205', '21-MAR-1960', '565 Jordan, Milwaukee, WI', 'F', 43000.0, '222222201', 7);
insert into EMPLOYEE values 
('Kim', 'C', 'Grace', '333333300', '23-OCT-1970', '6677 Mills Ave, Sacramento, CA', 'F', 79000.0, 'null', 6);
insert into EMPLOYEE values 
('Jeff', 'H', 'Chase', '333333301', '07-JAN-1970', '145 Bradbury, Sacramento, CA', 'M', 44000.0, '333333300', 6);
insert into EMPLOYEE values 
('Bonnie', 'S', 'Bays', '444444401', '19-JUN-1956', '111 Hollow, Milwaukee, WI', 'F', 70000.0, '444444400', 7);
insert into EMPLOYEE values 
('Alec', 'C', 'Best', '444444402', '18-JUN-1966', '233 Solid, Milwaukee, WI', 'M', 60000.0, '444444400', 7);
insert into EMPLOYEE values 
('Sam', 'S', 'Snedden', '444444403', '31-JUL-1977', '987 Windy St, Milwaukee, WI', 'M', 48000.0, '444444400', 7);
insert into EMPLOYEE values 
('Nandita', 'K', 'Ball', '555555501', '16-APR-1969', '222 Howard, Sacramento, CA', 'M', 62000.0, '555555500', 6);
insert into EMPLOYEE values 
('Bob', 'B', 'Bender', '666666600', '17-APR-1968', '8794 Garfield, Chicago, IL', 'M', 96000.0, 'null', 8);
insert into EMPLOYEE values 
('Jill', 'J', 'Jarvis', '666666601', '14-JAN-1966', '6234 Lincoln, Chicago, IL', 'F', 36000.0, '666666600', 8);
insert into EMPLOYEE values 
('Kate', 'W', 'King', '666666602', '16-APR-1966', '1976 Boone Trace, Chicago, IL', 'F', 44000.0, '666666600', 8);
insert into EMPLOYEE values 
('Lyle', 'G', 'Leslie', '666666603', '09-JUN-1963', '417 Hancock Ave, Chicago, IL', 'M', 41000.0, '666666601', 8);
insert into EMPLOYEE values 
('Billie', 'J', 'King', '666666604', '01-JAN-1960', '556 Washington, Chicago, IL', 'F', 38000.0, '666666603', 8);
insert into EMPLOYEE values 
('Jon', 'A', 'Kramer', '666666605', '22-AUG-1964', '1988 Windy Creek, Seattle, WA', 'M', 41500.0, '666666603', 8);
insert into EMPLOYEE values 
('Ray', 'H', 'King', '666666606', '16-AUG-1949', '213 Delk Road, Seattle, WA', 'M', 44500.0, '666666604', 8);
insert into EMPLOYEE values 
('Gerald', 'D', 'Small', '666666607', '15-MAY-1962', '122 Ball Street, Dallas, TX', 'M', 29000.0, '666666602', 8);
insert into EMPLOYEE values 
('Arnold', 'A', 'Head', '666666608', '19-MAY-1967', '233 Spring St, Dallas, TX', 'M', 33000.0, '666666602', 8);
insert into EMPLOYEE values 
('Helga', 'C', 'Pataki', '666666609', '11-MAR-1969', '101 Holyoke St, Dallas, TX', 'F', 32000.0, '666666602', 8);
insert into EMPLOYEE values 
('Naveen', 'B', 'Drew', '666666610', '23-MAY-1970', '198 Elm St, Philadelphia, PA', 'M', 34000.0, '666666607', 8);
insert into EMPLOYEE values 
('Carl', 'E', 'Reedy', '666666611', '21-JUN-1977', '213 Ball St, Philadelphia, PA', 'M', 32000.0, '666666610', 8);
insert into EMPLOYEE values 
('Sammy', 'G', 'Hall', '666666612', '11-JAN-1970', '433 Main Street, Miami, FL', 'M', 37000.0, '666666611', 8);
insert into EMPLOYEE values 
('Red', 'A', 'Bacher', '666666613', '21-MAY-1980', '196 Elm Street, Miami, FL', 'M', 33500.0, '666666612', 8);

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');

