Materialized view vs regular view decision in PostgreSQL - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a regular view named 'employee_view' that selects all columns from the 'employees' table.

PostgreSQL

CREATE [1] employee_view AS SELECT * FROM employees;

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

AINDEX

BTABLE

CVIEW

DMATERIALIZED VIEW

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'TABLE' instead of 'VIEW' to create a view.

Confusing materialized views with regular views.

A regular view is created using the CREATE VIEW statement. It does not store data physically but runs the query each time it is accessed.

2fill in blank

medium

Complete the code to create a materialized view named 'sales_summary' that stores the total sales per region from the 'sales' table.

PostgreSQL

CREATE [1] sales_summary AS SELECT region, SUM(amount) AS total_sales FROM sales GROUP BY region;

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

AMATERIALIZED VIEW

BTABLE

CVIEW

DINDEX

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'VIEW' instead of 'MATERIALIZED VIEW' when data storage is needed.

Forgetting that materialized views need manual refresh.

A materialized view stores the result of the query physically. Use CREATE MATERIALIZED VIEW to create it.

3fill in blank

hard

Fix the error in the code to refresh the materialized view named 'sales_summary'.

PostgreSQL

REFRESH [1] sales_summary;

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

ATABLE

BINDEX

CVIEW

DMATERIALIZED VIEW

Attempts:

3 left

💡 Hint

Common Mistakes

Using REFRESH VIEW instead of REFRESH MATERIALIZED VIEW.

Trying to refresh a regular view which is not allowed.

To update a materialized view with fresh data, use REFRESH MATERIALIZED VIEW. Regular views do not need refreshing.

4fill in blank

hard

Fill both blanks to create a materialized view named 'product_stats' that selects product_id and average price from 'products' table grouped by product_id.

PostgreSQL

CREATE [1] product_stats AS SELECT product_id, AVG(price) AS avg_price FROM products GROUP BY [2];

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

AMATERIALIZED VIEW

BVIEW

Cproduct_id

Dprice

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'VIEW' instead of 'MATERIALIZED VIEW' for the first blank.

Grouping by 'price' instead of 'product_id'.

The first blank must be 'MATERIALIZED VIEW' to store data physically. The second blank is the column to group by, which is 'product_id'.

5fill in blank

hard

Fill all three blanks to create a regular view named 'customer_orders' that selects customer_id, order_id, and order_date from 'orders' table where order_date is after '2023-01-01'.

PostgreSQL

CREATE [1] customer_orders AS SELECT [2], [3], order_date FROM orders WHERE order_date > '2023-01-01';

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

AVIEW

Bcustomer_id

Corder_id

DMATERIALIZED VIEW

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'MATERIALIZED VIEW' instead of 'VIEW' for the first blank.

Selecting wrong columns like 'order_date' twice.

The first blank is 'VIEW' to create a regular view. The second and third blanks are the columns 'customer_id' and 'order_id' to select.

Practice

1. Which statement best describes a regular view in PostgreSQL?

easy

A. It stores data physically and requires manual refresh.

B. It cannot be used in SELECT statements.

C. It runs the underlying query every time it is accessed.

D. It automatically updates data without running queries.

Solution

1. Step 1: Understand what a regular view does

   A regular view is a saved SQL query that runs fresh every time you use it.

2. Step 2: Compare with other options

   Storing data physically and requiring manual refresh describes a materialized view; saying it cannot be used in SELECT statements is false because views can be queried directly; automatically updating data without running queries is incorrect for regular views.

3. Final Answer:

   It runs the underlying query every time it is accessed. -> Option C

4. Quick Check:

   Regular view runs query each time [OK]

Hint: Regular views always run queries fresh on access [OK]

Common Mistakes:

• Confusing materialized views with regular views
• Thinking views store data physically
• Believing views auto-refresh data

2. Which of the following is the correct syntax to create a materialized view in PostgreSQL?

easy

A. CREATE MATERIALIZED VIEW my_mat_view AS SELECT * FROM sales;

B. CREATE VIEW my_view AS SELECT * FROM sales;

C. CREATE TABLE my_mat_view AS SELECT * FROM sales;

D. CREATE TEMP VIEW my_mat_view AS SELECT * FROM sales;

