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.

6. For each salesrep that has made an order, list the minimum, maximum and average order amount for all their orders. Include only those orders made anytime from 1990-1999. Omit from the list any salesrep that has only made 1 order in this time frame. Sort the results by Empl_Num.

7. Use a sub-query to list the Customer number; Name and Credit Limit of any customers who have exceeded their credit limit (amount > credit limit) on any order.

8. Use a subquery and using the all keyword to find the customer number, Salesrep id, and CreditLimit of every customer whose CreditLimit is larger than the CreditLimit of all of the customers of sales rep number 109.

9. Do question 8, still using the subquery but do not use the all keyword.

10. 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

SQL TABLES:

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(2111,'JCP Inc.',103,50000); insert into customers values(2102,'First Corp.',101,65000); insert into customers values(2103,'Acme Mfg.',105,50000); insert into customers values(2123,'Carter and Sons',102,40000); insert into customers values(2107,'Ace International',110,35000); insert into customers values(2115,'Smithson Corp.',101,20000); insert into customers values(2101,'Jones Mfg.',106,65000); insert into customers values(2112,'Zetacorp',108,50000); insert into customers values(2121,'QMA Assoc.',103,45000); insert into customers values(2114,'Orion Corp.',102,20000); insert into customers values(2124,'Peter Brothers',107,40000); insert into customers values(2108,'Holm and Landis',109,55000); insert into customers values(2117,'J.P. Sinclair',106,35000); insert into customers values(2122,'Three-Way Lines',105,30000); insert into customers values(2120,'Rico Enterprises',102,50000); insert into customers values(2106,'Fred Lewis Corp.',102,65000); insert into customers values(2119,'Solomon Inc.',109,25000); insert into customers values(2118,'Midwest Systems',108,60000); insert into customers values(2113,'Ian and Schmidt',104,20000); insert into customers values(2109,'Chen Associates',103,25000); insert into customers values(2105,'AAA Investments',101,45000);

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,300000,186042); insert into offices values(11,'New York','Eastern',106,575000,692637); insert into offices values(12,'Chicago','Eastern',104,800000,735042); insert into offices values(13,'Atlanta','Eastern',105,350000,367911); insert into offices values(21,'Los Angeles','Western',108,725000,835915);

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-89',2117,106,'REI','2A44L',7,31500); INSERT INTO ORDERS VALUES (113012,'11-JAN-90',2111,105,'ACI','41003',35,3745); INSERT INTO ORDERS VALUES (112989,'03-JAN-90',2101,106,'FEA','114',6,1458); INSERT INTO ORDERS VALUES (113051,'10-FEB-90',2118,108,'QSA','K47',4,1420); INSERT INTO ORDERS VALUES (112968,'12-OCT-89',2102,101,'ACI','41004',34,3978); INSERT INTO ORDERS VALUES (113036,'30-JAN-90',2107,110,'ACI','4100Z',9,22500); INSERT INTO ORDERS VALUES (113045,'02-FEB-90',2112,108,'REI','2A44R',10,45000); INSERT INTO ORDERS VALUES (112963,'17-DEC-89',2103,105,'ACI','41004',28,3276); INSERT INTO ORDERS VALUES (113013,'14-JAN-90',2118,108,'BIC','41003',1,652); INSERT INTO ORDERS VALUES (113058,'23-FEB-90',2108,109,'FEA','112',10,1480); INSERT INTO ORDERS VALUES (112997,'08-JAN-90',2124,107,'BIC','41003',1,652); INSERT INTO ORDERS VALUES (112983,'27-DEC-89',2103,105,'ACI','41004',6,702); INSERT INTO ORDERS VALUES (113024,'20-JAN-90',2114,108,'QSA','XK47',20,7100); INSERT INTO ORDERS VALUES (113062,'24-FEB-90',2124,107,'FEA','114',10,2430); INSERT INTO ORDERS VALUES (112979,'12-OCT-89',2114,102,'ACI','4100Z',6,15000); INSERT INTO ORDERS VALUES (113027,'22-JAN-90',2103,105,'ACI','41002',54,4104); INSERT INTO ORDERS VALUES (113007,'08-JAN-90',2112,108,'IMM','773C',3,2925); INSERT INTO ORDERS VALUES (113069,'02-MAR-90',2109,107,'IMM','775C',22,31350); INSERT INTO ORDERS VALUES (113034,'29-JAN-90',2107,110,'REI','2A45C',8,632); INSERT INTO ORDERS VALUES (112992,'04-NOV-89',2118,108,'ACI','41002',10,760); INSERT INTO ORDERS VALUES (112975,'12-OCT-89',2111,103,'REI','2A44G',6,2100); INSERT INTO ORDERS VALUES (113055,'15-FEB-90',2108,101,'ACI','4100X',6,150); INSERT INTO ORDERS VALUES (113048,'10-FEB-90',2120,102,'IMM','779C',2,3750); INSERT INTO ORDERS VALUES (112993,'04-JAN-89',2106,102,'REI','2A45C',24,1896); INSERT INTO ORDERS VALUES (113065,'27-FEB-90',2106,102,'QSA','XK47',6,2130); INSERT INTO ORDERS VALUES (113003,'25-JAN-90',2108,109,'IMM','779C',3,5625); INSERT INTO ORDERS VALUES (113049,'10-FEB-90',2118,108,'QSA','XK47',2,776); INSERT INTO ORDERS VALUES (112987,'31-DEC-89',2103,105,'ACI','4100Y',11,27500); INSERT INTO ORDERS VALUES (113057,'18-FEB-90',2111,103,'ACI','4100X',24,600); INSERT INTO ORDERS VALUES (113042,'02-FEB-90',2113,101,'REI','2A44R',5,22500);

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('REI','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('REI','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('REI','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-88',104,350000,367911); insert into salesreps values (109,'Mary Jones',31,11,'Sales Rep','12-OCT-89',106,300000,392725); insert into salesreps values (102,'Sue Smith',48,21,'Sales Rep','10-DEC-86',108,350000,474050); insert into salesreps values (106,'Sam Clark',52,11,'VP Sales','14-JUN-88',NULL,275000,299912); insert into salesreps values (104,'Bob Smith',33,12,'Sales Mgr','19-MAY-87',106,200000,142594); insert into salesreps values (101,'Dan Roberts',45,12,'Sales Rep','20-OCT-86',104,300000,305673); insert into salesreps values (110,'Tom Synder',41,NULL,'Sales Rep','13-JAN-90',101,NULL,75985); insert into salesreps values (108,'Larry Fitch',62,21,'Sales Mgr','12-OCT-89',106,350000,361865); insert into salesreps values (103,'Paul Cruz',29,12,'Sales Rep','01-MAR-87',104,275000,286775); insert into salesreps values (107,'Nacy Angelli',49,22,'Sales Rep','14-NOV-88',108,300000,186042);