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

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

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

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

Hint

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

Practice

1.

What is the main purpose of creating a single column index in a database?

easy

A. To store duplicate values in the column

B. To automatically backup the database

C. To prevent any changes to the column data

D. To speed up queries that filter or sort by that column

Solution

1. Step 1: Understand what an index does

   An index helps the database find rows faster when searching or sorting by the indexed column.

2. Step 2: Identify the purpose of a single column index

   A single column index speeds up queries that use that column in WHERE or ORDER BY clauses.

3. Final Answer:

   To speed up queries that filter or sort by that column -> Option D

4. Quick Check:

   Index speeds up queries = B [OK]

Hint: Index speeds up searches on one column [OK]

Common Mistakes:

• Thinking index stores duplicate values
• Believing index prevents data changes
• Confusing index with backup

2.

Which of the following is the correct syntax to create a single column index on the email column of a table named users?

easy

A. CREATE INDEX users ON idx_email(email);

B. CREATE INDEX idx_email ON users(email);

C. CREATE INDEX email ON users idx_email;

D. CREATE users INDEX idx_email(email);

Solution

1. Step 1: Recall the syntax for creating an index

   The correct syntax is: CREATE INDEX index_name ON table_name(column_name);

2. Step 2: Match the syntax with the options

   CREATE INDEX idx_email ON users(email); matches the correct syntax exactly.

3. Final Answer:

   CREATE INDEX idx_email ON users(email); -> Option B

4. Quick Check:

   CREATE INDEX index_name ON table(column) = A [OK]

Hint: Syntax: CREATE INDEX name ON table(column) [OK]

Common Mistakes:

• Swapping table and index names
• Missing ON keyword
• Incorrect order of keywords

3.

Given a table products with columns id, name, and category, and a single column index on category, what will the query below most likely do?

SELECT * FROM products WHERE category = 'Books';

medium

A. Use the index to quickly find rows where category is 'Books'

B. Sort the results by category before returning

C. Return an error because index cannot be used in WHERE clause

D. Scan the entire table without using the index

Solution

1. Step 1: Understand how an index affects a WHERE query

   An index on the category column helps the database quickly locate rows matching the condition category = 'Books'.

2. Step 2: Analyze the query behavior with the index

   The query will use the index to avoid scanning the whole table, speeding up the search.

3. Final Answer:

   Use the index to quickly find rows where category is 'Books' -> Option A

4. Quick Check:

   Index speeds WHERE search = D [OK]

Hint: WHERE on indexed column uses index for speed [OK]

Common Mistakes:

• Assuming index is ignored in WHERE clause
• Thinking index causes errors in queries
• Confusing index use with sorting

4.

Consider this SQL statement to create an index:

CREATE INDEX idx_price ON products price;

What is wrong with this statement?

medium

A. Table name should come after the column name

B. Index name cannot start with 'idx_'

C. Missing parentheses around the column name

D. CREATE INDEX cannot be used on numeric columns

Solution

1. Step 1: Check the syntax for CREATE INDEX

   The column name must be inside parentheses after the table name.

2. Step 2: Identify the syntax error

   The statement is missing parentheses around price. It should be ON products(price).

3. Final Answer:

   Missing parentheses around the column name -> Option C

4. Quick Check:

   Column name needs parentheses = C [OK]

Hint: Column name must be in parentheses after table [OK]

Common Mistakes:

• Omitting parentheses around column
• Misplacing table and column names
• Thinking index names have restrictions

5.

You have a table orders with columns order_id, customer_id, and order_date. You want to speed up queries that filter by customer_id and sort by order_date. Which approach is best?

hard

A. Create a composite index on customer_id and order_date

B. Create a single column index on order_date only

C. Create a single column index on customer_id only

D. Create no index to avoid slowing down inserts

Solution

1. Step 1: Understand query needs

   Queries filter by customer_id and sort by order_date, so both columns matter.

2. Step 2: Choose the best index type

   A composite index on both columns helps filter and sort efficiently, better than single column indexes.

3. Final Answer:

   Create a composite index on customer_id and order_date -> Option A

4. Quick Check:

   Composite index for filter + sort = A [OK]

Hint: Use composite index for multiple column queries [OK]

Common Mistakes:

• Using single column index when composite is better
• Avoiding indexes fearing insert slowdown
• Indexing only sort column ignoring filter