5.Mike's Daily Report, Automated

CREATE VIEW saves a query permanently under a name, queryable exactly like a real table. A view built from a JOIN and a GROUP BY like this one is usually called a complex view.

CREATE VIEW DailyRevenue AS
SELECT s.SaleDate, SUM(p.Price) AS Revenue
FROM Sales s
JOIN Products p ON s.ProductID = p.ProductID
GROUP BY s.SaleDate;

This is the exact JOIN-and-GROUP-BY logic from chapters 2 and 3, saved permanently under one name instead of retyped every morning.

SELECT * FROM DailyRevenue
ORDER BY SaleDate;

No JOIN, no GROUP BY, no mention of Sales or Products anywhere in this query — DailyRevenue quietly handles all of that underneath.

A view's underlying query can filter with WHERE just like any other SELECT. A single-table view with no JOIN or aggregation, like this one, is usually called a simple view — compare it to DailyRevenue above, a complex view.

CREATE VIEW CustomersWithBalance AS
SELECT CustomerID, Name, Phone, BalanceDue
FROM Customers
WHERE BalanceDue > 0;

Only the five customers who actually owe money show up here, permanently, without WHERE ever needing to be retyped.

CREATE VIEW PopularProducts AS
SELECT p.Name, COUNT(*) AS UnitsSold
FROM Sales s
JOIN Products p ON s.ProductID = p.ProductID
GROUP BY p.Name
HAVING COUNT(*) > 1;

The exact HAVING logic from chapter 3, saved permanently — only products that sold more than once ever appear in this view.

A view's underlying query can use a subquery too — this reuses the exact scalar subquery from the previous chapter.

CREATE VIEW CustomersAboveAverage AS
SELECT Name, BalanceDue
FROM Customers
WHERE BalanceDue > (SELECT AVG(BalanceDue) FROM Customers);

The average recalculates fresh every time this view is queried, using whatever balances exist at that moment — never the average from whenever the view was first created.

A view's underlying query can include a CTE too — this is the exact TopSeller logic from the previous chapter, now saved permanently.

CREATE VIEW TopSellerView AS
WITH ProductSales AS (
  SELECT p.Name, COUNT(*) AS UnitsSold
  FROM Sales s
  JOIN Products p ON s.ProductID = p.ProductID
  GROUP BY p.Name
)
SELECT Name FROM ProductSales ORDER BY UnitsSold DESC LIMIT 1;

Query this view any morning, and it always names whichever product is winning right now — never a name frozen from the day the view was created.

Unlike a CTE (only exists for one query), a VIEW persists in the schema for any future query to reuse.

CREATE VIEW BestSellers AS
SELECT p.Name, COUNT(*) AS UnitsSold
FROM Sales s
JOIN Products p ON s.ProductID = p.ProductID
GROUP BY p.Name;

Mike's two daily questions — revenue and best sellers — are now two permanent views, each queryable on its own.

A view can be built directly on top of another view — a nested view. Querying HighRevenueDays quietly runs DailyRevenue's own JOIN and GROUP BY underneath, two layers deep, then filters the result.

CREATE VIEW HighRevenueDays AS
SELECT * FROM DailyRevenue WHERE Revenue > 10;

Only two days — Aug 28th and Aug 29th — currently clear 10 in revenue.

A view can expose only the columns a consumer actually needs — this is security through views, one of a view's real practical advantages, alongside data abstraction (hiding the underlying JOIN/GROUP BY complexity from whoever queries it).

CREATE VIEW PublicCustomerDirectory AS
SELECT Name, Phone
FROM Customers;

Anyone querying PublicCustomerDirectory literally cannot see BalanceDue or Email — not because of a promise or a permission check, but because the view's own definition never selected those columns in the first place.

A view stores no data of its own — every query against it re-runs the underlying SELECT fresh.

INSERT INTO Sales (SaleID, CustomerID, ProductID, SaleDate)
VALUES (213, 103, 1, '2026-08-29');

SELECT * FROM DailyRevenue ORDER BY SaleDate;

Nothing about DailyRevenue was touched, but querying it again already includes this brand-new sale — a view is never looking at yesterday's snapshot.

A materialized view is a view that DOES store its results, unlike a normal view. SQLite has no native materialized view feature at all, so the closest simulation is a real table like this one — frozen the moment it's created, and never automatically refreshed.

CREATE TABLE DailyRevenueSnapshot AS
SELECT s.SaleDate, SUM(p.Price) AS Revenue
FROM Sales s
JOIN Products p ON s.ProductID = p.ProductID
GROUP BY s.SaleDate;

INSERT INTO Sales (SaleID, CustomerID, ProductID, SaleDate)
VALUES (214, 105, 6, '2026-08-29');

SELECT * FROM DailyRevenue ORDER BY SaleDate;
SELECT * FROM DailyRevenueSnapshot ORDER BY SaleDate;

After the new sale, DailyRevenue's Aug 29th total already moved. DailyRevenueSnapshot's Aug 29th total is stuck at whatever it was the instant CREATE TABLE ran — exactly the tradeoff a real materialized view makes: faster reads, staler data, until something explicitly refreshes it.

In most databases, a simple, single-table view like CustomersWithBalance is usually updatable — writes pass straight through to the real table. An aggregate view like BestSellers is essentially always non-updatable, since there's no sensible way to turn a change to one summary row back into changes on the individual rows underneath it. SQLite is stricter than most: every view is read-only by default, updatable-shaped or not, unless it has an INSTEAD OF trigger telling it exactly how to translate a write against the view into a real write against the underlying table.

CREATE TRIGGER UpdateCustomerBalance
INSTEAD OF UPDATE ON CustomersWithBalance
BEGIN
  UPDATE Customers
  SET BalanceDue = NEW.BalanceDue
  WHERE CustomerID = OLD.CustomerID;
END;

UPDATE CustomersWithBalance SET BalanceDue = 100 WHERE CustomerID = 101;

SELECT * FROM Customers WHERE CustomerID = 101;

Priya's real BalanceDue in Customers is now 100, changed entirely through a write against the view, not the table itself.

WITH CHECK OPTION ensures a write made through a view can't create a row that would violate the view's own WHERE condition — inserting a customer with no email through this view would be rejected outright, instead of silently succeeding and then vanishing from the view. Most databases (PostgreSQL, MySQL, Oracle) support it; SQLite does not, so this is reference syntax only, not something that runs in this Playground.

-- Not supported by SQLite -- shown as reference syntax only:
--
-- CREATE VIEW CustomersWithEmail AS
-- SELECT * FROM Customers
-- WHERE Email IS NOT NULL
-- WITH CHECK OPTION;

Without CHECK OPTION, a write that violates the view's WHERE clause would just quietly disappear from that view's results the next time it's queried — which is often the more surprising, and worse, behavior.

Most databases support CREATE OR REPLACE VIEW to redefine an existing view in one step. SQLite doesn't — altering a view here always means DROP VIEW first, then CREATE VIEW again with the new definition.

DROP VIEW BestSellers;

CREATE VIEW BestSellers AS
SELECT p.Name, COUNT(*) AS UnitsSold, SUM(p.Price) AS Revenue
FROM Sales s
JOIN Products p ON s.ProductID = p.ProductID
GROUP BY p.Name;

BestSellers now includes a Revenue column it never had before — the only way to add that in SQLite is to drop the old definition and create it fresh.