1) An order history for customer 2118 has been requested. List the Order Number, Date, Manufacturer, Product, Description, and Order Amount for each order place by this customer. Use a WHERE clause to define the table join. Orders should be listed by OrderNum.

2) Change your solution from question 1 to use a NATURAL JOIN.

3) The inventory department has concerns on some high-priced product items kept in stock. These items were brought in specifically for individual customers. The concern is that the products dont seem to be selling as fast as they used to. The department is considering removing the products from inventory and ordering them in when ordered by a customer. List the Mfr, the Product, Custnum, Company and Price of all products brought by customers where CustNum is one of (2107, 2110, 2111, 2124) and where the price of the item is from $5.00 to $25.00. Order the results by ascending Company and then by Price. Use the JOIN keyword in your solution.

4) Normally, orders for a customer are placed by their salesrep. Sometimes a customer will place an order when their salesrep is away (sick, on vacation, etc). Write a query to list all orders where this has occurred. List the salesrep number and name (the salesrep to list is the one who was away when this order was placed) as well as the orderNumber and orderdate.

5) Management has noticed that the age of the managers seems to be less than the age of the salesreps that report to them. List all salesreps names and their managers names where the manager is younger than the salesrep.

6) Due to factory recalls, information is required on a series of products provided by our suppliers. List all orders (OrderNum) and the product that was ordered (manufacturer and product) that contain the following products: (41003 Manufactured by BIC, L12 Manufactured by FEA or XK48 Manufactured by QSA). Also show the customer number and company name placing the order, the name of the CustRep assigned to the company and the Office id where the CustRep works. Use the JOIN keyword in your solution.

here is the stracture of the database

DROP TABLE Customers; DROP TABLE Salesreps; DROP TABLE Offices; DROP TABLE Orders; DROP TABLE Products;

CREATE TABLE Customers (CustNum NUMBER(10) NOT NULL, Company VARCHAR2(20) NOT NULL, CustRep NUMBER(10), CreditLimit NUMBER(6), CONSTRAINT CustomersPK PRIMARY KEY (CustNum));

CREATE TABLE SalesReps (SalesRep NUMBER(10) NOT NULL, Name VARCHAR2(15) NOT NULL, Age NUMBER(3), RepOffice NUMBER(10), Title VARCHAR2(15), HireDate DATE NOT NULL, Manager NUMBER(10), Quota NUMBER(10), Sales NUMBER(12,2) NOT NULL, CONSTRAINT SalesrepsPK PRIMARY KEY (Salesrep));

CREATE TABLE Offices (Office NUMBER(10) NOT NULL, CIty VARCHAR2(15) NOT NULL, Region VARCHAR2(10) NOT NULL, Mgr NUMBER(10), Target NUMBER(10), Sales NUMBER(12,2) NOT NULL, CONSTRAINT OfficesPK PRIMARY KEY (Office));

CREATE TABLE Orders (OrderNum NUMBER(10) NOT NULL, OrderDate DATE NOT NULL, Cust NUMBER(10), Rep NUMBER(10), Mfr CHAR(3) NOT NULL, Product CHAR(5) NOT NULL, Qty NUMBER(5) NOT NULL, Amount NUMBER(9,2) NOT NULL, CONSTRAINT OrdersPK PRIMARY KEY (OrderNum));

CREATE TABLE Products (Mfr CHAR(3) NOT NULL, Product CHAR(5) NOT NULL, Description VARCHAR2(20) NOT NULL, Price NUMBER(9,2) NOT NULL, QtyOnHand NUMBER(5), CONSTRAINT ProductsPK PRIMARY KEY (Mfr, Product));

Supervise Salesreps Products PK !Sales rep PK Mfr Offices PK Office Age RepOffice [FKJ Title Hiredate Manager [FK] Quota Sales Description Price QtyOnHand City Region -Manages--+ Mgr [FK] Target Sales Works Ino Contains Enters_ Represents Customers Corte PK Custnum PK Ordemum Places Orderdate Cust [FK] Rep [FK] Mfr[FK1] Product [FK2] Qty Amount Custrep [FK] CreditLimit 31 of 56 Supervise Salesreps Products PK !Sales rep PK Mfr Offices PK Office Age RepOffice [FKJ Title Hiredate Manager [FK] Quota Sales Description Price QtyOnHand City Region -Manages--+ Mgr [FK] Target Sales Works Ino Contains Enters_ Represents Customers Corte PK Custnum PK Ordemum Places Orderdate Cust [FK] Rep [FK] Mfr[FK1] Product [FK2] Qty Amount Custrep [FK] CreditLimit 31 of 56