← SQL roadmap

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

CommandWhat it doesExample
INSERT INTO ... VALUESAdds one or many rowsINSERT INTO dept VALUES (1,'CSE'),(2,'ECE');
INSERT INTO ... (cols) VALUESAdds rows for chosen columnsINSERT INTO students (name, dept_id) VALUES ('Asha', 1);
INSERT INTO ... SELECTCopies rows from a queryINSERT INTO alumni SELECT * FROM students WHERE year = 4;
INSERT IGNORESkips rows that break a unique keyINSERT IGNORE INTO dept VALUES (1,'CSE');
INSERT ... ON DUPLICATE KEY UPDATEUpsert: update when key existsINSERT INTO stock (sku, qty) VALUES ('A1', 5) ON DUPLICATE KEY UPDATE qty = qty + 5;
REPLACE INTODeletes conflicting row then insertsREPLACE INTO dept VALUES (1,'Computer Science');
UPDATE ... SET ... WHEREChanges matching rowsUPDATE students SET marks = marks + 5 WHERE dept_id = 2;
UPDATE with JOINUpdates using another tableUPDATE students s JOIN dept d ON d.id = s.dept_id SET s.marks = 0 WHERE d.name = 'OLD';
DELETE FROM ... WHERERemoves matching rowsDELETE FROM students WHERE marks < 35;
DELETE with JOINRemoves using another tableDELETE s FROM students s JOIN dept d ON d.id = s.dept_id WHERE d.name = 'OLD';
SELECT ... FROM ... WHEREReads filtered rowsSELECT name FROM students WHERE marks >= 60;
SELECT DISTINCTRemoves duplicate rowsSELECT DISTINCT dept_id FROM students;
ORDER BY ... LIMIT ... OFFSETSorts and pages resultsSELECT * FROM students ORDER BY marks DESC LIMIT 10 OFFSET 20;
GROUP BY ... HAVINGGroups and filters groupsSELECT dept_id, AVG(marks) FROM students GROUP BY dept_id HAVING AVG(marks) > 70;
UNION / UNION ALLStacks result setsSELECT name FROM students UNION SELECT name FROM alumni;
WITH (CTE)Named sub-queryWITH t AS (SELECT * FROM students) SELECT * FROM t;
SELECT ... FOR UPDATELocks rows inside a transactionSELECT qty FROM stock WHERE sku='A1' FOR UPDATE;
LOAD DATA INFILEBulk-loads a CSV fileLOAD 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.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “Commands Reference: DML & DQL (INSERT, UPDATE, DELETE, SELECT)” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.