Lesson 12 � Abstraction
COMPLEX QUERIES KO
SIMPLE BANAO.
Har baar complex JOIN ya filter query likhne ki jagah ek naam se call karo � yahi Views hain. Views saved SELECT queries hain jo tumhare database mein stored rehti hain. Code reuse bhi hota hai, security bhi improve hoti hai.
WHY: Views kyun zaroori hain?
Socho tumhara project hai jisme har roz 10 different reports nikalni hain. Har baar same complex JOIN + WHERE + GROUP BY likhna padta hai � boring aur error-prone. Views mein yeh query ek baar save karte ho aur phir sirf SELECT * FROM view_name se kaam ho jata hai. Plus, tum sensitive columns (password, salary) hide kar sakte ho � sirf relevant data dikhao.
Saved SELECT query. Database mein ek naam se stored rehti hai. Jaise function programming mein � query likho ek baar, call baar baar karo.
Naya view banana. SQL query likho aur usse ek view ka naam do. Ab wo view ek table jaisa dikhta hai � SELECT * chalao uspe.
View hataana. Jab view ki zaroorat na ho toh DROP VIEW se delete karo. Base table unaffected rehti hai.
HOW: Views banana aur use karna
Views banana simple hai � CREATE VIEW statement se. Ek baar view ban gaya toh tum usse table ki tarah query kar sakte ho. Views mein JOIN, WHERE, GROUP BY � sab use ho sakta hai.
1. Basic VIEW banao
Students table se sirf high scorers ka view banao:
-- High scorers ka view banao
CREATE VIEW high_scorers AS
SELECT name, course, marks
FROM students
WHERE marks > 80;
-- Ab aap seedha view se query kar sakte ho
SELECT * FROM high_scorers;
-- Result: sirf woh students dikhenge jinke marks 80 se zyada hain2. VIEW with JOIN
Students aur courses dono tables se data dikhao ek view mein:
-- Student + Course combined view
CREATE VIEW student_courses AS
SELECT s.name, c.course_name, c.duration
FROM students s
JOIN courses c ON s.course_id = c.id;
-- Ab simple query se saara data mil jayega
SELECT * FROM student_courses;
-- WHERE bhi laga sakte ho
SELECT * FROM student_courses WHERE duration > 6;SELECT, WHERE, ORDER BY � sab chalega view pe bhi. Bas underlying query complex hai, tumhari calling simple hai.3. VIEW modify karo � CREATE OR REPLACE
View ki definition change karni ho toh CREATE OR REPLACE use karo:
-- Pehle ka view update karo
CREATE OR REPLACE VIEW high_scorers AS
SELECT name, course, marks, marks * 10 AS percentage
FROM students
WHERE marks > 85;
-- Ab view mein percentage column bhi aayega
-- Aur sirf 88+ marks wale dikhenge
SELECT * FROM high_scorers;CREATE OR REPLACE se view overwrite ho jata hai � alag se DROP karne ki zaroorat nahi. Definition change karte waqt yeh sabse convenient hai.4. VIEW drop karo
View ki zaroorat khatam ho gayi toh hataao:
-- View hataao
DROP VIEW high_scorers;
-- Agar view exist nahi karta toh error na aaye
DROP VIEW IF EXISTS high_scorers;5. Saare views dekho
Database mein kaunse views hain:
-- MySQL mein saare views
SHOW FULL TABLES WHERE Table_type = 'VIEW';
-- PostgreSQL mein views ki list
SELECT viewname FROM pg_views
WHERE schemaname = 'public';
-- View ki definition dekho
SHOW CREATE VIEW high_scorers;6. View with Aggregation
Course-wise average marks ka view banao:
-- Course-wise stats view
CREATE VIEW course_stats AS
SELECT course,
COUNT(*) AS total_students,
AVG(marks) AS avg_marks,
MAX(marks) AS top_marks
FROM students
GROUP BY course;
-- Report nikalo
SELECT * FROM course_stats ORDER BY avg_marks DESC;
-- sirf top performing course dikhao
SELECT * FROM course_stats WHERE avg_marks > 85;Views ke real use cases
Views sirf shortcut nahi hain � production mein yeh key role play karte hain:
-- USE CASE 1: Security � sensitive columns hide karo
CREATE VIEW public_students AS
SELECT id, name, course, marks
FROM students;
-- Password aur salary columns nahi dikhenge
-- USE CASE 2: Report � complex query simplify karo
CREATE VIEW monthly_report AS
SELECT DATE_FORMAT(enrolled_at, '%Y-%m') AS month,
course, COUNT(*) AS enrollments,
AVG(marks) AS avg_score
FROM students
GROUP BY 1, 2;
-- USE CASE 3: Multi-table � JOIN ko simple banao
CREATE VIEW full_profile AS
SELECT s.name, s.marks, c.course_name,
t.name AS teacher, t.email AS teacher_email
FROM students s
JOIN courses c ON s.course_id = c.id
JOIN teachers t ON c.teacher_id = t.id;View types ka quick reference
-- 1. Simple view
CREATE VIEW view_name AS SELECT ... FROM table WHERE ...;
-- 2. View with JOIN
CREATE VIEW view_name AS SELECT ... FROM t1 JOIN t2 ON ...;
-- 3. View with aggregation
CREATE VIEW view_name AS SELECT col, COUNT(*) FROM table GROUP BY col;
-- 4. Modify view
CREATE OR REPLACE VIEW view_name AS SELECT ...;
-- 5. Drop view
DROP VIEW view_name;
-- 6. Conditional drop
DROP VIEW IF EXISTS view_name;Try it: Views banao aur query karo
Neeche ke editor mein views create karo aur unse data fetch karo. Pehle ek basic view banao, phir usse modify karo, aur phir usse drop karo.
Quick check
Ek view banao jiska naam top_performers ho aur students table se sirf woh students dikhaye jinke marks 85 se zyada hain. Sirf name aur marks columns dikhao.
CREATE VIEW se shuru karo, view ka naam likho (top_performers), phir AS keyword use karo aur SELECT query likho jo name aur marks select kare aur WHERE marks > 85 lagaye.
Common beginner mistakes
- View ko table samajhna: View mein data store nahi hota � sirf query saved hoti hai. Jab view query karte ho tabhi data aata hai. View apne aap mein data nahi rakhta.
- Har complex query ko view banana: Agar query ek baar use ho rahi hai toh view mat banao. Sirf frequently used queries ko view mein save karo � zyada views = maintenance burden.
- OR REPLACE ka use na karna: View modify karte waqt
DROPkarke phirCREATEkarna galat hai � do statements ki jagah ekCREATE OR REPLACEuse karo. - View se INSERT/UPDATE karna: Simple views (single table, no aggregation) se data modify ho sakta hai, lekin complex views (JOIN ya GROUP BY wale) se write nahi hota. View ki limitations samjho.
- IF EXISTS lagana bhoolna:
DROP VIEWkarte waqtIF EXISTSlagao � warna view exist nahi karta toh error aayega aur script ruk jayegi.
Ab Stored Procedures par chalo � reusable logic banao jo parameters accept kare.