SovranCode
SQL: Query, Model, and Analyze Data Views and materialized views
This device
Course contentsViews and materialized views · 32 topics

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsSelf joins and many-to-many dataSubqueries and correlated subqueriesUNION, INTERSECT, and EXCEPT

Designing reliable schemas

CREATE TABLE and schema designConstraints and data integrityNormalization and intentional denormalizationViews and materialized views

Writing and protecting data

INSERT, UPDATE, and DELETEUpserts and conflict handlingTransactions and savepointsIsolation, locks, and concurrency

Performance and administration

Indexes and access pathsEXPLAIN and query plansQuery tuning patternsBackups, restores, and migrations

Security and production workflow

Users, roles, and least privilegePreventing SQL injectionStored procedures, functions, and triggersProduction data project
SQL: Query, Model, and Analyze Data32 complete · 0 planned

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsSelf joins and many-to-many dataSubqueries and correlated subqueriesUNION, INTERSECT, and EXCEPT

Designing reliable schemas

CREATE TABLE and schema designConstraints and data integrityNormalization and intentional denormalizationViews and materialized views

Writing and protecting data

INSERT, UPDATE, and DELETEUpserts and conflict handlingTransactions and savepointsIsolation, locks, and concurrency

Performance and administration

Indexes and access pathsEXPLAIN and query plansQuery tuning patternsBackups, restores, and migrations

Security and production workflow

Users, roles, and least privilegePreventing SQL injectionStored procedures, functions, and triggersProduction data project
PREVIOUS LESSONNormalization and intentional denormalization
NEXT LESSONINSERT, UPDATE, and DELETE
Designing reliable schemas · Lesson 20 155 min

Views and materialized views

A view is a named query you can read like a table. A materialized view is a stored copy of that query's result. Choose the live interface when truth must stay current; choose the stored copy when a heavy summary can be a little stale.

Two different products, one family of names

People say “view” for both ideas. Keep them separate. An ordinary view recomputes. A materialized view, or any snapshot table, must be refreshed. This lesson's runner uses SQLite, which supports CREATE VIEW and not CREATE MATERIALIZED VIEW. The snapshot examples show the same maintenance problem PostgreSQL's materialized views solve.

A view is a saved SELECT

Once a filter or join is reviewed, you should not copy it into every report. CREATE VIEW stores the query in the schema. Consumers SELECT from the view name. They do not have to remember is_active = 1, and they cannot accidentally forget it.

The view below is a catalog interface: active products only, with the columns a storefront needs. The unpublished poster is present in products and absent from active_products.

SQL BROWSER RUNNER

Create a view of active products

Save a filter as a named interface, then read it like a table.

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  price_cents INTEGER NOT NULL,
  is_active INTEGER NOT NULL
);

INSERT INTO products VALUES
  (1, 'SQL notebook', 1499, 1),
  (2, 'Schema poster', 899, 0),
  (3, 'Database sticker', 299, 1);

CREATE VIEW active_products AS
SELECT product_id, product_name, price_cents
FROM products
WHERE is_active = 1;

SELECT product_id, product_name, price_cents
FROM active_products
ORDER BY product_name;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: How many rows does the view return, and which product is hidden even though it still exists in products?

Ordinary views follow live tables

A view does not keep its own rows. When the poster is published, the next SELECT from active_products includes it. There is no refresh step. That is the point: the interface always means “active products now.”

SQL BROWSER RUNNER

Change a base table and reread the view

Publish the poster, then confirm the view picked up the change.

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  is_active INTEGER NOT NULL
);

INSERT INTO products VALUES
  (1, 'SQL notebook', 1),
  (2, 'Schema poster', 0);

CREATE VIEW active_products AS
SELECT product_id, product_name
FROM products
WHERE is_active = 1;

UPDATE products SET is_active = 1 WHERE product_id = 2;

SELECT product_id, product_name
FROM active_products
ORDER BY product_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Did you UPDATE the view or the table? Why does the view show two rows afterward?

Name the columns you are willing to support

Avoid SELECT * in a view. A later column on the base table would change the view's shape and break consumers. List stable names, and alias aggregates so the interface reads like a product: paid_order_count, not COUNT(*).

Views can hide a join

A reporting view often joins several normalized tables and returns one card-shaped row. The consumer asks for paid order cards; the view owns the join and the paid filter. That is how a normalized model stays clean while screens stay simple.

SQL BROWSER RUNNER

Publish a paid-order card view

Join customers to orders once, then query the named result.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  amount_cents INTEGER NOT NULL
);

INSERT INTO customers VALUES (101, 'Amina Idrissi'), (102, 'Bilal Karim');
INSERT INTO orders VALUES
  (5001, 101, 'paid', 1499),
  (5002, 101, 'pending', 899),
  (5003, 102, 'paid', 299);

CREATE VIEW paid_order_cards AS
SELECT
  customer.customer_name,
  sale.order_id,
  sale.amount_cents