Solution

1. Step 1: Identify syntax for materialized view

   The correct syntax uses CREATE MATERIALIZED VIEW followed by the view name and query.

2. Step 2: Eliminate incorrect options

   CREATE VIEW my_view AS SELECT * FROM sales; creates a regular view, CREATE TABLE my_mat_view AS SELECT * FROM sales; creates a table, CREATE TEMP VIEW my_mat_view AS SELECT * FROM sales; creates a temporary view, not materialized.

3. Final Answer:

   CREATE MATERIALIZED VIEW my_mat_view AS SELECT * FROM sales; -> Option A

4. Quick Check:

   Materialized view syntax includes 'MATERIALIZED' keyword [OK]

Hint: Materialized views use 'CREATE MATERIALIZED VIEW' syntax [OK]

Common Mistakes:

• Using CREATE VIEW instead of CREATE MATERIALIZED VIEW
• Confusing tables with materialized views
• Using TEMP keyword incorrectly

3. Given a materialized view created as:
CREATE MATERIALIZED VIEW sales_summary AS SELECT region, SUM(amount) AS total FROM sales GROUP BY region;
What will be the result of:
SELECT * FROM sales_summary;
immediately after creation?

medium

A. The aggregated total sales per region based on current data.

B. An error because materialized views need manual refresh first.

C. Empty result set because no data is stored yet.

D. The detailed sales rows without aggregation.

Solution

1. Step 1: Understand materialized view creation behavior

   When created, a materialized view stores the query result immediately.

2. Step 2: Determine output of SELECT from materialized view

   It returns the aggregated totals per region as per the query at creation time.

3. Final Answer:

   The aggregated total sales per region based on current data. -> Option A

4. Quick Check:

   Materialized view stores query result on creation [OK]

Hint: Materialized views store data on creation, so SELECT returns stored results [OK]

Common Mistakes:

• Thinking materialized views are empty until refreshed
• Expecting detailed rows instead of aggregated results
• Assuming errors occur without manual refresh

4. You created a materialized view but notice it shows outdated data after the underlying table changes. What is the best way to fix this?

medium

A. Use SELECT * FROM materialized_view to refresh automatically.

B. Drop and recreate the materialized view every time.

C. Restart the PostgreSQL server to refresh views.

D. Run REFRESH MATERIALIZED VIEW to update the data.

Solution

1. Step 1: Identify how to update materialized view data

   Materialized views do not auto-update; REFRESH MATERIALIZED VIEW command updates stored data.

2. Step 2: Evaluate other options

   Dropping and recreating the materialized view every time is inefficient, using SELECT * FROM materialized_view does not refresh it, restarting the PostgreSQL server is unrelated.

3. Final Answer:

   Run REFRESH MATERIALIZED VIEW to update the data. -> Option D

4. Quick Check:

   Use REFRESH MATERIALIZED VIEW to update data [OK]

Hint: Use REFRESH MATERIALIZED VIEW to update stored data [OK]

Common Mistakes:

• Expecting SELECT to refresh data automatically
• Dropping and recreating instead of refreshing
• Restarting server to fix view data

5. You have a large sales table updated hourly. You want fast reports that summarize sales by region but can tolerate data up to 1 hour old. Which is the best choice?

hard

A. Use a regular view to always get fresh data.

B. Use a materialized view refreshed every hour for faster queries.

C. Create a table with triggers to update summaries instantly.

D. Use a temporary view refreshed on each query.

Solution

1. Step 1: Analyze requirements for speed and data freshness

   Reports need fast access and can accept data up to 1 hour old.

2. Step 2: Match options to requirements

   Materialized views store results and can be refreshed hourly, balancing speed and freshness. Regular views run slow on large data. Triggers add complexity. Temporary views do not persist.

3. Final Answer:

   Use a materialized view refreshed every hour for faster queries. -> Option B

4. Quick Check:

   Materialized view + hourly refresh = best for speed and acceptable freshness [OK]

Hint: Materialized views with scheduled refresh balance speed and freshness [OK]

Common Mistakes:

• Choosing regular views despite slow queries
• Using triggers unnecessarily complex
• Confusing temporary views with materialized views