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

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

CREATE TABLE AGENT (
AGENT_CODE varchar2(50),
AGENT_PHONE varchar2(50)
);

INSERT INTO AGENT VALUES('125','6152439887');
INSERT INTO AGENT VALUES('167','6153426778');
INSERT INTO AGENT VALUES('231','6152431124');
INSERT INTO AGENT VALUES('333','9041234445');

/* -- */

CREATE TABLE CUSTOMER (
CUS_CODE varchar2(50),
CUS_LNAME varchar2(50),
CUS_ZIP varchar2(50),
AGENT_CODE varchar2(50)
);

INSERT INTO CUSTOMER VALUES('1132445','Walker','32145','231');
INSERT INTO CUSTOMER VALUES('1217782','Adares','32145','125');
INSERT INTO CUSTOMER VALUES('1312243','Rakowski','34129','167');
INSERT INTO CUSTOMER VALUES('1321242','Rodriguez','37134','125');
INSERT INTO CUSTOMER VALUES('1542311','Smithson','37134','421');
INSERT INTO CUSTOMER VALUES('1657399','Vanloo','32145','231');


/* -- */


CREATE TABLE DIFF_TBL1 (
STU_FNAME varchar2(50),
STU_LNAME varchar2(50)
);

INSERT INTO DIFF_TBL1 VALUES('George', 'Jones');
INSERT INTO DIFF_TBL1 VALUES('Jane', 'Smith');
INSERT INTO DIFF_TBL1 VALUES('Peter', 'Robinson');
INSERT INTO DIFF_TBL1 VALUES('Franklin', 'Johnson');
INSERT INTO DIFF_TBL1 VALUES('Martin', 'Lopez');

/* -- */

CREATE TABLE DIFF_TBL2 (
EMP_FNAME varchar2(50),
EMP_LNAME varchar2(50)
);

INSERT INTO DIFF_TBL2 VALUES('Franklin', 'Lopez');
INSERT INTO DIFF_TBL2 VALUES('William', 'Turner');
INSERT INTO DIFF_TBL2 VALUES('Franklin', 'Johnson');
INSERT INTO DIFF_TBL2 VALUES('Susan', 'Rogers');

/* -- */

CREATE TABLE DIV_TBL1 (
P_CODE varchar(50),
CUS_CODE varchar(50)
);

INSERT INTO DIV_TBL1 VALUES('123456', '10400');
INSERT INTO DIV_TBL1 VALUES('123456', '11501');
INSERT INTO DIV_TBL1 VALUES('123456', '10030');
INSERT INTO DIV_TBL1 VALUES('123456', '12550');
INSERT INTO DIV_TBL1 VALUES('234567', '12350');
INSERT INTO DIV_TBL1 VALUES('234567', '10040');
INSERT INTO DIV_TBL1 VALUES('234567', '10500');
INSERT INTO DIV_TBL1 VALUES('234567', '10030');
INSERT INTO DIV_TBL1 VALUES('234567', '12550');
INSERT INTO DIV_TBL1 VALUES('345678', '10400');
INSERT INTO DIV_TBL1 VALUES('345678', '11630');
INSERT INTO DIV_TBL1 VALUES('345678', '12550');
INSERT INTO DIV_TBL1 VALUES('456789', '11630');
INSERT INTO DIV_TBL1 VALUES('567890', '10600');
INSERT INTO DIV_TBL1 VALUES('567890', '10030');
INSERT INTO DIV_TBL1 VALUES('567890', '12550');
INSERT INTO DIV_TBL1 VALUES('678901', '11500');
INSERT INTO DIV_TBL1 VALUES('678901', '10400');
INSERT INTO DIV_TBL1 VALUES('678901', '11630');

/* -- */

CREATE TABLE DIV_TBL2 (
P_CODE varchar(50)
);

INSERT INTO DIV_TBL2 VALUES('123456');
INSERT INTO DIV_TBL2 VALUES('234567');
INSERT INTO DIV_TBL2 VALUES('567890');

/* -- */

/* -- */

CREATE TABLE INT_TBL1 (
STU_FNAME varchar2(50),
STU_LNAME varchar2(50)
);

