7. Subqueries, EXISTS, CTEs and recursive CTEs

-- Scalar subquery
SELECT FullName, Salary FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees);

-- Correlated subquery: earn more than their department average
SELECT e.FullName FROM Employees e
WHERE e.Salary > (SELECT AVG(x.Salary) FROM Employees x WHERE x.DeptId = e.DeptId);

-- EXISTS / NOT EXISTS (NULL-safe, stops at first match)
SELECT c.Name FROM Customers c WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerId = c.CustomerId);

-- CTE
WITH DeptPay AS (
    SELECT DeptId, SUM(Salary) AS Payroll FROM Employees GROUP BY DeptId
)
SELECT d.DeptName, p.Payroll FROM DeptPay p JOIN Departments d ON d.DeptId = p.DeptId;

-- Recursive CTE: org hierarchy
WITH Org AS (
    SELECT EmpId, FullName, ManagerId, 0 AS Lvl FROM Employees WHERE ManagerId IS NULL     -- anchor
    UNION ALL
    SELECT e.EmpId, e.FullName, e.ManagerId, o.Lvl + 1                                     -- recursive member
    FROM Employees e JOIN Org o ON e.ManagerId = o.EmpId
)
SELECT * FROM Org ORDER BY Lvl
OPTION (MAXRECURSION 100);     -- default limit is 100; 0 = unlimited