Hi Mates,
We are continuing with the 5th part of this series.
1) 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.
SELECT ProductID,
UPPER(Name) AS ProductName,
ROUND(Weight, 0) AS ApproxWeight
FROM SalesLT.Product;
2) 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;
3) 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;
4) 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;
5) 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;
6) Filter the product sales list to include only products that cost over $1,000
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;
7) 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.
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;
We are continuing with the 5th part of this series.
1) 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.
SELECT ProductID,
UPPER(Name) AS ProductName,
ROUND(Weight, 0) AS ApproxWeight
FROM SalesLT.Product;
2) 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;
3) 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;
4) 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;
5) 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;
6) Filter the product sales list to include only products that cost over $1,000
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;
7) 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.
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;
Comments