Single column index in SQL - Mini Project: Build & Apply

Create and Use a Single Column Index in SQL

📖 Scenario: You are managing a small online bookstore database. The books table stores information about each book, including its id, title, and author. To speed up searches by book title, you want to create an index on the title column.

🎯 Goal: Build a single column index on the title column of the books table to improve query performance when searching by title.

📋 What You'll Learn

Create a books table with columns id (integer), title (varchar), and author (varchar).

Insert three specific book records into the books table.

Create a single column index named idx_title on the title column.

Write a query that selects all columns from books where the title matches a specific value.

💡 Why This Matters

🌍 Real World

Indexes help databases find data faster, just like an index in a book helps you find a topic quickly.

💼 Career

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

1

Create the books table and insert data

Create a table called books with columns id as integer, title as varchar(100), and author as varchar(100). Then insert these three rows exactly: (1, 'The Great Gatsby', 'F. Scott Fitzgerald'), (2, '1984', 'George Orwell'), and (3, 'To Kill a Mockingbird', 'Harper Lee').

SQL

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

Need a hint?

Use CREATE TABLE books (id INTEGER, title VARCHAR(100), author VARCHAR(100)); to create the table. Then use INSERT INTO books (id, title, author) VALUES (...), (...), (...); to add the rows.

2

Define the index name

Create a variable or note the index name idx_title that you will use to create the index on the title column.

SQL

CREATE TABLE books (
    id INTEGER,
    title VARCHAR(100),
    author VARCHAR(100)
);

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

-- Define the index name idx_title
-- Your code here

Need a hint?

Remember the index name idx_title because you will use it in the next step to create the index.

3

Create the single column index on title

Write a SQL statement to create a single column index named idx_title on the title column of the books table.

SQL

CREATE TABLE books (
    id INTEGER,
    title VARCHAR(100),
    author VARCHAR(100)
);

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

-- Create the index idx_title on title
-- Your code here

Need a hint?

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

4

Query the books table using the indexed column

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

SQL

CREATE TABLE books (
    id INTEGER,
    title VARCHAR(100),
    author VARCHAR(100)
);

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

CREATE INDEX idx_title ON books(title);

-- Query books where title is '1984'
-- Your code here

Need a hint?

Use SELECT * FROM books WHERE title = '1984'; to query the table using the indexed column.