Consider the following schema Customers CustomerID Product Vendors Orders OrderN
ID: 3752526 • Letter: C
Question
Consider the following schema Customers CustomerID Product Vendors Orders OrderNumber OrderDate CustStreetAddress Vendorld WholesalePrice DaysToDeliver Order Details Vendors CustState VendorlD EmployeelD QuotedPrice RetailPrice QuantityOnHand CategorylD VendCity VendZipCode VendFaxNumber VendWebPage VendEMailAddress Categories EmployeelD CategorylD CategoryDescription MonthYear EmpStreetAddress MonthNumber MonthStart MonthEnd January February March April May June July August September October EmpState EmpZipCode EmpAreaCode ztblPu LowSpend HighSpend Sequence Figure 1: Sales Orders Database Subtask 7.2.1 Write a query that creates a listing of the value of all the sales of each employee per product to answer the question: What is the value of the total sales of each employee per product? As before, you have to resort to the QuantityOrdered and QuotedPrice columns to find the sales. Order the result by EmpLastName and ProductName. EmpFirstName Matt EmpLastName Berg ProductName TotalSolo AeroFlo ATB Wheels 10179.54Explanation / Answer
Answer)
Query to list the above mentioned rows:
select Employees.EmpFirstName, Employees.EmpLastName, Products.ProductName, sum(Order_Details.QuantityOrdered*Order_Details.QuotedPrice) as TotalSold from
(((Employees
INNER JOIN Orders on Employees.EmployeeID=Orders.EmployeeID)
inner join Order_Details on Orders.OrderNumber=Order_Details.OrderNumber)
inner join Products on Order_Details.ProductNumber=Products.ProductNumber)
order by Employees.EmpLastName,Products.ProductName;