Question

CUSTTYPE (TID, TypeDesc)

CUSTOMER (CID, Lname, Fname, TID, Address, ZIPcode)

CUSTORDER (OrderNo, OrderDate, PromiseDate, ShippedDate, ShippingTerm, SalesID, CID)

SALESREP (SalesID, Name, Phone, Address, ZIPcode, RepType)

ORDERLINE (OrderNo, PID, Qty, Qty_RETD)

FTSALESREP (SalesID, MonthPay, Rank)

PTSALESREP (SalesID, HourlyRate, WeekHours)

ZIP_TABLE (ZIPcode, City, State)

VENDOR (VID, Name, Phone ZIPcode)

PRODUCT (PID, CateID, ProdName, ProdFinish, Price, Qty_on_Hand, Description, VID)

CATEGORY (CateID, CateType)

Local View #1: Use three base tables to develop a SQL statement that will list the following information for a customer, and label this query Customer_Info. CID, Lname, Fname, TypeDesc, Address, City, State, ZIPcode

Local View #2: Use two tables to develop a SQL statement that will list the following information for a SalesRep, and label this query SalesRep_Info. SalesID, Name, RepType, Phone, Address, City, State, ZIPcode B.

Question #1: Use the above two “Access SQL Statements (i.e., Views)” to develop an SQL statement that will list SalesID, Name, and Phone of each sales representative and his/her potential customers’ CID, Fname, Lname, and TypeDesc of those customers who live in the same state. Order your query results in an alphabetical order of the name of sales representative. (Hint: Use Left Join and Order By to complete this inquiry, and save your SQL statement under the name Query 1)

Question #2: Write an SQL to list SalesID, Name, and Phone who have handled at least one Customer Order [Hint: Use EXISTS in a correlated SQL. Save your SQL statement under the name Query 2]

Question #3: Write an SQL to list the PID, ProdName, ProdFinish, and Price for the Product that has the lowest price. [Hint: Use ALL operator in your nested SQL statement. Save your SQL statement under the name Query 3]

Question #4: Write an SQL to list PID, ProdName, and average of Qty sold when each product was ordered, if any, by the customer orders. [Hint: Note you should list all products whether they have been ordered or not. Your intent is to do some sales analysis that will indicate how each product is ordered by customer orders. To show your knowledge in developing SQL statements, for this query you are required to use a Right Join, and you will need a Group By and AVG aggregate function. Save your SQL statement under the name Query 4]

Question #5: Write an SQL to list SalesID, Name, Phone, RepType, the Annual Pay for all sales reps who are either a full time or a part time [Hint: You need to use the UNION operator to retrieve data from SalesRep joining with either FTSalesRep or PTSalesRep. The Annual Pay is calculated by MonthPay*12 for full-time sales rep and HourlyRate * WeekHours*52 for part-time sales rep. Save your SQL statement under the name Query 5]

Question #6: Develop an SQL that will list VID, Name, and State for all Vendors who live in either Wisconsin or Michigan and have not supplied any product at this moment [Hint: You need to use the remote view Vendor_Info to simplify your SQL statement. Also, you may to use IN operator to check against a list of ‘WI’ and ‘MI’ to simplify your query syntax. To prove your knowledge in SQL syntax, you are required to use NOT EXISTS to complete this inquiry. Save your SQL statement under the name Query 6] 9

Bonus Question #7: Develop an SQL that will list PID, ProdName, Price, CateType, and the current balance of inventory quantity for each product in the store. [Hint: You are recommended to use the view Product_Info to join with Orderline to complete this query. Note that the view uses Qty as the value for quantity on hands, and the same term Qty is used in Orderline. You need to use alias as way to calculate the balance (i.e., pr.Qty – SUM(od.Qty), assuming that you use pr as the alias for Product_Info and od for Orderline. In this query, you may find out that some products are in shortage of customer demands. Save your SQL statement under the name Query 7]

Answer 1

PLEASE LIKE THE SOLUTION

FEEL FREE TO DISCUSS IN COMMENT SECTION