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.

Practice

1. What is the main purpose of a materialized view in PostgreSQL?

easy

A. To store the result of a query physically for faster access

B. To create a temporary table that disappears after the session

C. To automatically update data in real-time without manual refresh

D. To define a view that does not store any data

Solution

1. Step 1: Understand what a materialized view does

   A materialized view stores the result of a query physically on disk, unlike a normal view which only stores the query definition.

2. Step 2: Compare options with this definition

   To store the result of a query physically for faster access matches this exactly. Options A, B, and C describe other concepts like temporary tables or normal views.

3. Final Answer:

   To store the result of a query physically for faster access -> Option A

4. Quick Check:

   Materialized view = stored query result [OK]

Hint: Materialized views store data physically, unlike normal views [OK]

Common Mistakes:

• Confusing materialized views with normal views
• Thinking materialized views auto-update without refresh
• Assuming materialized views are temporary

2. Which of the following is the correct syntax to create a materialized view named sales_summary from a table sales?

easy

A. CREATE MATERIALIZED TABLE sales_summary AS SELECT * FROM sales;

B. CREATE VIEW MATERIALIZED sales_summary AS SELECT * FROM sales;

C. CREATE MATERIALIZED VIEW sales_summary AS SELECT * FROM sales;

D. CREATE TABLE MATERIALIZED VIEW sales_summary AS SELECT * FROM sales;

Solution

1. Step 1: Recall the correct syntax for materialized views

   The correct syntax is: CREATE MATERIALIZED VIEW view_name AS SELECT ...

2. Step 2: Match options with correct syntax

   CREATE MATERIALIZED VIEW sales_summary AS SELECT * FROM sales; matches exactly. Options A, B, and D mix keywords incorrectly or use wrong object types.

3. Final Answer:

   CREATE MATERIALIZED VIEW sales_summary AS SELECT * FROM sales; -> Option C

4. Quick Check:

   CREATE MATERIALIZED VIEW = correct syntax [OK]

Hint: Remember: CREATE MATERIALIZED VIEW, not CREATE VIEW MATERIALIZED [OK]

Common Mistakes:

• Swapping keywords 'VIEW' and 'MATERIALIZED'
• Using CREATE MATERIALIZED TABLE instead of VIEW
• Adding TABLE keyword incorrectly

3. Given the materialized view mv_products created as CREATE MATERIALIZED VIEW mv_products AS SELECT id, price FROM products;, what will be the result of this query immediately after inserting a new product into products but before refreshing mv_products?

SELECT * FROM mv_products WHERE id = 101;

medium

A. The new product with id 101 will appear in the result

B. The materialized view will automatically refresh and show the new product

C. Query will cause a syntax error

D. No rows will be returned for id 101

Solution

1. Step 1: Understand materialized view data update behavior

   Materialized views do not update automatically when base tables change. They require manual refresh.

2. Step 2: Analyze query result after insert but before refresh

   The new product with id 101 is not yet in the materialized view, so the query returns no rows for id 101.

3. Final Answer:

   No rows will be returned for id 101 -> Option D

4. Quick Check:

   Materialized view data = stale until refresh [OK]

Hint: Materialized views need manual refresh to show new data [OK]

Common Mistakes:

• Assuming materialized views auto-update on insert
• Expecting syntax error from a valid SELECT
• Confusing materialized views with normal views

4. You have a materialized view mv_orders created as CREATE MATERIALIZED VIEW mv_orders AS SELECT * FROM orders;. You try to refresh it using REFRESH MATERIALIZED VIEW mv_orders; but get an error: ERROR: permission denied for relation orders. What is the most likely cause?

medium

A. The user running the refresh lacks SELECT permission on the base table orders

B. The materialized view name is misspelled

C. REFRESH MATERIALIZED VIEW syntax is incorrect

D. Materialized views cannot be refreshed after creation

Solution

1. Step 1: Understand permission requirements for refreshing

   Refreshing a materialized view requires SELECT permission on the underlying base tables.

2. Step 2: Analyze the error message

   The error says permission denied on relation orders, indicating the user lacks SELECT rights on that table.

3. Final Answer:

   The user running the refresh lacks SELECT permission on the base table orders -> Option A

4. Quick Check:

   Permission denied = missing SELECT rights [OK]

Hint: Check base table permissions if refresh gives permission denied [OK]

Common Mistakes:

• Assuming syntax error instead of permission issue
• Ignoring user privileges on base tables
• Thinking materialized views cannot be refreshed

5. You want to speed up a complex report query that aggregates sales data by month. You create a materialized view monthly_sales with the aggregation. However, the sales data updates daily. Which approach best keeps monthly_sales up to date with minimal performance impact?

hard

A. Manually drop and recreate the materialized view after each data change

B. Schedule a nightly REFRESH MATERIALIZED VIEW to update the data once per day

C. Use a normal view instead of a materialized view for real-time data

D. Set the materialized view to auto-refresh on every query

Solution

1. Step 1: Consider update frequency and performance

   Since sales data updates daily, refreshing the materialized view nightly balances freshness and performance.

2. Step 2: Evaluate other options

   Dropping and recreating is costly and manual. Normal views are slow for complex queries. Auto-refresh on every query is not supported and would hurt performance.

3. Final Answer:

   Schedule a nightly REFRESH MATERIALIZED VIEW to update the data once per day -> Option B

4. Quick Check:

   Nightly refresh = best balance for daily updates [OK]

Hint: Use scheduled refresh for daily updated materialized views [OK]

Common Mistakes:

• Trying to auto-refresh materialized views on every query
• Dropping and recreating instead of refreshing
• Using normal views for heavy aggregation queries