/* Database Systems, Coronel/Morris */
/* Type of SQL : MySQL  */



/* -- */
CREATE TABLE JOB (
  JOB_CODE        VARCHAR(3),
  JOB_DESCRIPTION VARCHAR(25),
  JOB_CHG_HOUR    FLOAT(8),
  JOB_LAST_UPDATE DATETIME,
  PRIMARY KEY(JOB_CODE)
);
INSERT INTO JOB VALUES('500','Programmer',           '35.75','2015-11-20');
INSERT INTO JOB VALUES('501','Systems Analyst',      '96.75','2015-11-20');
INSERT INTO JOB VALUES('502','Database Designer',    '125',  '2016-3-24');
INSERT INTO JOB VALUES('503','Electrical Engineer',  '84.5', '2015-11-20');
INSERT INTO JOB VALUES('504','Mechanical Engineer',  '67.9', '2015-11-20');
INSERT INTO JOB VALUES('505','Civil Engineer',       '55.78','2015-11-20');
INSERT INTO JOB VALUES('506','Clerical Support',     '26.87','2015-11-20');
INSERT INTO JOB VALUES('507','DSS Analyst',          '45.95','2015-11-20');
INSERT INTO JOB VALUES('508','Applications Designer','48.1', '2016-3-24');
INSERT INTO JOB VALUES('509','Bio Technician',       '34.55','2015-11-20');
INSERT INTO JOB VALUES('510','General Support',      '18.36','2015-11-20');


/* -- */

CREATE TABLE EMPLOYEE (
  EMP_NUM       VARCHAR(3),
  EMP_LNAME     VARCHAR(15),
  EMP_FNAME     VARCHAR(15),
  EMP_INITIAL   VARCHAR(1),
  EMP_HIREDATE  DATETIME,
  JOB_CODE      VARCHAR(3),
  EMP_YEARS     INT(3),
  PRIMARY KEY(EMP_NUM),
  FOREIGN KEY (JOB_CODE) REFERENCES JOB(JOB_CODE)
);
INSERT INTO EMPLOYEE VALUES('101','News','John','G','2000-11-8','502','4');
INSERT INTO EMPLOYEE VALUES('102','Senior','David','H','1989-7-12','501','15');
INSERT INTO EMPLOYEE VALUES('103','Arbough','June','E','1996-12-1','503','8');
INSERT INTO EMPLOYEE VALUES('104','Ramoras','Anne','K','1987-11-15','501','17');
INSERT INTO EMPLOYEE VALUES('105','Johnson','Alice','K','1993-2-1','502','12');
INSERT INTO EMPLOYEE VALUES('106','Smithfield','William',NULL,'2004-6-22','500','0');
INSERT INTO EMPLOYEE VALUES('107','Alonzo','Maria','D','1993-10-10','500','11');
INSERT INTO EMPLOYEE VALUES('108','Washington','Ralph','B','1991-8-22','501','13');
INSERT INTO EMPLOYEE VALUES('109','Smith','Larry','W','1997-7-18','501','7');
INSERT INTO EMPLOYEE VALUES('110','Olenko','Gerald','A','1995-12-11','505','9');
INSERT INTO EMPLOYEE VALUES('111','Wabash','Geoff','B','1991-4-4','506','14');
INSERT INTO EMPLOYEE VALUES('112','Smithson','Darlene','M','1994-10-23','507','10');
INSERT INTO EMPLOYEE VALUES('113','Joenbrood','Delbert','K','1996-11-15','508','8');
INSERT INTO EMPLOYEE VALUES('114','Jones','Annelise',NULL,'1993-8-20','508','11');
INSERT INTO EMPLOYEE VALUES('115','Bawangi','Travis','B','1992-1-25','501','13');
INSERT INTO EMPLOYEE VALUES('116','Pratt','Gerald','L','1997-3-5','510','8');
INSERT INTO EMPLOYEE VALUES('117','Williamson','Angie','H','1996-6-19','509','8');
INSERT INTO EMPLOYEE VALUES('118','Frommer','James','J','2005-1-4','510','0');

/* -- */

