🗄️ शाळेच्या पद्धतीने डेटाबेस शिका

# the 60-second wow — every lesson's queries, live:
git clone https://github.com/BaluRaut/learn-database-school.git && cd learn-database-school
python3 db/demo.py                 # builds school.db and runs reads, joins, a rollback, an index race, a lock, a restore…
python3 db/migrate.py              # renovates the room: three migrations, once each

तुम्हाला काय दिसायला हवे (छाटलेले — यश असे दिसते):

🗄️  school.db built from schema.sql + seed.sql

═══ reads ═══
── SELECT … WHERE … ORDER BY … LIMIT
    ('Aishwarya', '3A-01')
    ('Dipika', '3A-03')

═══ join ═══
── JOIN — cross-referencing three registers
    ('Aishwarya', '3A', 'maths', 'A')
    ('Meera', '3B', 'maths', 'A')
    ('Katrina', '3A', 'maths', 'A+')
    ('Rohan', '3B', 'maths', 'B')
    ('Dipika', '3A', 'maths', 'B+')

── GROUP BY — one row per class
    ('3A', 3)
    ('3B', 2)

═══ joins ═══
── INNER JOIN — only pupils WITH a card (matches on both sides)
    ('Aishwarya', 101)
    ('Katrina', 102)
    ('Meera', 103)

── LEFT JOIN — every pupil; no card → NULL
    ('Aishwarya', 101)
    ('Katrina', 102)
    ('Dipika', None)
    ('Meera', 103)
    ('Rohan', None)

── RIGHT JOIN — every card; a card with no pupil → NULL name
    ('Aishwarya', 101)
    ('Katrina', 102)
    ('Meera', 103)
    (None, 104)

── FULL OUTER JOIN — everyone from both sides
    ('Aishwarya', 101)
    ('Katrina', 102)
    ('Dipika', None)
    ('Meera', 103)
    ('Rohan', None)
    (None, 104)

── anti-join (LEFT JOIN … WHERE card IS NULL) — pupils with NO card
    ('Dipika',)
    ('Rohan',)

── CROSS JOIN — every class × every subject (the timetable grid)
    ('3A', 'maths')
    ('3A', 'science')
    ('3B', 'maths')
    ('3B', 'science')

── self join — two pupils in the same class (study buddies)
    ('Aishwarya', 'Katrina')
    ('Aishwarya', 'Dipika')
    ('Katrina', 'Dipika')
    ('Meera', 'Rohan')

═══ txn ═══
── before the transfer
    ('Aishwarya', 1)
    ('Meera', 2)

   💥 second update failed: FOREIGN KEY constraint failed → ROLLBACK

── after — the pencil was erased, BOTH changes gone
    ('Aishwarya', 1)
    ('Meera', 2)

═══ model ═══
── one fact, one place — the class name lives in ONE row
    (1, '3A')
    (2, '3B')

   💥 duplicate roll_no refused by the schema: UNIQUE constraint failed: students.roll_no 

   💥 impossible grade refused by CHECK: CHECK constraint failed: grade IN ('A+','A','B+','B','C') 

═══ index ═══
── without an index: SCAN attendance → 48 rows in 2.5 ms
── with the card catalogue: SEARCH attendance USING COVERING INDEX idx_attendance_student (student_id=?) → 48 rows in 0.0 ms

═══ locks ═══
── clerk 1 reads while clerk 2's change is uncommitted (isolation)
    (2, 'Katrina')

   ⏳ clerk 1 waited, then: database is locked — the lock protects the register

═══ backup ═══
── after the accident — Rohan is gone
    (1, 'Aishwarya')
    (2, 'Katrina')
    (3, 'Dipika')
    (4, 'Meera')

── after the restore — from the copy we tested
    (1, 'Aishwarya')
    (2, 'Katrina')
    (3, 'Dipika')
    (4, 'Meera')
    (5, 'Rohan')

═══ nplus1 ═══
── N+1: 6 queries, 0.11 ms · one JOIN: 1 query, 0.04 ms — same answer [('Aishwarya', 2), ('Katrina', 2)]…

═══ inject ═══
── the glued query the database actually received:
    SELECT name FROM students WHERE roll_no = 'x' OR '1'='1'
── ❌ string-glued login — every student leaks
    ('Aishwarya',)
    ('Katrina',)
    ('Dipika',)
    ('Meera',)
    ('Rohan',)

── ✅ parameterised (?) — the input is only ever a value
    (no rows)

