Views — creating, updatable views — Coding notes

WHAT is a view?

There are two flavours:

Standard (virtual) view — stores only the query. Re-evaluated every access.
Materialised view — actually stores the result rows on disk for speed, and must be refreshed. (Different trade-off; the default CREATE VIEW is virtual.)

HOW to create one — derived from first principles

You already know a SELECT returns a relation (a set of rows + columns). A view is just giving that relation a name so you can reuse it.

CREATE VIEW high_earners AS
SELECT emp_id, name, salary, dept_id
FROM employees
WHERE salary > 50000;

Now high_earners can be used anywhere a table can:

SELECT name FROM high_earners WHERE dept_id = 3;

Intuition What the engine actually does (query rewriting)

The optimiser substitutes the view definition into your query (view merging). Your query effectively becomes:

SELECT name FROM employees
WHERE salary > 50000 AND dept_id = 3;

This is why a virtual view costs no storage but re-pays the computation each call.

To name columns explicitly (needed when columns come from expressions):

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

Worked example Worked: a security view

Goal: let interns see employees but not salaries.

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

Why this step? Granting SELECT on the view (not the base table) hides the salary column entirely — the intern literally cannot reference a column the view never exposes. Views are an access-control surface.

WHY use views? (the 4 real reasons)

Reason	What it buys you
Simplicity	Hide a monster 5-table `JOIN` behind one friendly name.
Security	Expose only chosen rows/columns (row/column-level security).
Abstraction / stability	If the schema changes, you patch the view; apps using it don't break.
Consistency	A reused business rule (e.g. "active customer") is defined once.

Figure — Views — creating, updatable views

Updatable Views — the heart of the topic

Deriving the rule from the mapping idea

Take an updatable view:

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

UPDATE cheap_items SET price = 90 WHERE item_id = 7; rewrites cleanly to UPDATE items SET price = 90 WHERE item_id = 7; — one view row ↔ one base row, and price is a real base column. ✅

Now a non-updatable view:

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

Try UPDATE dept_stats SET avg_pay = 60000 WHERE dept_id = 3; Which employee's salary do you change, and to what, so the average becomes 60000? Infinitely many answers → the engine rejects it. ❌ That's the rule, derived — not memorised.

Worked example INSERT through a view — the hidden trap

CREATE VIEW cheap_items AS
SELECT item_id, name, price FROM items WHERE price < 100;
INSERT INTO cheap_items VALUES (50, 'Mug', 150);   -- price 150!

Why this is dangerous: the insert succeeds (it's a valid base-table insert), but the new row does not satisfy price < 100, so it instantly disappears from the view. To forbid this, add a guard:

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

Now the insert/update is rejected if the resulting row would fall outside the view. Why this step? WITH CHECK OPTION forces every write through the view to keep satisfying the view's WHERE.

Common Mistakes (Steel-manned)

Recall Feynman: explain to a 12-year-old

Imagine a magic window stuck on a big messy room. Through the window you only see the toys that are red (your WHERE). The window holds no toys itself — it just shows you a slice of the real room. If you reach through the window and move a toy, you're moving the real toy in the real room. But you can't reach through the window to change a sign that says "average toy height" — there's no single toy to move to make an average true. That's exactly why some views you can edit and some you can't.

Flashcards

What is a database view?

A named virtual relation defined by a stored SELECT; it holds no data, the rows are recomputed from base tables on each access.

Does a standard (virtual) view store data?

No — it stores only the query definition and re-evaluates it each time.

Difference between a virtual and a materialised view?

Virtual re-runs the query each call (no storage); materialised stores result rows on disk and must be refreshed.

Why are aggregate/GROUP BY views not updatable?

There's no one-to-one mapping from a view row back to a single base row, so the DB can't determine which base value to change.

List the constructs that make a view non-updatable.

Aggregates, GROUP BY/HAVING, DISTINCT, UNION/set ops, multi-table joins (usually), and expression/derived columns.

What does WITH CHECK OPTION do?

Rejects any insert/update through the view whose resulting row would not satisfy the view's WHERE condition.

You INSERT a row through a filtered view that violates the filter — what happens (no CHECK OPTION)?

The insert succeeds in the base table but the row vanishes from the view since it doesn't match the WHERE.

How can you make a complex/join view writable?

Define an INSTEAD OF trigger that maps the operation to specific base-table changes.

Where do successful writes through a view actually go?

Directly to the underlying base table(s).

Name two reasons to use views.

Security (expose only chosen rows/columns) and simplicity/abstraction (hide complex queries, stabilise the interface).

Connections

SQL SELECT and JOINs — a view is just a named SELECT.
Materialized Views — the stored-result alternative.
Database Security and GRANT — views as a permission surface.
Triggers — INSTEAD OF triggers enable writes to complex views.
Query Optimization and View Merging — how the engine rewrites view queries.
Normalization — views can re-present normalized data in denormalized form.

Concept Map

Hinglish (regional understanding)

Intuition Hinglish mein samjho

Dekho, ek view basically ek saved SQL query hota hai jo dikhne mein table jaisa lagta hai, par uske paas apna koi data nahi hota. Jab bhi tum view se SELECT karte ho, database peeche se wahi original query dobara chala deta hai aur fresh result dikha deta hai. Isiliye view hamesha base table ke saath sync mein rehta hai — base table change karo, view automatically update dikhega. Banane ke liye simple: CREATE VIEW naam AS SELECT ....

View use karne ke teen bade faayde: simplicity (badi complex join ko ek chhote naam ke peeche chhupa do), security (intern ko sirf name, dept dikhao, salary column view mein dalo hi mat), aur stability (schema badle to sirf view fix karo, app code safe). Yeh exam aur real projects dono mein bahut kaam aata hai.

Ab asli twist — updatable view. Tum view pe UPDATE/INSERT tabhi kar sakte ho jab database har view-row ko exactly ek base-table row se match kar paaye. Agar view mein GROUP BY, AVG/SUM, DISTINCT, UNION ya multi-table JOIN hai, to mapping unique nahi rehti — socho "average salary 60000 kar do" matlab kis employee ki salary badloge? Koi clear jawab nahi, isliye DB mana kar deta hai. Yaad rakhne ka mantra: GADU-JS wale views read-only.

Ek aakhri trap: filtered view (WHERE price<100) mein agar tum 150 price ka row insert karoge to wo base table mein chala jaata hai par view mein dikhega hi nahi (kyunki filter fail). Isko rokne ke liye WITH CHECK OPTION lagao — phir galat row reject ho jaata hai. Aur complex view pe likhna ho to INSTEAD OF trigger se DB ko khud bata do kaunsa base table change karna hai.

Views — creating, updatable views

WHAT is a view?

HOW to create one — derived from first principles

WHY use views? (the 4 real reasons)

Updatable Views — the heart of the topic

Deriving the rule from the mapping idea

Common Mistakes (Steel-manned)

Flashcards

Connections

Concept Map

Hinglish (regional understanding)

Go deeper — visual, from zero

Test yourself — Databases

Connections