Write the queries necessary to obtain the required information. Make sure all columns you return have descriptive column headings. You must show the result of the query. In this assignment, some questions require using Delete, Insert, and Update. Thus, if a table is modified, the modification may be cascaded to other questions. To avoid that, if a question changes table X, before doing the next question, drop and recreate table X. This ensures that all questions will be executed on original unchanged tables.

For example, if the query is:

Show the office id, the city, and the region

Your query should be:

select office, city, region

from offices;

and then you need to show the following on the screen: (snapshot of your result)

Use these table below to answer the questions:

drop table customers; CREATE TABLE CUSTOMERS (CUST_NUM NUMBER(4,0) PRIMARY KEY, COMPANY VARCHAR(20) NOT NULL, CUST_REP NUMBER(3,0), CREDIT_LIMIT NUMBER(10,2) ); insert into customers values(2110,'JJCP Inc.',103,500000); insert into customers values(2111,'First Corp.',101,650000); insert into customers values(2112,'Acme Mfg.',105,500000); insert into customers values(2123,'Carter and Sons',102,400000); insert into customers values(2124,'Ace International',110,350000); insert into customers values(2125,'Smithson Corp.',101,200000); insert into customers values(2126,'JJones Mfg.',106,650000); insert into customers values(2136,'Zetacorp',108,500000); insert into customers values(2137,'QMA Assoc.',103,450000); insert into customers values(2138,'Orion Corp.',102,200000); insert into customers values(2139,'Peter Brothers',107,40000); insert into customers values(2140,'Holm and Landis',109,550000); insert into customers values(2150, 'J.P.C. Sinclair',106,350000); insert into customers values(2155,'Three-Way Lines',105,300000); insert into customers values(2165,'Rico Enterprises',102,500000); insert into customers values(2170,'Fredrick Lewis Corp.',102,650000); insert into customers values(2171,'Jose Inc.',109,250000); insert into customers values(2174,'Midwest Systems',108,600000); insert into customers values(2175,'Iane and Schmidt',104,200000); insert into customers values(2189,'Chena Associates',103,250000); insert into customers values(2199,'AAAA Products',101,450000);

drop table offices; create table offices (office number(2,0) primary key, city varchar2(15) not null, region varchar2(10) not null, mgr number(3,0), target number(10,2), sales number(10,2) not null); insert into offices values(22,'Denver','Western',108,30000,116042); insert into offices values(11,'New York','Eastern',106,57000,612637); insert into offices values(12,'Chicago','Eastern',104,80000,725042); insert into offices values(13,'Atlanta','Eastern',105,35000,367912); insert into offices values(21,'Los Angeles','Western',108,725000,835919);

