Oracle 12c Ch 9 Advance Challenge

CREATE TABLE Warehouses (wh_id NUMBER(2), location VARCHAR(12) );

INSERT INTO warehouses VALUES (10, 'Boston'); INSERT INTO warehouses VALUES (20, 'Norfolk'); INSERT INTO warehouses VALUES (30, 'San Diego');

CREATE TABLE Publisher2 (ID NUMBER(2), Name VarCHAR2(23), Contact VARCHAR2(15), Phone VARCHAR2(12), CONSTRAINT publisher2_pubid_pk PRIMARY KEY(id));

INSERT INTO PUBLISHER2 VALUES(1,'PRINTING IS US','TOMMIE SEYMOUR','000-714-8321'); INSERT INTO PUBLISHER2 VALUES(2,'PUBLISH OUR WAY','JANE TOMLIN','010-410-0010'); INSERT INTO PUBLISHER2 VALUES(3,'AMERICAN PUBLISHING','DAVID DAVIDSON','800-555-1211'); INSERT INTO PUBLISHER2 VALUES(4,'READING MATERIALS INC.','RENEE SMITH','800-555-9743'); INSERT INTO PUBLISHER2 VALUES(5,'REED-N-RITE','SEBASTIAN JONES','800-555-8284');

CREATE TABLE Publisher3 (ID NUMBER(2), Name VarCHAR2(23), Contact VARCHAR2(15), Phone VARCHAR2(12), CONSTRAINT publisher3_pubid_pk PRIMARY KEY(id));

INSERT INTO PUBLISHER3 VALUES(2,'PUBLISH OUR WAY','JANE TOMLIN','010-410-0010'); INSERT INTO PUBLISHER3 VALUES(3,'AMERICAN PUB','DAVID DAVIDSON','800-555-1211'); INSERT INTO PUBLISHER3 VALUES(6,'PRINTING HERE','SAM HUNT','000-714-8321'); INSERT INTO PUBLISHER3 VALUES(7,'PRINT THERE','CINDY TIKE','010-410-0010');

CREATE TABLE Employees (EmpNo NUMBER(4), LName VARCHAR2(20), FName VARCHAR2(15), Job VARCHAR2(9), HireDate DATE, DeptNo NUMBER(2) NOT NULL, MthSal NUMBER(7,2), Mgr NUMBER(4), CONSTRAINT employees_empno_PK PRIMARY KEY (EMPNO));

INSERT INTO employees VALUES (7839,'KING','BEN', 'GTECH2',TO_DATE('17-NOV-91','DD-MON-YY'),10,6000,NULL); INSERT INTO employees VALUES (8888,'JONES','LARRY','MTech2',TO_DATE('17-NOV-98','DD-MON-YY'),10,4200,7839); INSERT INTO employees VALUES (7344,'SMITH','SAM','GTech1',TO_DATE('17-NOV-95','DD-MON-YY'),20,4900,7839); INSERT INTO employees VALUES (7355,'POTTS','JIM','GTech1',TO_DATE('17-NOV-95','DD-MON-YY'),20,4900,7839); INSERT INTO employees VALUES (8844,'STUART','SUE','MTech1',TO_DATE('17-NOV-98','DD-MON-YY'),10,3700,8888);

ALTER TABLE employees ADD (region CHAR(2));

Advanced Challenge dorm this activity, refer to the tables in the JustLee Books database. To peinlo Marketing Department of JustLee Books is preparing for its annual sales promotion. ustomer who ch gift will be based on the book's retail price. JustLee Books also part places an order during the promotion will receive a free gift with each book Fach c ase ooks also participates in advertising programs with certain publishers. If the publisher's name is included in p n JustLee Books is reimbursed a certain percentage of the advertisement costs advertemine the projected costs of this year's sales promotion, the Marketing Department To de the publisher's name, profit amount, and free gift description for each book in the Justlee ks inventory. 800o the Marketing Department is analyzing books that don't sell. A list of ISBNs for all with no sales recorded is needed. Use a set operation to complete this task. ks Create a document boo that includes a synopsis of these requests, the necessary SQL ents, and the output requested by the Marketing Department. Advanced Challenge dorm this activity, refer to the tables in the JustLee Books database. To peinlo Marketing Department of JustLee Books is preparing for its annual sales promotion. ustomer who ch gift will be based on the book's retail price. JustLee Books also part places an order during the promotion will receive a free gift with each book Fach c ase ooks also participates in advertising programs with certain publishers. If the publisher's name is included in p n JustLee Books is reimbursed a certain percentage of the advertisement costs advertemine the projected costs of this year's sales promotion, the Marketing Department To de the publisher's name, profit amount, and free gift description for each book in the Justlee ks inventory. 800o the Marketing Department is analyzing books that don't sell. A list of ISBNs for all with no sales recorded is needed. Use a set operation to complete this task. ks Create a document boo that includes a synopsis of these requests, the necessary SQL ents, and the output requested by the Marketing Department