🖼️ See the drawing + lab 🏠 Course home 🌿 Branch on GitHub ✏️ View source

🖼️ आकृती आणि labThe drawing + lab पूर्ण पानावर उघडा ↗Open full page ↗

🔍 धडा 03 — SQL reads: दप्तरदाराला विचारणे

📍 तुम्ही इथे आहात: 18 पैकी धडा 03 · मागे: lesson-02-tables-rows-keys · पुढे: lesson-04-transactions

📦 या ब्रँचमध्ये काय आहे

धडे 01–02, आणि प्रश्नांची भाषा: SELECT, WHERE, ORDER BY, LIMIT, नोंदवह्यांमध्ये JOIN, आणि मोजण्यासाठी GROUP BY.

🧒 5 वर्षांच्या मुलाला समजावल्यासारखे

तुम्ही स्वतः कपाटांमध्ये फिरत नाही. तुम्ही दप्तरदाराला 🧑‍💼 प्रमाणित पद्धतीने लिहिलेला प्रश्न देता, आणि तो सर्वात जलद मार्ग ठरवतो:

"3A च्या विद्यार्थिनींची नावे आणि roll numbers, अकारविल्हे, पहिल्या दोन." SELECT name, roll_no FROM students WHERE class_id = 1 ORDER BY name LIMIT 2
"प्रत्येक maths grade, विद्यार्थिनीचे नाव आणि वर्ग सोबत." तीन नोंदवह्या, त्यांच्या keys ने एकमेकांशी जोडलेल्या — एक JOIN: FROM grades g JOIN students s ON s.id = g.student_id JOIN classes c ON c.id = s.class_id
"प्रत्येक वर्गात किती विद्यार्थी?" — एक GROUP BY: प्रत्येक वर्गासाठी उत्तराची एक ओळ, COUNT सह.

दप्तरदाराचा कामाचा क्रम नेहमी तोच असतो: आधी गाळणे (WHERE), मग गट करणे (GROUP BY), मग क्रम लावणे (ORDER BY), मग पाने (LIMIT/OFFSET) — API शाळेचा counter वापरतो तेच filter → sort → paginate, कारण counter याच दप्तरदाराला विचारतो.

🗺️ आकृती

flowchart LR
    q["❓ SELECT s.name, c.name, g.grade<br/>FROM grades g JOIN students s … JOIN classes c …<br/>WHERE g.subject = 'maths' ORDER BY g.grade"]
    f["1 WHERE — filter lines"]
    j["2 JOIN — cross-reference registers by key"]
    o["3 ORDER BY / LIMIT — sort, then the first few"]
    a["📄 ('Aishwarya','3A','A') ('Katrina','3A','A+') …"]
    q --> f --> j --> o --> a

❓ काय

SELECT columns FROM table — कोणती शीर्षके, कोणती नोंदवही. SELECT * शोधाशोधीसाठी आहे, programs साठी नाही (columns बदलतात).
WHERE rows गाळतो: =, <, IN (…), LIKE 'Ka%', IS NULL, AND/OR. NULL म्हणजे "अज्ञात": = NULL कधीच खरे नसते — IS NULL वापरा.
JOIN … ON नोंदवह्यांना key ने जोडतो — खाली JOIN कुटुंब पाहा.
GROUP BY rows चे गट करतो; COUNT, SUM, AVG, MAX त्यांचा सारांश देतात; HAVING गट गाळतो.
ORDER BY … LIMIT n OFFSET m — पाने. Cursor pagination (API शाळा L08) म्हणजे WHERE id > :last ORDER BY id LIMIT n.
SQL declarative आहे: तुम्ही काय ते सांगता; planner कसे ते निवडतो (धडा 06 तुम्हाला EXPLAIN QUERY PLAN ने त्याचा मार्ग दाखवतो).

🔗 JOIN कुटुंब — जोडीदार नसेल तेव्हा कोणत्या rows टिकतात?

python3 db/demo.py joins एक छोटा library_cards table बनवतो: ऐश्वर्या, कतरिना आणि मीरा यांच्याकडे cards आहेत, दीपिका आणि Rohan कडे नाहीत, आणि card 104 एका पाहुण्याचे आहे (student 9, शाळेतील विद्यार्थी नव्हे). मग तो students s … library_cards l ON l.student_id = s.id वर प्रत्येक प्रकारचा join चालवतो:

