CTEs (Common Table Expressions)

Overview

CTE (Common Table Expression) — Write Cleaner, Multi-Step SQL

A CTE (Common Table Expression) is a temporary, named result set that you create before your main query.

It makes SQL much easier to read, especially when your logic has multiple steps.

A CTE is perfect for:

• Breaking a long query into simple parts
• Doing a calculation once, then reusing it
• Making multi-step logic readable for beginners

---

The Basic Pattern

WITH cte_name AS (
    SELECT ...
    FROM table_name
)
SELECT *
FROM cte_name;
  

Think of a CTE as a “temporary table” you create just for the next query.

---

Examples Using The Education Database

Example 1: Count enrollments per student

WITH StudentCounts AS (
    SELECT student_id,
           COUNT(*) AS TotalEnrollments
    FROM enrollments
    GROUP BY student_id
)
SELECT *
FROM StudentCounts;
  

This creates a small table showing how many courses each student is taking.

Example 2: Join a CTE back to students

WITH StudentCounts AS (
    SELECT student_id,
           COUNT(*) AS TotalEnrollments
    FROM enrollments
    GROUP BY student_id
)
SELECT s.first_name,
       s.last_name,
       sc.TotalEnrollments
FROM students s
LEFT JOIN StudentCounts sc
    ON s.student_id = sc.student_id;
  

A clean way to add a computed column without repeating code.

Example 3: Count students per course

WITH CourseCounts AS (
    SELECT course_id,
           COUNT(*) AS NumStudents
    FROM enrollments
    GROUP BY course_id
)
SELECT *
FROM CourseCounts;
  

A simple, readable version of the course popularity query.

Example 4: Courses with more than 3 students

WITH CourseCounts AS (
    SELECT course_id,
           COUNT(*) AS NumStudents
    FROM enrollments
    GROUP BY course_id
)
SELECT course_id,
       NumStudents
FROM CourseCounts
WHERE NumStudents > 3;
  

A classic “compute then filter” pattern.

---

Why Use CTEs?

• They make SQL easier to read
• They break complex logic into simple steps
• They avoid repeating the same subquery
• They are perfect for teaching beginners

---

CTEs — Summary

Feature	Description
Purpose	Create a temporary named result set
Syntax	WITH name AS (subquery)
Best for	Multi-step logic, readability
Strength	Makes SQL cleaner and easier to understand

---

A CTE is like writing your rough work at the top of the page…
Then using it to solve the real problem

---

Practice Tasks (Your Turn!)

Task 1: Students Table

Create a CTE that counts enrollments per student, then select from it.

Task 2: Courses Table

Create a CTE that counts students per course, then show only courses with more than 2 students.

Task 3: Instructors Table

Create a CTE that counts courses per department, then join it to instructors.