🔍 Ctrl+K
🟡 Intermediate

Subqueries

A Subquery is a query nested inside another query, often used to compare a value against an aggregated result.

SQL 
SELECT first_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees
);

Step 1: AVG, Step 2: compare.

🔴 Advanced

Second Highest Salary

Finding the second highest salary tests your ability to handle ranking and distinct values, commonly solved with a MAX subquery or DENSE_RANK.

SQL 
-- Method 1: MAX
SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Method 2: DENSE_RANK
WITH ranked AS (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT * FROM ranked WHERE rnk = 2;
Interview Tip
Clarify distinct salary vs second row. DENSE_RANK is clearest.
🟡 Intermediate

CTE (Common Table Expression)

A Common Table Expression (CTE) defined with WITH creates a named temporary result that makes complex queries readable and modular.

SQL 
WITH high_salary AS (
    SELECT employee_id, first_name, salary
    FROM employees
    WHERE salary > 70000
)
SELECT * FROM high_salary;

Improves readability, organization, multi-step logic.