Data Manipulation & Transactions

Data Manipulation Language (DML)

In SQL, Data Manipulation Language (DML) is used to manage and manipulate data stored in the database. DML statements let you insert new data, update existing rows, and delete unwanted records — the core operations your app uses every day.

The INSERT Statement

The INSERT statement allows you to add one or more rows into a table.

Syntax

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Guidelines:

List columns in the exact order you provide values.
Ensure the number of columns matches the number of values.

Example

-- Insert a new product
INSERT INTO Products (id, name, price)
VALUES (101, 'USB-C Cable', 6.99);

-- Insert multiple rows at once (supported by many engines)
INSERT INTO Products (id, name, price) VALUES
  (102, 'Notebook', 5.50),
  (103, 'Pencil', 1.25);

The UPDATE Statement

UPDATE modifies one or more columns of matching rows. Be precise with WHERE.

Syntax

UPDATE table_name
SET
  column_name1 = expr1,
  column_name2 = expr2
WHERE condition;   -- optional but highly recommended!

Table: specify the table to change.
SET: provide new values (literals, expressions, or subqueries).
WHERE: filters which rows to update. Omitting it updates every row.

Examples

-- Increase price by 10% for a specific product
UPDATE Products
SET price = price * 1.10
WHERE id = 101;

-- Mark old orders as archived
UPDATE Orders
SET status = 'archived'
WHERE created_at < '2024-01-01';

Important: Always double-check your WHERE to avoid unintended bulk updates.

The DELETE Statement

Use DELETE to remove rows that match a condition.

Syntax

DELETE FROM table_name
WHERE condition;   -- omit WHERE to delete all rows (danger!)

Example

-- Delete discontinued products below a price threshold
DELETE FROM Products
WHERE discontinued = 1 AND price < 2.00;

ACID Properties of Transactions

Transactions follow four key properties to ensure accuracy and reliability. Think of a mobile money transfer — these guarantees keep balances correct.

A — Atomicity (“All or Nothing”)

All steps succeed as a unit or none are saved.

-- Transfer 1000 Ksh from Alex to Bella (conceptual)
-- 1) Deduct 1000 from Alex
-- 2) Add   1000 to Bella
-- If either fails, roll back both so no partial transfer occurs.

C — Consistency (“Keep It Accurate”)

Transactions respect rules/constraints (e.g., non‑negative balances).

-- If Alex has only 500 Ksh, sending 1000 violates a constraint
-- The DB should reject the transaction to preserve valid state.

I — Isolation (“No Interference”)

Concurrent transactions behave as if run one by one.

-- Alex→Bella and Charles→David transfers occur simultaneously
-- Isolation prevents them from affecting each other’s results.

D — Durability (“It’s Permanent!”)

Once committed, changes survive crashes or restarts.

-- After a successful transfer, balances remain updated
-- even if the system goes down and recovers later.

Challenge: SQL Table Creation and Insertion

Create a table named student with the following columns, then insert at least three records.

name — String, maximum length 100
age — Integer
gender — String, maximum length 10

Starter

-- 1) Create the table
CREATE TABLE student (
  name   VARCHAR(100) NOT NULL,
  age    INT,
  gender VARCHAR(10)
);

-- 2) Insert at least three records
INSERT INTO student (name, age, gender) VALUES
  ('Aisha', 19, 'Female'),
  ('Brian', 22, 'Male'),
  ('Chloe', 21, 'Female');

-- 3) Verify results
SELECT * FROM student;

Stretch: Add an id primary key (e.g., AUTO_INCREMENT), and try updating/deleting specific students.

More Resources

← Back to Database Home