SQL JOIN, GROUP BY and HAVING: Worked Examples | DBMS Part 3

DBMS Part 3: SQL queries ko result ke saath samjho. Part 2 mein ER model se Student-Course design banaya. Ab isi design par SQL likhenge. Examples PostgreSQL-style hain; apne DBMS version ke syntax ko check karo. Yeh general exam notes hain, official syllabus nahi.

1. SQL kya hai? DDL, DML aur query

Definition: Structured Query Language (SQL) relational databases mein data structures define karne, data insert/update/delete karne aur data retrieve karne ki language hai. SQL query result rows ka set (ya bag, duplicate rows allowed unless DISTINCT) banati hai; bina ORDER BY row order guaranteed nahi.

Hinglish: DDL (Data Definition Language) table ka shape banata hai, jaise CREATE TABLE. DML (Data Manipulation Language) data badalta hai, jaise INSERT, UPDATE, DELETE. SELECT data read karta hai; textbooks mein ise DQL bhi bolte hain, lekin ye naming convention hai. Production data par UPDATE/DELETE se pehle WHERE aur affected rows check karo.

2. Worked dataset: Students, Courses, Enrollment

Students: (101, Asha, 3), (102, Ravi, 3), (103, Meera, 5). Courses: (10, DBMS), (20, OS), (30, Networks). Enrollment: (101, 10, 82), (101, 20, 74), (102, 10, 68). Columns respectively Students(id,name,sem), Courses(id,title), Enrollment(student_id,course_id,marks). Meera ka koi enrollment nahi; Networks mein bhi abhi koi student nahi.

CREATE TABLE Students (id INTEGER PRIMARY KEY, name VARCHAR(80), sem INTEGER);
CREATE TABLE Courses (id INTEGER PRIMARY KEY, title VARCHAR(80));
CREATE TABLE Enrollment (
  student_id INTEGER REFERENCES Students(id),
  course_id INTEGER REFERENCES Courses(id),
  marks INTEGER,
  PRIMARY KEY (student_id, course_id)
);

Populate the empty tables before running the queries. Run once in a new practice database:

INSERT INTO Students VALUES (101,'Asha',3),(102,'Ravi',3),(103,'Meera',5);
INSERT INTO Courses VALUES (10,'DBMS'),(20,'OS'),(30,'Networks');
INSERT INTO Enrollment VALUES (101,10,82),(101,20,74),(102,10,68);

Why keys? Primary key duplicate student/course row identity rokta hai; Enrollment ke foreign keys missing IDs ko reject karte hain. Real design mein names aur marks ke NOT NULL/CHECK constraints business rule ke hisaab se add karoge.

3. SELECT, WHERE, ORDER BY aur DISTINCT

SELECT definition: query expressions se columns/rows choose karta hai. WHERE rows filter karta hai; ORDER BY output order decide karta hai; DISTINCT duplicate result rows hatata hai. Aliases output ko readable banate hain.

SELECT name, sem FROM Students WHERE sem = 3 ORDER BY name;
-- Result: Asha | 3, then Ravi | 3
SELECT DISTINCT course_id FROM Enrollment ORDER BY course_id;
-- Result: 10, then 20

Common trap: NULL unknown/missing ko indicate karta hai; WHERE marks = NULL match nahi karega. WHERE marks IS NULL use karo. ORDER BY na ho to example input order ko output guarantee mat samjho.

4. INNER JOIN aur LEFT JOIN with actual output

JOIN definition: do relations ke rows ko matching condition se combine karta hai. INNER JOIN sirf matched combinations deta hai. LEFT JOIN left table ki unmatched rows bhi rakhta hai aur right columns NULL hoti hain.

SELECT s.name, c.title, e.marks
FROM Students s
JOIN Enrollment e ON e.student_id = s.id
JOIN Courses c ON c.id = e.course_id
ORDER BY s.name, c.title;
-- Asha | DBMS | 82
-- Asha | OS   | 74
-- Ravi | DBMS | 68

Meera missing hai kyunki inner join mein uska Enrollment match nahi. Agar sab students chahiye:

SELECT s.name, c.title
FROM Students s
LEFT JOIN Enrollment e ON e.student_id = s.id
LEFT JOIN Courses c ON c.id = e.course_id
ORDER BY s.name, c.title;
-- Asha | DBMS
-- Asha | OS
-- Meera | NULL
-- Ravi | DBMS