DROP TABLE ORDERS; CREATE TABLE ORDERS (ORDER_NUM NUMBER(6,0) PRIMARY KEY, ORDER_DATE Date NOT NULL, CUST NUMBER(4,0) NOT NULL, REP NUMBER(3,0), MFR VARCHAR2(3) NOT NULL, PRODUCT VARCHAR2(5) NOT NULL, QTY NUMBER(4,0) NOT NULL, AMOUNT NUMBER(9,2) NOT NULL ); INSERT INTO ORDERS VALUES (112961,'17-DEC-21',2110,106,'RIE','2A44L',72,310500); INSERT INTO ORDERS VALUES (113012,'11-JAN-20',2110,105,'ACI','773C',35,307745); INSERT INTO ORDERS VALUES (112989,'03-JAN-22',2111,106,'FEA','114',63,122458); INSERT INTO ORDERS VALUES (113051,'10-FEB-19',2123,108,'QSA','K47',4,122420); INSERT INTO ORDERS VALUES (112968,'12-OCT-18',2123,101,'ACI','41004',34,321978); INSERT INTO ORDERS VALUES (113036,'30-JAN-20',2124,110,'ACI','4100Z',93,221500); INSERT INTO ORDERS VALUES (113045,'02-FEB-21',2124,108,'ACI','2A44R',10,452000); INSERT INTO ORDERS VALUES (112963,'17-DEC-20',2125,105,'ACI','41004',28,322276); INSERT INTO ORDERS VALUES (113013,'14-JAN-18',2155,108,'BIC','41003',1,111652); INSERT INTO ORDERS VALUES (113058,'23-FEB-14',2150,109,'FEA','112',100,211480); INSERT INTO ORDERS VALUES (112997,'08-JAN-20',2140,107,'BIC','41003',11,292652); INSERT INTO ORDERS VALUES (112983,'27-DEC-21',2155,105,'ACI','41004',69,921702); INSERT INTO ORDERS VALUES (113024,'01-JAN-22',2171,108,'QSA','XK47',20,527100); INSERT INTO ORDERS VALUES (113062,'24-FEB-21',2175,107,'FEA','114',10,22430); INSERT INTO ORDERS VALUES (112979,'12-OCT-19',2189,102,'ACI','4100Z',69,152000); INSERT INTO ORDERS VALUES (113027,'22-JAN-18',2165,105,'ACI','41002',54,412204); INSERT INTO ORDERS VALUES (113007,'08-JAN-20',2138,108,'IMM','773C',32,29250); INSERT INTO ORDERS VALUES (113069,'02-MAR-20',2175,107,'IMM','779C',21,312350); INSERT INTO ORDERS VALUES (113034,'29-JAN-21',2199,110,'REI','2A45C',8,121632); INSERT INTO ORDERS VALUES (112992,'04-NOV-20',2189,108,'ACI','41002',100,722260); INSERT INTO ORDERS VALUES (112975,'12-OCT-21',2125,103,'RIE','2A44G',6,210211); INSERT INTO ORDERS VALUES (113055,'15-FEB-21',2124,101,'ACI','4100X',6,150211); INSERT INTO ORDERS VALUES (113048,'10-FEB-18',2124,102,'IMM','779C',2,375212); INSERT INTO ORDERS VALUES (112993,'04-JAN-17',2125,102,'REI','2A45C',24,122896); INSERT INTO ORDERS VALUES (113065,'27-FEB-19',2124,102,'QSA','XK47',6,299130); INSERT INTO ORDERS VALUES (113003,'25-FEB-20',2124,109,'IMM','779C',32,578625); INSERT INTO ORDERS VALUES (113049,'10-JAN-21',2123,108,'QSA','XK47',29,212776); INSERT INTO ORDERS VALUES (112987,'31-DEC-21',2111,105,'ACI','4100Y',12,767500); INSERT INTO ORDERS VALUES (113057,'18-FEB-19',2111,103,'ACI','4100X',28,278200); INSERT INTO ORDERS VALUES (113042,'02-FEB-18',2113,101,'RIE','2A44R',55,225002);

DROP TABLE PRODUCTS; CREATE TABLE PRODUCTS (MFR_ID VARCHAR2(3) NOT NULL, PRODUCT_ID VARCHAR2(5) NOT NULL, DESCRIPTION VARCHAR2(20) NOT NULL, PRICE NUMBER(9,2) NOT NULL, QTY_ON_HAND NUMBER(5,0) NOT NULL); INSERT INTO PRODUCTS VALUES('RIE','2A45C','RATCHET LINK',79,210); INSERT INTO PRODUCTS VALUES('ACI','4100Y','WIDGET REMOVER',2750,25); INSERT INTO PRODUCTS VALUES('QSA','XK47','REDUCER',355,38); INSERT INTO PRODUCTS VALUES('BIC','41672','PLATE',180,0); INSERT INTO PRODUCTS VALUES('IMM','779C','900-LB BRACE',1875,9); INSERT INTO PRODUCTS VALUES('ACI','41003','SIZE 3 WIDGET',107,207); INSERT INTO PRODUCTS VALUES('ACI','41004','SIZE 4 WIDGET',117,139); INSERT INTO PRODUCTS VALUES('BIC','41003','HANDLE',652,3); INSERT INTO PRODUCTS VALUES('IMM','887P','BRACE PIN',250,24); INSERT INTO PRODUCTS VALUES('QSA','XK48','REDUCER',134,203); INSERT INTO PRODUCTS VALUES('RIE','2A44L','LEFT HINGE',4500,12); INSERT INTO PRODUCTS VALUES('FEA','112','HOUSING',148,115); INSERT INTO PRODUCTS VALUES('IMM','887F','BRACE HOLDER',54,223); INSERT INTO PRODUCTS VALUES('BIC','41089','RETAINER',225,78); INSERT INTO PRODUCTS VALUES('ACI','41001','SIZE 1 WIDGET',55,277); INSERT INTO PRODUCTS VALUES('IMM','775C','500-LB BRACE',1425,5); INSERT INTO PRODUCTS VALUES('ACI','4100Z','WIDGET INSTALLER',2500,28); INSERT INTO PRODUCTS VALUES('QSA','XK48A','REDUCER',177,37); INSERT INTO PRODUCTS VALUES('ACI','41002','SIZE 2 WIDGET',76,167); INSERT INTO PRODUCTS VALUES('REI','2A44R','RIGHT HINGE',4500,12); INSERT INTO PRODUCTS VALUES('IMM','773C','300-LB BRACE',975,28); INSERT INTO PRODUCTS VALUES('ACI','4100X','WIDGET ADJUSTER',25,37); INSERT INTO PRODUCTS VALUES('FEA','114','MOTOR MOUNT',243,15); INSERT INTO PRODUCTS VALUES('IMM','887X','BRACE RETAINER',475,32); INSERT INTO PRODUCTS VALUES('RIE','2A44G','HINGE PIN',350,14);

