Instruction
Now, let's move on to materialized views. Materialized views are not present in the SQL standard; however, they're available in some popular database engines (including PostgreSQL and Oracle), so it's a good idea to know what they are.
We previously told you that a standard view runs the underlying query each time you retrieve information from that view. This is not the case with materialized views. When you create a materialized view, the underlying query is run once and the resulting rows are physically stored in the database.
Creating a materialized view in PostgreSQL is quite easy. If we have a table like this:
CREATE TABLE university ( id integer PRIMARY KEY, name varchar(64), country varchar(64), city varchar(64), type varchar(32), student_count integer, rank integer );
We can now create a materialized view in the following way:
CREATE MATERIALIZED VIEW top100_universities AS SELECT * FROM university WHERE rank <= 100;
As you can see, instead of CREATE VIEW we simply use CREATE MATERIALIZED VIEW.
Exercise
A grocery store has the following table recording the products it currently offers or has offered:
CREATE TABLE product ( id integer PRIMARY KEY, name varchar(64), category varchar(64), price decimal(5, 2), is_removed boolean );
Create a materialized view named available_dairy_products, which will select the columns name and price for all products which have not been removed and come from the category 'dairy'.
Stuck? Here's a hint!
Start with:
CREATE MATERIALIZED VIEW
