Data Modification (DML Operations)

DML (Data Manipulation Language) commands change the data inside tables.

The main DML operations are:

• INSERT

• UPDATE

• DELETE

• Transactions (BEGIN, COMMIT, ROLLBACK)

• Using constraints to keep data safe

1. INSERT

A. Insert a Single Row

INSERT INTO customers (customer_name, email, country) VALUES ('Alice Johnson', 'alice@example.com', 'USA');

B. Insert Multiple Rows (Bulk Insert)

INSERT INTO customers (customer_name, email, country) VALUES ('Bob Miller', 'bob@example.com', 'Canada'), ('Sara Kim', 'sara@example.com', 'Korea'), ('Liam Patel', 'liam@example.com', 'UK'); 

2. UPDATE

Update existing rows.

Example: Increase a product’s price by 10%.

UPDATE products SET price = price * 1.10;

Update only specific products:

UPDATE products SET price = price * 1.10 WHERE category = 'Electronics';

Always include a WHERE clause unless you intend to update every row.

3. DELETE

Delete specific rows:

DELETE FROM orders WHERE order_date < '2023-01-01';

Delete all rows (use carefully):

DELETE FROM customers;

Tip: Many DBMSs support TRUNCATE TABLE for faster deletion, but it cannot be rolled back in some systems.

4. Transactions

Transactions ensure that a set of operations is treated as a single unit.

Useful when multiple changes must succeed or fail together.

BEGIN

Start a transaction.

COMMIT

Save all changes.

ROLLBACK

Undo changes since BEGIN.

Example: Safe Product Price Update

BEGIN; UPDATE products SET price = price * 1.10; -- Oops… something looks wrong ROLLBACK; -- Undo everything

If everything is good:

BEGIN; UPDATE products SET price = price * 1.10 WHERE category = 'Electronics'; COMMIT; -- Finalize the changes

5. Using Constraints to Protect Data

SQL constraints prevent invalid or unsafe data changes.

Example: Protect product quantity

CHECK (quantity >= 0)

Example: Prevent invalid deletions

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

Prevents deleting customers with existing orders (unless cascading rules are set).

6. Practical Exercises

Exercise 1: Add new customers

Insert three customers:

INSERT INTO customers (customer_name, email, country) VALUES ('John Doe', 'john@example.com', 'USA'), ('Emma Liu', 'emma@example.com', 'China'), ('Carlos Diaz', 'carlos@example.com', 'Mexico');

Exercise 2: Update product prices

Increase prices of all clothing items by 5%:

UPDATE products SET price = price * 1.05 WHERE category = 'Clothing';

Exercise 3: Delete old records safely

Delete orders older than 2021 — but do it using a transaction:

BEGIN; DELETE FROM orders WHERE order_date < '2021-01-01'; -- Check number of rows affected -- If correct: COMMIT; -- If not correct: -- ROLLBACK;

1. INSERT — BEFORE & AFTER

Table: customers

BEFORE INSERT

INSERT

INSERT INTO customers (customer_id, name, email) VALUES (3, 'Charlie', 'charlie@example.com');

AFTER INSERT

2. UPDATE — BEFORE & AFTER

Table: products

BEFORE UPDATE

UPDATE

Increase all prices by 10%.

UPDATE products SET price = price * 1.10;

AFTER UPDATE

3. DELETE — BEFORE & AFTER

Table: orders

BEFORE DELETE

DELETE

Delete orders before 2021:

DELETE FROM orders WHERE order_date < '2021-01-01'; 

AFTER DELETE

(Orders 5001 and 5003 were removed.)

4. Transaction Example — BEFORE / AFTER (with ROLLBACK)

Scenario: Accidentally reducing all salaries

Table: employees BEFORE

BEGIN + Wrong UPDATE

BEGIN; UPDATE employees SET salary = salary * 0.5; -- OOPS: Cut salaries in half!

State AFTER update (but not committed yet)

ROLLBACK

ROLLBACK;

AFTER ROLLBACK (Back to original)

(Changes were undone.)