Finding gaps in sequences in SQL - Mini Project: Build & Apply

Finding Gaps in Sequences with SQL

📖 Scenario: You work for a library that tracks book IDs assigned sequentially. Sometimes, IDs are missing due to errors or removals. You want to find these missing book IDs to fix the records.

🎯 Goal: Build an SQL query that finds missing numbers (gaps) in a sequence of book IDs stored in a table.

📋 What You'll Learn

Create a table called books with a column book_id containing these exact values: 1, 2, 3, 5, 6, 8, 9, 10

Create a variable or CTE called max_id that stores the maximum book_id from the books table

Generate a sequence of numbers from 1 to max_id using a recursive CTE called sequence

Write a query that selects numbers from sequence that are not present in books to find missing book_ids

💡 Why This Matters

🌍 Real World

Finding missing IDs or sequence gaps is common in inventory, order tracking, or any system that uses sequential numbering.

💼 Career

Database developers and analysts often need to detect missing records or gaps to ensure data integrity and correct errors.

1

Create the books table with sample data

Create a table called books with a single column book_id and insert these exact values: 1, 2, 3, 5, 6, 8, 9, 10.

SQL

-- Create table books and insert book_id values
-- Your code here

Need a hint?

Use CREATE TABLE to make the table and INSERT INTO to add the exact book IDs.

2

Find the maximum book_id

Create a Common Table Expression (CTE) called max_id that selects the maximum book_id from the books table.

SQL

CREATE TABLE books (book_id INT);
INSERT INTO books (book_id) VALUES (1), (2), (3), (5), (6), (8), (9), (10);

-- Create CTE max_id to find max book_id
WITH max_id AS (
    -- Your code here
)

Need a hint?

Use SELECT MAX(book_id) AS max_book_id FROM books inside the CTE.

3

Generate a sequence from 1 to max_id

Extend the CTEs by adding a recursive CTE called sequence that generates numbers from 1 up to max_book_id from the max_id CTE.

SQL

CREATE TABLE books (book_id INT);
INSERT INTO books (book_id) VALUES (1), (2), (3), (5), (6), (8), (9), (10);

WITH max_id AS (
    SELECT MAX(book_id) AS max_book_id FROM books
),
sequence AS (
    -- Your code here
)

Need a hint?

Use a recursive CTE starting at 1 and increment by 1 until it reaches max_book_id.

4

Find missing book IDs by comparing sequence to books

Write a final query that selects numbers from the sequence CTE that are not present in the books table to find missing book_ids.

SQL

CREATE TABLE books (book_id INT);
INSERT INTO books (book_id) VALUES (1), (2), (3), (5), (6), (8), (9), (10);

WITH max_id AS (
    SELECT MAX(book_id) AS max_book_id FROM books
),
sequence AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num + 1 FROM sequence, max_id WHERE num < max_id.max_book_id
)

-- Select missing book_ids from sequence not in books
-- Your code here

Need a hint?

Use WHERE num NOT IN (SELECT book_id FROM books) to find missing IDs.