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

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a B-tree index on the column 'name' in the 'employees' table.

SQL

CREATE INDEX idx_name ON employees ([1]);

Drag options to blanks, or click blank then click option'

Aage

Bname

Csalary

Ddepartment

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing a column that is not used in queries.

Using a column with many duplicate values which may not benefit much from indexing.

The index is created on the 'name' column to speed up searches based on employee names.

2fill in blank

medium

Complete the SQL query to use the index on 'name' to find employees named 'Alice'.

SQL

SELECT * FROM employees WHERE [1] = 'Alice';

Drag options to blanks, or click blank then click option'

Asalary

Bage

Cname

Ddepartment

Attempts:

3 left

💡 Hint

Common Mistakes

Filtering by a column that does not have an index.

Using a different column name in the WHERE clause.

The query filters employees by the 'name' column, which matches the indexed column.

3fill in blank

hard

Fix the error in the SQL query to use the index correctly for a range search on 'age'.

SQL

SELECT * FROM employees WHERE age [1] 30;

Drag options to blanks, or click blank then click option'

A>

BIN

CLIKE

D=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' which only finds exact matches.

Using 'LIKE' which is for pattern matching, not numeric ranges.

Using '>' allows the query to find employees older than 30, which can use a B-tree index on 'age' for range searches.

4fill in blank

hard

Fill both blanks to create a B-tree index on 'department' and use it in a query filtering by 'department'.

SQL

CREATE INDEX idx_dept ON employees ([1]);
SELECT * FROM employees WHERE [2] = 'Sales';

Drag options to blanks, or click blank then click option'

Adepartment

Bname

Dsalary

Attempts:

3 left

💡 Hint

Common Mistakes

Using different columns in the index and WHERE clause.

Using a column that is not indexed in the WHERE clause.

The index is created on 'department' and the query filters by 'department' to use the index efficiently.

5fill in blank

hard

Fill all three blanks to create a B-tree index on 'salary', select employees with salary greater than 50000, and order results by salary.

SQL

CREATE INDEX idx_salary ON employees ([1]);
SELECT * FROM employees WHERE [2] [3] 50000 ORDER BY salary;

Drag options to blanks, or click blank then click option'

Asalary

C>

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '>' in the WHERE clause.

Using different columns in the index and WHERE clause.

The index is on 'salary'. The query filters employees with salary greater than 50000 and orders by salary to use the index efficiently.