Covering indexes with INCLUDE in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a basic index on the column 'last_name'.

PostgreSQL

CREATE INDEX idx_last_name ON employees([1]);

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

Alast_name

Bfirst_name

Cemployee_id

Dsalary

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing a different column name than 'last_name'.

Forgetting to put the column name inside parentheses.

The index should be created on the 'last_name' column as specified.

2fill in blank

medium

Complete the code to create an index on 'last_name' and include 'first_name' as an included column.

PostgreSQL

CREATE INDEX idx_last_name_include ON employees(last_name) INCLUDE ([1]);

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

Asalary

Bemployee_id

Cdepartment

Dfirst_name

Attempts:

3 left

💡 Hint

Common Mistakes

Including the wrong column instead of 'first_name'.

Omitting the parentheses around the included column.

The INCLUDE clause adds 'first_name' as a non-key column to the index for covering queries.

3fill in blank

hard

Fix the error in the index creation by completing the INCLUDE clause with the correct column.

PostgreSQL

CREATE INDEX idx_salary_include ON employees(salary) INCLUDE ([1]);

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

Alast_name

Bsalary

Cemployee_id

Ddepartment

Attempts:

3 left

💡 Hint

Common Mistakes

Including the same column as the indexed key column.

Choosing a column that does not help cover queries.

The INCLUDE clause should have a column different from the indexed key column. 'employee_id' is a good choice to cover queries.

4fill in blank

hard

Fill both blanks to create an index on 'department' and include 'salary' and 'hire_date' columns.

PostgreSQL

CREATE INDEX idx_dept_include ON employees([1]) INCLUDE ([2]);

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

Adepartment

Bsalary, hire_date

Csalary

Dhire_date

Attempts:

3 left

💡 Hint

Common Mistakes

Putting multiple columns in the index key instead of INCLUDE.

Not separating included columns with commas.

The index is on 'department' and includes both 'salary' and 'hire_date' columns to cover queries.

5fill in blank

hard

Fill all three blanks to create an index on 'last_name' and include 'first_name' and 'email' columns.

PostgreSQL

CREATE INDEX idx_full_include ON employees([1]) INCLUDE ([2], [3]);

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

Alast_name

Bfirst_name

Cemail

Ddepartment

Attempts:

3 left

💡 Hint

Common Mistakes

Including the indexed column in the INCLUDE clause.

Forgetting commas between included columns.

The index is on 'last_name' and includes 'first_name' and 'email' to cover queries efficiently.