SQL DML — SELECT, INSERT, UPDATE, DELETE

WHAT is DML?

WHY this split matters: DML statements (except some setups) run inside a transaction, so they can be ROLLBACK-ed. A wrong UPDATE is recoverable if you haven't committed yet — this is your safety net.

The running table

We use one table for every example:

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

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

1. INSERT — add rows

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

WHY name the columns? If you write INSERT INTO employees VALUES (...) without column names, the values must match the table's column order exactly and completely. Naming columns makes it order-independent and future-proof (if someone adds a column later, your statement still works).

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);

Why this step? One statement, multiple value-tuples → fewer round-trips to the DB and it's atomic (all 3 insert, or none).

2. SELECT — read rows

Worked example Filtering with WHERE

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

Why this step? WHERE evaluates a boolean predicate per row; rows where it's TRUE survive. With our data → Ben 70000, Cira 90000.

Worked example Aggregation with GROUP BY / HAVING

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

Why this step? GROUP BY dept collapses all rows of a dept into one bucket; aggregate functions (AVG, COUNT) summarize each bucket. HAVING filters buckets (it runs after grouping), whereas WHERE filters rows (before grouping). 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;

Why this step? ORDER BY salary DESC sorts high→low, LIMIT 2 keeps the top two → Cira 90000, Ben 70000.

3. UPDATE — change existing rows

HOW it works: for each row where the predicate is TRUE, the SET assignments are applied. The right side can reference the current value (salary * 1.10 = a 10% raise). Eng rows 70000→77000, 90000→99000. Sales rows untouched.

Common mistake The most expensive typo in databases

Wrong belief: "I'll add the WHERE after." Then you run:

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

Why it feels right: the statement is syntactically valid and runs without error — the DB doesn't warn you. The damage: UPDATE/DELETE with no WHERE hit every row. The fix:

Write the WHERE first, before SET.
Test the same WHERE with a SELECT first (same rows that will change).
Run inside a transaction: BEGIN; ... ; ROLLBACK; to preview, then COMMIT only when sure.

4. DELETE — remove rows

Forecast-then-Verify exercise

Recall Predict the final table, THEN check

Starting from the 4 inserted rows, run in order:

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

Predict first. … Now verify:

Sales raised: Asha 55000, Dia 45000.
Delete salary < 60000 removes Asha(55000), Dia(45000) → gone. Ben 70000, Cira 90000 stay.
Final SELECT → Ben 70000, Cira 90000. Why: UPDATE changed values first, so the DELETE filter saw the new salaries.

Active Recall

What are the four core DML statements?

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

DML vs DDL?

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

What does an UPDATE or DELETE with no WHERE clause affect?

Every row in the table.

Why can't you use a SELECT-list alias inside WHERE?

WHERE is logically evaluated before SELECT, so the alias doesn't exist yet.

Difference between WHERE and HAVING?

WHERE filters individual rows before grouping; HAVING filters groups after GROUP BY/aggregation.

Logical execution order of a SELECT query?

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

DELETE vs TRUNCATE?

DELETE removes filtered rows, is logged and rollback-able; TRUNCATE removes ALL rows fast and is usually not rollback-able.

Why name columns in an INSERT?

Makes it order-independent and survives later schema changes.

How do you preview a risky UPDATE before committing?

Run the same WHERE with SELECT, or wrap in BEGIN … ROLLBACK to test, COMMIT only when sure.

In SET salary = salary * 1.10, what does the right-hand salary refer to?

The row's current value before the update.

Recall Feynman: explain to a 12-year-old

Imagine a notebook of class info, one line per kid. SELECT is asking "show me all kids who scored above 80" — you only look, you don't change anything. INSERT is adding a new kid's line. UPDATE is erasing one number and writing a new one (like fixing a wrong score). DELETE is crossing out a whole line. The magic word WHERE is like saying "only the kids named Ben" — if you forget it, you accidentally change every single kid in the notebook! So always say who you mean.

Connections

SQL DDL — CREATE, ALTER, DROP — defines the tables DML operates on.
Transactions — ACID, COMMIT, ROLLBACK — why DML is recoverable before COMMIT.
SQL Joins — extend SELECT across multiple tables.
WHERE clause and Predicates — the filter shared by SELECT/UPDATE/DELETE.
Aggregate Functions & GROUP BY — the bucketing used in SELECT.
Indexes — make WHERE-filtered SELECT/UPDATE/DELETE fast.

Concept Map

Hinglish (regional understanding)

Intuition Hinglish mein samjho

Dekho, ek database table basically ek smart spreadsheet hai jisse hum sentences (SQL) likhke baat karte hain. DML matlab "Data Manipulation Language" — yeh sirf data ke saath khelta hai, table ka structure nahi badalta. Chaar main verbs yaad rakho: SELECT se rows padhte ho (sirf dekhna, change nahi), INSERT se nayi row add karte ho, UPDATE se existing values badalte ho, aur DELETE se poori row hata dete ho.

Sabse zaroori cheez ek hi hai bhai: WHERE clause. WHERE batata hai ki kaun si rows pe kaam karna hai. Agar tum UPDATE ya DELETE mein WHERE bhool gaye, to woh saari rows pe lag jayega — yeh real life ka sabse bada blunder hai. Isliye golden rule: "No WHERE → everywhere." Hamesha pehle wahi WHERE ek SELECT ke saath test karo, dekho kitni rows aati hain, phir UPDATE/DELETE chalao. Aur ho sake to BEGIN ... ROLLBACK use karke preview karo, sahi lage tabhi COMMIT.

SELECT ka ek mast trick: hum likhte SELECT ... FROM ... WHERE ... order mein hain, lekin database actually execute karta hai is order mein — FROM, phir WHERE, phir GROUP BY, phir HAVING, phir SELECT, phir ORDER BY, phir LIMIT. Isi wajah se SELECT ka alias WHERE mein use nahi kar sakte, kyunki WHERE pehle chal jaata hai. Aur yaad rakho: WHERE individual rows filter karta hai, HAVING groups (GROUP BY ke baad) filter karta hai.

Bas itna pakka kar lo aur tum 80% database kaam aaram se kar loge. Practice ke liye apne mann mein result predict karo, phir query chalake verify karo — yahi forecast-then-verify habit tumhe strong banayegi.

SQL DML — SELECT, INSERT, UPDATE, DELETE

WHAT is DML?

The running table

1. INSERT — add rows

2. SELECT — read rows

3. UPDATE — change existing rows

4. DELETE — remove rows

Forecast-then-Verify exercise

Active Recall

Connections

Concept Map

Hinglish (regional understanding)

Go deeper — visual, from zero

Test yourself — Databases

Connections