Project Overview

Hey there! Welcome to my SQL project. I got to dive into the classic AdventureWorks database to solve some common business problems. It was a great chance to show off my SQL skills! My main goal was to write clean, efficient queries to dig up specific info, run some calculations, and organize data in useful ways.

Here's a peek at the skills I used:

• Complex Joins (INNER, LEFT)
• Conditional Logic (CASE statements)
• Data Filtering and Sorting
• Aggregate Functions (COUNT, SUM)
• Date and String Manipulation (YEAR, LEFT, CONCAT)
• Common Table Expressions (CTE) and Window Functions (ROW_NUMBER)
• View Creation (CREATE VIEW)

The Challenges & My Solutions

So, here are the challenges I was given and the SQL code I used to figure them out.

SELECT
    Name,
    Color,
    ListPrice,
    StandardCost AS Price
FROM
    Production.Product
WHERE
    Color IS NOT NULL
    AND Color NOT IN ('Red', 'Silver/Black', 'White')
    AND ListPrice BETWEEN 75 AND 750
ORDER BY
    ListPrice DESC;
                

-- Query for Male Employees
SELECT Gender, BirthDate, HireDate
FROM HumanResources.Employee
WHERE Gender = 'M'
  AND YEAR(BirthDate) BETWEEN 1962 AND 1970
  AND YEAR(HireDate) > 2001

UNION ALL

-- Query for Female Employees
SELECT Gender, BirthDate, HireDate
FROM HumanResources.Employee
WHERE Gender = 'F'
  AND YEAR(BirthDate) BETWEEN 1972 AND 1975
  AND YEAR(HireDate) BETWEEN 2001 AND 2002;
                

SELECT TOP 10
    ProductID,
    Name,
    Color
FROM
    Production.Product
WHERE
    ProductNumber LIKE 'BK%'
ORDER BY
    StandardCost DESC;
                

SELECT
    p.FirstName,
    p.LastName,
    ea.EmailAddress,
    CASE
        WHEN LEFT(p.FirstName, 1) = LEFT(p.LastName, 1)
        THEN CONCAT(p.FirstName, ' ', p.LastName)
        ELSE NULL
    END AS FullName,
    CASE
        WHEN LEFT(p.FirstName, 1) = LEFT(p.LastName, 1)
        THEN LEN(CONCAT(p.FirstName, ' ', p.LastName))
        ELSE NULL
    END AS FullNameLength
FROM
    Person.Person AS p
INNER JOIN
    Person.EmailAddress AS ea ON p.BusinessEntityID = ea.BusinessEntityID
WHERE
    LEFT(p.LastName, 4) = LEFT(ea.EmailAddress, 4);
                

SELECT DISTINCT
    psc.Name,
    p.DaysToManufacture
FROM
    Production.ProductSubcategory AS psc
LEFT JOIN
    Production.Product AS p ON p.ProductSubcategoryID = psc.ProductSubcategoryID
WHERE
    p.DaysToManufacture >= 3;
                

SELECT
    Name,
    ListPrice,
    Color,
    CASE
        WHEN ListPrice < 200 THEN 'Low Value'
        WHEN ListPrice BETWEEN 201 AND 750 THEN 'Mid Value'
        WHEN ListPrice BETWEEN 751 AND 1250 THEN 'Mid to High Value'
        ELSE 'Higher Value'
    END AS ProductSegmentation
FROM
    Production.Product
WHERE
    Color IN ('Black', 'Silver', 'Red');
                

SELECT
    COUNT(DISTINCT JobTitle) AS DistinctJobTitleCount
FROM
    HumanResources.Employee;
                

SELECT
    BusinessEntityID,
    DATEDIFF(YEAR, BirthDate, HireDate) AS AgeAtHiring
FROM
    HumanResources.Employee;
                

SELECT
    COUNT(*) AS EligibleForAward
FROM
    HumanResources.Employee
WHERE
    DATEDIFF(YEAR, HireDate, GETDATE()) BETWEEN 15 AND 19;
                

SELECT
    BusinessEntityID,
    DATEDIFF(YEAR, BirthDate, GETDATE()) AS CurrentAge,
    65 - DATEDIFF(YEAR, BirthDate, GETDATE()) AS YearsToRetirement
FROM
    HumanResources.Employee;
                

