Indexing materialized views in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a materialized view named sales_summary.

PostgreSQL

CREATE MATERIALIZED VIEW [1] AS SELECT product_id, SUM(quantity) AS total_qty FROM sales GROUP BY product_id;

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

Asales_summary

Bsummary_sales

Csales_view

Dsummary_view

Attempts:

3 left

💡 Hint

Common Mistakes

Using a regular view instead of a materialized view.

Choosing a name that does not reflect the content.

The materialized view is named sales_summary as specified.

2fill in blank

medium

Complete the code to create an index on the product_id column of the materialized view.

PostgreSQL

CREATE INDEX idx_[1] ON sales_summary (product_id);

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

Aproduct

Bproduct_id

Csummary

Dsales

Attempts:

3 left

💡 Hint

Common Mistakes

Using a generic index name that doesn't indicate the column.

Misspelling the column name.

The index is named idx_product_id to indicate it is on the product_id column.

3fill in blank

hard

Fix the error in the code to refresh the materialized view concurrently.

PostgreSQL

REFRESH MATERIALIZED VIEW [1] CONCURRENTLY;

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

Asummary_sales

Bsales_summary_view

Csales_summary

Dsales_view

Attempts:

3 left

💡 Hint

Common Mistakes

Using a wrong or incomplete materialized view name.

Omitting the CONCURRENTLY keyword when needed.

The materialized view name must match exactly. sales_summary is correct.

4fill in blank

hard

Fill both blanks to create a unique index on the materialized view for product_id and date columns.

PostgreSQL

CREATE [1] INDEX idx_sales_summary_[2] ON sales_summary (product_id, sales_date);

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

AUNIQUE

BCONCURRENTLY

Cproduct_date

Dsales_date

Attempts:

3 left

💡 Hint

Common Mistakes

Using non-unique index when uniqueness is required.

Naming the index unclearly.

The index is UNIQUE and named idx_sales_summary_product_date to reflect the columns.

5fill in blank

hard

Fill all three blanks to create a materialized view with data and an index on the customer_id column.

PostgreSQL

CREATE MATERIALIZED VIEW [1] AS SELECT customer_id, COUNT(order_id) AS order_count FROM orders GROUP BY [2] WITH DATA; CREATE INDEX [3] ON [1] (customer_id);

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

Acustomer_orders

Bcustomer_id

Cidx_customer_orders_customer_id

Dorders_summary

Attempts:

3 left

💡 Hint

Common Mistakes

Mismatching group by column and selected column.

Using inconsistent names for the view and index.

The materialized view is named customer_orders, grouped by customer_id, and indexed with idx_customer_orders_customer_id.