FROM customers AS customer
JOIN orders AS sale ON sale.customer_id = customer.customer_id
WHERE sale.status = 'paid';

SELECT customer_name, order_id, amount_cents
FROM paid_order_cards
ORDER BY order_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Why is Amina's pending order absent, and which table would you change to make it appear?

A view can be an aggregate interface

Grouped results are awkward to copy. A view that returns one row per customer with paid-order counts becomes a stable source for dashboards. The grouping still happens on every read. If that becomes expensive, you are looking at a snapshot problem, not a naming problem.

SQL BROWSER RUNNER

Name paid totals per customer

Store an aggregate query as a view with explicit result columns.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount_cents INTEGER NOT NULL,
  status TEXT NOT NULL
);

INSERT INTO orders VALUES
  (5001, 101, 1499, 'paid'),
  (5002, 101, 899, 'pending'),
  (5003, 102, 299, 'paid');

CREATE VIEW customer_paid_totals AS
SELECT
  customer_id,
  COUNT(*) AS paid_order_count,
  SUM(amount_cents) AS paid_total_cents
FROM orders
WHERE status = 'paid'
GROUP BY customer_id;

SELECT customer_id, paid_order_count, paid_total_cents
FROM customer_paid_totals
ORDER BY customer_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Add a paid order for customer 102. Predict the new paid_order_count before you change the INSERT list.

Inspect what the schema stored

In this SQLite runner, sqlite_master lists tables and views. A view is a schema object, not an application-only nickname. Other databases expose views through information schema or catalog views. The important check is the same: is this a stored query, or a stored result?

SQL BROWSER RUNNER

List tables and views in the catalog

Create one table and one view, then read SQLite's schema list.

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  is_active INTEGER NOT NULL
);

INSERT INTO products VALUES (1, 'SQL notebook', 1);

CREATE VIEW active_products AS
SELECT product_id, product_name
FROM products
WHERE is_active = 1;

SELECT name, type
FROM sqlite_master
WHERE type IN ('table', 'view')
ORDER BY type, name;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Which object is type 'view', and why is active_products missing from the table list?

Treat most views as read interfaces

A single-table view with a simple filter is sometimes updatable. A join or aggregate view usually is not, unless the database documents triggers or rules that define the write. Do not INSERT into a view because it looks like a table. Write the base tables, or add an explicit write path.

A materialized view stores a result

PostgreSQL's CREATE MATERIALIZED VIEW keeps the query output on disk. Reads can skip a heavy join. The cost is freshness: after base tables change, REFRESH MATERIALIZED VIEW (or an equivalent job) must run. Until then, the snapshot can lie.

SQLite has no native MATERIALIZED VIEW syntax. The next example uses an ordinary table filled with INSERT ... SELECT. The maintenance question is identical: who rebuilds it, and how stale may it be?

SQL BROWSER RUNNER

Watch a snapshot fall behind live data

Store paid totals, add another paid order, then compare snapshot and live sums.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  status TEXT NOT NULL,
  amount_cents INTEGER NOT NULL
);

INSERT INTO orders VALUES
  (5001, 'paid', 1499),
  (5002, 'paid', 299);

CREATE TABLE paid_totals_snapshot (
  paid_order_count INTEGER NOT NULL,
  paid_total_cents INTEGER NOT NULL,
  refreshed_at TEXT NOT NULL
);

INSERT INTO paid_totals_snapshot
SELECT COUNT(*), SUM(amount_cents), '2026-10-01 09:00'
FROM orders
WHERE status = 'paid';

INSERT INTO orders VALUES (5003, 'paid', 500);

SELECT snapshot.paid_order_count AS snapshot_count,
  snapshot.paid_total_cents AS snapshot_total,
  (SELECT COUNT(*) FROM orders WHERE status = 'paid') AS live_count,
  (SELECT SUM(amount_cents) FROM orders WHERE status = 'paid') AS live_total
FROM paid_totals_snapshot AS snapshot;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Why is snapshot_count still 2 after the third paid order exists in orders?

SQL BROWSER RUNNER

Refresh the snapshot on purpose

Clear the snapshot table and rebuild it from the live paid orders.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  status TEXT NOT NULL,
  amount_cents INTEGER NOT NULL
);

INSERT INTO orders VALUES
  (5001, 'paid', 1499),
  (5002, 'paid', 299),
  (5003, 'paid', 500);

CREATE TABLE paid_totals_snapshot (
  paid_order_count INTEGER NOT NULL,
  paid_total_cents INTEGER NOT NULL,
  refreshed_at TEXT NOT NULL
);

INSERT INTO paid_totals_snapshot
SELECT COUNT(*), SUM(amount_cents), '2026-10-01 09:00'
FROM orders
WHERE status = 'paid';

DELETE FROM paid_totals_snapshot;
INSERT INTO paid_totals_snapshot
SELECT COUNT(*), SUM(amount_cents), '2026-10-01 10:00'
FROM orders
WHERE status = 'paid';