WITH NewPriceCTE AS (
    SELECT
        ProductID,
        Name,
        Color,
        StandardCost,
        CASE
            WHEN Color = 'White' THEN StandardCost * 1.08
            WHEN Color = 'Yellow' THEN StandardCost * 0.925
            WHEN Color = 'Black' THEN StandardCost * 1.172
            WHEN Color IN ('Multi', 'Silver', 'Silver/Black', 'Blue') THEN SQRT(StandardCost) * 2
            ELSE StandardCost
        END AS NewPrice
    FROM
        Production.Product
)
SELECT
    *,
    NewPrice * 0.375 AS Commission
FROM
    NewPriceCTE;
                

SELECT
    p.FirstName,
    p.LastName,
    e.HireDate,
    e.SickLeaveHours,
    st.Name AS Region,
    sp.SalesQuota
FROM
    Sales.SalesPerson AS sp
JOIN
    HumanResources.Employee AS e ON sp.BusinessEntityID = e.BusinessEntityID
JOIN
    Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID
JOIN
    Sales.SalesTerritory AS st ON sp.TerritoryID = st.TerritoryID
ORDER BY
    p.LastName, p.FirstName;
                

SELECT
    p.Name AS ProductName,
    pc.Name AS ProductCategoryName,
    psc.Name AS ProductSubcategoryName,
    CONCAT(pp.FirstName, ' ', pp.LastName) AS SalesPerson,
    SUM(sod.LineTotal) AS Revenue,
    DATEPART(month, soh.OrderDate) AS MonthOfTransaction,
    DATEPART(quarter, soh.OrderDate) AS QuarterOfTransaction,
    st.Name AS Region
FROM
    Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID
JOIN Production.Product AS p ON sod.ProductID = p.ProductID
JOIN Production.ProductSubcategory AS psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID
JOIN Production.ProductCategory AS pc ON psc.ProductCategoryID = pc.ProductCategoryID
JOIN Sales.SalesPerson AS sp ON soh.SalesPersonID = sp.BusinessEntityID
JOIN Person.Person AS pp ON sp.BusinessEntityID = pp.BusinessEntityID
JOIN Sales.SalesTerritory AS st ON sp.TerritoryID = st.TerritoryID
GROUP BY
    p.Name, pc.Name, psc.Name, pp.FirstName, pp.LastName,
    DATEPART(month, soh.OrderDate), DATEPART(quarter, soh.OrderDate), st.Name
ORDER BY
    pp.LastName, pp.FirstName;
                

SELECT
    soh.SalesOrderNumber,
    soh.OrderDate,
    soh.TotalDue AS OrderAmount,
    soh.CustomerID,
    CONCAT(p.FirstName, ' ', p.LastName) AS SalespersonName,
    sp.CommissionPct AS CommissionPercentage
FROM
    Sales.SalesOrderHeader AS soh
JOIN
    Sales.SalesPerson AS sp ON soh.SalesPersonID = sp.BusinessEntityID
JOIN
    Person.Person AS p ON sp.BusinessEntityID = p.BusinessEntityID;
                

SELECT
    ProductID,
    Name,
    Color,
    StandardCost,
    StandardCost * 0.14790 AS Commission,
    (CASE
        WHEN Color = 'Black' THEN StandardCost * 1.22
        WHEN Color = 'Red' THEN StandardCost * 0.88
        WHEN Color = 'Silver' THEN StandardCost * 1.15
        WHEN Color = 'Multi' THEN StandardCost * 1.05
        WHEN Color = 'White' AND StandardCost > 0 THEN (2 * StandardCost) / SQRT(StandardCost)
        ELSE StandardCost
     END) - StandardCost AS Margin
FROM
    Production.Product;
                

CREATE VIEW vTop5ProductsByColor AS
WITH ProductRankCTE AS (
    SELECT
        ProductID,
        Name,
        Color,
        ListPrice,
        ROW_NUMBER() OVER(PARTITION BY Color ORDER BY ListPrice DESC) AS PriceRank
    FROM
        Production.Product
    WHERE
        Color IS NOT NULL
)
SELECT
    ProductID,
    Name,
    Color,
    ListPrice
FROM
    ProductRankCTE
WHERE
    PriceRank <= 5;
                

Conclusion

This SQL project successfully demonstrates a practical application of advanced SQL skills using the AdventureWorks database. By tackling a range of business challenges, I've showcased proficiency in complex joins, conditional logic, data filtering, aggregation, and advanced T-SQL features like CTEs and window functions. The solutions provided not only address specific business needs but also highlight the importance of writing clean, efficient, and well-structured queries. This experience reinforced my ability to extract meaningful insights from large datasets, a critical skill for any data professional aiming to drive informed decision-making within an organization.