JOIN	काय ठेवतो	rows	खरा निकाल
`INNER JOIN`	दोन्ही बाजूंना जुळणाऱ्या जोड्याच	3	Aishwarya 101 · Katrina 102 · Meera 103
`LEFT JOIN`	प्रत्येक डावी row (विद्यार्थी); जोडीदार नाही → `NULL`	5	… + Dipika `NULL` · Rohan `NULL`
`RIGHT JOIN`	प्रत्येक उजवी row (cards); जोडीदार नाही → `NULL`	4	… + `NULL` 104
`FULL OUTER JOIN`	दोन्ही बाजूंचे सगळे	6	Dipika `NULL` · Rohan `NULL` · `NULL` 104 + 3 जोड्या
anti-join: `LEFT JOIN … WHERE l.card IS NULL`	जोडीदार नसलेल्या डाव्या rows	2	Dipika · Rohan
`CROSS JOIN` (`ON` नाही)	प्रत्येक संयोग	2 × 2 = 4	3A maths · 3A science · 3B maths · 3B science
self join: `students a JOIN students b ON a.class_id = b.class_id AND a.id < b.id`	table स्वतःशीच	4	Aishwarya–Katrina · Aishwarya–Dipika · Katrina–Dipika · Meera–Rohan

नुसता JOIN म्हणजे INNER JOIN; LEFT JOIN = LEFT OUTER JOIN.
RIGHT JOIN म्हणजे tables अदलाबदल केलेला LEFT JOIN — बहुतेक teams LEFT च लिहितात.
FULL OUTER JOIN ला SQLite 3.39+ लागते; MySQL मध्ये तो नाहीच (LEFT … UNION … RIGHT लिहा).
anti-join असाही लिहिला जातो: WHERE NOT EXISTS (SELECT 1 FROM library_cards l WHERE l.student_id = s.id).
जुळणी नसलेल्या डाव्या rows ठेवायच्या असतील, तर उजव्या table वरची अट WHERE मध्ये नव्हे तर ON मध्ये हवी — WHERE l.card > 101 गुपचूप LEFT JOIN ला पुन्हा INNER बनवतो.

🤔 का

कारण एका ओळीत लिहिता येणारा प्रश्न, तुम्हाला लिहावा, test करावा आणि सांभाळावा लागणाऱ्या program ची जागा घेतो. प्रत्येक dashboard, report आणि API endpoint म्हणजे वेशांतर केलेला SELECT — आणि त्या सर्वांमागे तीच चार clauses, त्याच क्रमाने असतात.

🔧 कसे (या repo मध्ये)

db/demo.py मधील reads(), join() आणि joins() म्हणजेच हा धडा; चालवण्याआधी SQL strings शाळेच्या शब्दांत मोठ्याने वाचा.

🧪 करून पाहा

python3 db/demo.py reads join joins
python3 - <<'EOF'
import sqlite3; c = sqlite3.connect("db/school.db")
print(c.execute("SELECT name FROM students WHERE name LIKE 'K%' OR roll_no IN ('3B-01')").fetchall())
print(c.execute("SELECT c.name, AVG(CASE g.grade WHEN 'A+' THEN 4.3 WHEN 'A' THEN 4 WHEN 'B+' THEN 3.3 WHEN 'B' THEN 3 ELSE 2 END) FROM grades g JOIN students s ON s.id=g.student_id JOIN classes c ON c.id=s.class_id GROUP BY c.name").fetchall())
print(c.execute("SELECT s.name, COUNT(g.id) FROM students s LEFT JOIN grades g ON g.student_id=s.id GROUP BY s.id HAVING COUNT(g.id) >= 2").fetchall())
EOF

✅ तपासा — तुम्हाला काय दिसायला हवे

reads अकारविल्हे क्रमाने 3A च्या दोन विद्यार्थिनी छापतो; join आधी grade आणि मग नावानुसार लावलेले पाच maths grades छापतो, आणि ('3A', 3) व ('3B', 2) असलेला एक GROUP BY. joins INNER साठी 3 rows, LEFT साठी 5 (Dipika आणि Rohan None सह), RIGHT साठी 4 ((None, 104)), FULL OUTER साठी 6, anti-join साठी Dipika आणि Rohan, 4 timetable जोड्या आणि 4 study-buddy जोड्या छापतो. तुमच्या तीन queries Katrina आणि Meera, प्रत्येक वर्गाची सरासरी, आणि दोन किंवा जास्त grades असलेला प्रत्येक विद्यार्थी छापतात.

🏁 तुम्ही आत्ताच काय सिद्ध केले

