Why indexing speeds up data retrieval in DBMS Theory - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to show how an index helps find data faster.

DBMS Theory

SELECT * FROM employees WHERE employee_id = [1];

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

A'12345'

B12345

Cemployee_id

Dname

Attempts:

3 left

💡 Hint

Common Mistakes

Adding quotes around numeric values, causing potential type mismatch.

Using column names instead of values.

The employee_id is typically numeric, so use the numeric literal without quotes to match the column type properly and utilize the index.

2fill in blank

medium

Complete the code to create an index on the 'name' column.

DBMS Theory

CREATE INDEX idx_name ON employees([1]);

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

Aname

Bemployee_id

Csalary

Ddepartment

Attempts:

3 left

💡 Hint

Common Mistakes

Creating index on wrong column.

Using table name instead of column name.

Creating an index on the 'name' column speeds up searches by that column.

3fill in blank

hard

Fix the error in the query that tries to use an index.

DBMS Theory

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

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

Asalary

BName

Cemployee_id

Dname

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong case for column names.

Using a column without an index.

The column name is case-sensitive in some databases; 'name' is correct, 'Name' may cause error.

4fill in blank

hard

Fill both blanks to create a query that uses an index to find employees with salary greater than 50000.

DBMS Theory

SELECT * FROM employees WHERE [1] [2] 50000;

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

Asalary

B>

C<

Demployee_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong column for filtering.

Using wrong comparison operator.

The query filters employees where salary is greater than 50000, using the 'salary' column and the '>' operator. An index on salary enables faster range scans.

5fill in blank

hard

Fill all three blanks to create a dictionary comprehension that maps employee names to their salaries, but only for salaries above 60000.

DBMS Theory

{ [3]['[1]']: [3]['[2]'] for [3] in employees if [3]['[2]'] > 60000 }

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

Aname

Bsalary

Cemployee

Demployee_id

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up keys and values.

Using wrong variable names.

Not filtering correctly.

This comprehension creates a dictionary with employee names as keys and salaries as values, filtering for salaries above 60000.