CREATE MATERIALIZED VIEW in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a materialized view named 'sales_summary' that selects all columns from the 'sales' table.

PostgreSQL

CREATE [1] MATERIALIZED VIEW sales_summary AS SELECT * FROM sales;

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

AMATERIALIZED

BVIEW

CTABLE

DINDEX

Attempts:

3 left

💡 Hint

Common Mistakes

Using just VIEW instead of MATERIALIZED VIEW.

Using TABLE keyword which is for tables, not views.

The keyword MATERIALIZED is required to create a materialized view in PostgreSQL.

2fill in blank

medium

Complete 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'

AVIEW

BTABLE

CMATERIALIZED VIEW

DINDEX

Attempts:

3 left

💡 Hint

Common Mistakes

Using REFRESH VIEW which is invalid for materialized views.

Using REFRESH TABLE which is not correct syntax.

To refresh a materialized view, use REFRESH MATERIALIZED VIEW followed by the view name.

3fill in blank

hard

Fix the error in the code to create a materialized view that selects the total sales per product from the 'sales' table.

PostgreSQL

CREATE MATERIALIZED VIEW total_sales AS SELECT product_id, SUM(amount) [1] sales GROUP BY product_id;

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

AWHERE

BFROM

CON

DIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using WHERE instead of FROM before the table name.

Using ON or IN which are not correct here.

The FROM keyword is required to specify the source table in a SELECT statement.

4fill in blank

hard

Fill both blanks to create a materialized view named 'top_customers' that selects customer_id and total spent from 'orders' where total spent is greater than 1000.

PostgreSQL

CREATE MATERIALIZED VIEW top_customers AS SELECT customer_id, SUM(amount) [1] orders GROUP BY customer_id [2] SUM(amount) > 1000;

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

AFROM

BWHERE

CHAVING

DON

Attempts:

3 left

💡 Hint

Common Mistakes

Using WHERE instead of HAVING to filter aggregated sums.

Using ON which is for joins, not filtering.

The FROM keyword specifies the table, and HAVING filters groups after aggregation.

5fill in blank

hard

Fill all three blanks to create a materialized view named 'recent_orders' that selects order_id, customer_id, and order_date from 'orders' where order_date is within the last 30 days.

PostgreSQL

CREATE MATERIALIZED VIEW recent_orders AS SELECT [1], [2], [3] FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';

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

Aorder_id

Bcustomer_id

Corder_date

Damount

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting columns not related to the order identity or date.

Missing one or more required columns.

The selected columns are order_id, customer_id, and order_date to show recent orders.