Covering index concept in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a covering index on columns 'name' and 'age'.

SQL

CREATE INDEX idx_name_age ON users([1]);

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

Aname, age

Bage

Cid

Demail

Attempts:

3 left

💡 Hint

Common Mistakes

Creating an index on only one column when the query uses two.

Using columns not involved in the query.

The covering index includes both 'name' and 'age' columns, so the query can be answered using only the index.

2fill in blank

medium

Complete the query to select 'name' and 'age' using the covering index.

SQL

SELECT [1] FROM users WHERE age > 30;

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

Aname, age

B*

Cid

Demail

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting all columns with '*', which may cause a table lookup.

Selecting columns not in the index.

Selecting 'name' and 'age' matches the covering index columns, allowing efficient retrieval.

3fill in blank

hard

Fix the error in the index creation by choosing the correct syntax for including columns.

SQL

CREATE INDEX idx_user_info ON users([1]);

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

Aname age

Bname; age

Cname, age

D(name, age)

Attempts:

3 left

💡 Hint

Common Mistakes

Separating columns by space or semicolon instead of comma.

Using parentheses around columns.

Columns in an index must be comma-separated without parentheses.

4fill in blank

hard

Fill both blanks to create a covering index on 'email' and include 'last_login' as an included column.

SQL

CREATE INDEX idx_email ON users([1]) INCLUDE ([2]);

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

Aemail

Blast_login

Cage

Dname

Attempts:

3 left

💡 Hint

Common Mistakes

Putting included columns in the main index column list.

Using columns not relevant to the query.

The index is on 'email' and includes 'last_login' to cover queries needing both without extra lookups.

5fill in blank

hard

Fill all three blanks to write a query that uses the covering index on 'email' and 'last_login' to find users who logged in after 2023-01-01.

SQL

SELECT [1], [2] FROM users WHERE [3] > '2023-01-01';

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

Aemail

Blast_login

Dage

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting columns not in the index.

Filtering by a column not in the index.

Selecting 'email' and 'last_login' matches the covering index columns, and filtering by 'last_login' uses the index efficiently.