SQL: grouping, aggregates और quantified subqueries
लक्ष्य और पूर्वज्ञान
Grouped results निकालें, row filters और group filters अलग करें, तथा NULL traps से बचते हुए existence और “हर” queries लिखें। SQL bags, joins और three-valued logic का पूर्वज्ञान चाहिए। Standard core उपयोग है; नीचे correlation के identifier columns non-null हैं। ORDER BY बिना output order unspecified है।
1. Aggregates अलग चीजें गिनते हैं
COUNT() सभी rows गिनता है, NULL वाली भी। COUNT(x) non-null x गिनता है। COUNT(DISTINCT x) distinct non-null x गिनता है। SUM, AVG, MIN, MAX अपने NULL inputs ignore करते हैं। x=10,10,NULL,30 के लिए COUNT()=4, COUNT(x)=3, COUNT(DISTINCT x)=2, SUM(x)=50, AVG(x)=50/3। Average का denominator चार नहीं। Types numeric display तथा arithmetic को प्रभावित करते हैं, यहाँ logical denominator को नहीं।
Empty input पर बिना GROUP BY aggregate SELECT एक group देता है: COUNT(*)=0, जबकि SUM, AVG, MIN, MAX=NULL। इसके विपरीत empty input पर GROUP BY dept में कोई group और कोई result row नहीं। Empty SUM को zero तभी मानें जब COALESCE(SUM(x),0) जैसा explicit replacement हो।
2. WHERE और HAVING
Sale(dept,amount) में (A,10),(A,20),(A,NULL),(B,5),(B,30),(C,NULL) लें। Query:
SELECT dept,COUNT(*) AS n,SUM(amount) AS total FROM Sale WHERE amount>=10 GROUP BY dept HAVING SUM(amount)>=30;
WHERE से (A,10),(A,20),(B,30) बचते हैं; NULL rows UNKNOWN और 5 false हैं। A group में n=2,total=30; B में n=1,total=30। दोनों HAVING पास करते हैं। WHERE aggregation से पहले individual rows और HAVING aggregation के बाद groups filter करता है। Current query group का aggregate उसी query block के WHERE में सीधे नहीं रख सकते।
Portable और स्पष्ट grouped queries में selected expression grouping expression या aggregate रखें। कुछ systems विशेष rules में functionally dependent columns भी allow करते हैं; dialect बताए बिना ऐसा assume न करें। NULL department markers एक common group में आते हैं।
3. Outer join के बाद counting
Student(sid)={1,2,3}; Enrol(eid,sid)={(11,1),(12,1),(13,2)}, eid non-null primary key है। sid पर LEFT JOIN के बाद student 3 की एक null-extended row है। s.sid से group करें: COUNT(*) से 2,1,1; COUNT(e.eid) से 2,1,0 मिलता है। असली enrollments गिनने के लिए non-null right key गिनें, preserved row नहीं। अतिरिक्त one-to-many joins rows multiply करके aggregates बढ़ा सकते हैं; सही grain पर aggregate करें या intended identifier को स्पष्ट deduplicate करें।
4. EXISTS और correlated subqueries
SELECT s.sid FROM Student s WHERE EXISTS (SELECT 1 FROM Enrol e WHERE e.sid=s.sid);
Subquery कम-से-कम एक row दे तो EXISTS TRUE है; selected value महत्वपूर्ण नहीं, SELECT NULL भी चलेगा। s.sid का reference इसे correlated बनाता है। ऊपर result 1 और 2 एक-एक बार हैं, अगर Student.sid key है। JOIN करके sid project करने पर DISTINCT बिना 1 दो बार आएगा। NOT EXISTS student 3 देता है।
Scalar subquery अलग है: एक column और अधिकतम एक row देती है। Zero rows पर NULL; एक से अधिक rows पर core SQL model में cardinality error। EXISTS पर at-most-one-row restriction नहीं है।
5. IN, NOT IN, ANY और ALL
Non-null x के लिए equal value मिले तो IN true है। Equal value न मिले और subquery में NULL हो तो IN UNKNOWN; NOT IN भी UNKNOWN। 5 NOT IN (2,NULL) UNKNOWN है और WHERE से हटता है। 2 NOT IN (2,NULL) FALSE है। List में NULL होने से हर result UNKNOWN नहीं होता, क्योंकि decisive equality मौजूद हो सकती है।
NOT EXISTS (SELECT 1 FROM R WHERE R.y=x) पूछता है कि कोई equality TRUE है या नहीं। x=5 और R.y={2,NULL} पर यह TRUE है। इसलिए nullable data में NOT IN और NOT EXISTS सामान्यतः interchangeable नहीं। Outer x का NULL भी exclude करना हो तो x IS NOT NULL अलग जोड़ें।
x > ANY(subquery) को कम-से-कम एक TRUE comparison चाहिए; x > ALL(subquery) को सभी TRUE चाहिए। {2,8} पर 5>ANY TRUE और 5>ALL FALSE। Empty subquery पर ANY FALSE, ALL TRUE, x NULL हो तब भी, क्योंकि comparisons नहीं हैं। NULL comparisons हों और ANY के लिए decisive TRUE या ALL के लिए decisive FALSE न मिले तो UNKNOWN हो सकता है। एक FALSE से ALL false; एक TRUE से ANY true।
6. Double NOT EXISTS से “हर”
Student(sid), Required(cid), Enrol(sid,cid) के सभी correlation keys non-null हों, तो हर required course लेने वाले students:
SELECT s.sid FROM Student s WHERE NOT EXISTS (SELECT 1 FROM Required r WHERE NOT EXISTS (SELECT 1 FROM Enrol e WHERE e.sid=s.sid AND e.cid=r.cid));
Inner NOT EXISTS किसी required course का missing enrollment ढूँढता है। Outer NOT EXISTS ऐसे किसी missing course वाले student को हटाता है। Required={A,B}, Enrol={(1,A),(1,B),(2,A)}, Student={1,2,3} में केवल 1 पास है। Required empty हो तो तीनों पास। Repeated Enrol rows existence नहीं बदलतीं, जबकि naive count comparison बदल सकता है। Distinct matched requirements गिनना दूसरा तरीका हो सकता है, लेकिन candidate domain, duplicates और empty requirements संभालना जरूरी है।
सावधानियाँ
- COUNT(*) null-extended rows भी गिनता है; COUNT(non-null right key) matches गिनता है।
- AVG numerator तथा denominator दोनों में NULL inputs ignore करता है।
- Correlated query logically हर outer candidate से जुड़ी है, लेकिन optimizer implementation अलग हो सकता है।
- Equal match न मिले तो NULL, NOT IN को UNKNOWN कर सकता है।
- Empty requirement set पर universal condition दिए candidate domain में true है।
मौलिक अभ्यास: छह single-correct MCQ
ये मौलिक शिक्षण प्रश्न हैं, पिछले वर्षों के प्रश्न नहीं। ठीक एक उत्तर चुनें।
P01. x values 4,NULL,4,12 पर COUNT(*) और COUNT(x)?
A. (4,4) B. (4,3) C. (3,2) D. (3,3)
उत्तर: B
A: COUNT(x), NULL ignore करता है।
B: चार rows और तीन non-null x हैं।
C: COUNT(*) NULL या duplicates नहीं हटाता।
D: COUNT(*) चारों rows गिनता है।
P02. x values 10,NULL,20 पर AVG(x)?
A. 10 B. 15 C. 30 D. NULL
उत्तर: B
A: यह गलत तरह तीन से divide करता है।
B: NULL ignore होने से (10+20)/2=15।
C: तीस SUM है, average नहीं।
D: Non-null inputs मौजूद हैं, इसलिए average defined है।
P03. Aggregates के आधार पर groups कौन filter करता है?
A. WHERE in the same query block B. HAVING C. FROM D. ORDER BY
उत्तर: B
A: WHERE इस grouping से पहले rows filter करता है।
B: HAVING हर group के aggregate results जाँच सकता है।
C: FROM input rows बनाता है।
D: ORDER BY sort करता है, groups filter नहीं।
P04. SQL three-valued logic में 5 NOT IN (2,NULL) क्या है?
A. TRUE B. FALSE C. UNKNOWN D. Syntax error
उत्तर: C
A: NULL के कारण सभी comparisons unequal सिद्ध नहीं होते।
B: कोई equal non-null value NOT IN को false नहीं बनाती।
C: 5=2 false और 5=NULL unknown; IN negate करने पर भी unknown।
D: List में NULL allowed है।
P05. Empty subquery पर x>ALL(subquery) क्या है?
A. TRUE B. FALSE C. UNKNOWN for every x D. A cardinality error
उत्तर: A
A: Universal condition को violate करने वाला comparison नहीं।
B: यह empty ANY का result है, ALL का नहीं।
C: Zero rows पर कोई NULL comparison नहीं होता।
D: ALL किसी भी subquery row count को स्वीकार करता है।
P06. LEFT JOIN में student का enrollment match नहीं; असली rows में eid non-null है। उस student का COUNT(*) और COUNT(eid)?
A. (0,0) B. (1,1) C. (1,0) D. (0,1)
उत्तर: C
A: Outer join एक null-extended row बचाता है।
B: Extended eid NULL है, इसलिए COUNT(eid) zero।
C: एक preserved row है, non-null enrollment key नहीं।
D: उस group में non-null eid गिनते समय COUNT(*) zero नहीं हो सकता।
स्रोत और दायरा
यह 2026 course के बताए भाग का पाठ है, पूरे Databases syllabus की समाप्ति नहीं। सभी गणनात्मक उदाहरण और प्रश्न मौलिक हैं। 2027 की पूर्णता का दावा नहीं।
प्रगति सहेजने के लिए साइन इन करें। साइन इन