When indexes help and when they hurt in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

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

SQL

CREATE INDEX [1] ON users(email);

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

Aidx_email

Bemail_index

Cusers_email_idx

Dindex_email

Attempts:

3 left

💡 Hint

Common Mistakes

Using reserved keywords as index names.

Leaving out the index name entirely.

The index name should be unique and descriptive. 'users_email_idx' is a common naming convention.

2fill in blank

medium

Complete the code to select rows where 'last_name' is 'Smith' using the index.

SQL

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

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

A!=

BLIKE

CIN

D=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '!=' which excludes the value.

Using 'LIKE' without wildcards.

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 'created_at' for filtering recent records.

SQL

SELECT * FROM orders WHERE created_at [1] '2023-01-01';

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

A!=

B>=

C=

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' which limits to one date only.

Using '!=' which excludes the date.

Using '>=' selects records created on or after the date, which can use the index efficiently.

4fill in blank

hard

Fill both blanks to create a composite index on 'category' and 'price' columns.

SQL

CREATE INDEX [1] ON products([2], price);

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

Aidx_category_price

Bcategory

Ccategory_id

Didx_price_category

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong column names in the index.

Naming the index unclearly.

The index name 'idx_category_price' clearly describes the columns. The first column in the index is 'category'.

5fill in blank

hard

Fill all three blanks to write a query that uses an index to find products priced above 100 in category 'Electronics'.

SQL

SELECT * FROM products WHERE [1] = 'Electronics' AND [2] [3] 100;

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

Acategory

Bprice

C>

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '>' for price comparison.

Swapping column names in conditions.

The query filters by category 'Electronics' and price greater than 100, which can use an index on (category, price).