Data Manipulation

Starting Table: employees

We'll modify this table throughout the lesson:

┌────┬────────────┬───────────┬─────────────┬────────┐
│ id │ first_name │ last_name │ department  │ salary │
├────┼────────────┼───────────┼─────────────┼────────┤
│ 1  │ Alice      │ Johnson   │ Engineering │ 95000  │
│ 2  │ Bob        │ Smith     │ Marketing   │ 65000  │
│ 3  │ Charlie    │ Brown     │ Engineering │ 105000 │
└────┴────────────┴───────────┴─────────────┴────────┘

INSERT: Adding New Data

INSERT adds new rows to a table. You specify which columns to fill and what values to use.

Insert One Row

Add a single employee with all required fields.

INSERT INTO employees (id, first_name, last_name, department, salary)
VALUES (4, 'Diana', 'Martinez', 'Sales', 72000);

Result: 1 row inserted

Table now contains:
┌────┬────────────┬───────────┬─────────────┬────────┐
│ id │ first_name │ last_name │ department  │ salary │
├────┼────────────┼───────────┼─────────────┼────────┤
│ 1  │ Alice      │ Johnson   │ Engineering │ 95000  │
│ 2  │ Bob        │ Smith     │ Marketing   │ 65000  │
│ 3  │ Charlie    │ Brown     │ Engineering │ 105000 │
│ 4  │ Diana      │ Martinez  │ Sales       │ 72000  │ ← NEW
└────┴────────────┴───────────┴─────────────┴────────┘

Insert Partial Data

You don't need to specify every column, omitted columns get NULL or their default values.

INSERT INTO employees (id, first_name, last_name)
VALUES (5, 'Eve', 'Davis');

Result: department and salary will be NULL

┌────┬────────────┬───────────┬────────────┬────────┐
│ id │ first_name │ last_name │ department │ salary │
├────┼────────────┼───────────┼────────────┼────────┤
│ 5  │ Eve        │ Davis     │ NULL       │ NULL   │
└────┴────────────┴───────────┴────────────┴────────┘

Insert Multiple Rows

Insert several rows in a single statement (more efficient).

INSERT INTO employees (id, first_name, last_name, department, salary)
VALUES 
    (6, 'Frank', 'Wilson', 'Marketing', 70000),
    (7, 'Grace', 'Lee', 'Sales', 78000),
    (8, 'Henry', 'Taylor', 'Engineering', 92000);

Result: 3 rows inserted at once

Added to table:
│ 6  │ Frank      │ Wilson    │ Marketing   │ 70000  │
│ 7  │ Grace      │ Lee       │ Sales       │ 78000  │
│ 8  │ Henry      │ Taylor    │ Engineering │ 92000  │

Insert from Another Table

Copy data from one table to another using SELECT.

INSERT INTO employees_archive (id, first_name, last_name)
SELECT id, first_name, last_name
FROM employees
WHERE department = 'Sales';

Copies all Sales employees to the archive table

UPDATE: Modifying Existing Data

UPDATE changes values in existing rows. Always use a WHERE clause unless you want to update every row.

Update One Row

Give Alice a raise.

UPDATE employees
SET salary = 100000
WHERE id = 1;

Before:

│ 1  │ Alice      │ Johnson   │ Engineering │ 95000  │

After:

│ 1  │ Alice      │ Johnson   │ Engineering │ 100000 │ ← Updated

Update Multiple Columns

Bob gets promoted and a raise.

UPDATE employees
SET 
    department = 'Engineering',
    salary = 85000
WHERE id = 2;

Before:

│ 2  │ Bob        │ Smith     │ Marketing   │ 65000  │

After:

│ 2  │ Bob        │ Smith     │ Engineering │ 85000  │ ← Both changed

Update Multiple Rows

Give all Marketing employees a 10% raise.

UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Marketing';

Result: All Marketing employees get 10% increase

Before:  Frank: $70,000
After:   Frank: $77,000 (70000 * 1.10)

Conditional Update

Update only if certain conditions are met.

UPDATE employees
SET salary = salary + 5000
WHERE department = 'Engineering' AND salary < 90000;

Only Engineering employees earning less than $90k get the bonus

-- DISASTER: Sets everyone's salary to 50000
UPDATE employees SET salary = 50000;

-- SAFE: Only updates specific employee
UPDATE employees SET salary = 50000 WHERE id = 2;

DELETE: Removing Data

DELETE removes rows from a table permanently. There's no undo button.

Delete One Row

Remove a specific employee by ID.

DELETE FROM employees
WHERE id = 5;

Before: 8 employees

│ 5  │ Eve        │ Davis     │ NULL        │ NULL   │ ← Will be deleted

After: 7 employees (Eve is gone)

Delete Multiple Rows

Remove all employees from a specific department.

DELETE FROM employees
WHERE department = 'Sales';

Result: Removes Diana and Grace (both in Sales)

Deleted:
│ 4  │ Diana      │ Martinez  │ Sales       │ 72000  │ ✗
│ 7  │ Grace      │ Lee       │ Sales       │ 78000  │ ✗

Delete with Complex Condition

Remove employees meeting multiple criteria.

DELETE FROM employees
WHERE salary < 70000 AND department = 'Marketing';

Only removes Marketing employees earning less than $70k

Delete All Rows (Dangerous!)

Remove everything from the table (structure remains).

DELETE FROM employees;

Warning: Deletes all rows! Table is now empty.

Before: 8 rows
After:  0 rows (but table structure still exists)

-- Step 1: TEST with SELECT
SELECT * FROM employees WHERE id = 5;

-- Step 2: Verify it returns what you expect

-- Step 3: THEN delete
DELETE FROM employees WHERE id = 5;

TRUNCATE: Fast Delete All

TRUNCATE removes all rows instantly, faster than DELETE. Cannot be undone and has no WHERE clause.

TRUNCATE TABLE employees;

Immediately removes all rows, resets auto-increment counters

RETURNING: Get Data Back

Some databases (PostgreSQL) let you see what was inserted, updated, or deleted.

INSERT with RETURNING

INSERT INTO employees (first_name, last_name, salary)
VALUES ('John', 'Doe', 80000)
RETURNING id, first_name, salary;

Returns:
┌────┬────────────┬────────┐
│ id │ first_name │ salary │
├────┼────────────┼────────┤
│ 9  │ John       │ 80000  │ ← Shows newly created row
└────┴────────────┴────────┘

UPDATE with RETURNING

UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Engineering'
RETURNING first_name, salary;

Shows all updated rows with new salaries:
┌────────────┬────────┐
│ first_name │ salary │
├────────────┼────────┤
│ Alice      │ 110000 │
│ Charlie    │ 115500 │
│ Henry      │ 101200 │
└────────────┴────────┘

Best Practices & Safety

Using Transactions for Safety

Transactions let you test changes before making them permanent.

-- Start transaction
BEGIN;

-- Make changes
UPDATE employees SET salary = salary * 1.20 WHERE department = 'Engineering';

-- Check if it looks correct
SELECT * FROM employees WHERE department = 'Engineering';

-- If good: make it permanent
COMMIT;

-- If bad: undo everything
ROLLBACK;

Changes aren't permanent until COMMIT. Use ROLLBACK to undo.