Partial indexes with WHERE clause in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a partial index on the "users" table for active users only.

PostgreSQL

CREATE INDEX idx_active_users ON users (last_login) WHERE [1];

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

Aage > 18

Bstatus = 'active'

Clast_login IS NOT NULL

Dcountry = 'US'

Attempts:

3 left

💡 Hint

Common Mistakes

Using a condition unrelated to active users.

Omitting the WHERE clause for partial index.

The partial index is created only for rows where the status is 'active'. This helps speed up queries filtering active users.

2fill in blank

medium

Complete the code to create a partial index on the "orders" table for orders with status 'pending'.

PostgreSQL

CREATE INDEX idx_pending_orders ON orders (order_date) WHERE [1];

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

Astatus = 'pending'

Bstatus = 'shipped'

Camount > 100

Dcustomer_id IS NOT NULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'shipped' instead of 'pending' in the WHERE clause.

Indexing all orders without a WHERE clause.

The partial index targets only orders where the status is 'pending', improving query speed for those orders.

3fill in blank

hard

Fix the error in the partial index creation for the "products" table to index only discontinued products.

PostgreSQL

CREATE INDEX idx_discontinued_products ON products (product_name) WHERE [1];

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

Adiscontinued = TRUE

Bdiscontinued = 'yes'

Cdiscontinued IS NULL

Ddiscontinued = FALSE

Attempts:

3 left

💡 Hint

Common Mistakes

Using string 'yes' instead of boolean TRUE.

Indexing products that are not discontinued.

The column 'discontinued' is a boolean, so the correct condition is 'discontinued = TRUE' to index only discontinued products.

4fill in blank

hard

Fill both blanks to create a partial index on the "employees" table for employees in the 'Sales' department with active status.

PostgreSQL

CREATE INDEX idx_active_sales ON employees (employee_id) WHERE [1] AND [2];

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

Adepartment = 'Sales'

Bstatus = 'active'

Cstatus = 'inactive'

Ddepartment = 'HR'

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'HR' instead of 'Sales' for department.

Using 'inactive' instead of 'active' for status.

The partial index filters employees who are in the Sales department and have an active status.

5fill in blank

hard

Fill all three blanks to create a partial index on the "tickets" table for open tickets assigned to user 'john_doe' with priority higher than 3.

PostgreSQL

CREATE INDEX idx_open_high_priority ON tickets (ticket_id) WHERE [1] AND [2] AND [3];

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

Astatus = 'open'

Bassigned_to = 'john_doe'

Cpriority > 3

Dpriority < 3

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'closed' instead of 'open' for status.

Using priority less than 3 instead of greater than 3.

Wrong assigned user name.

The partial index covers tickets that are open, assigned to 'john_doe', and have priority greater than 3.