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.