Views

Introduction

A view is a stored named query — it behaves like a virtual table. Views encapsulate joins/aggregations behind a clean interface and can hide complexity from app code.

Understanding the topic

Core concepts to understand:

• CREATE VIEW user_stats AS SELECT ...;
• Query a view like a table.
• Updates may be allowed for simple views.
• Materialized views store the result physically — refresh on schedule.
• Views simplify access control: grant SELECT only on the view.

Syntax reference

Visual workflow / architecture:

CREATE VIEW user_stats AS
SELECT u.id, u.email,
       COUNT(o.id) AS orders,
       SUM(o.amount) AS lifetime_value
FROM   users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP  BY u.id;

SELECT * FROM user_stats WHERE lifetime_value > 1000;

Real-world use

BI tools, BFF APIs and reporting layers expose views to keep the SQL logic in one place. Materialized views power slow dashboards refreshed every 5 minutes.

Best practices

• Use views to encapsulate complex SQL.
• Use materialized views for expensive read-heavy queries.
• Document refresh cadence and freshness expectations.

Common mistakes

• Views over views over views — perf hard to reason about.
• Stale materialized views breaking dashboards.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. View vs materialized view.
• Q2. Are views always read-only?
• Q3. How to refresh a materialized view.
• Q4. Pros and cons of views.
• Q5. Stack of views — performance concern?