drop table salesreps; create table salesreps (empl_num number(3,0) primary key, name varchar2(15) not null, age number(3,0), rep_office number(2,0), title varchar2(10), hire_date varchar2(10) not null, manager number(3,0), quota number(10,2), sales number(10,2) not null); insert into salesreps values (105,'Bill Adams',37,13,'Sales Rep','12-FEB-18',104,350000,367911); insert into salesreps values (109,'Mary Jones',31,11,'Sales Rep','12-OCT-19',106,300000,392725); insert into salesreps values (102,'Sue Smith',48,21,'Sales Rep','10-DEC-20',108,350000,474050); insert into salesreps values (106,'Sam Clark',52,11,'VP Sales','14-JUN-21',NULL,275000,299912); insert into salesreps values (104,'Bob Smith',33,12,'Sales Mgr','19-MAY-21',106,200000,142594); insert into salesreps values (101,'Dan Roberts',45,12,'Sales Rep','20-OCT-20',104,300000,305673); insert into salesreps values (110,'Tom Synder',41,NULL,'Sales Rep','13-JAN-20',101,NULL,75985); insert into salesreps values (108,'Larry Fitch',62,21,'Sales Mgr','12-OCT-19',106,350000,361865); insert into salesreps values (103,'Paul Cruz',29,12,'Sales Rep','01-MAR-17',104,275000,286775); insert into salesreps values (107,'Nacy Angelli',49,22,'Sales Rep','14-NOV-18',108,300000,186042);

1) Use sub query and in keyword to print the salesreps (ids) who have taken order for the companies Zetacorp or JCP Inc. . Duplicate rows are not allowed

1a) Use sub query to find the id and the name of every sales rep that represents at least one customer with a credit limit of greater than $5000.

1b)Use sub query and keyword exists to list the id and the name of the salesreps in which some customers have orders some products in their hiredate.

1c) List all the products (Mfr_ID and Product_ID) that have never been sold. Use the Exists clause.

1d) Insert the following information into the OFFICES table:

Office: 39 City: Miami Region: Southern Manager: 106 Target: 1000000 Sales: 0

1e) Write an insert statement to add Your Name as Empl_Num 772. Use the date the insert is run as the Hire date (sysdate). Sales are zero. Other column remain NULL;

1g) Write an insert statement to add 'Tom Sawyer' Empl_Num 814. Use the date the insert is run as the Hire date (sysdate). Sales are zero. Use implicit null values for columns that are not mentioned.

1h) Delete all sales reps that have no orders and were hired before Jan 1 1987.

1i) Update your employee record with the following:

Age: 37 Rep_Office:39 Title: Senior VP Manager: NULL Quota: 100000

1j) Increase customers credit limit by 25% for all customers that have 2 or more orders in which each order is more than 25,000.

1k) Increase the credit limit of any customer who has any order that exceeds their credit limit. The new credit limit should be set to their maximum order amount plus $1,000. This must be done in 1 SQL statement.