Index selection guidelines in DBMS Theory - 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.

DBMS Theory

CREATE INDEX [1] ON users(email);

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

Aidx_email

Busers_email_idx

Cemail_index

Dindex_email

Attempts:

3 left

💡 Hint

Common Mistakes

Using reserved keywords as index names.

Leaving the index name empty.

Using vague names like 'index1'.

The index name should be descriptive and unique. 'users_email_idx' clearly indicates the table and column indexed.

2fill in blank

medium

Complete the code to create a composite index on 'last_name' and 'first_name' columns.

DBMS Theory

CREATE INDEX [1] ON employees(last_name, first_name);

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

Aemp_name_idx

Bname_index

Cidx_employees_name

Demployee_index

Attempts:

3 left

💡 Hint

Common Mistakes

Creating an index with only one column when multiple columns are needed.

Using generic names that don't specify columns.

'idx_employees_name' clearly indicates the table and columns involved in the composite index.

3fill in blank

hard

Fix the error in the code to create a unique index on the 'username' column.

DBMS Theory

CREATE [1] INDEX idx_username ON users(username);

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

ACHECK

BPRIMARY

CFOREIGN

DUNIQUE

Attempts:

3 left

💡 Hint

Common Mistakes

Using PRIMARY instead of UNIQUE for unique indexes.

Omitting the UNIQUE keyword when uniqueness is required.

The keyword 'UNIQUE' ensures that the index enforces uniqueness on the 'username' column.

4fill in blank

hard

Fill both blanks to create an index on the 'order_date' column in descending order.

DBMS Theory

CREATE INDEX idx_order_date ON orders(order_date [1]); -- [2]

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

ADESC

BASC

Cdescending

Dascending

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'ascending' instead of 'ASC' or 'DESC'.

Omitting the order keyword, which defaults to ascending.

The correct SQL keyword for descending order is 'DESC'. The comment uses 'ASC' as a distractor but the code requires descending order.

5fill in blank

hard

Fill all three blanks to create a partial index on the 'status' column where status is 'active'.

DBMS Theory

CREATE INDEX idx_active_users ON users([1]) WHERE [2] = '[3]';

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

Astatus

Cactive

Dinactive

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong column name in the WHERE clause.

Using 'inactive' instead of 'active' in the filter.

The partial index is created on the 'status' column, filtering rows where status equals 'active'.