SQL DML — SELECT, INSERT, UPDATE, DELETE — Hinglish notes

4.4.4 · Coding › Databases

DML KYA hai?

WHY yeh split matter karta hai: DML statements (kuch setups ke alawa) ek transaction ke andar chalte hain, isliye inhe ROLLBACK kiya ja sakta hai. Ek galat UPDATE recover ho sakta hai agar tumne abhi commit nahi kiya hai — yahi tumhara safety net hai.

Woh table jo hum use karenge

Har example ke liye hum ek hi table use karte hain:

CREATE TABLE employees (
  id     INT PRIMARY KEY,
  name   VARCHAR(50),
  dept   VARCHAR(20),
  salary INT
);

Figure — SQL DML — SELECT, INSERT, UPDATE, DELETE

1. INSERT — rows add karo

INSERT INTO employees (id, name, dept, salary)
VALUES (1, 'Asha', 'Sales', 50000);

WHY columns ka naam kyun dein? Agar tum INSERT INTO employees VALUES (...) bina column names ke likhte ho, toh values table ki column order se bilkul exactly aur completely match karni chahiye. Columns ka naam dene se yeh order-independent aur future-proof ban jaata hai (agar baad mein koi column add kare, tumhara statement phir bhi kaam karega).

Worked example Multi-row insert

INSERT INTO employees (id, name, dept, salary) VALUES
  (2, 'Ben',  'Eng',   70000),
  (3, 'Cira', 'Eng',   90000),
  (4, 'Dia',  'Sales', 40000);

Ye step kyun? Ek statement, multiple value-tuples → DB ke saath kam round-trips aur yeh atomic hai (ya toh teeno insert honge, ya koi nahi).

2. SELECT — rows padhna

Worked example WHERE se filtering

SELECT name, salary
FROM employees
WHERE dept = 'Eng' AND salary > 60000;

Ye step kyun? WHERE har row pe ek boolean predicate evaluate karta hai; jinke liye woh TRUE hai woh survive karti hain. Hamare data se → Ben 70000, Cira 90000.

Worked example GROUP BY / HAVING se aggregation

SELECT dept, AVG(salary) AS avg_pay, COUNT(*) AS n
FROM employees
GROUP BY dept
HAVING COUNT(*) >= 2;

Ye step kyun? GROUP BY dept ek dept ki saari rows ko ek bucket mein collapse karta hai; aggregate functions (AVG, COUNT) har bucket ko summarize karte hain. HAVING buckets filter karta hai (yeh grouping ke baad chalta hai), jabki WHERE rows filter karta hai (grouping se pehle). Result: Sales avg 45000 (n=2), Eng avg 80000 (n=2).

Worked example Sort + top N

SELECT name, salary FROM employees
ORDER BY salary DESC
LIMIT 2;

Ye step kyun? ORDER BY salary DESC high→low sort karta hai, LIMIT 2 top do rakhhta hai → Cira 90000, Ben 70000.

3. UPDATE — existing rows badalna

HOW yeh kaam karta hai: har us row ke liye jahan predicate TRUE hai, SET assignments apply hoti hain. Right side current value reference kar sakta hai (salary * 1.10 = 10% raise). Eng rows 70000→77000, 90000→99000. Sales rows untouched.

Common mistake Databases ki sabse mehengi typo

Galat soch: "Main WHERE baad mein add kar lunga." Phir tum likhte ho:

UPDATE employees SET salary = 0;   -- 😱 no WHERE

Kyun sahi lagta hai: yeh statement syntactically valid hai aur bina error ke chal jaata hai — DB tumhe warn nahi karta. Nuksan: WHERE ke bina UPDATE/DELETE har row ko hit karta hai. Fix:

WHERE pehle likho, SET se pehle.
Pehle usi WHERE ko SELECT se test karo (wahi rows badlengi jo change hongi).
Transaction ke andar chalao: BEGIN; ... ; ROLLBACK; preview ke liye, phir tabhi COMMIT karo jab sure ho.

4. DELETE — rows hatana

Forecast-then-Verify exercise

Recall Pehle final table predict karo, PHIR check karo

4 inserted rows se shuru karke, is order mein chalao:

UPDATE employees SET salary = salary + 5000 WHERE dept = 'Sales';
DELETE FROM employees WHERE salary < 60000;
SELECT name, salary FROM employees ORDER BY name;

Pehle predict karo. … Ab verify karo:

Sales raised: Asha 55000, Dia 45000.
salary < 60000 delete karta hai Asha(55000), Dia(45000) → gone. Ben 70000, Cira 90000 rehte hain.
Final SELECT → Ben 70000, Cira 90000. Kyun: UPDATE ne values pehle change kiye, toh DELETE filter ne nayi salaries dekhi.

Active Recall

Chaar core DML statements kya hain?

SELECT (read), INSERT (add), UPDATE (modify), DELETE (remove).

DML vs DDL?

DML data/rows change karta hai (SELECT/INSERT/UPDATE/DELETE); DDL schema/structure change karta hai (CREATE/ALTER/DROP).

UPDATE ya DELETE bina WHERE clause ke kya affect karta hai?

Table ki har row ko.

SELECT-list alias ko WHERE ke andar kyun use nahi kar sakte?

WHERE logically SELECT se pehle evaluate hota hai, isliye alias tab tak exist nahi karta.

WHERE aur HAVING mein fark?

WHERE individual rows ko grouping se pehle filter karta hai; HAVING groups ko GROUP BY/aggregation ke baad filter karta hai.

SELECT query ka logical execution order?

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

DELETE vs TRUNCATE?

DELETE filtered rows remove karta hai, logged aur rollback-able hai; TRUNCATE saari rows fast remove karta hai aur usually rollback-able nahi hota.

INSERT mein columns ka naam kyun dein?

Order-independent banata hai aur baad mein schema changes ke baad bhi survive karta hai.

Ek risky UPDATE ko commit se pehle kaise preview karein?

Usi WHERE ko SELECT ke saath chalao, ya BEGIN … ROLLBACK mein wrap karke test karo, COMMIT tabhi karo jab sure ho.

SET salary = salary * 1.10 mein right-hand side ka salary kya refer karta hai?

Row ki current value update se pehle.

Recall Feynman: ek 12-saal ke bacche ko samjhao

Socho ek class ka notebook jisme har line pe ek bacche ki info hai. SELECT matlab poochna "mujhe woh bacche dikhao jinke 80 se zyada marks hain" — tum sirf dekhte ho, kuch change nahi karta. INSERT matlab ek naye bacche ki line add karna. UPDATE matlab ek number mitakar naya likhna (jaise galat score theek karna). DELETE matlab ek poori line cross out karna. Magic word WHERE matlab kehna "sirf woh bacche jinka naam Ben hai" — agar tum use bhool gaye, toh tum notebook ke har ek bacche ko accidentally change kar doge! Isliye hamesha batao tum kiska matlab kar rahe ho.

Connections

SQL DDL — CREATE, ALTER, DROP — woh tables define karta hai jinpe DML operate karta hai.
Transactions — ACID, COMMIT, ROLLBACK — kyun DML COMMIT se pehle recoverable hai.
SQL Joins — SELECT ko multiple tables tak extend karta hai.
WHERE clause and Predicates — woh filter jo SELECT/UPDATE/DELETE mein common hai.
Aggregate Functions & GROUP BY — SELECT mein use hone wala bucketing.
Indexes — WHERE-filtered SELECT/UPDATE/DELETE ko fast banata hai.