DROP TABLE EMPLOYEE;
CREATE TABLE EMPLOYEE
     (FNAME VARCHAR(10),
     MINIT CHAR(1),
     LNAME VARCHAR(10),
     SSN CHAR(9) NOT NULL,
     BDATE DATETIME,
     ADDRESS VARCHAR(25),
     SEX CHAR(1),
     SALARY MONEY,
     SUPERSSN CHAR(9),
     DNO INT,
     PRIMARY KEY (SSN));

DROP TABLE DEPT_LOCATIONS;
CREATE TABLE DEPT_LOCATIONS
     (DNUMBER INT NOT NULL,
     DLOCATION CHAR(12) NOT NULL,
     PRIMARY KEY (DNUMBER, DLOCATION));

DROP TABLE DEPARTMENT;
CREATE TABLE DEPARTMENT
     (DNAME VARCHAR(16),
     DNUMBER INT NOT NULL,
     MGRSSN CHAR(9),
     MGRSTARTDATE DATETIME,
     PRIMARY KEY (DNUMBER));

DROP TABLE WORKS_ON;
CREATE TABLE WORKS_ON
     (ESSN CHAR(9) NOT NULL,
     PNO INT NOT NULL,
     HOURS DECIMAL(5,1),
     PRIMARY KEY (ESSN, PNO));

INSERT INTO EMPLOYEE VALUES
     ('John','B','Smith','123456789','09-Jan-55', '731 Fondren,Houston,TX','M',30000,'333445555',5);
INSERT INTO EMPLOYEE VALUES
     ('Franklin','T','Wong','333445555','08-Dec-45', '638 Voss,Houston,TX','M',40000,'888665555',5);
INSERT INTO EMPLOYEE VALUES
     ('Alicia','J','Zelaya','999887777','19-Jul-58','3321 Castle, Spring,TX','F',25000,'987654321',4);
INSERT INTO EMPLOYEE VALUES
     ('Jennifer','S','Wallace','987654321','20-Jun-31','291 Berry, Bellaire,TX','F',43000,'888665555',4);
INSERT INTO EMPLOYEE VALUES
     ('Ramesh','K','Narayan','666884444','15-Sep-52','975 Fire Oak, Humble,TX','M',38000,'333445555',5);
INSERT INTO EMPLOYEE VALUES
     ('Joyce','A','English','453453453','31-Jul-62','5631 Rice, Houston,TX','F',25000,'333445555',5);
INSERT INTO EMPLOYEE VALUES
     ('Ahmad','V','Jabbar','987987987','29-Mar-59','980 Dallas, Houston,TX','M',25000,'987654321',4);
INSERT INTO EMPLOYEE VALUES
     ('James','E','Borg','888665555','10-Nov-27','450 Stone, Houston, TX','M',55000,NULL,1);

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 DEPARTMENT VALUES
     ('Research',5,'333445555','22-May-78');
INSERT INTO DEPARTMENT VALUES
     ('Administration',4,'987654321','01-Jan-85');
INSERT INTO DEPARTMENT VALUES
     ('Headquarters',1,'888665555','19-Jun-71');

INSERT INTO WORKS_ON VALUES
     ('123456789',1,32.5);
INSERT INTO WORKS_ON VALUES
     ('123456789',2,7.5);
INSERT INTO WORKS_ON VALUES
     ('453453453',1,20);
INSERT INTO WORKS_ON VALUES
     ('453453453',2,20);
INSERT INTO WORKS_ON VALUES
     ('333445555',1,10);
INSERT INTO WORKS_ON VALUES
     ('333445555',2,10);
INSERT INTO WORKS_ON VALUES
     ('333445555',3,10);
INSERT INTO WORKS_ON VALUES
     ('333445555',10,10);
INSERT INTO WORKS_ON VALUES
     ('333445555',20,10);
INSERT INTO WORKS_ON VALUES
     ('333445555',30,10);
INSERT INTO WORKS_ON VALUES
     ('999887777',30,30);
INSERT INTO WORKS_ON VALUES
     ('999887777',10,10);
INSERT INTO WORKS_ON VALUES
     ('987987987',10,35);
INSERT INTO WORKS_ON VALUES
     ('987987987',30,5);
INSERT INTO WORKS_ON VALUES
     ('987654321',30,20);
INSERT INTO WORKS_ON VALUES
     ('987654321',20,15);
INSERT INTO WORKS_ON VALUES
     ('888665555',20,NULL);

drop table project;

create table project
(pname varchar(20),
pnumber INT not null,
plocation varchar(12),
dnum INT,
primary key (pnumber));

insert into project values ('ProductX',1,'Bellaire',5);
insert into project values ('ProductY',2,'Sugarland',5);
insert into project values ('ProductZ',3,'Houston',5);
insert into project values ('Computerization',10,'Stafford',4);
insert into project values ('Reorganization',20,'Houston',1);
insert into project values ('New benefits',30,'Stafford',4);