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.

Practice

1. What is the main benefit of a covering index in a database?

easy

A. It contains all columns needed by a query, avoiding table lookups.

B. It compresses data to save disk space.

C. It automatically backs up the database.

D. It encrypts data for security.

Solution

1. Step 1: Understand what a covering index stores

   A covering index includes all columns that a query needs, so the database can get results from the index alone.

2. Step 2: Identify the benefit of avoiding table lookups

   By not accessing the main table, queries run faster because less data is read and fewer disk operations occur.

3. Final Answer:

   It contains all columns needed by a query, avoiding table lookups. -> Option A

4. Quick Check:

   Covering index = all needed columns [OK]

Hint: Covering index has all query columns, no table needed [OK]

Common Mistakes:

• Thinking it compresses data
• Confusing with backup or encryption
• Assuming it stores only keys

2. Which of the following is the correct syntax to create a covering index on columns name and age in a table users?

easy

A. CREATE INDEX idx_users_name ON users(name, age);

B. CREATE INDEX idx_users_name_age ON users(name, age);

C. CREATE INDEX idx_users ON users WHERE name, age;

D. CREATE INDEX idx_users_name_age ON users USING name, age;

Solution

1. Step 1: Recall the syntax for creating an index on multiple columns

   The correct syntax is CREATE INDEX index_name ON table_name(column1, column2);

2. Step 2: Identify the option matching this syntax with both columns

   CREATE INDEX idx_users_name_age ON users(name, age); correctly names the index and lists both columns in parentheses separated by a comma.

3. Final Answer:

   CREATE INDEX idx_users_name_age ON users(name, age); -> Option B

4. Quick Check:

   CREATE INDEX with columns in parentheses [OK]

Hint: Use parentheses with columns separated by commas [OK]

Common Mistakes:

• Using WHERE instead of column list
• Missing parentheses around columns
• Incorrect keyword usage like USING

3. Given the table orders(order_id, customer_id, order_date, total) and an index on (customer_id, order_date, total), what will the query below use to speed up retrieval?

SELECT customer_id, order_date, total FROM orders WHERE customer_id = 5;

medium

A. The index covers all needed columns, so no table access is needed.

B. The index is ignored because it lacks order_id.

C. The query will scan the entire table ignoring the index.

D. The index is used only for filtering, then table rows are accessed.

Solution

1. Step 1: Check if the index includes all columns in SELECT and WHERE

   The index has customer_id, order_date, and total, which are all requested in SELECT and filtered in WHERE.

2. Step 2: Understand covering index usage

   Since the index has all needed columns, the database can answer the query using only the index without accessing the main table.

3. Final Answer:

   The index covers all needed columns, so no table access is needed. -> Option A

4. Quick Check:

   Index covers SELECT and WHERE columns [OK]

Hint: If index has all SELECT columns, no table needed [OK]

Common Mistakes:

• Thinking order_id is required in SELECT
• Assuming index is ignored if not all table columns included
• Believing table scan always happens

4. You created an index on (product_id, price) but your query SELECT product_id, price, stock FROM products WHERE product_id = 10; is slow. What is the likely reason?

medium

A. The index columns are in wrong order.

B. The index should be on stock only.

C. The query syntax is incorrect.

D. The index is not covering because stock is missing, causing table lookups.

Solution

1. Step 1: Identify columns used in SELECT and index

   The query selects product_id, price, and stock, but the index only has product_id and price.

2. Step 2: Understand why missing columns slow query

   Because stock is missing from the index, the database must access the main table to get stock values, slowing the query.

3. Final Answer:

   The index is not covering because stock is missing, causing table lookups. -> Option D

4. Quick Check:

   Missing SELECT column in index causes table access [OK]

Hint: Covering index must include all SELECT columns [OK]

Common Mistakes:

• Thinking column order affects covering
• Blaming query syntax without error
• Assuming index on stock only helps

5. You want to optimize this query:

SELECT user_id, last_login, status FROM users WHERE status = 'active' AND last_login > '2024-01-01';

Which covering index will best speed up this query?

hard

A. CREATE INDEX idx_users_userid ON users(user_id);

B. CREATE INDEX idx_users_login_status ON users(last_login, status);

C. CREATE INDEX idx_users_status_login ON users(status, last_login, user_id);

D. CREATE INDEX idx_users_status ON users(status);

Solution

1. Step 1: Identify columns used in WHERE and SELECT

   The query filters by status and last_login, and selects user_id, last_login, and status.

2. Step 2: Choose index covering all these columns

   CREATE INDEX idx_users_status_login ON users(status, last_login, user_id); includes status, last_login, and user_id, covering all needed columns for filtering and selection.

3. Step 3: Confirm column order for filtering

   Status is first in the index, matching the equality filter, then last_login for range filter, optimizing index usage.

4. Final Answer:

   CREATE INDEX idx_users_status_login ON users(status, last_login, user_id); -> Option C

5. Quick Check:

   Covering index with WHERE and SELECT columns in filter order [OK]

Hint: Include all WHERE and SELECT columns in index, filter columns first [OK]

Common Mistakes:

• Ignoring SELECT columns in index
• Wrong column order reducing index efficiency
• Creating index on only one filter column