Composite index and column order in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

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

SQL

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

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

Alast_name

Bemployee_id

Cfirst_name

Ddepartment

Attempts:

3 left

💡 Hint

Common Mistakes

Putting 'first_name' before 'last_name' which may reduce index efficiency.

Using a column not relevant to the query.

The composite index should start with 'last_name' followed by 'first_name' to optimize queries filtering by last name first.

2fill in blank

medium

Complete the code to select rows using the composite index on 'last_name' and 'first_name'.

SQL

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

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

Adepartment

Bfirst_name

Cemployee_id

Dhire_date

Attempts:

3 left

💡 Hint

Common Mistakes

Filtering on columns not part of the composite index.

Using only one column in the WHERE clause when both are needed.

The composite index is on 'last_name' and 'first_name', so the query should filter on both columns to use the index efficiently.

3fill in blank

hard

Fix the error in the composite index creation by placing columns in the correct order.

SQL

CREATE INDEX idx_order ON orders([1], order_date);

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

Acustomer_id

Border_date

Cproduct_id

Dstatus

Attempts:

3 left

💡 Hint

Common Mistakes

Putting 'order_date' before 'customer_id' which reduces index effectiveness.

Choosing unrelated columns for the index.

The composite index should start with 'customer_id' before 'order_date' to optimize queries filtering by customer first.

4fill in blank

hard

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

SQL

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

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

Acategory

Bprice

Cname

Dstock

Attempts:

3 left

💡 Hint

Common Mistakes

Reversing the order of columns in the index.

Using columns not related to filtering.

The composite index should be on 'category' first, then 'price' to optimize queries filtering by category and price range.

5fill in blank

hard

Fill all three blanks to create a composite index on 'region', 'sales_rep', and 'sale_date' columns.

SQL

CREATE INDEX idx_sales ON sales([1], [2], [3]);

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

Aregion

Bsales_rep

Csale_date

Dcustomer_id

Attempts:

3 left

💡 Hint

Common Mistakes

Changing the order of columns which affects index usage.

Including columns not used in filtering.

The composite index should be created with 'region' first, then 'sales_rep', and finally 'sale_date' to optimize queries filtering by these columns in this order.