Materialized views concept 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 sales_summary AS SELECT [1] FROM sales;

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

A*

BDISTINCT

CALL

DUNIQUE

Attempts:

3 left

💡 Hint

Common Mistakes

Using DISTINCT or UNIQUE here will cause syntax errors in this context.

Forgetting to specify columns or using incorrect keywords.

The * selects all columns from the sales table to create the materialized view.

2fill in blank

medium

Complete the code to refresh the materialized view 'sales_summary'.

PostgreSQL

REFRESH MATERIALIZED VIEW [1];

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

Asales_summary

Bsales

Csummary_sales

Dsales_view

Attempts:

3 left

💡 Hint

Common Mistakes

Trying to refresh the base table instead of the materialized view.

Using a wrong or misspelled materialized view name.

You refresh the materialized view by its exact name, which is sales_summary.

3fill in blank

hard

Fix the error in the code to create a materialized view with a unique index.

PostgreSQL

CREATE MATERIALIZED VIEW sales_summary AS SELECT customer_id, SUM(amount) AS total FROM sales GROUP BY [1];

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

Aamount

Bsales_id

Ccustomer_id

Dtotal

Attempts:

3 left

💡 Hint

Common Mistakes

Grouping by the aggregated column total which is invalid.

Grouping by unrelated columns like amount or sales_id.

The GROUP BY clause must include the column customer_id because it is selected and aggregated by SUM(amount).

4fill in blank

hard

Fill both blanks to create a unique index on the materialized view 'sales_summary'.

PostgreSQL

CREATE UNIQUE INDEX [1] ON sales_summary([2]);

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

Asales_summary_idx

Bsales_idx

Ccustomer_id

Dtotal

Attempts:

3 left

💡 Hint

Common Mistakes

Using a non-unique column like total for the unique index.

Using an index name that does not relate to the materialized view.

The unique index is named sales_summary_idx and is created on the customer_id column to ensure uniqueness.

5fill in blank

hard

Fill all three blanks to create and refresh a materialized view with data.

PostgreSQL

CREATE MATERIALIZED VIEW [1] AS SELECT [2] FROM sales WHERE amount [3] 100;

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

Ahigh_value_sales

B*

C>

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using < instead of > changes the filter logic.

Selecting specific columns instead of all columns when the task asks for all.

The materialized view high_value_sales selects all columns * from sales where amount is greater than 100.