Covering index concept in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

What is a covering index?

Which of the following best describes a covering index in a database?

A An index that stores only unique values from a single column.

B An index that contains all the columns needed to satisfy a query without accessing the table data.

C An index that automatically updates itself when the database schema changes.

D An index that covers all tables in the database for faster joins.

💡 Hint

Think about how an index can help avoid reading the main table data.

A covering index includes all columns required by a query, so the database can answer the query using only the index, avoiding extra reads from the table.

❓ query_result

intermediate

Output of query using covering index

Given a table Employees(id, name, department, salary) with a covering index on (department, salary), what will the query below return?

SELECT department, salary FROM Employees WHERE department = 'Sales';

A All rows with department 'Sales' showing all columns including id and name.

B An error because the index does not include the 'name' column.

C All rows with department 'Sales' showing only department and salary columns.

D No rows because the index only covers salary.

💡 Hint

Remember what columns the covering index includes and what the query requests.

The query requests only columns in the covering index (department, salary), so the database can use the index to return matching rows without accessing the full table.

📝 Syntax

advanced

Create a covering index

Which SQL statement correctly creates a covering index on the Orders table to cover the columns customer_id and order_date?

A CREATE INDEX idx_covering ON Orders WHERE customer_id, order_date;

B CREATE INDEX idx_covering ON Orders (customer_id, order_date);

C CREATE COVERING INDEX idx_covering ON Orders (customer_id, order_date);

D CREATE INDEX idx_covering ON Orders (customer_id) INCLUDE (order_date);

💡 Hint

Some SQL dialects support INCLUDE to add columns to the index without sorting on them.

Option D creates an index on customer_id and includes order_date as non-key columns, making it a covering index. Option D creates a composite index but may not cover all queries if order_date is not a key column.

❓ optimization

advanced

Benefit of covering index on query performance

How does a covering index improve query performance compared to a regular index?

A It reduces the number of disk reads by avoiding access to the main table data.

B It automatically compresses the data to save storage space.

C It allows queries to run without locking the table.

D It duplicates the entire table in the index for faster access.

💡 Hint

Think about how indexes help the database find data faster.

A covering index contains all columns needed by the query, so the database can answer the query using only the index, reducing disk reads and improving speed.

🔧 Debug

expert

Why does this query not use the covering index?

Given a covering index on (product_id, price) for the Sales table, why does the query below not use the covering index?

SELECT product_id, price, sale_date FROM Sales WHERE product_id = 101;

A Because the query requests <code>sale_date</code> which is not included in the covering index.

B Because the WHERE clause does not filter on <code>price</code>.

C Because the index is on <code>price, product_id</code> not <code>product_id, price</code>.

D Because the query uses SELECT instead of SELECT DISTINCT.

💡 Hint

Check which columns the index covers and which columns the query requests.

The query requests sale_date which is not part of the covering index, so the database must access the main table data, preventing the index from fully covering the query.