Single column index in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Effect of Single Column Index on Query Performance

Consider a table Employees with columns id, name, and department. A single column index is created on the department column.

What will be the output of the following query?

SELECT name FROM Employees WHERE department = 'Sales';

Assume the table has these rows:

id | name   | department
1  | Alice  | Sales
2  | Bob    | HR
3  | Carol  | Sales
4  | Dave   | IT

SQL

SELECT name FROM Employees WHERE department = 'Sales';

A ["Alice", "Carol"]

B ["Alice", "Bob", "Carol", "Dave"]

C []

D ["Bob", "Dave"]

💡 Hint

Think about which rows match the condition department = 'Sales'.

The query selects names where the department is 'Sales'. Only Alice and Carol belong to Sales.

🧠 Conceptual

intermediate

Purpose of Single Column Index

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

A To automatically backup the table data

B To speed up queries filtering or sorting by that column

C To prevent any data from being inserted into that column

D To store duplicate values in the column

💡 Hint

Think about how indexes help when searching data.

A single column index helps the database find rows faster when queries filter or sort by that column.

📝 Syntax

advanced

Correct Syntax to Create Single Column Index

Which SQL statement correctly creates a single column index named idx_department on the department column of the Employees table?

A INDEX CREATE idx_department ON Employees(department);

B CREATE INDEX ON Employees idx_department(department);

C CREATE Employees INDEX idx_department ON department;

D CREATE INDEX idx_department ON Employees(department);

💡 Hint

Remember the order: CREATE INDEX index_name ON table(column);

The correct syntax is CREATE INDEX idx_department ON Employees(department);

❓ optimization

advanced

Query Optimization Using Single Column Index

Given a large Orders table with millions of rows, which query will benefit most from a single column index on the customer_id column?

A SELECT * FROM Orders WHERE customer_id = 12345;

B SELECT * FROM Orders WHERE order_date = '2023-01-01';

C SELECT COUNT(*) FROM Orders;

D SELECT * FROM Orders ORDER BY order_date;

💡 Hint

Indexes help when filtering by the indexed column.

The query filtering by customer_id uses the index to quickly find matching rows.

🔧 Debug

expert

Why Does This Query Not Use the Single Column Index?

Consider a single column index on department in the Employees table. Why does this query not use the index?

SELECT * FROM Employees WHERE UPPER(department) = 'SALES';

A Because the query is missing a JOIN clause

B Because the index only works with numeric columns

C Because the function <code>UPPER()</code> on the column prevents index usage

D Because the table has no rows

💡 Hint

Think about how functions on indexed columns affect index use.

Applying a function like UPPER() on the indexed column disables the index usage because the database cannot use the index on transformed values.