CREATE INDEX syntax in SQL - Mini Project: Build & Apply

Create and Use an Index in SQL

📖 Scenario: You are managing a small online bookstore database. To help speed up searches for books by their titles, you want to create an index on the title column of the books table.

🎯 Goal: Build a SQL script that creates a books table, then creates an index on the title column to improve search speed.

📋 What You'll Learn

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

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

Use standard SQL syntax for creating the index.

💡 Why This Matters

🌍 Real World

Indexes help databases find data faster, which is important for websites and apps that need quick responses.

💼 Career

Database administrators and developers use indexes to optimize database performance and improve user experience.

1

Create the books table

Write a SQL statement to create a table called books with two columns: id as an integer primary key, and title as text.

SQL

-- Create the books table with id and title columns
-- Your code here

Need a hint?

Use CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT);

2

Prepare to create an index

Add a comment line that says -- Prepare to create index on title to indicate the next step.

SQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT
);
-- Your code here

Need a hint?

Just add a comment line starting with --

3

Create the index on title

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

SQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT
);
-- Prepare to create index on title
-- Your code here

Need a hint?

Use CREATE INDEX idx_title ON books(title);

4

Complete the SQL script

Add a comment line at the end that says -- Index creation complete to mark the script completion.

SQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT
);
-- Prepare to create index on title
CREATE INDEX idx_title ON books(title);
-- Your code here

Need a hint?

Just add a comment line starting with --