Recursive CTEs in MySQL - Mini Project: Build & Apply

Building a Recursive CTE to Calculate Factorials

📖 Scenario: You are working with a database that needs to calculate factorial values for numbers 1 through 5. Factorials are useful in many real-world calculations like probability and statistics.

🎯 Goal: Create a recursive Common Table Expression (CTE) in MySQL to calculate factorials for numbers from 1 to 5.

📋 What You'll Learn

Create a recursive CTE named factorial_cte

Start the recursion with the base case for factorial of 1

Recursively calculate factorial for numbers 2 through 5

Select all numbers and their factorials from the CTE

💡 Why This Matters

🌍 Real World

Recursive CTEs help calculate sequences or hierarchical data like organizational charts or factorials directly in the database.

💼 Career

Understanding recursive CTEs is useful for database developers and analysts who work with complex data relationships or need to perform iterative calculations efficiently.

1

Create the base case for the recursive CTE

Write a recursive CTE named factorial_cte that starts with the base case: number 1 and factorial 1.

MySQL

WITH RECURSIVE factorial_cte AS (
  -- Base case
  SELECT 1 AS number, 1 AS factorial
)
-- Your code here

Need a hint?

The base case starts the recursion with number 1 and factorial 1.

2

Add the recursive part to calculate factorials

Extend the factorial_cte by adding a recursive SELECT that calculates factorial for the next number by multiplying the current factorial by the next number. Continue recursion while number is less than 5.

MySQL

WITH RECURSIVE factorial_cte AS (
  SELECT 1 AS number, 1 AS factorial
  UNION ALL
  -- Recursive part
  SELECT number + 1, factorial * (number + 1) FROM factorial_cte WHERE number < 5
)
-- Your code here

Need a hint?

The recursive part selects the next number and multiplies the factorial by that number.

3

Select all numbers and their factorials from the CTE

Write a SELECT statement to retrieve all number and factorial columns from factorial_cte.

MySQL

WITH RECURSIVE factorial_cte AS (
  SELECT 1 AS number, 1 AS factorial
  UNION ALL
  SELECT number + 1, factorial * (number + 1) FROM factorial_cte WHERE number < 5
)
SELECT number, factorial FROM factorial_cte;
-- Your code here

Need a hint?

Select both columns from the CTE to see the factorial results.

4

Complete the query with ordering

Add an ORDER BY number ASC clause to the SELECT statement to display factorials in ascending order of number.

MySQL

WITH RECURSIVE factorial_cte AS (
  SELECT 1 AS number, 1 AS factorial
  UNION ALL
  SELECT number + 1, factorial * (number + 1) FROM factorial_cte WHERE number < 5
)
SELECT number, factorial FROM factorial_cte ORDER BY number ASC;
-- Your code here

Need a hint?

Ordering the results makes it easier to read the factorials from smallest to largest number.