Recursive CTE for series generation in SQL - Mini Project: Build & Apply

Generate a Number Series Using Recursive CTE

📖 Scenario: You are working with a database that needs a list of numbers from 1 to 10 for a report. Instead of manually typing each number, you will use a recursive query to generate this list automatically.

🎯 Goal: Create a recursive Common Table Expression (CTE) in SQL that generates a series of numbers from 1 to 10.

📋 What You'll Learn

Create a recursive CTE named number_series starting with the number 1.

Use recursion to add the next number by incrementing the previous number by 1.

Stop the recursion when the number reaches 10.

Select all numbers from the number_series CTE.

💡 Why This Matters

🌍 Real World

Generating sequences of numbers or dates is common in reports, data analysis, and filling missing data in databases.

💼 Career

Understanding recursive CTEs helps database developers and analysts automate repetitive data generation tasks efficiently.

1

Create the base case of the recursive CTE

Write the first part of the recursive CTE named number_series that starts with the number 1. Use WITH RECURSIVE number_series AS (SELECT 1 AS num to begin.

SQL

WITH RECURSIVE number_series AS (SELECT 1 AS num
-- Your code here

Need a hint?

Start the recursive CTE by selecting the number 1 as the base case.

2

Add the recursive part to increment the number

Add the recursive part of the CTE using UNION ALL and select num + 1 from number_series. Use WHERE num < 10 to stop at 10.

SQL

WITH RECURSIVE number_series AS (SELECT 1 AS num
UNION ALL
-- Your code here

Need a hint?

Use UNION ALL to combine the base case with the recursive step that adds 1 to the previous number.

3

Close the recursive CTE and select the series

Close the recursive CTE with a closing parenthesis ) and write a SELECT num FROM number_series statement to get the full series.

SQL

WITH RECURSIVE number_series AS (SELECT 1 AS num
UNION ALL
SELECT num + 1 FROM number_series WHERE num < 10
)
-- Your code here

Need a hint?

Close the CTE and select all numbers generated by it.

4

Complete the query with ordering

Add ORDER BY num ASC at the end of the query to display the numbers in ascending order.

SQL

WITH RECURSIVE number_series AS (SELECT 1 AS num
UNION ALL
SELECT num + 1 FROM number_series WHERE num < 10
)
SELECT num FROM number_series
-- Your code here

Need a hint?

Ordering the results makes the output easier to read.