Topic 32 of 52 · Beginner
Commands Reference: DML & DQL (INSERT, UPDATE, DELETE, SELECT)
Every way to add, change, remove and read rows — including upserts and multi-table updates.
Concept
DML commands change rows: INSERT, UPDATE, DELETE, REPLACE and MERGE-style upserts. DQL is the SELECT statement used to read rows.
Always run a SELECT with the same WHERE first before an UPDATE or DELETE, and use transactions for risky changes.
SELECT clauses run in this logical order: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT.
Cheat sheet
| Command | What it does | Example |
|---|---|---|
| INSERT INTO ... VALUES | Adds one or many rows | INSERT INTO dept VALUES (1,'CSE'),(2,'ECE'); |
| INSERT INTO ... (cols) VALUES | Adds rows for chosen columns | INSERT INTO students (name, dept_id) VALUES ('Asha', 1); |
| INSERT INTO ... SELECT | Copies rows from a query | INSERT INTO alumni SELECT * FROM students WHERE year = 4; |
| INSERT IGNORE | Skips rows that break a unique key | INSERT IGNORE INTO dept VALUES (1,'CSE'); |
| INSERT ... ON DUPLICATE KEY UPDATE | Upsert: update when key exists | INSERT INTO stock (sku, qty) VALUES ('A1', 5) ON DUPLICATE KEY UPDATE qty = qty + 5; |
| REPLACE INTO | Deletes conflicting row then inserts | REPLACE INTO dept VALUES (1,'Computer Science'); |
| UPDATE ... SET ... WHERE | Changes matching rows | UPDATE students SET marks = marks + 5 WHERE dept_id = 2; |
| UPDATE with JOIN | Updates using another table | UPDATE students s JOIN dept d ON d.id = s.dept_id SET s.marks = 0 WHERE d.name = 'OLD'; |
| DELETE FROM ... WHERE | Removes matching rows | DELETE FROM students WHERE marks < 35; |
| DELETE with JOIN | Removes using another table | DELETE s FROM students s JOIN dept d ON d.id = s.dept_id WHERE d.name = 'OLD'; |
| SELECT ... FROM ... WHERE | Reads filtered rows | SELECT name FROM students WHERE marks >= 60; |
| SELECT DISTINCT | Removes duplicate rows | SELECT DISTINCT dept_id FROM students; |
| ORDER BY ... LIMIT ... OFFSET | Sorts and pages results | SELECT * FROM students ORDER BY marks DESC LIMIT 10 OFFSET 20; |
| GROUP BY ... HAVING | Groups and filters groups | SELECT dept_id, AVG(marks) FROM students GROUP BY dept_id HAVING AVG(marks) > 70; |
| UNION / UNION ALL | Stacks result sets | SELECT name FROM students UNION SELECT name FROM alumni; |
| WITH (CTE) | Named sub-query | WITH t AS (SELECT * FROM students) SELECT * FROM t; |
| SELECT ... FOR UPDATE | Locks rows inside a transaction | SELECT qty FROM stock WHERE sku='A1' FOR UPDATE; |
| LOAD DATA INFILE | Bulk-loads a CSV file | LOAD DATA INFILE '/tmp/s.csv' INTO TABLE students FIELDS TERMINATED BY ','; |
Code (SQL · MySQL 8)
INSERT INTO dept VALUES (1, 'CSE'), (2, 'ECE');
INSERT INTO students (name, dept_id, marks) VALUES
('Asha', 1, 88), ('Ravi', 2, 64), ('Meena', 1, 32);
-- Upsert: add stock or increase it
INSERT INTO stock (sku, qty) VALUES ('A1', 5)
ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty);
UPDATE students SET marks = LEAST(marks + 5, 100) WHERE dept_id = 2;
DELETE FROM students WHERE marks < 35;
SELECT d.name, COUNT(*) AS students, ROUND(AVG(s.marks), 1) AS avg_marks
FROM students s
JOIN dept d ON d.id = s.dept_id
GROUP BY d.name
HAVING COUNT(*) >= 1
ORDER BY avg_marks DESC;Remember
- UPDATE/DELETE without WHERE affect every row.
- WHERE filters rows before grouping; HAVING filters groups after.
- ON DUPLICATE KEY UPDATE keeps the row id; REPLACE deletes and re-inserts it.
- UNION removes duplicates (slower); UNION ALL keeps them.
Interview questions
WHERE vs HAVING?
WHERE filters individual rows before GROUP BY and cannot use aggregates; HAVING filters grouped results and can use aggregates like COUNT(*).
REPLACE vs INSERT ... ON DUPLICATE KEY UPDATE?
REPLACE deletes the conflicting row and inserts a new one (firing delete triggers, changing auto ids); ON DUPLICATE KEY UPDATE updates the existing row in place.
Write the logical execution order of SELECT.
FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT.
Lesson test
Take an 8-question test on “Commands Reference: DML & DQL (INSERT, UPDATE, DELETE, SELECT)” — concepts, output prediction, bug spotting and interview questions.