Point	INNER JOIN	LEFT JOIN
Rows	Only matching pairs	All left rows, matched right rows
Unmatched student	Meera absent	Meera appears with NULL title
Use	Only enrollment report	All-student report

5. GROUP BY, aggregate aur HAVING

Aggregate: COUNT, AVG etc. group par result calculate karte hain. GROUP BY shared key ki rows ek group mein rakhta hai. WHERE grouping se pehle rows filter karta hai; HAVING group banne ke baad aggregate condition lagata hai.

SELECT c.title, COUNT(e.student_id) AS enrolled
FROM Courses c
LEFT JOIN Enrollment e ON e.course_id = c.id
GROUP BY c.id, c.title
ORDER BY c.title;
-- DBMS | 2; Networks | 0; OS | 1

COUNT(e.student_id) NULL ko ignore karta hai, isliye Networks 0. Is LEFT JOIN par COUNT(*) Networks ke liye 1 de sakta hai, kyunki unmatched course row bhi output mein hai. HAVING COUNT(e.student_id) >= 2 lagao to sirf DBMS bachega.

6. INSERT, UPDATE, DELETE: safe example

INSERT INTO Enrollment (student_id, course_id, marks) VALUES (103, 20, 91);
UPDATE Enrollment SET marks = 92 WHERE student_id = 103 AND course_id = 20;
DELETE FROM Enrollment WHERE student_id = 103 AND course_id = 20;

Ye teen statements is order mein ek sequence hain: INSERT ke baad row banti hai, UPDATE usi row ko badalta hai, DELETE usi row ko hata deta hai. Missing WHERE se bahut saari rows badal sakti hain. Constraint aur transaction behavior aage ke parts mein aayega.

7. Query ka logical flow: join se final output tak

FROM / JOIN - input rows aur matching combinations
↓
WHERE - individual rows ka filter
↓
GROUP BY / aggregates - groups aur calculations
↓
HAVING - groups ka filter
↓
SELECT / DISTINCT - output expressions aur duplicate removal
↓
ORDER BY - final output ordering

Yeh logical teaching model hai, physical execution plan nahi. Optimizer actual operations ko reorganize kar sakta hai while preserving query semantics. SELECT alias ko same query ke WHERE mein use karne ka assumption mat karo: WHERE logically output expressions se pehle filter karta hai.

8. LEFT JOIN filter trap: ON vs WHERE

Question: har student dikhao, par sirf marks ≥80 wale enrollment match karo. Asha ka DBMS82 match hai. Ravi68 qualifies nahi; Meera ka row nahi. ON condition matching decide karti hai, isliye Ravi aur Meera preserved NULL-extended rows bante hain. WHERE condition joined result par lagti hai: NULL ≥80 unknown hai, aur WHERE sirf true rows rakhta hai.

ON keeps unmatched students

SELECT s.name, e.course_id, e.marks FROM Students s LEFT JOIN Enrollment e ON e.student_id=s.id AND e.marks>=80 ORDER BY s.name, e.course_id;

Tested result, in query order:

Asha | 10 | 82
Meera | NULL | NULL
Ravi | NULL | NULL

WHERE removes unmatched students

SELECT s.name, e.course_id, e.marks FROM Students s LEFT JOIN Enrollment e ON e.student_id=s.id WHERE e.marks>=80 ORDER BY s.name, e.course_id;

Tested result, in query order:

Asha | 10 | 82

9. Missing rows: NOT EXISTS ko trace karo

Meera ke liye correlated subquery koi row return nahi karti, so NOT EXISTS true. Name ke badle stable student ID se relation match karo. NOT IN ka subquery NULL la sakta ho to result unknown ho sakta hai; NOT EXISTS is missing-link task mein clearer hai. Dono ka universal interchange assume mat karo.

Find students without enrollment

SELECT s.name FROM Students s WHERE NOT EXISTS (SELECT 1 FROM Enrollment e WHERE e.student_id=s.id) ORDER BY s.name;

Tested result, in query order:

Meera

10. COUNT, SUM aur AVG: NULL-aware output

Networks ka left join ek unmatched row banata hai. COUNT(*) us row ko count karta hai; COUNT(e.student_id) NULL ko ignore karta hai. Real known zero value count aur average mein included hai; unknown NULL excluded hai. SUM/AVG empty input par NULL ho sakte hain, zero nahi. COALESCE ko sirf jab business meaning zero justify kare tab use karo.

Count rows versus non-null matches

