Why indexes matter in SQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create an index on the column 'email' in the 'users' table.

SQL

CREATE INDEX [1] ON users(email);

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

Aidx_email

Bindex_email

Cemail_index

Dusers_email_idx

Attempts:

3 left

💡 Hint

Common Mistakes

Using a name that is too generic or conflicts with existing indexes.

The index name is usually descriptive. Here, users_email_idx is a common naming style for indexes.

2fill in blank

medium

Complete the query to find users with the last name 'Smith' using the index.

SQL

SELECT * FROM users WHERE last_name [1] 'Smith';

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

A=

BLIKE

C!=

DIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using LIKE without wildcards, which is less efficient.

Using != which finds non-matching rows.

The = operator is used to find exact matches, which can use an index efficiently.

3fill in blank

hard

Fix the error in the query to use the index on 'email' correctly.

SQL

SELECT * FROM users WHERE email [1] '[e-mail]';

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

AIN

BLIKE

CBETWEEN

D=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' which looks for exact matches only.

Using IN or BETWEEN which are not suitable here.

The LIKE operator with a wildcard is used to find emails ending with '@example.com'. Note: Leading wildcards may prevent full index usage in some databases.

4fill in blank

hard

Fill both blanks to create a unique index on the 'username' column in the 'accounts' table.

SQL

CREATE [1] INDEX [2] ON accounts(username);

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

AUNIQUE

BPRIMARY

CTEMPORARY

Daccounts_username_idx

Attempts:

3 left

💡 Hint

Common Mistakes

Using PRIMARY which is for primary keys only.

Using TEMPORARY which is for temporary indexes.

The UNIQUE keyword creates a unique index, preventing duplicate usernames. accounts_username_idx is a descriptive name following common conventions.

5fill in blank

hard

Fill all three blanks to write a query that uses the index on 'created_at' to find records after '2023-01-01'.

SQL

SELECT [1] FROM orders WHERE created_at [2] '[3]';

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

A*

B>

C2023-01-01

DCOUNT(*)

Attempts:

3 left

💡 Hint

Common Mistakes

Using COUNT(*) when all columns are needed.

Using '=' instead of '>' for records after the date.

Using an incorrect date format.

The query selects all columns (*) where the date is greater than (>) '2023-01-01'. This range query can efficiently use an index on 'created_at'.