SELECT paid_order_count, paid_total_cents, refreshed_at
FROM paid_totals_snapshot;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Which timestamp proves the rebuild happened, and what would go wrong if two jobs refreshed at once without a transaction?

Choose on purpose

Use an ordinary view when readers need current rows and the query is cheap enough. Use a stored snapshot when a summary is read constantly, rebuilt on a known schedule, and allowed to lag by minutes or hours. If you cannot name the refresher, do not store the copy.

Review a view before you ship it

  1. Write the one-row meaning of the view in a sentence.
  2. List explicit column names; do not use SELECT *.
  3. Decide whether the result must be live or may be stale.
  4. If it is live, CREATE VIEW and query that name from applications.
  5. If it is stored, name the refresh job, the allowed lag, and the source query.
  6. Prove it: change a base row, then check whether the interface should follow or stay still.

Independent lab: a live catalog and a snapshot

Build a published-workshop card interface. The view joins workshops to registrations, keeps unpublished workshops out, and returns registration and seat counts. The snapshot table copies that result so a dashboard could read it without repeating the join.

After you run the starter, insert another registration for workshop 10 and compare published_workshop_cards with workshop_card_snapshot. The view should move; the snapshot should not until you rebuild it.

SQL BROWSER RUNNER

Ship a workshop card view and a copy

Create a live grouped view, copy it into a snapshot table, then reason about freshness.

CREATE TABLE workshops (
  workshop_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  city TEXT NOT NULL,
  is_published INTEGER NOT NULL
);

CREATE TABLE registrations (
  registration_id INTEGER PRIMARY KEY,
  workshop_id INTEGER NOT NULL,
  attendee_email TEXT NOT NULL,
  seats INTEGER NOT NULL
);

INSERT INTO workshops VALUES
  (10, 'SQL foundations', 'Casablanca', 1),
  (20, 'Schema design', 'Rabat', 1),
  (30, 'Draft internals', 'Tangier', 0);
INSERT INTO registrations VALUES
  (1, 10, 'amina@example.com', 1),
  (2, 10, 'bilal@example.com', 2),
  (3, 20, 'amina@example.com', 1);

CREATE VIEW published_workshop_cards AS
SELECT
  workshop.workshop_id,
  workshop.title,
  workshop.city,
  COUNT(registration.registration_id) AS registration_count,
  COALESCE(SUM(registration.seats), 0) AS seat_count
FROM workshops AS workshop
LEFT JOIN registrations AS registration
  ON registration.workshop_id = workshop.workshop_id
WHERE workshop.is_published = 1
GROUP BY workshop.workshop_id, workshop.title, workshop.city;

CREATE TABLE workshop_card_snapshot (
  workshop_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  city TEXT NOT NULL,
  registration_count INTEGER NOT NULL,
  seat_count INTEGER NOT NULL
);

INSERT INTO workshop_card_snapshot
SELECT workshop_id, title, city, registration_count, seat_count
FROM published_workshop_cards;

SELECT title, city, registration_count, seat_count
FROM published_workshop_cards
ORDER BY title;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Add a registration for workshop 10. Which interface changes immediately, and what SQL would refresh the snapshot?

Common mistakes to avoid

  • Copying the same join into five reports instead of naming one view.
  • Using SELECT * so a base-table change silently reshapes the interface.
  • Assuming a view stores rows, then wondering why it “refreshed itself.”
  • Writing into a join view without an explicit, documented write rule.
  • Creating a snapshot with no owner, schedule, or comparison against live totals.
  • Using a stale dashboard number as the only copy of a financial fact.

Lesson review

  • I can create a view as a named, reviewed SELECT.
  • I can explain why an ordinary view follows base-table changes.
  • I can hide a join or aggregate behind stable column names.
  • I can treat most views as read interfaces.
  • I can tell a live view from a stored snapshot that needs a refresh.
  • I can simulate a materialized view in SQLite with a snapshot table.
KNOWLEDGE CHECK

Check your view reasoning

Answer all ten questions, then revisit the example whose freshness or interface still feels unclear.

01What is a view, at its core?
02What happens when a base table changes under an ordinary view?
03Why use a view instead of repeating a join in every report?
04What should you assume about inserting into a multi-table view?
05How does a materialized view differ from an ordinary view?
06Why does this browser lesson simulate a materialized view with a table?
07What is the main risk of a stored snapshot?
08When is an ordinary view the better choice?
09When is a stored snapshot the better choice?
10What belongs in a view's SELECT list?
PREVIOUS LESSONNormalization and intentional denormalization
NEXT LESSONINSERT, UPDATE, and DELETE
ON THIS PAGENamed queriesLive viewsJoin viewsAggregate viewsSchema catalogMaterialized copiesReview planIndependent labCommon mistakesKnowledge check
Course contents