advanced sql

Subqueries (Nested Queries) #

What is a Subquery? #

A subquery is a query inside another query.

Used when:

• You need intermediate results
• You want dynamic filtering

Basic Syntax #

SELECT column
FROM table
WHERE column = (SELECT column FROM table WHERE condition);

Example #

Scenario: Find students with highest marks #

SELECT Name, Marks
FROM Students
WHERE Marks = (
    SELECT MAX(Marks) FROM Students
);

query finds max marks
Outer query returns student details

Types of Subqueries #

Single Row Subquery #

Returns one value

WHERE Age > (SELECT AVG(Age) FROM Students)

Multiple Row Subquery #

Returns multiple values

WHERE City IN (SELECT City FROM Customers)

Correlated Subquery #

Runs once for each row

SELECT Name, Salary
FROM Employees e1
WHERE Salary > (
    SELECT AVG(Salary)
    FROM Employees e2
    WHERE e1.Department = e2.Department
);

Compares salary within each department

When to Use #

• Complex filtering
• Comparing with aggregate values
• Dynamic queries

Common Table Expressions (CTEs) #

What is a CTE? #

A CTE (Common Table Expression) is a temporary result set you can reuse in a query.

Makes queries cleaner and easier to read

Syntax #

WITH cte_name AS (
    SELECT column
    FROM table
    WHERE condition
)
SELECT * FROM cte_name;

Example #

Scenario: High salary employees #

WITH HighSalary AS (
    SELECT Name, Salary
    FROM Employees
    WHERE Salary > 50000
)
SELECT * FROM HighSalary;

CTE with Aggregation #

WITH AvgSalary AS (
    SELECT Department, AVG(Salary) AS avg_sal
    FROM Employees
    GROUP BY Department
)
SELECT * FROM AvgSalary;

CTE vs Subquery #

Window Functions #

What are Window Functions? #

Window functions perform calculations across rows without grouping them.

Unlike GROUP BY, they do NOT reduce rows

Syntax #

FUNCTION() OVER (PARTITION BY column ORDER BY column)

ROW_NUMBER() #

Definition #

Assigns a unique row number to each row

Example #

SELECT Name, Salary,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS row_num
FROM Employees;

Highest salary = row 1

RANK() #

Definition #

Assigns rank with gaps in ranking

Example #

If two salaries are same → same rank, next rank skipped

DENSE_RANK() (Bonus) #

No gaps in ranking

PARTITION BY (Important) #

Divides data into groups

Example: Rank within Department #

SELECT Name, Department, Salary,
RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS dept_rank
FROM Employees;

Ranking separately for each department

Practical Example #

Scenario: Top 2 employees per department #

WITH RankedEmployees AS (
    SELECT Name, Department, Salary,
    ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS rn
    FROM Employees
)
SELECT *
FROM RankedEmployees
WHERE rn <= 2;

Summary Table #