Build a Materialized Snapshot of Employee Data

Create a Simple Materialized View

Write a PostgreSQL query to create a materialized view that selects specific columns from the Employees table.

Solution:

-- Create a materialized view to store a snapshot of employee IDs, names, and departments.
CREATE MATERIALIZED VIEW EmployeeSummary AS
SELECT employee_id, name, department
FROM Employees;

Explanation:

• Purpose of the Query:
• The goal is to create a materialized view that holds a snapshot of key employee data.
• This demonstrates how to use the CREATE MATERIALIZED VIEW statement to store query results physically.
• Key Components:
• CREATE MATERIALIZED VIEW EmployeeSummary AS : Defines the new materialized view.
• SELECT employee_id, name, department FROM Employees : Specifies the data to be stored in the view.
• Real-World Application:
• Useful for reporting purposes where data does not change frequently and quick read performance is required.

Notes:

• Materialized views must be refreshed manually to reflect changes in the underlying table.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to create a materialized view that selects employee_id, name, and hire_date from the Employees table, ensuring duplicate rows are eliminated.
• Write a PostgreSQL query to create a materialized view that extracts the first 100 records from the Orders table with specific columns.
• Write a PostgreSQL query to create a materialized view that lists distinct department names from the Departments table.
• Write a PostgreSQL query to create a materialized view that captures product_id and product_description from the Products table with a filter to remove null descriptions.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous PostgreSQL Exercise: Creating and refreshing materialized views Home.
Next PostgreSQL Exercise: Create a Materialized View with Aggregation.