INSERT INTO DIFF_TBL1 VALUES('George', 'Jones');
INSERT INTO DIFF_TBL1 VALUES('Jane', 'Smith');
INSERT INTO DIFF_TBL1 VALUES('Peter', 'Robinson');
INSERT INTO DIFF_TBL1 VALUES('Franklin', 'Johnson');
INSERT INTO DIFF_TBL1 VALUES('Martin', 'Lopez');

/* -- */

CREATE TABLE INT_TBL2 (
EMP_FNAME varchar2(50),
EMP_LNAME varchar2(50)
);

INSERT INTO DIFF_TBL2 VALUES('Franklin', 'Lopez');
INSERT INTO DIFF_TBL2 VALUES('William', 'Turner');
INSERT INTO DIFF_TBL2 VALUES('Franklin', 'Johnson');
INSERT INTO DIFF_TBL2 VALUES('Susan', 'Rogers');

/* -- */

CREATE TABLE PROD_TBL1 (
P_CODE varchar2(50),
P_DESCRIPT varchar2(50),
PRICE number(4,2)
);

INSERT INTO PROD_TBL1 VALUES('123456','Flashlight','5.26');
INSERT INTO PROD_TBL1 VALUES('123457','Lamp','25.15');
INSERT INTO PROD_TBL1 VALUES('123458','Box Fan','10.99');
INSERT INTO PROD_TBL1 VALUES('213345','9v battery','1.92');
INSERT INTO PROD_TBL1 VALUES('311452','Powerdrill','34.99');
INSERT INTO PROD_TBL1 VALUES('254467','100W bulb','1.47');

/* -- */

CREATE TABLE PROD_TBL2 (
STORE varchar2(50),
AISLE varchar2(50),
SHELF varchar2(50)
);

INSERT INTO PROD_TBL2 VALUES('23','W','5');
INSERT INTO PROD_TBL2 VALUES('24','K','9');
INSERT INTO PROD_TBL2 VALUES('25','Z','6');

/* -- */

CREATE TABLE PROJ_TBL1 (
P_CODE varchar2(50),
P_DESCRIPT varchar2(50),
PRICE number(4,2)
);

INSERT INTO PROJ_TBL1 VALUES('123456','Flashlight','5.26');
INSERT INTO PROJ_TBL1 VALUES('123457','Lamp','25.15');
INSERT INTO PROJ_TBL1 VALUES('123458','Box Fan','10.99');
INSERT INTO PROJ_TBL1 VALUES('213345','9v battery','1.92');
INSERT INTO PROJ_TBL1 VALUES('311452','Powerdrill','34.99');
INSERT INTO PROJ_TBL1 VALUES('254467','100W bulb','1.47');

/* -- */


CREATE TABLE SEL_TBL1 (
P_CODE varchar2(50),
P_DESCRIPT varchar2(50),
PRICE number(4,2)
);

INSERT INTO SEL_TBL1 VALUES('123456','Flashlight','5.26');
INSERT INTO SEL_TBL1 VALUES('123457','Lamp','25.15');
INSERT INTO SEL_TBL1 VALUES('123458','Box Fan','10.99');
INSERT INTO SEL_TBL1 VALUES('213345','9v battery','1.92');
INSERT INTO SEL_TBL1 VALUES('311452','Powerdrill','34.99');
INSERT INTO SEL_TBL1 VALUES('254467','100W bulb','1.47');

/* -- */

CREATE TABLE UNI_TBL1 (
P_CODE varchar2(50),
P_DESCRIPT varchar2(50),
PRICE number(4,2)
);

INSERT INTO UNI_TBL1 VALUES('123456','Flashlight','5.26');
INSERT INTO UNI_TBL1 VALUES('123457','Lamp','25.15');
INSERT INTO UNI_TBL1 VALUES('123458','Box Fan','10.99');
INSERT INTO UNI_TBL1 VALUES('213345','9v battery','1.92');
INSERT INTO UNI_TBL1 VALUES('311452','Powerdrill','34.99');
INSERT INTO UNI_TBL1 VALUES('254467','100W bulb','1.47');

/* -- */

CREATE TABLE UNI_TBL2 (
P_CODE varchar2(50),
P_DESCRIPT varchar2(50),
PRICE number(5,2)
);

INSERT INTO UNI_TBL2 VALUES('345678','Microwave','160.00');
INSERT INTO UNI_TBL2 VALUES('345679','Dishwasher','500.00');
INSERT INTO UNI_TBL2 VALUES('123458','Box Fan', '10.99');
/* -- */