तुम्ही दप्तरदाराला गाळलेले, जोडलेले, गट केलेले आणि पाने पाडलेले प्रश्न विचारू शकता — आणि JOIN ला "या नोंदवह्या त्यांच्या keys ने जोडा" असे वाचू शकता.

⚠️ नेहमीच्या चुका

application code मध्ये SELECT * — नवा column गुपचूप तुमच्या program चे input बदलतो
WHERE grade = NULL — नेहमीच false; IS NULL वापरा
ON अट विसरणे — cross join प्रत्येक row ला प्रत्येक row ने गुणतो
LEFT JOIN नंतर उजव्या table वर WHERE — तुम्ही ज्या NULL rows ठेवण्यासाठी join केले त्याच तो फेकून देतो
report मध्ये सर्वांची यादी हवी असताना INNER JOIN — जुळणी नसलेले विद्यार्थी गुपचूप गायब होतात
मोठ्या table वर LIMIT शिवाय ORDER BY (किंवा ORDER BY शिवाय LIMIT — "कशाचे पहिले 10?")

🏭 प्रत्यक्ष वापरात हे का महत्त्वाचे: हळू pages बहुतेक वेळा एकाच query मुळे असतात — WHERE नसलेली, key शिवाय JOIN, किंवा index नसलेल्या column वर ORDER BY — चार clauses माहीत असल्या की हे सगळे SQL मध्येच वाचता येते.

⏭️ पुढे

वाचणे सुरक्षित आहे; लिहिण्यासाठी खोडरबर असलेली pencil लागते. Writes आणि transactions — INSERT, UPDATE, DELETE, आणि ACID.

git checkout lesson-04-transactions

🔍 Lesson 03 — SQL reads: asking the archivist

📍 You are here: Lesson 03 of 18 · Previous: lesson-02-tables-rows-keys · Next: lesson-04-transactions

📦 What's in this branch

Lessons 01–02, plus the question language: SELECT, WHERE, ORDER BY, LIMIT, JOIN across registers, and GROUP BY for counting.

🧒 Explain like I'm 5

You do not walk the shelves yourself. You hand the archivist 🧑‍💼 a question written the standard way, and they decide the fastest route:

"Names and roll numbers of 3A students, alphabetical, first two." SELECT name, roll_no FROM students WHERE class_id = 1 ORDER BY name LIMIT 2
"Every maths grade, with the student's name and class." Three registers, cross-referenced by their keys — a JOIN: FROM grades g JOIN students s ON s.id = g.student_id JOIN classes c ON c.id = s.class_id
"How many students per class?" — a GROUP BY: one answer line per class, with a COUNT.

The archivist's order of work is always the same: filter (WHERE), then group (GROUP BY), then order (ORDER BY), then page (LIMIT/OFFSET) — the same filter → sort → paginate the API school's counter uses, because the counter asks this archivist.

🗺️ Diagram

flowchart LR
    q["❓ SELECT s.name, c.name, g.grade<br/>FROM grades g JOIN students s … JOIN classes c …<br/>WHERE g.subject = 'maths' ORDER BY g.grade"]
    f["1 WHERE — filter lines"]
    j["2 JOIN — cross-reference registers by key"]
    o["3 ORDER BY / LIMIT — sort, then the first few"]
    a["📄 ('Aishwarya','3A','A') ('Katrina','3A','A+') …"]
    q --> f --> j --> o --> a

❓ What

SELECT columns FROM table — which headings, which register. SELECT * is for exploring, not for programs (columns change).
WHERE filters rows: =, <, IN (…), LIKE 'Ka%', IS NULL, AND/OR. NULL is "unknown": = NULL is never true — use IS NULL.
JOIN … ON combines registers by key — see the JOIN family below.
GROUP BY collapses rows into groups; COUNT, SUM, AVG, MAX summarise them; HAVING filters groups.
ORDER BY … LIMIT n OFFSET m — pages. Cursor pagination (API school L08) is WHERE id > :last ORDER BY id LIMIT n.
SQL is declarative: you say what; the planner picks how (lesson 06 shows you its route with EXPLAIN QUERY PLAN).

🔗 The JOIN family — which rows survive when a partner is missing?

python3 db/demo.py joins builds a tiny library_cards table: Aishwarya, Katrina and Meera have cards, Dipika and Rohan have none, and card 104 belongs to a visitor (student 9, not a pupil). Then it runs every kind of join on students s … library_cards l ON l.student_id = s.id:

