Day 9: Subqueries and Common Table Expressions (CTEs)

Day 9: Subqueries and Common Table Expressions (CTEs)

1. Subqueries
        A subquery is a query nested inside another query. It can be used in SELECT, FROM, or WHERE clauses.

Types of Subqueries:

1. Single-row Subquery: Returns one row.
2. Multi-row Subquery: Returns multiple rows.
3. Correlated Subquery: Depends on the outer query.

    Example 1: Single-row Subquery 

        SQL

        -- Find employees earning more than the average salary
           SELECT Name, Salary
            FROM Employees
            WHERE Salary > (SELECT AVG(Salary) FROM Employees);

    Example 2: Multi-row Subquery

        SQL

        -- Find employees in specific departments
        SELECT Name
        FROM Employees
        WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location = 'NY');

    Example 3: Correlated Subquery

        SQL

        -- Find employees with the highest salary in each department
        SELECT Name, Salary
        FROM Employees E1
        WHERE Salary = (
            SELECT MAX(Salary)
            FROM Employees E2
            WHERE E1.DepartmentID = E2.DepartmentID
        );

2. Common Table Expressions (CTEs)

        CTEs provide a way to define temporary result sets that can be reused in the main query.

        Syntax:

            WITH CTEName AS (
                SELECT Column1, Column2
                FROM TableName
                WHERE Condition )
            SELECT * FROM CTEName;

        Example 1: Simple CTE 

            SQL

        -- Get total salary per department
        WITH DepartmentSalary AS (
            SELECT DepartmentID, SUM(Salary) AS TotalSalary
            FROM Employees
            GROUP BY DepartmentID
        )
        SELECT *
        FROM DepartmentSalary;

Example 2: Recursive CTE Used for hierarchical data (e.g., organizational structures).

        SQL

        WITH RecursiveCTE AS (
        -- Anchor member
            SELECT EmployeeID, ManagerID, Name
            FROM Employees
            WHERE ManagerID IS NULL
            UNION ALL
        -- Recursive member
            SELECT E.EmployeeID, E.ManagerID, E.Name
            FROM Employees E
            INNER JOIN RecursiveCTE R
            ON E.ManagerID = R.EmployeeID
        )
        SELECT *
        FROM RecursiveCTE;

Action Steps

1. Practice writing subqueries in WHERE, SELECT, and FROM.
2. Use a CTE to simplify complex queries.
3. Create a recursive CTE for hierarchical data if applicable.