DBMS Part 3: SQL INSERT, UPDATE, DELETE Explained with Live Demo

on

Namaste. Up to now, every DBMS post has been about looking at data, tables, keys, SELECT, WHERE. Today we actually start changing it, adding new records, fixing mistakes, and removing what's no longer needed. These three commands, INSERT, UPDATE, and DELETE, are also exactly where real beginners cause real damage if they're not careful, so pay close attention to the warnings in this one.

What's in this post

  1. INSERT, adding a new row
  2. UPDATE, correcting existing data
  3. DELETE, removing data
  4. The golden rule you must never forget
  5. A live database you can actually break (try it!)
  6. Example 1 (Easy): Adding a new student
  7. Example 2 (Medium): Correcting a mistaken mark
  8. Example 3 (Harder): Removing students who dropped out
  9. Example 4 (Hero level): A full add, fix, remove, verify cycle
  10. Common mistakes
  11. Practice tasks

1. INSERT, adding a new row

Analogy: INSERT is simply writing a brand new line in the register book, a new student joining the class, given the next free row.
insert.sql
INSERT INTO Students (RollNo, Name, Section, Marks)
VALUES (9, 'Kiran Thapa', 'B', 70);

Column names first, then the matching values, in the exact same order. Text values need quotes, numbers don't.

2. UPDATE, correcting existing data

update.sql
UPDATE Students
SET Marks = 85
WHERE RollNo = 2;

This changes Ramesh's marks to 85, and nobody else's, because of that WHERE clause. Hold onto that thought.

3. DELETE, removing data

delete.sql
DELETE FROM Students
WHERE RollNo = 4;

4. The golden rule you must never forget

If you run UPDATE or DELETE without a WHERE clause, it applies to every single row in the table, not just the one you meant. This isn't a rare, theoretical mistake, it's one of the most common real disasters in database work, a shop accidentally setting every product's price to the same number, a school accidentally wiping every student's attendance. Before you run UPDATE or DELETE on a real system, always double check your WHERE clause first, ideally by running the same WHERE as a SELECT first, to see exactly which rows you're about to affect.

5. A live database you can actually break

This is a real, working mini database sitting in your browser, starting with the same 8 students from the last post. Unlike the SELECT simulator in Part 2, this one actually remembers changes, insert something, and it stays inserted until you reset it. Try the danger case too, I've deliberately left it possible, so you can see exactly what the warning above actually looks like.

Try it: INSERT, UPDATE, DELETE, and SELECT

Click a chip to load an example, or write your own. Supports all four commands against the Students table.

Insert a student Update someone's marks Delete a student Verify with SELECT Danger: UPDATE with no WHERE

Current table state

6. Example 1 (Easy): Adding a new student

add_student.sql
INSERT INTO Students (RollNo, Name, Section, Marks)
VALUES (10, 'Maya Lama', 'C', 60);

7. Example 2 (Medium): Correcting a mistaken mark

fix_mark.sql
UPDATE Students
SET Marks = 72
WHERE RollNo = 7;

8. Example 3 (Harder): Removing students who dropped out

remove_dropouts.sql
DELETE FROM Students
WHERE Marks < 40;

A WHERE clause here doesn't have to target one specific RollNo, it can be a whole condition, every row matching "failed badly enough to be marked inactive" gets removed in one command.

9. Example 4 (Hero level): A full add, fix, remove, verify cycle

Try this exact sequence in the sandbox above, one line at a time, and watch the "Current table state" change after each one.

full_cycle.sql
INSERT INTO Students (RollNo, Name, Section, Marks)
VALUES (11, 'Rojina KC', 'A', 59);

UPDATE Students SET Marks = 64 WHERE RollNo = 11;

DELETE FROM Students WHERE RollNo = 4;

SELECT * FROM Students ORDER BY Marks DESC;

Add someone, realize their mark was entered wrong, fix it, remove someone else entirely, then confirm the final state with SELECT. That exact sequence, add, fix, remove, verify, is what real data entry work actually looks like.

10. Common mistakes

  1. Running UPDATE or DELETE without WHERE, covered above, worth repeating a third time because it matters that much.
  2. Mismatched column and value counts in INSERT, if you list 4 columns, you need exactly 4 values, in the same order.
  3. Trying to INSERT a duplicate primary key. Since RollNo must be unique, inserting RollNo 2 again when it already exists will, and should, fail.
  4. Forgetting quotes around text in SET or VALUES, same rule as WHERE from the last post.

11. Practice tasks

  1. In the sandbox, insert yourself as a new student with a RollNo that doesn't already exist.
  2. Update your own row to give yourself full marks, purely for the fun of it.
  3. Delete every student in Section B, then run a SELECT to confirm they're gone.
  4. Challenge: reset the data, then try to INSERT a student using RollNo 3, which already exists, and read the error message carefully, it's explaining a real rule, not just refusing randomly.

That's the full CRUD set, Create, Read, Update, Delete, the four operations that make up almost everything any real application does to a database. Next in the rotation, we're back to Python for Part 5, strings in proper depth. After that, DBMS Part 4, ALTER TABLE and constraints.

— Diwakar

← DBMS Part 2: SELECT and WHERE DBMS Part 4: ALTER TABLE and Constraints (coming soon) →

Comments

Post a Comment