Using Functions and Aggregating Data

This post is lab exercise I did on Querying with Transact-SQL edx course.

In this lab, I will write queries that use functions to retrieve, aggregate, and group data from the AdventureWorksLT database.

Retrieve Product Information

• Retrieve the name and approximate weight of each product
  Write a query to return the product ID of each product, together with the product name formatted as upper case and a column named ApproxWeight with the weight of each product rounded to the nearest whole unit.

SELECTProductID,
   UPPER(Name) AS ProductName,
   ROUND(Weight, 0) AS ApproxWeight
FROM SalesLT.Product;

• Retrieve the year and month in which products were first sold
  Extend your query to include columns named SellStartYear and SellStartMonth containing the year and month in which Adventure Works started selling each product. The month should be displayed as the month name (for example, ‘January’).

SELECT ProductID,
       UPPER(Name) AS ProductName,
       ROUND(Weight, 0) AS ApproxWeight,
       YEAR(SellStartDate) as SellStartYear,
       DATENAME(m, SellStartDate) as SellStartMonth
FROM SalesLT.Product;

• Extract product types from product numbers
  Extend your query to include a column named ProductType that contains the leftmost two characters from the product number.

SELECT ProductID,
       UPPER(Name) AS ProductName,
       ROUND(Weight, 0) AS ApproxWeight,
       YEAR(SellStartDate) as SellStartYear,
       DATENAME(m, SellStartDate) as SellStartMonth,
       LEFT(ProductNumber, 2) AS ProductType
FROM SalesLT.Product;

• Retrieve only products with a numeric size
  Extend your query to filter the product returned so that only products with a numeric size are included.

SELECT ProductID,
       UPPER(Name) AS ProductName,
       ROUND(Weight, 0) AS ApproxWeight,
       YEAR(SellStartDate) as SellStartYear,
       DATENAME(m, SellStartDate) as SellStartMonth,
       LEFT(ProductNumber, 2) AS ProductType
FROM SalesLT.Product
WHERE ISNUMERIC(Size)=1;

Rank Customers by Revenue

• Retrieve companies ranked by sales totals
  Write a query that returns a list of company names with a ranking of their place in a list of highest TotalDue values from the SalesOrderHeader table.

SELECT CompanyName,
       TotalDue AS Revenue,
       RANK() OVER (ORDER BY TotalDue DESC) AS RankByRevenue
FROM SalesLT.SalesOrderHeader AS SOH
JOIN SalesLT.Customer AS C
ON SOH.CustomerID=C.CustomerID;

Aggregate Product Sales

• Retrieve total sales by product
  Write a query to retrieve a list of the product names and the total revenue calculated as the sum of the LineTotal from the SalesLT.SalesOrderDetail table, with the results sorted in descending order of total revenue.

SELECT Name,SUM(LineTotal) AS TotalRevenue
FROM SalesLT.SalesOrderDetail AS SOD
JOIN SalesLT.Product AS P ON SOD.ProductID=P.ProductID
GROUP BY P.Name
ORDER BY TotalRevenue DESC;

• Filter the product sales list to include only products that cost over 1,000 dollars
  Modify the previous query to include sales totals for products that have a list price of more than $1000.

SELECT Name,SUM(LineTotal) AS TotalRevenue
FROM SalesLT.SalesOrderDetail AS SOD
JOIN SalesLT.Product AS P ON SOD.ProductID=P.ProductID
WHERE P.ListPrice > 1000
GROUP BY P.Name
ORDER BY TotalRevenue DESC;

• Filter the product sales groups to include only total sales over 20,000
  Modify the previous query to only include only product groups with a total sales value greater than 20,000 dollars.

SELECT Name,SUM(LineTotal) AS TotalRevenue
FROM SalesLT.SalesOrderDetail AS SOD
JOIN SalesLT.Product AS P ON SOD.ProductID=P.ProductID
WHERE P.ListPrice > 1000
GROUP BY P.Name
HAVING SUM(LineTotal) > 20000
ORDER BY TotalRevenue DESC;