Advanced SQL Queries

Welcome to the advanced SQL queries section of the MSDN documentation. This guide explores powerful techniques to retrieve and manipulate data beyond basic SELECT statements. We will cover concepts like Common Table Expressions (CTEs), Window Functions, PIVOT and UNPIVOT operations, and more complex JOIN strategies.

Common Table Expressions (CTEs)

CTEs provide a way to create temporary, named result sets that you can reference within a single SQL statement. They are excellent for breaking down complex queries into more readable and manageable parts.

Syntax

WITH cte_name (column1, column2, ...) AS
(
    -- SELECT statement that defines the CTE
    SELECT column1, column2, ...
    FROM your_table
    WHERE condition
)
-- Main query that uses the CTE
SELECT *
FROM cte_name
WHERE another_condition;

Example: Finding Employees with Above-Average Salary

WITH EmployeeSalaries AS
(
    SELECT
        EmployeeID,
        FirstName,
        LastName,
        Salary,
        AVG(Salary) OVER () AS AvgSalary
    FROM Employees
)
SELECT
    EmployeeID,
    FirstName,
    LastName,
    Salary
FROM EmployeeSalaries
WHERE Salary > AvgSalary;

Window Functions

Window functions perform calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions, they do not collapse rows into a single output row. Instead, they retain the individual rows and add computed values.

Common Window Functions:

ROW_NUMBER(): Assigns a unique sequential integer to each row within a partition.
RANK(): Assigns a rank to each row within its partition; ranks may have gaps.
DENSE_RANK(): Assigns a rank to each row within its partition; ranks have no gaps.
LAG() and LEAD(): Access data from a previous or next row in the same result set.
SUM() OVER (...), AVG() OVER (...), etc.: Aggregate functions used as window functions.

Example: Ranking Products by Sales

SELECT
    ProductName,
    Category,
    SalesAmount,
    RANK() OVER (PARTITION BY Category ORDER BY SalesAmount DESC) AS RankInCategory
FROM ProductSales;

Tip: The OVER() clause is crucial for window functions. It can include PARTITION BY to divide rows into partitions and ORDER BY to define the order of rows within each partition.

PIVOT and UNPIVOT

PIVOT transforms rows into columns, and UNPIVOT does the opposite, transforming columns into rows. This is useful for restructuring data for reporting purposes.

Example: PIVOTING Sales Data

Imagine you have sales data by product and month, and you want to see total sales for each product with months as columns.

SELECT
    [Jan], [Feb], [Mar], ..., [Dec]
FROM
(
    SELECT ProductName, MonthName, SalesAmount
    FROM SalesData
) AS SourceTable
PIVOT
(
    SUM(SalesAmount)
    FOR MonthName IN ([Jan], [Feb], [Mar], ..., [Dec])
) AS PivotTable;

Advanced JOINs

Beyond standard INNER and LEFT JOINs, explore techniques like FULL OUTER JOINs, CROSS JOINs, and self-joins for more intricate data relationships.

Self-Join Example: Finding Employees and Their Managers

SELECT
    e.FirstName AS EmployeeName,
    m.FirstName AS ManagerName
FROM
    Employees e
LEFT JOIN
    Employees m ON e.ManagerID = m.EmployeeID;

Note: Always test complex queries in a development environment before deploying them to production to ensure performance and accuracy.

Continue exploring the various features and capabilities of SQL to optimize your data management and retrieval strategies.