Chapter5 Lab - Coding Summary Queries (1)

Chapter 5 Lab – Coding Summary Queries Name: Emily Shackelford Date: 2/5/2024 Exercise Instructions 1. Type your name and the date into the space provided. 2. Use the SQL Server Management Studio to complete this lab. 3. Write T-SQL statements to query the tables contained in the IST272EagleCorp database and complete each of the exercises in this lab per the directions provided below. 4. Upload and submit before the due date. 1. Write a SELECT statement that returns two columns from the CustOrderLine table: PartNumber and NumOfPartsOrdered, where NumOfPartsOrdered is the sum of the OrderQuantity column. Group the result set by PartNumber. Paste below the code you wrote and type the number of rows returned for exercise 1: SELECT PartNumber , SUM ( OrderQuantity ) AS NumOfPartsOrdered FROM CustOrderLine GROUP BY PartNumber ; 192 ------------------------------------------------ 2. Write a SELECT statement that returns the CustomerID and NumOfPartsOrdered, where NumOfPartsOrdered is the total number of parts ordered by the customer (sum the OrderQuantity). Group the result set by CustomerID. Return only 3 rows, corresponding to the 3 customers who've ordered the most parts. Hint: Use the TOP clause and join CustOrder to CustOrderLine. Paste below the code you wrote and the run results you obtained for exercise 2: SELECT TOP 3 CustomerID , SUM ( OrderQuantity ) AS NumOfPartsOrdered FROM CustOrder CO JOIN CustOrderLine COL ON CO . OrderID = COL . OrderID GROUP BY CustomerID ORDER BY NumOfPartsOrdered DESC

------------------------------------------------ 3. Write a SELECT statement that returns three columns consisting of the CustomerID, the total number of orders the customer has placed, and the undiscounted total dollar value of the orders the customer has placed. Name these columns: CustomerID NumberOfOrders TotalDollarValueOfOrders Sort the result set by number of orders in either DESC or ASC sequence. Paste below the code you wrote and type the number of rows returned for exercise 3: SELECT CustomerID , Count ( DISTINCT CustOrderLine . OrderID ) AS NumberOfOrders , SUM ( UnitPrice * OrderQuantity ) AS TotalDollarValueOfOrders FROM CustOrder JOIN CustOrderLine ON CustOrder . OrderID = CustOrderLine . OrderID GROUP BY CustomerID ORDER BY NumberOfOrders ASC 277 ------------------------------------------------ 4. Modify the solution to exercise 3 to filter for CustomerID’s that start with C-30001 (only include rows where the CustomerID starts with C-30001) Hint: add a where clause Paste below the code you wrote and the run results you obtained for exercise 4: SELECT CustomerID , Count ( DISTINCT CustOrderLine . OrderID ) AS NumberOfOrders , SUM ( UnitPrice * OrderQuantity ) AS TotalDollarValueOfOrders FROM CustOrder JOIN CustOrderLine ON CustOrder . OrderID = CustOrderLine . OrderID WHERE CustomerID LIKE ( 'C-30001%' )

bases its calculation on the number of lineitems not the number of orders. Paste below the code you wrote and type the number of rows returned for exercise 5: SELECT CustomerID , COUNT ( Distinct Co . OrderID ) AsNumberofLineItems , SUM ( UnitPrice * OrderQuantity )/ COUNT ( DISTINCT COL . OrderID ) AS AVGDollarValueOfLineItems FROM CustOrderLine AS COL Join CustOrder AS CO ON COL . OrderID = CO . OrderID GROUP BY CustomerID ORDER BY AsNumberofLineItems DESC Rows Returned = 227 ------------------------------------------------ 6. Modify the solution to exercise 5 to only include groups in the final result set that have a NumberOfOrders value of 5 or greater. Hint: add a HAVING clause Paste below the code you wrote and the run results you obtained for exercise 6: SELECT CustomerID , COUNT ( Distinct Co . OrderID ) AsNumberofLineItems , SUM ( UnitPrice * OrderQuantity )/ COUNT ( DISTINCT COL . OrderID ) AS AVGDollarValueOfLineItems FROM CustOrderLine AS COL Join CustOrder AS CO ON COL . OrderID = CO . OrderID GROUP BY CustomerID HAVING COUNT ( DISTINCT COL . OrderID ) >= 5 ORDER BY AsNumberofLineItems DESC

------------------------------------------------ 7. Write a SELECT statement that returns two columns from the PackingSlip table: EmployeeID and PackageCount, where PackageCount is a count of the rows on the packing slip table associated with the EmployeeID. Sort the result set by EmployeeID in Descending sequence. Paste below the code you wrote and the run results you obtained for exercise 7: SELECT EmployeeID , Count ( Packingslip . EmployeeID ) AS PackageCount FROM PackingSlip GROUP BY EmployeeID ORDER BY EmployeeID DESC

------------------------------------------------ 10. Write a SELECT statement that returns two columns from the PackingSlip table: ShippedDate and PackageCount, where PackageCount is a count of the rows on the packing slip table associated with the ShippedDate. Include a row that gives the total number of packages packed. Hint: make use of WITH ROLLUP Paste below the code you wrote and type the number of rows returned for exercise 10: SELECT ShippedDate , COUNT (*) AS PackageCount FROM PackingSlip GROUP BY ShippedDate with rollup Rows Returned = 194 ------------------------------------------------