CREATE TABLE PROJECT (
  PROJ_NUM      VARCHAR(3),
  PROJ_NAME     VARCHAR(25),
  PROJ_VALUE    FLOAT(8),
  PROJ_BALANCE  FLOAT(8),
  EMP_NUM       VARCHAR(3),
  PRIMARY KEY(PROJ_NUM),
  FOREIGN KEY(EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
);
INSERT INTO PROJECT VALUES('15','Evergreen','1453500','1002350','103');
INSERT INTO PROJECT VALUES('18','Amber Wave','3500500','2110346','108');
INSERT INTO PROJECT VALUES('22','Rolling Tide','805000','500345.2','102');
INSERT INTO PROJECT VALUES('25','Starflight','2650500','2309880','107');

/* -- */
CREATE TABLE ASSIGNMENT (
  ASSIGN_NUM      INT(5),
  ASSIGN_DATE     DATETIME,
  PROJ_NUM        VARCHAR(3),
  EMP_NUM         VARCHAR(3),
  ASSIGN_JOB      VARCHAR(3),
  ASSIGN_CHG_HR   FLOAT(8),
  ASSIGN_HOURS    FLOAT(8),
  ASSIGN_CHARGE   FLOAT(8),
  PRIMARY KEY (ASSIGN_NUM),
  FOREIGN KEY (PROJ_NUM) REFERENCES PROJECT(PROJ_NUM),
  FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
);
INSERT INTO ASSIGNMENT VALUES('1001','2016-3-22','18','103','503','84.5','3.5','295.75');
INSERT INTO ASSIGNMENT VALUES('1002','2016-3-22','22','117','509','34.55','4.2','145.11');
INSERT INTO ASSIGNMENT VALUES('1003','2016-3-22','18','117','509','34.55','2','69.10');
INSERT INTO ASSIGNMENT VALUES('1004','2016-3-22','18','103','503','84.5','5.9','498.55');
INSERT INTO ASSIGNMENT VALUES('1005','2016-3-22','25','108','501','96.75','2.2','212.85');
INSERT INTO ASSIGNMENT VALUES('1006','2016-3-22','22','104','501','96.75','4.2','406.35');
INSERT INTO ASSIGNMENT VALUES('1007','2016-3-22','25','113','508','50.75','3.8','192.85');
INSERT INTO ASSIGNMENT VALUES('1008','2016-3-22','18','103','503','84.5','0.9','76.05');
INSERT INTO ASSIGNMENT VALUES('1009','2016-3-23','15','115','501','96.75','5.6','541.80');
INSERT INTO ASSIGNMENT VALUES('1010','2016-3-23','15','117','509','34.55','2.4','82.92');
INSERT INTO ASSIGNMENT VALUES('1011','2016-3-23','25','105','502','105','4.3','451.5');
INSERT INTO ASSIGNMENT VALUES('1012','2016-3-23','18','108','501','96.75','3.4','328.95');
INSERT INTO ASSIGNMENT VALUES('1013','2016-3-23','25','115','501','96.75','2','193.5');
INSERT INTO ASSIGNMENT VALUES('1014','2016-3-23','22','104','501','96.75','2.8','270.9');
INSERT INTO ASSIGNMENT VALUES('1015','2016-3-23','15','103','503','84.5','6.1','515.45');
INSERT INTO ASSIGNMENT VALUES('1016','2016-3-23','22','105','502','105','4.7','493.5');
INSERT INTO ASSIGNMENT VALUES('1017','2016-3-23','18','117','509','34.55','3.8','131.29');
INSERT INTO ASSIGNMENT VALUES('1018','2016-3-23','25','117','509','34.55','2.2','76.01');
INSERT INTO ASSIGNMENT VALUES('1019','2016-3-24','25','104','501','110.5','4.9','541.45');
INSERT INTO ASSIGNMENT VALUES('1020','2016-3-24','15','101','502','125','3.1','387.5');
INSERT INTO ASSIGNMENT VALUES('1021','2016-3-24','22','108','501','110.5','2.7','298.35');
INSERT INTO ASSIGNMENT VALUES('1022','2016-3-24','22','115','501','110.5','4.9','541.45');
INSERT INTO ASSIGNMENT VALUES('1023','2016-3-24','22','105','502','125','3.5','437.5');
INSERT INTO ASSIGNMENT VALUES('1024','2016-3-24','15','103','503','84.5','3.3','278.85');
INSERT INTO ASSIGNMENT VALUES('1025','2016-3-24','18','117','509','34.55','4.2','145.11');

/* -- */

CREATE TABLE EMP_1 (
  EMP_NUM       VARCHAR(3),
  EMP_LNAME     VARCHAR(15),
  EMP_FNAME     VARCHAR(15),
  EMP_INITIAL   VARCHAR(1),
  EMP_HIREDATE  DATETIME,
  JOB_CODE      VARCHAR(3)
);
INSERT INTO EMP_1 VALUES('101','News','John','G','2000-11-8','502');
INSERT INTO EMP_1 VALUES('102','Senior','David','H','1989-7-12','501');
INSERT INTO EMP_1 VALUES('103','Arbough','June','E','1996-12-1','500');
INSERT INTO EMP_1 VALUES('104','Ramoras','Anne','K','1987-11-15','501');
INSERT INTO EMP_1 VALUES('105','Johnson','Alice','K','1993-2-1','502');
INSERT INTO EMP_1 VALUES('106','Smithfield','William',NULL,'2004-6-22','500');
INSERT INTO EMP_1 VALUES('107','Alonzo','Maria','D','1993-10-10','500');
INSERT INTO EMP_1 VALUES('108','Washington','Ralph','B','1991-8-22','501');
INSERT INTO EMP_1 VALUES('109','Smith','Larry','W','1997-1-18','501');

/* -- */

CREATE TABLE EMP_2 (
  EMP_NUM       VARCHAR(3),
  EMP_LNAME     VARCHAR(15),
  EMP_FNAME     VARCHAR(15),
  EMP_INITIAL   VARCHAR(1),
  EMP_HIREDATE  DATETIME,
  JOB_CODE      VARCHAR(3),
  EMP_PCT       FLOAT(8),
  PROJ_NUM      VARCHAR(2)
);
INSERT INTO EMP_2 VALUES('101','News','John','G','2000-11-8','502','5',NULL);
INSERT INTO EMP_2 VALUES('102','Senior','David','H','1989-7-12','501','8',NULL);
INSERT INTO EMP_2 VALUES('103','Arbough','June','E','1996-12-1','500','3.85',NULL);
INSERT INTO EMP_2 VALUES('104','Ramoras','Anne','K','1987-11-15','501','10',NULL);
INSERT INTO EMP_2 VALUES('105','Johnson','Alice','K','1993-2-1','502','5',NULL);
INSERT INTO EMP_2 VALUES('106','Smithfield','William',NULL,'2004-6-22','500','6.2',NULL);
INSERT INTO EMP_2 VALUES('107','Alonzo','Maria','D','1993-10-10','500','5.15',NULL);
INSERT INTO EMP_2 VALUES('108','Washington','Ralph','B','1991-8-22','501','10',NULL);
INSERT INTO EMP_2 VALUES('109','Smith','Larry','W','1997-7-18','501','2',NULL);

/* -- */

CREATE TABLE EMP_2_MOD (
  EMP_NUM       VARCHAR(3),
  EMP_LNAME     VARCHAR(15),
  EMP_FNAME     VARCHAR(15),
  EMP_INITIAL   VARCHAR(1),
  EMP_HIREDATE  DATETIME,
  JOB_CODE      VARCHAR(3),
  EMP_PCT       FLOAT(8),
  PROJ_NUM      VARCHAR(2)
);
INSERT INTO EMP_2_MOD VALUES('101','News','John','G','2000-11-8','502','5','25');
INSERT INTO EMP_2_MOD VALUES('102','Senior','David','H','1989-7-12','501','8',NULL);
INSERT INTO EMP_2_MOD VALUES('103','Arbough','June','E','1996-12-1','500','3.85','18');
INSERT INTO EMP_2_MOD VALUES('104','Ramoras','Anne','K','1987-11-15','501','10',NULL);
INSERT INTO EMP_2_MOD VALUES('105','Johnson','Alice','K','1993-2-1','502','5','25');
INSERT INTO EMP_2_MOD VALUES('106','Smithfield','William',NULL,'2004-6-22','500','6.2','18');
INSERT INTO EMP_2_MOD VALUES('107','Alonzo','Maria','D','1993-10-10','500','5.15','18');
INSERT INTO EMP_2_MOD VALUES('108','Washington','Ralph','B','1991-8-22','501','10',NULL);
INSERT INTO EMP_2_MOD VALUES('109','Smith','Larry','W','1997-7-18','501','2',NULL);




