How an index works (B-tree mental model) in SQL - Practice Exercises

Challenge - 5 Problems

🧠 Conceptual

intermediate

How does a B-tree index speed up data search?

Imagine a phone book organized as a B-tree index. What is the main reason this structure helps find a name faster than scanning every page?

A It randomly picks entries to check, hoping to find the name quickly.

B It stores all data in one big list, so the computer reads everything faster.

C It divides the data into smaller groups, allowing quick jumps to the right section instead of checking every entry.

D It duplicates the entire data multiple times to speed up reading.

💡 Hint

Think about how you find a word in a dictionary without reading every page.

A B-tree index splits data into smaller parts, letting the search jump directly to the relevant section, avoiding a full scan.

❓ query_result

intermediate

What rows does this indexed query return?

Given a table Employees with a B-tree index on LastName, what rows will this query return?

SELECT * FROM Employees WHERE LastName = 'Smith';

SQL

CREATE TABLE Employees (ID INT, FirstName VARCHAR(50), LastName VARCHAR(50));
CREATE INDEX idx_lastname ON Employees(LastName);
-- Assume data includes multiple 'Smith' entries

A Only the first row where LastName is 'Smith', ignoring others.

B All rows where LastName is exactly 'Smith', found quickly using the index.

C All rows, because the index is ignored in equality searches.

D No rows, because indexes do not affect query results.

💡 Hint

Indexes help find matching rows faster but do not change which rows are returned.

The index helps the database quickly locate all rows with LastName 'Smith', returning all matching rows.

📝 Syntax

advanced

Which SQL statement correctly creates a B-tree index?

Choose the correct SQL command to create a B-tree index on the email column of the Users table.

A CREATE INDEX idx_email USING BTREE ON Users(email);

B CREATE BTREE INDEX idx_email ON Users(email);

C CREATE INDEX idx_email ON Users USING BTREE(email);

D CREATE INDEX idx_email ON Users(email);

💡 Hint

Standard SQL uses a simple syntax for creating indexes; specifying B-tree is often default.

Option D is the standard syntax. Options A, B, and D are invalid or non-standard in most SQL databases.

❓ optimization

advanced

Why might a B-tree index be inefficient for a column with many repeated values?

Consider a B-tree index on a column that has only three distinct values repeated many times. Why might this index not improve query speed much?

A Because the index nodes become large and searching them is slower than scanning the table.

B Because B-tree indexes only work with unique values and ignore duplicates.

C Because the database cannot use indexes on columns with repeated values.

D Because the index will cause the database to scan the entire table anyway.

💡 Hint

Think about how useful an index is when many rows share the same value.

When many rows share the same value, the index points to large groups, making it less efficient than scanning.

🔧 Debug

expert

What error occurs with this incorrect B-tree index creation?

What error will this SQL statement produce?

CREATE INDEX idx_wrong ON Users(email,);

A Syntax error due to trailing comma in column list.

B Runtime error because the index name is invalid.

C No error; index created successfully.

D Error because 'email' column does not exist.

💡 Hint

Check the syntax carefully, especially commas in lists.

The trailing comma after 'email' causes a syntax error in SQL.