Subqueries and Common Table Expressions

Sometimes the answer to one question is needed to ask the next. Subqueries and CTEs let you build that step by step.

Subquery in WHERE

Compare a value against the result of another query.

SELECT name, score
FROM students
WHERE score > (SELECT AVG(score) FROM students);

Subquery with IN

Use IN when the inner query returns a list.

SELECT name
FROM students
WHERE id IN (
    SELECT student_id
    FROM enrolments
    WHERE course_id = 3
);

Common Table Expressions

A CTE names a temporary result with WITH. It is easier to read than nesting three subqueries and can be referenced more than once.

WITH level_averages AS (
    SELECT level, AVG(score) AS average_score
    FROM students
    GROUP BY level
)
SELECT level, average_score
FROM level_averages
WHERE average_score > 60;

Practice

1. Find students scoring above the class average.
2. List courses that have at least one enrolment.
3. Rewrite a nested query using WITH.

Hands-on exercise

Write a query that returns students who scored above the average for their own level.
Use a CTE that computes the average score per level.

Recap

Subqueries answer one question inside another. CTEs name the steps so complex reports stay readable.