CREATE

Create a materialized view.

CREATE

With CREATE MATERIALIZED VIEW you physically store a summary result.

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT sale_date, SUM(amount) AS revenue
FROM sales GROUP BY sale_date;

This query stores the daily revenue summary.

The materialized view daily_revenue is created. sale_date, SUM(amount) AS revenue is selected from sales. Rows are grouped by sale_date; aggregate functions are computed per group.

CREATE — details

When created, the query runs and the result is stored. It can later be queried like a table.

Even if the underlying tables change later, the stored result does not; so you make a deliberate trade-off between freshness and speed.

Exercise CREATE

Create the daily_revenue materialized view with daily revenue.

Show example solution

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT sale_date, SUM(amount) AS revenue
FROM sales GROUP BY sale_date;

Test yourself

Add AS

CREATE MATERIALIZED VIEW daily_revenue ____
SELECT sale_date, SUM(amount) AS revenue FROM sales GROUP BY sale_date;

Show answer

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT sale_date, SUM(amount) AS revenue FROM sales GROUP BY sale_date;

How is a materialized view queried?
- Like a view
- With a procedure
- Like a table
- With a trigger
Show answer

Like a table — A materialized view is queried like a table.
CREATE: match the terms

CREATE

create
MATERIALIZED

physical
AS

define
GROUP BY

group

Next lesson: REFRESH

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies