How an index works (B-tree mental model) in SQL - Try It Yourself

How an index works (B-tree mental model)

📖 Scenario: You are managing a small library database. You want to understand how indexes help find books faster by organizing data like a tree.

🎯 Goal: Build a simple table of books, create an index on the book titles, and write a query that uses the index to find a book quickly.

📋 What You'll Learn

Create a table called books with columns id (integer) and title (text).

Insert exactly these three rows into books: (1, 'Algorithms'), (2, 'Databases'), (3, 'Networking').

Create an index called idx_title on the title column of books.

Write a query to select all columns from books where title equals 'Databases'.

💡 Why This Matters

🌍 Real World

Indexes are used in real databases to make searching large tables fast, like finding a book by title in a library system.

💼 Career

Database administrators and developers use indexes to optimize queries and improve application performance.

1

Create the books table and insert data

Write SQL to create a table called books with columns id as integer and title as text. Then insert these rows exactly: (1, 'Algorithms'), (2, 'Databases'), and (3, 'Networking').

SQL

-- Create the books table and insert rows
-- Your code here

Need a hint?

Use CREATE TABLE to make the table, then INSERT INTO to add each row.

2

Create an index on the title column

Write SQL to create an index called idx_title on the title column of the books table.

SQL

CREATE TABLE books (id INTEGER, title TEXT);
INSERT INTO books (id, title) VALUES (1, 'Algorithms');
INSERT INTO books (id, title) VALUES (2, 'Databases');
INSERT INTO books (id, title) VALUES (3, 'Networking');
-- Create the index idx_title on title
-- Your code here

Need a hint?

Use CREATE INDEX idx_title ON books(title); to make the index.

3

Write a query to find the book titled 'Databases'

Write a SQL query to select all columns from books where the title is exactly 'Databases'.

SQL

CREATE TABLE books (id INTEGER, title TEXT);
INSERT INTO books (id, title) VALUES (1, 'Algorithms');
INSERT INTO books (id, title) VALUES (2, 'Databases');
INSERT INTO books (id, title) VALUES (3, 'Networking');
CREATE INDEX idx_title ON books(title);
-- Write the SELECT query to find the book with title 'Databases'
-- Your code here

Need a hint?

Use SELECT * FROM books WHERE title = 'Databases'; to find the book.

4

Explain how the index helps find the book faster

Add a comment explaining that the index idx_title organizes the title values in a B-tree structure, which helps the database find the book titled 'Databases' quickly without scanning all rows.

SQL

CREATE TABLE books (id INTEGER, title TEXT);
INSERT INTO books (id, title) VALUES (1, 'Algorithms');
INSERT INTO books (id, title) VALUES (2, 'Databases');
INSERT INTO books (id, title) VALUES (3, 'Networking');
CREATE INDEX idx_title ON books(title);
SELECT * FROM books WHERE title = 'Databases';
-- Add a comment explaining how the index helps
-- Your code here

Need a hint?

Write a comment starting with -- explaining the B-tree helps find titles fast.