Index selection guidelines in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

When to use an index on a database column?

Which situation is the best reason to create an index on a database column?

A The column stores large text data that is rarely searched.

B The column is updated very frequently but never used in queries.

C The column is frequently used in WHERE clauses to filter rows.

D The column contains unique values but is never queried.

💡 Hint

Think about when indexes help speed up data retrieval.

Indexes improve query speed when columns are used to filter or join data. Columns rarely searched or frequently updated may not benefit from indexes.

❓ query_result

intermediate

Effect of index on query execution

Given a table Employees with an index on DepartmentID, what is the expected output of this query's execution plan?

DBMS Theory

EXPLAIN SELECT * FROM Employees WHERE DepartmentID = 5;

A The query sorts all rows by DepartmentID before filtering.

B The query performs a full table scan ignoring the index.

C The query returns an error because indexes cannot be used in WHERE clauses.

D The query uses the index to quickly find rows with DepartmentID = 5.

💡 Hint

Indexes help speed up searches on indexed columns.

When a column has an index, the database can use it to quickly locate matching rows instead of scanning the whole table.

📋 Factual

advanced

Correct syntax to create a composite index

Which SQL statement correctly creates a composite index on columns LastName and FirstName in the Customers table?

A CREATE INDEX idx_name ON Customers (LastName FirstName);

B CREATE INDEX idx_name ON Customers (LastName, FirstName);

C CREATE INDEX idx_name ON Customers LastName, FirstName;

D CREATE INDEX idx_name (LastName, FirstName) ON Customers;

💡 Hint

Check the order of keywords and parentheses in CREATE INDEX syntax.

The correct syntax places the index name after CREATE INDEX, then ON table name, then the columns in parentheses separated by commas.

❓ optimization

advanced

Choosing the best index for query optimization

You have a query filtering on City and Age columns. Which index choice will most likely improve performance best?

A A composite index on (City, Age).

B Separate indexes on City and Age columns.

C No index, rely on full table scan.

D An index only on Age column.

💡 Hint

Think about how composite indexes help with multiple column filters.

A composite index on both columns helps the database quickly filter rows matching both conditions in one index lookup.

🔍 Analysis

expert

Why does this index not improve query speed?

A table has an index on LastName. The query is:
SELECT * FROM Users WHERE UPPER(LastName) = 'SMITH';
Why might the index not be used?

A Because the query applies a function (UPPER) on the indexed column, preventing index use.

B Because the index is only for numeric columns, not text.

C Because the query is missing an ORDER BY clause.

D Because the table has too few rows to use indexes.

💡 Hint

Consider how functions on columns affect index usage.

Applying a function on an indexed column usually disables the use of that index unless a function-based index exists.