── the reporting app's read-only connection reads: 5 students
   🔒 …and cannot write: attempt to write a readonly database 

═══ window ═══
── CTE + window: RANK() and a running total inside each class
    ('3A', 'Katrina', 20, 1, 20)
    ('3A', 'Aishwarya', 17, 2, 37)
    ('3A', 'Dipika', 15, 3, 52)
    ('3B', 'Meera', 18, 1, 18)
    ('3B', 'Rohan', 13, 2, 31)

── subquery: above the school average (16.6)
    ('Katrina', 20)
    ('Meera', 18)
    ('Aishwarya', 17)

── VIEW report_card — a saved question, used like a table
    ('Katrina', 2, 20)
    ('Meera', 2, 18)
    ('Aishwarya', 2, 17)

═══ procs ═══
── SQLite has no stored procedures (it runs inside your app); db/postgres/procedures.sql is the Postgres version.
   The closest SQLite pieces: a function the SQL can call, and a rule the room enforces for every app.

── points(grade) — a function used inside SQL (Postgres: CREATE FUNCTION points)
    ('Katrina', 20)
    ('Meera', 18)
    ('Aishwarya', 17)

   ⛔ Meera → 3A: class is full (3 pupils) — refused by the room itself, whichever app asked
   ✅ Aishwarya → 3B: moved

═══ wal ═══
── journal mode before: delete → after: wal
   ✍️  writer committed 'Aishwarya S' while the reader was mid-transaction — no 'database is locked'
   👀 reader, same transaction, still sees its snapshot: 'Aishwarya' (was 'Aishwarya')
   👀 reader, new transaction: 'Aishwarya S'
   📼 school.db-wal holds the new pages first: 4,152 bytes
   🧹 checkpoint copies them into school.db: -wal now 0 bytes
── journal mode back to: delete 

═══ replica ═══
── (simulated: school.db is the primary, an in-memory copy is the replica, a Python list is the shipped log)
── primary: 6 students · replica, before the log arrives: 5 — replication lag
   ↳ a parent who just enrolled Diya and reloads from the replica sees NO Diya: read your own writes from the primary
── log shipped → replica: 6 students
   💥 the primary dies before shipping Ishaan's line → promote the replica (failover)
── new primary has 6 students: Ishaan is LOST — async replication can lose the last writes (RPO > 0);
   synchronous replication waits for the replica's 'got it' before COMMIT returns, so nothing is lost — each commit pays a round trip

═══ appcode ═══
── 2,000 inserts: commit per row 459 ms · one transaction 1 ms — every COMMIT is a trip to the disk
   ⏳ attempt 1: database is locked → retry in 50 ms (backoff)
   ⏳ attempt 2: database is locked → retry in 100 ms (backoff)
   ✅ attempt 3: done

═══ olap ═══
── report on the live 200,000-line table: 43.6 ms · on the nightly summary (12 rows): 0.04 ms
   same answer: True → [('2026-01', 49.7), ('2026-02', 50.0), ('2026-03', 50.5)]
── CDC: a trigger records every grade change for the warehouse to pick up
    (9, 'B', 'A')

✅ done — the record room survived every lesson

🗺️ संपूर्ण चित्र — एक आकृती, पूर्ण कोर्स

संपूर्ण कोर्स एका कॅनव्हासवर. 4K आवृत्तीसाठी क्लिक करा.

📇 भाग 1 — रेकॉर्ड रूम (धडे 1–6)

एक git ब्रँच = एक कल्पना; ब्रँच 04 मध्ये धडे 01–04 आहेत. लॅब साध्या Python 3 वर चालतात — डेटाबेस server स्थापायची गरज नाही.

🏃 भाग 2 — रेकॉर्ड रूम खऱ्या जगात चालवणे (धडे 7–12)

production डेटाबेसला लागणारे सगळे, प्रत्येक गोष्ट त्याच छोट्या शाळेच्या डेटाबेसवर दाखवलेली.

🔬 भाग 3 — अधिक खोलात (धडे 13–18)

interviews आणि incidents मध्ये विचारले जाणारे विषय: सुरक्षा, advanced SQL, log आणि MVCC, replication आणि failover, app ची बाजू, आणि analytics — प्रत्येक school.db वर प्रत्यक्ष चालवलेला.

📐 धड्यांच्या आकृत्या — क्रमांक अनुसरा

प्रत्येक धडा एका क्रमांकित बॉक्स-आणि-बाण आकृतीत — स्वतंत्र पानावरही.