Advanced SQL Queries Explained

1. Subqueries

Subqueries, also known as nested queries, are queries embedded within another query. They are used to retrieve data that will be used in the main query as a condition to further restrict the data to be retrieved. Subqueries can be used in the SELECT, FROM, WHERE, and HAVING clauses.

Example:

SELECT ProductName
FROM Products
WHERE CategoryID = (SELECT CategoryID FROM Categories WHERE CategoryName = 'Electronics');
    

In this example, the subquery retrieves the CategoryID for the category named 'Electronics', and the main query uses this result to find all products in that category.

2. Common Table Expressions (CTEs)

Common Table Expressions (CTEs) are temporary result sets that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. They are defined using the WITH clause and can be used to simplify complex queries and make them more readable.

Example:

WITH SalesCTE AS (
    SELECT ProductID, SUM(Quantity) AS TotalSales
    FROM OrderDetails
    GROUP BY ProductID
)
SELECT ProductName, TotalSales
FROM Products
JOIN SalesCTE ON Products.ProductID = SalesCTE.ProductID
ORDER BY TotalSales DESC;
    

Here, the CTE named SalesCTE calculates the total sales for each product, and the main query uses this result to join with the Products table and order the products by their total sales.

3. Window Functions

Window functions perform calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions, window functions do not cause rows to become grouped into a single output row. Instead, the rows retain their separate identities.

Example:

SELECT ProductName, Price,
       RANK() OVER (ORDER BY Price DESC) AS PriceRank
FROM Products;
    

In this example, the RANK() window function assigns a rank to each product based on its price, with the most expensive product getting the highest rank.

4. Recursive Queries

Recursive queries are used to query hierarchical data structures, such as organizational charts or file systems. They use a common table expression (CTE) with a recursive part that references the CTE itself.

Example:

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT EmployeeID, ManagerID, EmployeeName
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.EmployeeName
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;
    

This query recursively retrieves all employees in an organizational hierarchy, starting from the top-level manager (where ManagerID is NULL) and moving down the hierarchy.

5. PIVOT and UNPIVOT

The PIVOT operator is used to rotate rows into columns, while the UNPIVOT operator does the opposite, rotating columns into rows. These operators are useful for transforming data for reporting and analysis.

Example of PIVOT:

SELECT 'TotalSales' AS TotalSales, [1], [2], [3]
FROM (
    SELECT ProductID, Quantity
    FROM OrderDetails
) AS SourceTable
PIVOT (
    SUM(Quantity) FOR ProductID IN ([1], [2], [3])
) AS PivotTable;
    

In this example, the PIVOT operator transforms the rows of ProductID into columns, with the sum of quantities for each product displayed in the respective columns.

Example of UNPIVOT:

SELECT ProductID, Quantity
FROM (
    SELECT [1], [2], [3]
    FROM PivotTable
) AS SourceTable
UNPIVOT (
    Quantity FOR ProductID IN ([1], [2], [3])
) AS UnpivotTable;
    

Here, the UNPIVOT operator transforms the columns of ProductID back into rows, with the quantities displayed in the respective rows.