JOIN	keeps	rows	real result
`INNER JOIN`	only pairs that match on both sides	3	Aishwarya 101 · Katrina 102 · Meera 103
`LEFT JOIN`	every left row (pupils); no partner → `NULL`	5	… + Dipika `NULL` · Rohan `NULL`
`RIGHT JOIN`	every right row (cards); no partner → `NULL`	4	… + `NULL` 104
`FULL OUTER JOIN`	everything from both sides	6	Dipika `NULL` · Rohan `NULL` · `NULL` 104 + the 3 pairs
anti-join: `LEFT JOIN … WHERE l.card IS NULL`	left rows with no partner	2	Dipika · Rohan
`CROSS JOIN` (no `ON`)	every combination	2 × 2 = 4	3A maths · 3A science · 3B maths · 3B science
self join: `students a JOIN students b ON a.class_id = b.class_id AND a.id < b.id`	a table against itself	4	Aishwarya–Katrina · Aishwarya–Dipika · Katrina–Dipika · Meera–Rohan

JOIN alone means INNER JOIN; LEFT JOIN = LEFT OUTER JOIN.
RIGHT JOIN is a LEFT JOIN with the tables swapped — most teams just write LEFT.
FULL OUTER JOIN needs SQLite 3.39+; MySQL has none (write LEFT … UNION … RIGHT).
The anti-join is also written WHERE NOT EXISTS (SELECT 1 FROM library_cards l WHERE l.student_id = s.id).
A condition on the right table belongs in ON, not WHERE, if you want to keep the unmatched left rows — WHERE l.card > 101 quietly turns a LEFT JOIN back into an INNER one.

🤔 Why

Because a question you can write in one line replaces a program you would have to write, test and keep. Every dashboard, report and API endpoint is a SELECT in a costume — and the same four clauses, in the same order, are behind all of them.

🔧 How (in this repo)

reads(), join() and joins() in db/demo.py are this lesson; read the SQL strings aloud in school words before you run them.

🧪 Try it

python3 db/demo.py reads join joins
python3 - <<'EOF'
import sqlite3; c = sqlite3.connect("db/school.db")
print(c.execute("SELECT name FROM students WHERE name LIKE 'K%' OR roll_no IN ('3B-01')").fetchall())
print(c.execute("SELECT c.name, AVG(CASE g.grade WHEN 'A+' THEN 4.3 WHEN 'A' THEN 4 WHEN 'B+' THEN 3.3 WHEN 'B' THEN 3 ELSE 2 END) FROM grades g JOIN students s ON s.id=g.student_id JOIN classes c ON c.id=s.class_id GROUP BY c.name").fetchall())
print(c.execute("SELECT s.name, COUNT(g.id) FROM students s LEFT JOIN grades g ON g.student_id=s.id GROUP BY s.id HAVING COUNT(g.id) >= 2").fetchall())
EOF

✅ Verify — what you should see

reads prints two 3A students in alphabetical order; join prints five maths grades sorted by grade then name, and a GROUP BY with ('3A', 3) and ('3B', 2). joins prints 3 rows for INNER, 5 for LEFT (Dipika and Rohan with None), 4 for RIGHT ((None, 104)), 6 for FULL OUTER, Dipika and Rohan for the anti-join, 4 timetable pairs and 4 study-buddy pairs. Your three queries print Katrina and Meera, an average per class, and every student with two or more grades.

🏁 What you just proved

You can ask the archivist filtered, joined, grouped and paged questions — and read a JOIN as "cross-reference these registers by their keys".

⚠️ Common mistakes

SELECT * in application code — a new column silently changes your program's input
WHERE grade = NULL — always false; use IS NULL
forgetting the ON condition — a cross join multiplies every row by every row
a WHERE on the right table after a LEFT JOIN — it throws away the NULL rows you joined to keep
INNER JOIN when the report must list everyone — pupils without a match silently vanish
ORDER BY without LIMIT on a huge table (or LIMIT without ORDER BY — "first 10 of what?")

🏭 Why this matters in production: slow pages are almost always one query with a missing WHERE, a JOIN without a key, or an ORDER BY on an unindexed column — all readable in the SQL once you know the four clauses.

⏭️ Next

Reading is safe; writing needs a pencil with an eraser. Writes and transactions — INSERT, UPDATE, DELETE, and ACID.

git checkout lesson-04-transactions

← Previoustables rows keys Next →transactions

This page is the lesson's README from the lesson-03-sql-reads branch, shown here so the whole School stays on one site. Code files open on GitHub at the same branch.