For each information request below, formulate a single SQL query to produce the

Question

For each information request below, formulate a single SQL query to produce the required information.

1) What is the total amount of purchases made in 2015 made by each employee? Include EmployeeID, First and Last names and total amount. (Use a join with a select subquery in the FROM clause. Remember to name the subquery.) Name the column TotalPurchases. When you create it, use both the round and sum functions, i.e. Round (Sum ( ... ) , 2).

2) What is the total amount of purchases made in 2015 made by each employee. Include EmployeeID, First and Last names, and total amount. (Use a select subquery in the SELECT clause). Name the column TotalPurchases. When you create it, use both the round and sum functions, i.e. Round (Sum ( ... ) , 2).

3) What is the largest purchase made by each employee? Include EmployeeID, FirstName, LastName and amount of the largest purchase. (Name it MaxAmt). (Hint, use a subquery in the FROM clause to total each Purchase. To create a total use the Sum ( ) function. Use a MAX function in the main select query to get the max on the total for each Purchase. Use joins in main query and subquery. ) Note: Due to an idiosyncracy of SQL Server, to evaluate the problem correctly, you must also use the round function to elimnate extraneous decimal digits, i.e. to create the total use Round (Sum (....), 2). Otherwise you may not get full credit.

*** ISO help on these three SQL questions. Looking for how to create the appropriate query for each and an explanation of the query in order to help me understand them to help with the other problems I must do. ****

Customer customerID First Name LastName Street Address City State PostalCode Count Phone Saleltem Product ID tem Size SaleID Quanti SalePrice Sale SaleID SaleDate Customer ID Ta Shipping Salary Employee EmployeeID Salary Wage Employee EmployeeID age MaxHours Product ProductID ProductName anufacturerID Composition List Price Gender Catego Colo Description Inventoryltem Product ID tem Size Qty OnHand ItemSize tem Size Employee EmployeeID First Name LastName ddress City State ZIP Phone ManagerID SSN Ema ddress HireDate Manufacturer ManufacturerID ManufacturerName Address1 ddress2 City State PostalCode Phone Fax Contact URL Purchaseltem Product ID tem Size PurchaseID Quanti PurchasePrice Purchase PurchaseID PurchaseDate Employee ID ExpectedDeliveryDate ManufacturerID Shipping

Explanation / Answer

1)Here we need to calculate the total amount of pruchasesi.e.SUM(Quantity) made by employee.At first retrieve the quantity values in 2015 from purchaseItem by joining with Purchase table and group it based on Employee-id to get the total purchases by the employee.

(SELECT p.EmployeeID, SUM(pi.Quantity) as Qty

FROM Purchase p

JOIN PurchaseItem pi on p. PurchaseID=pi. PurchaseID

WHERE PurchaseDate like ‘2015%’

Group by p.EmployeeID)PurchaseQty

Final query to get the Firstname and last name from employee by joining the subquery with employee-id

SELECT EmployeeID, FirstName, LastName, ROUND(Qty,2) as TotalPurchases

FROM EmployeeID emp

JOIN PurchaseQty pq on emp. EmployeeID=pq. EmployeeID;

(SELECT p.EmployeeID, SUM(pi.Quantity) as Qty

FROM Purchase p

JOIN PurchaseItem pi on p. PurchaseID=pi. PurchaseID

WHERE PurchaseDate like ‘2015%’

Group by p.EmployeeID)PurchaseQty

2)the answer is same as above but we have to SELECT subquery in the SELECT clause.

Its simple now instead using join keyword ,we have to keep the join constraint in where clause as follows:

SELECT EmployeeID, FirstName, LastName, ROUND(Qty,2) as TotalPurchases

FROM EmployeeID emp,PurchaseQty pq

WHERE emp. EmployeeID=pq. EmployeeID;

(SELECT p.EmployeeID, SUM(pi.Quantity) as Qty

FROM Purchase p

JOIN PurchaseItem pi on p. PurchaseID=pi. PurchaseID

WHERE PurchaseDate like ‘2015%’

Group by p.EmployeeID)PurchaseQty

3)Already we have totalPurchase data ,we just have to consider the above resultant query as subquery and retrieve the max out of it as below:

SELECT EmployeeID, FirstName, LastName,Max(TotalPurchases) as MaxAmt

FROM

(SELECT EmployeeID, FirstName, LastName, ROUND(Qty,2) as TotalPurchases

FROM EmployeeID emp

JOIN PurchaseQty pq on emp. EmployeeID=pq. EmployeeID) EmpData

GROUP BY EmpData.EmployeeID

(SELECT p.EmployeeID, SUM(pi.Quantity) as Qty

FROM Purchase p

JOIN PurchaseItem pi on p. PurchaseID=pi. PurchaseID

WHERE PurchaseDate like ‘2015%’

Group by p.EmployeeID)PurchaseQty

Please note that the totalPurchases and maxamt cannot be calculated in one single query as we have to evaluate the total purchase first (no direct column available)and then only will be able to retrieve the max among the reulst obtained.

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.