Exercises — Views — creating, updatable views

No. A standard (virtual) view stores ==only the SELECT query text== (that highlighted phrase is the whole point), not the rows. The rows are recomputed from the base table every time you query the view — so there is exactly one physical copy of the data (in employees/items/etc.). Only a materialised view would store a second physical copy.

• (a) ❌ missing the keyword AS. The grammar is CREATE VIEW name AS SELECT .... Fix: insert AS.
• (b) ✅ correct.
• (c) creates a real table (a physical copy of the current rows), not a view. It will not stay in sync with items. So it's valid SQL but the wrong tool if you wanted a view.

The keyword AS is what tells the engine "store the following query as this object's definition."

CREATE VIEW emp_public AS
SELECT emp_id, name, dept_id
FROM employees;
 
GRANT SELECT ON emp_public TO intern;

Why it works: the intern is granted access to the view, not the base table. Because the view's SELECT list never mentions salary, the column simply does not exist in the intern's world — they cannot reference a column that the view never exposes. The view is an access-control surface (column-level security).

CREATE VIEW cheap_items AS
SELECT item_id, name, price
FROM items
WHERE price < 100
WITH CHECK OPTION;

Why WITH CHECK OPTION? A plain WHERE filters only reads. Without the guard, INSERT INTO cheap_items VALUES (50,'Mug',150) would succeed at the base table and then vanish from the view (because a price of 150 is not less than 100, written $150\nless 100$). WITH CHECK OPTION forces every write through the view to still satisfy the view's WHERE — otherwise the write is rejected.

CASCADED vs LOCAL (matters the moment views stack): the standard lets you write WITH CASCADED CHECK OPTION or WITH LOCAL CHECK OPTION. Plain WITH CHECK OPTION defaults to CASCADED. The difference only shows up when this view is built on top of another view: CASCADED enforces this view's WHERE plus every underlying view's WHERE, while LOCAL enforces only this view's own WHERE (and any underlying view that also declared a check). For a single-table view like cheap_items the two behave identically — but for a stacked security setup, CASCADED is the safe default. We work the full stacked example in L5.1.

An expression like AVG(salary) has no natural column name, so you must supply one — either in the view header or with a column alias:

CREATE VIEW dept_stats(dept_id, avg_pay) AS
SELECT dept_id, AVG(salary)
FROM employees
GROUP BY dept_id;

or equivalently SELECT dept_id, AVG(salary) AS avg_pay. The header-list form names every output column positionally; the alias form names them inline. Both produce a two-column view.

• V1 — updatable ✅. Plain columns from one table with a WHERE. Each view row maps to exactly one items row via item_id, and price/name are real base columns. UPDATE v1 SET price=90 WHERE item_id=7 rewrites to UPDATE items SET price=90 WHERE item_id=7.
• V2 — NOT updatable ❌. Has AVG computed over a GROUP BY — rows get collapsed. A single view row summarises many base rows, so "set avg_pay = 60000" has infinitely many base solutions and the engine can't translate it.
• V3 — NOT updatable ❌. DISTINCT collapses many rows into one; one view row maps to several base rows → no unique target.
• V4 — partly. name maps back cleanly, but price_incl_tax = price*1.10 is an expression, not a base column. You cannot UPDATE v4 SET price_incl_tax = ... because there is no such base column to write; the engine would have to invert the arithmetic, which standard SQL forbids. So that column is read-only even though the row mapping is unique.

The insert succeeds — it is a perfectly legal insert into the base table items. So:

