Views — creating, updatable views — Hinglish notes

4.4.11 · Coding › Databases

View KYA hai?

Do flavours hain:

Standard (virtual) view — sirf query store karta hai. Har access par re-evaluate hota hai.
Materialised view — actually result rows ko speed ke liye disk par store karta hai, aur ise refresh karna padta hai. (Alag trade-off; default CREATE VIEW virtual hota hai.)

Kaise banate hain — first principles se derive karke

Tum pehle se jaante ho ki SELECT ek relation (rows + columns ka set) return karta hai. View sirf us relation ko ek naam dena hai taaki tum ise reuse kar sako.

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

Ab high_earners ko kahin bhi use kar sakte hain jahan table use hoti hai:

SELECT name FROM high_earners WHERE dept_id = 3;

Intuition Engine actually kya karta hai (query rewriting)

Optimiser view definition ko tumhari query mein substitute kar deta hai (view merging). Tumhari query effectively ban jaati hai:

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

Yahi reason hai ki ek virtual view koi storage cost nahi karta lekin har call par computation re-pay karta hai.

Columns ko explicitly naam dene ke liye (zaruri jab columns expressions se aate hain):

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

Worked example Worked: ek security view

Goal: interns ko employees dikhne do lekin salaries nahi.

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

Yeh step kyun? View par SELECT grant karna (base table par nahi) salary column ko poori tarah chhupaata hai — intern literally us column ko reference nahi kar sakta jo view kabhi expose hi nahi karta. Views ek access-control surface hain.

Views kyun use karte hain? (4 asli reasons)

Reason	Kya milta hai
Simplicity	Ek monster 5-table `JOIN` ko ek friendly naam ke peeche chhupao.
Security	Sirf chosen rows/columns expose karo (row/column-level security).
Abstraction / stability	Agar schema change ho, tum view ko patch karo; ise use karne wale apps nahi tootte.
Consistency	Ek reused business rule (jaise "active customer") ek baar define hoti hai.

Figure — Views — creating, updatable views

Updatable Views — topic ka dil

Mapping idea se rule derive karna

Ek updatable view lo:

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; cleanly rewrite hota hai UPDATE items SET price = 90 WHERE item_id = 7; pe — ek view row ↔ ek base row, aur price ek real base column hai. ✅

Ab ek non-updatable view:

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

Try karo UPDATE dept_stats SET avg_pay = 60000 WHERE dept_id = 3; Kaunse employee ki salary change karoge, aur kitni, taaki average 60000 ban jaye? Infinitely many answers → engine ise reject karta hai. ❌ Yahi hai rule, derived — memorise nahi kiya.

Worked example View ke through INSERT — 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!

Yeh dangerous kyun hai: insert succeed ho jaata hai (yeh ek valid base-table insert hai), lekin naya row price < 100 satisfy nahi karta, isliye woh turant view se disappear ho jaata hai. Ise rokne ke liye, ek guard add karo:

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

Ab insert/update reject ho jaata hai agar resulting row view ke bahar fall kare. Yeh step kyun? WITH CHECK OPTION force karta hai ki view ke through har write view ke WHERE ko satisfy karta rahe.

Common Mistakes (Steel-manned)

Recall Feynman: ek 12-saal ke bacche ko explain karo

Socho ek magic window jo ek badi messy room par lagi hai. Window ke through tum sirf woh toys dekhte ho jo red hain (tumhara WHERE). Window khud koi toys hold nahi karti — yeh sirf tumhe real room ka ek slice dikhati hai. Agar tum window ke through reach karke ek toy move karte ho, tum real room mein real toy move kar rahe ho. Lekin tum window ke through ek sign jo "average toy height" kehta hai change karne ke liye reach nahi kar sakte — koi single toy nahi hai jise move karke average sach ho sake. Yahi exact reason hai kyun kuch views tum edit kar sakte ho aur kuch nahi.

Flashcards

Database view kya hai?

Ek named virtual relation jo ek stored SELECT se define hoti hai; yeh koi data hold nahi karti, rows har access par base tables se recompute hoti hain.

Kya ek standard (virtual) view data store karta hai?

Nahi — yeh sirf query definition store karta hai aur har baar ise re-evaluate karta hai.

Virtual aur materialised view mein kya fark hai?

Virtual har call par query re-run karta hai (no storage); materialised result rows disk par store karta hai aur use refresh karna padta hai.

Aggregate/GROUP BY views updatable kyun nahi hote?

View row se ek single base row ki one-to-one mapping nahi hoti, isliye DB determine nahi kar sakta ki kaunsi base value change karni hai.

Woh constructs list karo jo view ko non-updatable banate hain.

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

WITH CHECK OPTION kya karta hai?

View ke through koi bhi insert/update reject karta hai jiska resulting row view ki WHERE condition satisfy nahi karta.

Tum ek filtered view ke through ek row INSERT karte ho jo filter violate karta hai — kya hota hai (no CHECK OPTION)?

Insert base table mein succeed ho jaata hai lekin row view se disappear ho jaata hai kyunki yeh WHERE match nahi karta.

Ek complex/join view ko writable kaise banaate hain?

Ek INSTEAD OF trigger define karo jo operation ko specific base-table changes se map karta hai.

View ke through successful writes actually kahan jaati hain?

Directly underlying base table(s) mein.

Views use karne ke do reasons batao.

Security (sirf chosen rows/columns expose karo) aur simplicity/abstraction (complex queries chhupao, interface stabilise karo).

Connections

SQL SELECT and JOINs — ek view sirf ek named SELECT hai.
Materialized Views — stored-result alternative.
Database Security and GRANT — views ek permission surface ke roop mein.
Triggers — INSTEAD OF triggers complex views par writes enable karte hain.
Query Optimization and View Merging — engine view queries ko kaise rewrite karta hai.
Normalization — views normalized data ko denormalized form mein re-present kar sakte hain.