Skip to main content

Basic Data Manipulation

  • Data Manipulation Language (DML) commands allow you to add, modify, and remove data in a database. The most common DML commands are INSERT, UPDATE, and DELETE. Understanding these operations is essential for working with live data in any relational database.

  • Transactions introduce an important safety layer by grouping multiple operations so they can be committed or undone.

INSERT​

  • Adds a new record to a table. You specify the table, the columns, and the values.

  • Insert a new student.

INSERT INTO students (first_name, last_name, age)
VALUES ('John', 'Doe', 20);
  • Insert multiple rows.
INSERT INTO courses (course_name, credits)
VALUES
  ('Database Fundamentals', 3),
  ('Web Development', 4);

UPDATE​

  • Changes data in rows that match a given condition.
UPDATE and WHERE clause
  • Like DELETE, always use a WHERE clause unless you intend to update everything.
  • Update a student's age for a student with an id equal to 5.
UPDATE students
SET age = 21
WHERE id = 5;
  • Update multiple columns. Update the salary and active status of employees from the HR department.
UPDATE employees
SET salary = 60000, is_active = TRUE
WHERE department = 'HR';

DELETE​

  • DELETE removes rows from a table.
DELETE and WHERE clause
  • Like UPDATE, always use a WHERE clause unless you intend to delete everything.
  • Delete one student. Delete a student with a student id equal to 10.
DELETE FROM students
WHERE id = 10;
  • Delete all inactive accounts. Remove rows from the users table, if their status is false.
DELETE FROM users
WHERE is_active = FALSE;
  • Delete ALL rows (use carefully!).
DELETE FROM logs;

Transactions (COMMIT and ROLLBACK)​

  • A transaction is a group of SQL operations that are treated as a single unit of work. Either all actions succeed or none do.

  • This ensures data integrity, especially when multiple tables are involved.

  • Transactions are the foundation for reliable, real-world applications like banking, booking systems, and inventory management.

  • Key commands:

    • COMMIT: Save all changes permanently.

    • ROLLBACK: Undo all changes since the transaction started.

  • Successful transaction, money is safely transferred only if both updates succeed.

START TRANSACTION;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

COMMIT;
  • Rollback transaction, if something goes wrong. All changes are undone, and data stays consistent.
START TRANSACTION;

UPDATE orders
SET status = 'Shipped'
WHERE id = 50;

-- Assuming an error happens here
ROLLBACK;