Use the following SQL tables(customers,offices,orders,products,salesrep) to answer the questions below, snippet the results with syntax used:

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) List the order numbers with the name of the rep who placed the order and the name of the customer (Company Name) who made the order and the name of the rep for that customer as long as the rep and the cust_rep are not the same.

1a) List the Mfr Id, the Product Id, Company and PRICE of all products brought by customers where customer number is one of (2111,2112,2105,2119) and where the amount of the order < $3000. Order the results by ascending Company.

1b) List the id and the name of the Salesreps who have taken some order and they are at least 45 years old.

1c) List all customers (Customer number, Company, and Credit Limit) and orders (Order Number, Amount) where the order is within $5000.00 of the Credit Limit (less than $5000) [Hint: Amount must be greater than zero and Credit_Limit Amount < 5000). Sort the result by Customer number in descending order.

1d) List all salesreps (Name) and the City they work in where the sales of the salesrep < Quota and the sales for the office is < Target.

1e) List the name, id, and hire date of the salesreps where both of the following conditions hold:

- The salesrep sales should be greater than the quota

- The salesrep has taken an order from Customer number 2117, 2111, or 2101.

Sort the result by the salesreps id.

1f) List all orders (Order Number) over $25500 showing the company placing the order, the Customer Rep assign to the company, the Office id and the city where the Customer Rep works in, such that the Customer Reps manager is not the person who took the order.

1g) List order_num and name of the rep who took the order and the name of his/her manager and long as the managers office is in New York

1h) List all products (Mfr_id, ProductId, and Description), customers (CustNum, Company) who have bought that product, and orders (Order Number, and Order Date) where the order < $700. Sort the rows by the OrderDate.

1i) List the name of the salesreps and the name of their managers only if the manager has taken care of any order.