SELECT c.title, COUNT(*) AS joined_rows, COUNT(e.student_id) AS enrolled FROM Courses c LEFT JOIN Enrollment e ON e.course_id=c.id GROUP BY c.id,c.title ORDER BY c.title;

Tested result, in query order:

DBMS | 2 | 2
Networks | 1 | 0
OS | 1 | 1

NULL is not zero

SELECT COUNT(*) AS rows_seen, COUNT(marks) AS known_marks, SUM(marks) AS total_marks, AVG(marks) AS average_known FROM (SELECT 80 AS marks UNION ALL SELECT NULL UNION ALL SELECT 0) t;

Tested result, in query order:

3 | 2 | 80 | 40.0

11. HAVING aur grouping key

DBMS ke2enrollments retain hote hain. Course ID ke saath title group karna identity ko clear rakhta hai; do different course IDs ka same title ho to title-only grouping unhe merge kar sakti hai. Portable rule: SELECT ka non-aggregated column GROUP BY mein rakho, unless engine ke documented functional-dependency exception par deliberately rely kar rahe ho.

HAVING selects groups

SELECT c.title, COUNT(e.student_id) AS enrolled FROM Courses c LEFT JOIN Enrollment e ON e.course_id=c.id GROUP BY c.id,c.title HAVING COUNT(e.student_id)>=2 ORDER BY c.title;

Tested result, in query order:

DBMS | 2

12. Point-by-point distinctions and exam practice

Pair	First	Second	Trap
WHERE / HAVING	Filters input rows	Filters groups	Aggregate predicate belongs after grouping
ON / WHERE with LEFT JOIN	Controls right-side matches	Filters joined rows	Right-side WHERE can discard preserved unmatched rows
COUNT(*) / COUNT(column)	All input rows	Only non-NULL column values	Outer join placeholder counts as a row
NULL / zero	Unknown or missing	Known numeric value	Replacing NULL with0changes average meaning
DISTINCT / GROUP BY	Removes duplicate output rows	Forms groups for calculations	Neither guarantees ordering

1. Why Ravi survives ON marks≥80? LEFT JOIN preserves his Student row even though no Enrollment satisfies that match condition.
2. Why Ravi disappears with WHERE marks≥80? His joined68fails the predicate; unmatched NULL also is not true.
3. Does COUNT(*) count Meera after a left join? Yes, her NULL-extended row is still an input row. COUNT(e.student_id) returns0for her group.
4. Average of80,NULL,0?40, because two known values80and0are averaged; not80/3.
5. Can courses with same title merge? Yes if grouping only by title; group by course identity when distinct courses must stay separate.
6. Does DISTINCT sort? No guaranteed order. Add ORDER BY for ordered output.
7. Can an INNER JOIN duplicate a student's name? Yes, one output row per matching enrollment. Asha appears twice in the base join.
8. Is this logical flow the optimizer plan? No. Use the DBMS EXPLAIN facility for its physical plan; timing and plan depend on data/indexes.

Test scope: complete schema/population, existing SELECT/JOIN/GROUP examples, mutation sequence and six additional queries were run on SQLite3 with foreign_keys=ON. Syntax is PostgreSQL-style as stated above; PostgreSQL semantics were checked against official documentation, not claimed as a PostgreSQL runtime test. Try these only in a scratch database, never blindly on production data.

Practice questions: answered

1. WHERE aur HAVING mein farq? WHERE individual rows ko grouping se pehle filter karta hai; HAVING grouped result ko aggregate condition se filter karta hai.
2. INNER vs LEFT JOIN ka output? Example mein inner sirf Asha/Ravi enrollments, left mein Meera with NULL title bhi.
3. Networks enrollment count 0 kaise? Courses se LEFT JOIN aur COUNT(e.student_id); NULL student_id count nahi hota.
4. NULL check syntax? IS NULL ya IS NOT NULL, na ki = NULL.

Five-minute revision

CREATE schema; INSERT/UPDATE/DELETE rows badalte hain. SELECT columns/rows, WHERE row filter, ORDER BY ordering, DISTINCT duplicate output removal. INNER matched rows; LEFT sab left rows. GROUP BY grouping, HAVING group filter. COUNT(column) NULL ignore karta hai. Queries ko dataset ke against haath se trace karo.

Sources: PostgreSQL SQL tutorial, PostgreSQL join examples, PostgreSQL aggregate functions. Next: relational algebra aur SQL se uska connection.

DBMS Exam Notes series: Previous: Part 2: ER Model | DBMS series and hub | Next: Part 4: Relational Algebra