I will pay 50$ over PayPal if you solve this problem for me. Post here if you are interested or email me at <EMAIL SNIPPED> I will provide the .sql file with the database
Guidelines:
-UPPER CASE for all SQL reserved words, and Mixed or Lower Cases for names you defined.
-Start each SQL clause on a new line; use white space to align code to improve readability.
Consider the following database. Using SQL, provide the information required by each of the following queries.
Customer (CustNo, CustFirstName, CustLastName, CustStreet, CustCity, CustState, CustZip, CustBal)
Employee (EmpNo, EmpFirstName, EmpLastName, EmpPhone, EmpEmail, SupEmpNo, EmpCommRate)
Product (ProdNo, ProdName, ProdMfg, ProdQOH, ProdPrice, ProdNextShipDate)
OrderTbl (OrdNo, OrdDate, CustNo, EmpNo, ShpName, ShpStreet, ShpCity, ShpState, ShpZip)
OrdProd (OrdNo, ProdNo, Qty)
Queries:
1. 2 answers: first one using 3 simple subqueries nested, second one with JOIN (using no subqueries). List the product number, name, and price of those products with a price greater than $150 and were ordered in Jan 2008 by customers with balances greater than $400.
2. For each Ink Jet product ordered in Jan 2008, list the OrdNo, OrdDate, CustNo, CustFirstName, CustLastName, EmpNo (if present), EmpFirstName, EmpLastName, Qty, ProdNo, ProdName. Include products containing Ink Jet in the product name. Include both Internet (OrderTbl EmpNo is null) and phone orders (taken by employee, i.e. with EmpNo).
3. Using a simple subquery with NOT IN, list the CustNo and name of Colorado customers who have not placed orders in Feb 2008.
4. Using a correlated subquery (i.e. using NOT EXISTS), list the customer number and name of Colorado customers who have not placed orders in Feb 2008.
5. Using the MINUS keyword (i.e. using set operations), list the CustNo and name of Colorado customers who have not placed orders in Feb 2008.
6. (Hint: this can be done using a LEFT JOIN and a FROM clause subquery)
List the CustNo and name of Colorado customers who have not placed any orders in Feb 2008.
(continue on next page)
Asn3DMLSubqView.doc
7. Using simple subqueries, delete orders placed by Colorado customers that were taken by Landi Santos in Jan 2008. (assume CASCADE DELETE action defined)
8. For Colorado customers, compute the average amount of their orders. The average amount of a customer's orders is the sum of the amount (quantity ordered times the product price) on each order divided by the number of orders. The result should include the CustNo, CustLastName, and average order amount.
Answer the above query in 2 steps:
8a. Define a VIEW containing the customer state, customer number, customer lastname, order number and the order amount (i.e. total dollar amount for the order number.)
8b. Using the view defined in Q.8a above, write a query to list the customer number, customer lastname and the average amount of their orders for customers that are in 'CO' state.
8c. Answer the above query (Q.8) WITHOUT using VIEW (i.e. combine the logic from 8a and 8b into a single SELECT statement.
