B-tree index (default) behavior in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a B-tree index on the column 'username' in the 'users' table.

PostgreSQL

CREATE INDEX idx_username ON users ([1]);

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

Aemail

Buser_id

Cusername

Dcreated_at

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing a column not related to the query filter.

Using a column that does not exist in the table.

The B-tree index is created on the 'username' column to speed up queries filtering by username.

2fill in blank

medium

Complete the code to create a B-tree index that sorts the 'created_at' column in descending order.

PostgreSQL

CREATE INDEX idx_created_desc ON orders ([1] DESC);

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

Aorder_id

Bcreated_at

Ccustomer_id

Dstatus

Attempts:

3 left

💡 Hint

Common Mistakes

Using a column unrelated to date or time.

Forgetting to specify DESC for descending order.

The index is created on 'created_at' column with descending order to optimize queries sorting by newest orders first.

3fill in blank

hard

Fix the error in the index creation statement to use the default B-tree index on the 'email' column.

PostgreSQL

CREATE INDEX idx_email ON customers ([1]);

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

Aemail

Bemail DESC

Cemail ASC NULLS LAST

Demail NULLS FIRST

Attempts:

3 left

💡 Hint

Common Mistakes

Adding DESC or NULLS clauses unnecessarily.

Using unsupported syntax for B-tree index.

Using just the column name 'email' creates a default B-tree index with ascending order and default NULLS placement.

4fill in blank

hard

Fill both blanks to create a B-tree index on 'last_name' and 'first_name' columns for sorting by last name ascending and first name descending.

PostgreSQL

CREATE INDEX idx_name ON employees ([1] ASC, [2] DESC);

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

Alast_name

Bfirst_name

Cmiddle_name

Demployee_id

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the order of columns.

Using unrelated columns for the index.

The index sorts by 'last_name' ascending and 'first_name' descending to optimize queries ordering by full name.

5fill in blank

hard

Fill all three blanks to create a B-tree index on 'category', 'price' ascending, and 'stock' descending columns.

PostgreSQL

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

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

Acategory

Bprice

Cstock

Dproduct_id

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up the order or sort directions.

Including unrelated columns.

This index helps queries filtering by category and sorting by price ascending and stock descending.