PART 3 • SQL MASTERY

Answer Multi-Step Questions without Losing Row Detail

Compose queries in layers, test existence safely and calculate ranks, comparisons and running results while keeping each source row visible.

Level 15 of 18Advanced SQL180–220 minutes

BY THE END, YOU CAN

Choose the correct subquery shape.
Use IN, EXISTS, ANY and ALL.
Trace a correlated subquery.
Layer logic with CTEs.
Explain recursive termination.
Rank rows with window functions.

01 • MATCH THE SHAPE TO THE CONTEXT

A Subquery Is a Query Whose Result Feeds Another Query

Before choosing syntax, ask how many rows and columns the inner query is allowed to return.

ONE VALUE

Scalar subquery

WHERE salary > (
  SELECT AVG(salary)
  FROM employees
)

Valid where one expression is expected. More than one returned row causes an error.

ONE COLUMN, MANY ROWS

List subquery

WHERE dept_id IN (
  SELECT dept_id
  FROM active_departments
)

Feeds a membership or quantified comparison such as IN, ANY or ALL.

ROWS AND COLUMNS

Derived table

FROM (
  SELECT dept_id, AVG(salary) avg_sal
  FROM employees GROUP BY dept_id
) AS stats

Behaves like a temporary row source inside this statement and needs an alias.

DEPENDS ON OUTER ROW

Correlated subquery

WHERE salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.dept_id = e.dept_id
)

References the current outer row. Think row-by-row even if the optimizer rewrites it.

02 • ASK THE PRECISE SET QUESTION

IN Compares Values; EXISTS Tests Whether a Row Exists

Is this value equal to a member of the returned list?

dept_id IN (SELECT dept_id ...)

EXISTS

Does the correlated inner query return at least one row?

EXISTS (SELECT 1 FROM ...)

> ANY

Is the value greater than at least one value in the set?

salary > ANY (SELECT salary ...)

> ALL

Is the value greater than every value in the set?

salary > ALL (SELECT salary ...)

The NOT IN + NULL trap

If the subquery set contains NULL, x NOT IN (...) can become UNKNOWN for every candidate and return no rows. For anti-matching, a correlated NOT EXISTS is usually clearer and NULL-safe.

WHERE NOT EXISTS (
  SELECT 1 FROM registrations r
  WHERE r.student_id = s.student_id
)

ANY needs one TRUE comparisonALL needs every comparison TRUEEXISTS ignores selected column valuesNOT EXISTS keeps zero-match outer rows

03 • UNDERSTAND THE DEPENDENCY

A Correlated Subquery Receives a Value from the Outer Row

OUTER ROWMeera · CSE · ₹82k

Alias e supplies e.dept_id.

→

INNER QUERYAverage salary for CSE

Runs conceptually with the supplied department.

→

PREDICATE₹82k > ₹75k = TRUE

The outer row remains in the result.

Logical model

For each outer row, evaluate the inner query using that row’s correlated value, then test the predicate.

Physical execution

The database optimizer may decorrelate the query into joins or other operations. SQL states the result, not a guaranteed loop.

Performance habit

Check indexes on correlated lookup columns and compare an equivalent join or pre-aggregation when data is large.

04 • NAME EACH QUERY STAGE

CTEs Turn Complex SQL into Explainable Steps

WITH department_stats AS (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
), above_average AS (
  SELECT e.*
  FROM employees e
  JOIN department_stats d USING (dept_id)
  WHERE e.salary > d.avg_salary
)
SELECT * FROM above_average;

READABILITY

Name intermediate results by meaning, not by implementation details.

SCOPE

A CTE exists only for the statement that follows it.

OPTIMIZATION

A CTE is not automatically a stored table or guaranteed optimization barrier; behavior varies by database and version.

Recursive CTE = anchor + recursive member + termination

1 • ANCHORSELECT employee_id, manager_id, 0 depth

Creates the initial working rows.

→

2 • RECURSIVE MEMBERJOIN hierarchy h ON e.manager_id = h.employee_id

Finds the next layer from the previous result.

→

3 • STOPNo new rows

Termination occurs when the recursive member produces nothing new, or an explicit depth guard stops it.

