Lesson 12 � Intermediate

QUERIES KE ANDAR
QUERIES BANAO.

Subquery ek query ke andar doosri query hai � complex analysis ke liye zaroori hai. CTE (Common Table Expression) se readable hota hai. Yeh SQL ka advanced tool hai jo har data analyst ko aana chahiye.

? 22 min✓ Intermediate✓ Prerequisite: Triggers

WHY: Subqueries kyun zaroori hain?

Socho tumhe pata karna hai ki kaunse students ka marks class average se zyada hai. Tum ek baar AVG nikal sakte ho aur phir doosri query mein use kar sakte ho � ya ek subquery likh sakte ho jo ek hi step mein kaam kar de. Real-world databases mein data complex hota hai � subqueries se tum multiple queries ko ek saath combine kar sakte ho.

SUBQUERY

Query ke andar query. Pehle inner query run hoti hai, phir uska result outer query use karti hai. Jaise ek function ke andar doosra function call karna.

Subquery ke result mein check karta hai. Agar value subquery ke result set mein hai toh row include hoti hai. "Yeh student un courses mein hai jo active hain?"

CTE

Temporary named result set. WITH keyword se banta hai. Complex queries ko readable banata hai � jaise variable rakhna programming mein. Subquery se better hai readability ke liye.

HOW: Subquery ka syntax samjho

Subquery ka formula simple hai � SELECT ... FROM ... WHERE column IN (SELECT ...). Inner query pehle run hoti hai, phir outer query us result ko use karti hai.

1. Subquery in WHERE

WHERE clause mein subquery lagao � jaise above-average marks wale students dikhao:

-- Students jinka marks class average se zyada hai
SELECT * FROM students
WHERE marks > (SELECT AVG(marks) FROM students);

-- Students jo Data Science course mein hain
SELECT * FROM students
WHERE course_id IN (SELECT id FROM courses WHERE course_name = 'Data Science');

-- Students jinka marks sabse zyada hai
SELECT * FROM students
WHERE marks = (SELECT MAX(marks) FROM students);

ℹ

Mental model: Subquery jaise ek assistant hai � pehle assistant kaam karta hai (inner query), phir boss (outer query) us result par faisla leta hai. Single value ke liye =, multiple values ke liye IN use karo.

2. Subquery in FROM

FROM clause mein subquery lagao � temporary table banao:

-- Course-wise average marks
SELECT course, avg_marks FROM (
 SELECT course, AVG(marks) as avg_marks
 FROM students
 GROUP BY course
) AS course_stats;

-- Har course ke top 3 students
SELECT * FROM (
 SELECT name, course, marks,
 ROW_NUMBER() OVER (PARTITION BY course ORDER BY marks DESC) as rank
 FROM students
) AS ranked_students
WHERE rank <= 3;

3. Subquery in SELECT

SELECT clause mein subquery lagao � har row ke saath calculated value dikhao:

-- Har student ke saath class average dikhao
SELECT name, marks,
 (SELECT AVG(marks) FROM students) as class_avg
FROM students;

-- Har student ke saath course ka naam dikhao
SELECT name, marks,
 (SELECT course_name FROM courses WHERE id = s.course_id) as course_name
FROM students s;

4. CTE (Common Table Expression)

CTE se complex queries readable hoti hain � WITH keyword se temporary result set banao:

-- CTE: course statistics banao
WITH course_stats AS (
 SELECT course, AVG(marks) as avg_marks, COUNT(*) as count
 FROM students
 GROUP BY course
)
SELECT * FROM course_stats WHERE avg_marks > 80;

-- Multiple CTEs ek saath
WITH active_courses AS (
 SELECT * FROM courses WHERE status = 'active'
),
course_enrollments AS (
 SELECT c.course_name, COUNT(s.id) as enrolled
 FROM active_courses c
 LEFT JOIN students s ON c.id = s.course_id
 GROUP BY c.course_name
)
SELECT * FROM course_enrollments WHERE enrolled > 10;

ℹ

Pro tip: CTE vs Subquery � CTE zyada readable hai especially jab multiple temporary tables chahiye. Ek CTE ko doosre CTE mein refer kar sakte ho. Subquery chhoti hai lekin CTE ka naam se reuse kar sakte ho.

5. CTE for complex analysis

Ranking, window functions ke saath CTE use karo:

-- Har course ke top 3 students dikhao
WITH ranked AS (
 SELECT name, course, marks,
 ROW_NUMBER() OVER (PARTITION BY course ORDER BY marks DESC) as rank
 FROM students
)
SELECT * FROM ranked WHERE rank <= 3;

-- Running total nikalo
WITH daily_sales AS (
 SELECT sale_date, SUM(amount) as total
 FROM sales
 GROUP BY sale_date
)
SELECT sale_date, total,
 SUM(total) OVER (ORDER BY sale_date) as running_total
FROM daily_sales;

Subquery types

SCALAR

Single value return karta hai. (SELECT AVG(marks) FROM students) � ek number aayega. WHERE clause mein comparison ke liye use hota hai.

MULTI-ROW

Multiple rows return karta hai. IN clause ke saath use hota hai. Jaise "yeh students un courses mein hain jo exist karte hain."

CORRELATED

Outer query ka column use karta hai. Har row ke liye alag se run hota hai � slower lekin powerful. Jaise "har student ka uske course ke average se comparison."

Try it: Subqueries aur CTEs likho

Neeche ka editor SQL jaisa hai. Yahan apni subquery ya CTE likho aur "Run Query" dabao. Screen par sample results dikhenge � yeh browser-based simulation hai, real database nahi.

SQL playgroundSubqueries aur CTEs se results dikhenge

query.sql

Run Query dabayein

Quick check

Ek subquery likho jo above-average marks wale students dikhaye. Students table mein marks column hai aur average nikalna hai subquery se.

SELECT * se shuru karo, FROM students likho, WHERE marks > ke baad subquery likho jo SELECT AVG(marks) FROM students return kare.

Common subquery mistakes

Single row ke liye IN use karna: Agar subquery single value return karti hai toh = use karo, IN nahi. WHERE marks = (SELECT MAX(marks) FROM students) � correct. WHERE marks IN (SELECT MAX(marks) FROM students) � galat nahi hai lekin unnecessary.
Correlated subquery ka overuse: Correlated subquery har row ke liye alag se run hota hai � 1 lakh rows mein 1 lakh baar subquery chalegi. Jab possible ho, CTE ya JOIN use karo.
CTE mein naming na samajhna: CTE ka naam unique hona chahiye. Ek CTE doosre CTE ko refer kar sakta hai lekin circular reference nahi hona chahiye.
Subquery ko alias dena bhoolna: FROM clause mein subquery ko hamesha alias do � (SELECT ...) AS alias_name � warna SQL error aayega.
Performance na check karna: Correlated subquery slow ho sakti hai bade datasets mein. Hamesha EXPLAIN se check karo ki query kaise run ho rahi hai.

Subqueries aur CTEs clear hai?

Ab Window Functions par chalo � ROW_NUMBER, RANK, LAG, LEAD jaise advanced functions seekho jo data analysis ko next level le jaate hain.

QUERIES KE ANDARQUERIES BANAO.