Complex Queries & CTEs

📋 Master Dataset Base

💡 Company-Wide Average Salary Value Baseline: ₹71,250

1. Nested Subqueries

Definition: An inner lookup script nested deep inside an outer statement block to dynamic-filter results.

SELECT name, salary 
FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

2. CTEs (Common Table Expressions)

Definition: Declares a neat virtual named table query temporarily using the WITH operator for complex calculations.

WITH DeptKPI AS ( 
    SELECT department, AVG(salary) AS d_avg 
    FROM employees 
    GROUP BY department 
) 
SELECT e.name, e.department, d.d_avg 
FROM employees e 
JOIN DeptKPI d ON e.department = d.department;