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

ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY';

CREATE TABLE ASSIGNMENT (
ASSIGN_NUM varchar2(50),
EMP_NUM varchar2(5),
PROJ_CODE varchar2(5)
);
INSERT INTO ASSIGNMENT VALUES('1','10123','1');
INSERT INTO ASSIGNMENT VALUES('2','10121','2');
INSERT INTO ASSIGNMENT VALUES('3','10123','3');
INSERT INTO ASSIGNMENT VALUES('4','10123','4');
INSERT INTO ASSIGNMENT VALUES('5','10121','1');
INSERT INTO ASSIGNMENT VALUES('6','10124','2');
INSERT INTO ASSIGNMENT VALUES('7','10124','3');
INSERT INTO ASSIGNMENT VALUES('8','10124','5');

/* -- */


CREATE TABLE EMPLOYEE (
EMP_NUM varchar2(5),
EMP_LNAME varchar2(15)
);
INSERT INTO EMPLOYEE VALUES('10121','Rogers');
INSERT INTO EMPLOYEE VALUES('10122','O''Leery');
INSERT INTO EMPLOYEE VALUES('10123','Panera');
INSERT INTO EMPLOYEE VALUES('10124','Johnson');

/* -- */

CREATE TABLE ORGANIZATION (
ORG_CODE varchar2(15),
ORG_NAME varchar2(20)
);
INSERT INTO ORGANIZATION VALUES('RC','Red Cross');
INSERT INTO ORGANIZATION VALUES('UW','United Way');
INSERT INTO ORGANIZATION VALUES('WF','Wildlife Fund');

/* -- */


CREATE TABLE PROJECT (
PROJ_CODE varchar2(5),
PROJ_NAME varchar2(15),
PROJ_BUDGET number
);
INSERT INTO PROJECT VALUES('1','BeThere','1023245');
INSERT INTO PROJECT VALUES('2','BlueMoon','20198608');
INSERT INTO PROJECT VALUES('3','GreenThumb','3234456');
INSERT INTO PROJECT VALUES('4','GoFast','5674000');
INSERT INTO PROJECT VALUES('5','GoSlow','1002500');

/* -- */


CREATE TABLE SERVICE_V1 (
EMP_NUM varchar2(5),
ORG_CODE varchar2(15)
);
INSERT INTO SERVICE_V1 VALUES('10123','RC');
INSERT INTO SERVICE_V1 VALUES('10123','UW');
INSERT INTO SERVICE_V1 VALUES('10123','WF');

/* -- */


CREATE TABLE SERVICE_V2 (
EMP_NUM varchar2(5),
EMP_ASSIGN integer
);
INSERT INTO SERVICE_V2 VALUES('1023','1');
INSERT INTO SERVICE_V2 VALUES('1023','3');
INSERT INTO SERVICE_V2 VALUES('1023','4');

/* -- */


CREATE TABLE VOLUNTEER_V1 (
EMP_NUM varchar2(5),
ORG_CODE varchar2(5),
ASSIGN_NUM integer
);
INSERT INTO VOLUNTEER_V1 VALUES('10123','RC','1');
INSERT INTO VOLUNTEER_V1 VALUES('10123','UW','3');
INSERT INTO VOLUNTEER_V1 VALUES('10123','','4');

/* -- */


CREATE TABLE VOLUNTEER_V2 (
EMP_NUM varchar2(5),
ORG_CODE varchar2(5),
ASSIGN_NUM integer
);
INSERT INTO VOLUNTEER_V2 VALUES('10123','RC','');
INSERT INTO VOLUNTEER_V2 VALUES('10123','UW','');
INSERT INTO VOLUNTEER_V2 VALUES('10123','','1');
INSERT INTO VOLUNTEER_V2 VALUES('10123','','3');
INSERT INTO VOLUNTEER_V2 VALUES('10223','','4');

/* -- */

CREATE TABLE VOLUNTEER_V3 (
EMP_NUM varchar2(5),
ORG_CODE varchar2(5),
ASSIGN_NUM integer
);
INSERT INTO VOLUNTEER_V3 VALUES('10123','RC','1');
INSERT INTO VOLUNTEER_V3 VALUES('10123','RC','3');
INSERT INTO VOLUNTEER_V3 VALUES('10123','UW','4');
