Covering index concept in SQL - Mini Project: Build & Apply

Using a Covering Index to Speed Up Queries

📖 Scenario: You manage a small online bookstore database. Customers often search for books by their title and want to see the author and price quickly. To make these searches faster, you will create a special database index called a covering index that includes all the columns needed for the search.

🎯 Goal: Build a covering index on the books table that includes the title, author, and price columns. Then write a query that uses this index to quickly find books by title and show their author and price.

📋 What You'll Learn

Create a books table with columns id, title, author, and price

Insert sample data into the books table

Create a covering index on title including author and price

Write a SELECT query to find books by title showing author and price

💡 Why This Matters

🌍 Real World

Covering indexes are used in real databases to speed up searches by including all needed columns in the index, reducing the time to get results.

💼 Career

Database administrators and developers use covering indexes to optimize query performance and improve user experience in applications.

1

Create the books table and insert data

Create a table called books with columns id (integer primary key), title (text), author (text), and price (decimal). Then insert these three rows exactly: (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99), (2, '1984', 'George Orwell', 8.99), (3, 'To Kill a Mockingbird', 'Harper Lee', 12.99).

SQL

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

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Create a covering index on title including author and price

Create an index called idx_books_title_covering on the books table that uses title as the indexed column and includes author and price as covering columns.

SQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    price DECIMAL
);

INSERT INTO books (id, title, author, price) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99),
(2, '1984', 'George Orwell', 8.99),
(3, 'To Kill a Mockingbird', 'Harper Lee', 12.99);

-- Create the covering index here
-- Your code here

Need a hint?

Use CREATE INDEX with INCLUDE to add covering columns.

3

Write a SELECT query to find books by title

Write a SELECT query that finds all columns title, author, and price from the books table where the title is exactly '1984'.

SQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    price DECIMAL
);

INSERT INTO books (id, title, author, price) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99),
(2, '1984', 'George Orwell', 8.99),
(3, 'To Kill a Mockingbird', 'Harper Lee', 12.99);

CREATE INDEX idx_books_title_covering ON books (title) INCLUDE (author, price);

-- Write the SELECT query here
-- Your code here

Need a hint?

Use SELECT with WHERE to filter by title.

4

Explain how the covering index helps the query

Add a SQL comment explaining that the index idx_books_title_covering helps the query by allowing the database to find the title and get author and price from the index without reading the full table.

SQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    price DECIMAL
);

INSERT INTO books (id, title, author, price) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99),
(2, '1984', 'George Orwell', 8.99),
(3, 'To Kill a Mockingbird', 'Harper Lee', 12.99);

CREATE INDEX idx_books_title_covering ON books (title) INCLUDE (author, price);

SELECT title, author, price FROM books WHERE title = '1984';

-- Add your explanation comment here
-- Your code here

Need a hint?

Write a SQL comment starting with -- explaining the benefit of the covering index.