• SELECT * FROM items WHERE item_id = 50 → returns one row: (50, 'Mug', 150, NULL). That fourth value is the stock column, which the view never lists. Because the insert only supplied the three view columns (item_id, name, price), the engine fills every unmentioned base column with its default — here stock has no explicit default, so it becomes NULL. Why no error? The insert is valid as long as no NOT NULL / constraint is violated; stock accepts NULL, so the base row is accepted. (If stock were declared NOT NULL with no default, this insert would fail — a separate reason from the view.)
• SELECT * FROM cheap_items → the Mug does not appear, because a price of 150 is not less than 100 (the view's filter fails).

This is the "ghost insert": the write happened, but the view can't see its own child. WITH CHECK OPTION would have rejected the insert instead.

Why rejected: a multi-table join means one view row is stitched from an employees row and a departments row. When you write to the view, the engine can't always know which base table(s) you meant — the mapping isn't a single, unambiguous base row. So standard SQL treats the join view as read-only unless it can prove a key-preserved table (see L5.3); many engines simply reject it to be safe.

The escape hatch: supply the missing mapping yourself with a trigger that fires instead of the automatic translation.

CREATE TRIGGER emp_dept_upd
INSTEAD OF UPDATE ON emp_dept
REFERENCING NEW AS n OLD AS o        -- exposes the new/old row values
FOR EACH ROW
BEGIN
    UPDATE employees
    SET salary = n.salary,
        name   = n.name
    WHERE emp_id = o.emp_id;
END;

A note on syntax (dialects vary): the REFERENCING NEW AS n OLD AS o clause is the standard SQL way to name the incoming/existing row so you can read n.salary (the new value) and o.emp_id (the old key). Real engines spell this differently: PostgreSQL exposes built-in NEW/OLD records (no REFERENCING needed), SQL Server uses inserted/deleted pseudo-tables, and Oracle uses :NEW/:OLD. The idea — reference new and old values to write the base update by hand — is universal; only the keywords change.

Why this works: the trigger runs your explicit UPDATE employees ..., so you told the engine exactly which base table and key to touch, and the ambiguity is gone. We deliberately do not touch dname (that lives in departments) — keeping the write to a single table.

Put a view between the analysts and the schema. Assume a base table customers(cust_id PK, name, total_spend, is_active).

CREATE VIEW active_customers AS
SELECT cust_id, name, total_spend
FROM customers
WHERE is_active = 1 AND total_spend > 1000;

Analysts query active_customers, never customers directly:

SELECT * FROM active_customers WHERE total_spend > 5000;   -- analyst query

When the DBA renames the column, you patch one line in the view definition — the analysts' queries are never touched:

CREATE OR REPLACE VIEW active_customers AS
SELECT cust_id, name, total_spend
FROM customers
WHERE active_flag = 1 AND total_spend > 1000;   -- only this line changed

Why this is the point of views: the view is an abstraction layer. The analysts' queries reference only the view's stable column names (cust_id, name, total_spend), so a base-schema change is absorbed in one place (the single view definition) instead of rippling through every application query. CREATE OR REPLACE VIEW swaps the definition atomically without dropping any permissions granted on the view — so the analysts keep working across the rename with zero query edits.

The candidate row has price = 5. Check against each view's WHERE:

• v_outer needs price < 100 → 5 is less than 100 ✅ passes locally.
• v_inner needs price >= 10 → 5 is not ≥ 10 ❌ fails.

WITH CASCADED CHECK OPTION (the SQL default) checks this view AND all underlying views. Because v_inner's condition fails, the insert is rejected.

WITH LOCAL CHECK OPTION checks only this view's own WHERE (plus any underlying view that also declared a check — here v_inner declared none). So only price < 100 is enforced → the insert is accepted, even though the row violates v_inner's intent. This is exactly why CASCADED is the safer default.

• (a) WHERE 1 = 0 is never true, so the view returns 0 rows — always empty, whatever items contains.
• (b) Yes — it's still a single-table, plain-column view, so it is technically updatable (updatability is a property of the definition, not of how many rows currently show).
• (c) With WITH CHECK OPTION, no insert can ever succeed: any candidate row must satisfy 1 = 0, which is impossible. So the guarded empty view is effectively write-locked. This is the limiting case — a filter that admits nothing.

A base table in a join view is key-preserved if its primary key stays unique in the joined result — i.e. every row of the view maps back to exactly one row of that table. Here the join employees.dept_id = departments.dept_id is many-to-one: each employee has exactly one department, so each employees row appears exactly once in emp_dept2. Therefore employees is key-preserved, and its emp_id still uniquely identifies each view row.

• UPDATE ... SET salary = 71000 touches only a column of the key-preserved table employees. One view row still points to exactly one employees row, so the engine can translate it unambiguously → allowed on dialects that implement key-preserved updatability.
• UPDATE ... SET dname = 'Sales' touches departments, which is not key-preserved: one department row can appear in many view rows (many employees share a department). Changing dname "for emp 7" is ambiguous — it would silently rename the department for everyone in it, or the engine simply can't map it to a single departments row. So it is disallowed (or requires an INSTEAD OF trigger).

Takeaway: the "no joins" rule is a safe default, not an absolute law. The deeper, exact rule is the same as always — one view row ↔ one base row on a real base column — and a key-preserved table satisfies it even inside a join. This behaviour is engine-dependent, so verify it in your dialect before relying on it.

A standard view stores no rows — every one of the ~1800 reads/hour re-scans and re-aggregates all 5 million rows via view merging. That is 1800 full aggregations per hour to recompute a number that only changes once per night. Wasteful.

Fix — a materialised view refreshed nightly:

CREATE MATERIALIZED VIEW dept_avg_mv AS
SELECT dept_id, AVG(salary) AS avg_pay FROM employees GROUP BY dept_id;
-- refresh once a night, after the batch load:
REFRESH MATERIALIZED VIEW dept_avg_mv;

Now the aggregation runs once per day (1 time) instead of ~43,200 times per day (that is 1800 × 24). Reads become a cheap lookup of a tiny pre-stored table. Trade-off: the data is stale between refreshes — acceptable here because the base changes only nightly. (This view has AVG + GROUP BY, so it is read-only either way — see Normalization for why summary data usually lives outside the normalised base.)

• L1: a view stores a query, not data.
• L2: views hide columns/rows for security; WITH CHECK OPTION (defaulting to CASCADED) makes the filter guard writes.
• L3: updatable ⇔ one view row ↔ one base row on real base columns; collapsing/expression features break that.
• L4: an INSTEAD OF trigger lets you supply the missing mapping for a join view; a view layer absorbs schema changes.
• L5: CASCADED (default) checks all the way down; key-preserved joins can be updatable on some engines; degenerate WHERE 1=0 views admit nothing; use materialised views when recompute cost dwarfs change frequency.