05 • CALCULATE ACROSS RELATED ROWS

Window Functions Keep Row Detail

SELECT name, branch, score,
       DENSE_RANK() OVER (
         PARTITION BY branch
         ORDER BY score DESC
       ) AS branch_rank
FROM students;

Function

DENSE_RANK() defines the calculation.

PARTITION BY

Restarts the calculation for each branch; omit it for one global window.

ORDER BY

Defines row sequence inside each partition.

Frame

For running aggregates, the frame identifies which neighboring rows participate.

ROW_NUMBER1, 2, 3, 4

Unique sequence even when scores tie.

RANK1, 2, 2, 4

Ties share rank and leave a gap.

DENSE_RANK1, 2, 2, 3

Ties share rank without a gap.

LAG / LEADPrevious / next

Reads a neighboring row without a self join.

SUM(...) OVERRunning total

Aggregates over a window while preserving every detail row.

GROUP BY

Collapses rows

One result row per group, unless further joins restore detail.

VERSUS

WINDOW

Annotates rows

Each input row remains, with a calculation added beside it.

06 • CHOOSE THE SIMPLEST CORRECT TOOL

Start from the Result Shape You Need

Question	Best starting tool	Why
Compare every salary with one overall average	Scalar subquery	Inner query returns one value.
Keep students who have at least one registration	EXISTS	The existence of a match matters, not its columns.
Find employees above their own department average	Correlated subquery or grouped CTE + join	The comparison value varies by department.
Make a long transformation readable	CTE	Each stage receives a meaningful name.
Walk an organization hierarchy	Recursive CTE	Each iteration discovers the next level.
Rank students without hiding individual rows	Window function	The result keeps one row per student.

07 • FOLLOW THE DATA DEPENDENCY

Interactive Subquery Execution Tracer

Choose a scenario, then move through outer rows to see the inner result, truth value and final decision.

GENERATED SQL

OUTER ROW

→

INNER RESULT

→

PREDICATE

→

DECISION

08 • SEE TIES AND PARTITIONS

Window Ranking Laboratory

Change the function and partition. The same detail rows remain while calculated values change.

Window calculationPartition

Equal scores are ties Shaded band is a partition

GENERATED SQL

09 • CHECK YOUR UNDERSTANDING

Ten Formative Concept Checks

1. A scalar subquery must return:

2. EXISTS becomes TRUE when its subquery:

3. Which is generally safer for an anti-match when the inner key may contain NULL?

4. salary > ALL (subquery) means salary is greater than:

5. A correlated subquery refers to:

6. A non-recursive CTE is best described as:

7. A recursive CTE must have a reliable:

8. Which rank sequence has no gap after a tie?

9. PARTITION BY in a window definition:

10. The key difference from GROUP BY is that a window function:

Answered correctly: 0 of 10

10 • EXPLAIN & PREPARE

University and Interview Questions

2-MARK

Define a scalar subquery.
IN versus EXISTS?
What is a CTE?
RANK versus DENSE_RANK?

5-MARK / PRACTICAL

Trace a correlated subquery.
Explain the NOT IN NULL trap.
Write a recursive hierarchy query.
Find top three salaries per department.

INTERVIEW

When would you replace correlation with a join?
Do CTEs always improve performance?
How does a window frame affect running totals?
How do you handle deterministic tie ordering?

Show the advanced-SQL answer framework

State the required output grain.
Identify whether the comparison value is scalar, a set or row-dependent.
Choose subquery, EXISTS, CTE or window logic.
Describe NULL and empty-set behavior.
Explain partitions, ordering and frames when used.
Predict row count and whether detail survives.
Discuss indexes and an equivalent rewrite where relevant.

LEVEL 15 SUMMARY

You Can Now Structure Advanced SQL Deliberately

Subquery cardinality must fit its surrounding expression.
EXISTS tests rows; IN compares values.
NOT EXISTS avoids the common NOT IN NULL trap.
CTEs name stages; recursion needs termination.
Window functions annotate rows instead of collapsing them.
Partitions, ordering and frames define analytical context.

COURSE CHECKPOINT

Mark this level when you can choose and explain the simplest correct advanced-SQL tool.

Saved in this browser only.