SQL
This work sample demonstrates the ability to query a dataset using SQL.
Dataset and environments:
- AdventureWorks Data Warehouse 2017 (Publicly available dataset provided by Microsoft)
- Fact table_Internet Sales ER Diagram
- SQL Server: SQL Express
- Connet and Query: SQL Server Management Studio 21
Demonstrated skills:
- SQL Clause and Sub Queries (SQL order or operation)
- Joining Tables (Inner Join, Left Join, Right Join)
- Operators (Logical operators, Comparison operators)
- Functions (Aggregate, Numeric, Data and Time)
Question No.1:
AdventureWorks is a large multinational fictious company focusing on bicycles. What are the sales by product category in United Kingdom between 2012 and 2013?
Answer:
Select
c.EnglishProductCategoryName as ProductCategory,
subc.EnglishProductSubcategoryName as ProdcutSubCategory,
Sum(fa.Salesamount) as Amount,
ROUND(Sum(fa.Salesamount)/(Select Sum(Salesamount) from FactInternetSales where SalesTerritoryKey = 10 AND Year(Orderdate) IN (2012,2013) ),2)as PercentofTotal
From FactInternetSales as fa
LEFT JOIN DimProduct as prod
On fa.ProductKey = prod.ProductKey
LEFT JOIN DimProductSubcategory as subc
On prod.ProductSubcategoryKey = subc.ProductSubcategoryKey
LEFT JOIN DimProductCategory as c
On subc.ProductCategoryKey = c.ProductCategoryKey
WHERE fa.SalesTerritoryKey = 10
AND Year(Orderdate) IN (2012,2013)
Group BY c.EnglishProductCategoryName, subc.EnglishProductSubcategoryName
ORDER BY Amount DESC
Note:
Line 3-4: Use original column name not an alias in Select statement due to the order of SQL operation
Line 6: Use SubQuery to get total sales amount
Line 6 & Line 10: SalesTerritoryKey for UK = 10 was queried beforehand by seperately querying DimmSalesTerritory Table
Line 8-14: Left Join from fact sales table to retrieve product category corresponding to each sales record.
Line 17: Year Function to filter for the selected year
Line 17: IN operator to simplify multiple OR operators
Line 17: AND Operator for multiple conditions
Line 19: Group BY statement to group by product category
Query Result:
| ProductCategory | ProductSubCategory | Amount | PercentofTotal |
|---|---|---|---|
| Bikes | Road Bikes | 1165576.8175 | 0.41 |
| Bikes | Mountain Bikes | 1044330.6366 | 0.37 |
| Bikes | Touring Bikes | 521644.89 | 0.18 |
| Accessories | Helmets | 27957.01 | 0.01 |
| Accessories | Tires and Tubes | 24906.32 | 0.01 |
| Clothing | Jerseys | 18718.37 | 0.01 |
| Accessories | Bottles and Cages | 6522.84 | 0.00 |
| Accessories | Bike Stands | 4293.00 | 0.00 |
| Accessories | Hydration Packs | 3629.34 | 0.00 |
| Accessories | Bike Racks | 3360.00 | 0.00 |
| Clothing | Shorts | 3289.53 | 0.00 |
| Clothing | Gloves | 3036.76 | 0.00 |
| Accessories | Fenders | 2989.28 | 0.00 |
| Clothing | Caps | 2903.77 | 0.00 |
| Clothing | Vests | 2413.00 | 0.00 |
| Accessories | Cleaners | 588.30 | 0.00 |
| Clothing | Socks | 548.39 | 0.00 |
Question No.2:
Download full product catalog including Model name and color with corresponding sales.
Answer:
Select
prod.ProductKey,
prod.Englishproductname,
ISNULL(prod.ModelName,N'not_assigned') as ModelName,
prod.Color,
ISNULL(sum(fa.Salesamount),0) as Amount
From FactInternetSales as fa
Right join DimProduct as prod
On fa.ProductKey = prod.ProductKey
GROUP BY prod.ProductKey,prod.Englishproductname,prod.ModelName,prod.Color
ORDER BY Amount DESC
Note:
Line 4: ISNULL function to replace null with “not assigned” for Model name.
Line6: ISNULL function to replace null with “0” for Sales amount for products with no sales record.
Line 8-10: Right join from fact sales table to retrieve full list of products including product with no sales record.