INSERT, UPDATE, DELETE and DDL

Read queries dominate SQL interviews, but writes come up reliably in two forms: the DELETE/TRUNCATE/DROP comparison, and "how would you write an upsert". Both are short questions with precise answers.

INSERT

-- Always name the columns: positional inserts break when the table changes
INSERT INTO employees (first_name, last_name, salary)
VALUES ('Ana', 'Silva', 75000);

-- Multi-row: one statement, one round trip, one transaction
INSERT INTO employees (first_name, last_name, salary) VALUES
    ('Ben', 'Cruz', 68000),
    ('Cy',  'Diaz', 82000);

-- INSERT … SELECT: copy or archive without pulling data into the client
INSERT INTO employees_archive (id, first_name, salary)
SELECT id, first_name, salary FROM employees WHERE left_on IS NOT NULL;

-- Get the generated key back
INSERT INTO employees (first_name) VALUES ('Dee') RETURNING employee_id;  -- Postgres

Multi-row INSERT is dramatically faster than a loop of single inserts — one parse, one round trip, one transaction — and saying so is usually the follow-up answer.

Upsert

Insert, or update if it already exists. Every engine spells it differently:

-- PostgreSQL / SQLite
INSERT INTO inventory (sku, quantity) VALUES ('A1', 10)
ON CONFLICT (sku) DO UPDATE SET quantity = inventory.quantity + EXCLUDED.quantity;

-- MySQL
INSERT INTO inventory (sku, quantity) VALUES ('A1', 10)
ON DUPLICATE KEY UPDATE quantity = quantity + VALUES(quantity);

-- Standard SQL / SQL Server / Oracle
MERGE INTO inventory AS t
USING (SELECT 'A1' AS sku, 10 AS qty) AS s ON t.sku = s.sku
WHEN MATCHED THEN UPDATE SET quantity = t.quantity + s.qty
WHEN NOT MATCHED THEN INSERT (sku, quantity) VALUES (s.sku, s.qty);

An upsert needs a UNIQUE or PRIMARY KEY constraint to detect the conflict against — that is the detail to mention. Doing it as SELECT then INSERT in application code is the wrong answer: two concurrent requests both see "not there" and both insert.

UPDATE

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

-- UPDATE from another table — the syntax differs most here
UPDATE employees e                                     -- PostgreSQL
SET department_name = d.name
FROM departments d
WHERE d.id = e.department_id;

UPDATE employees e                                     -- MySQL
JOIN departments d ON d.id = e.department_id
SET e.department_name = d.name;

An `UPDATE` with no `WHERE` updates every row. The habit that prevents it: write the

SELECT first, confirm the row set, then convert it to an UPDATE. Inside an explicit transaction if the database allows one, so a wrong count can be rolled back.

DELETE vs TRUNCATE vs DROP

The comparison question, asked almost every time:

The short version: DELETE for some rows, TRUNCATE to empty a table quickly, DROP to remove the table itself. TRUNCATE skipping triggers and per-row logging is exactly what makes it fast — and what makes it unsafe when triggers carry business logic.

DDL and constraints

CREATE TABLE employees (
    employee_id   SERIAL PRIMARY KEY,
    email         VARCHAR(255) NOT NULL UNIQUE,
    salary        DECIMAL(10, 2) CHECK (salary > 0),
    department_id INT REFERENCES departments(id) ON DELETE SET NULL,
    created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE employees ADD COLUMN phone VARCHAR(20);
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary <= 1000000);
CREATE INDEX idx_employees_dept ON employees (department_id);

Both NULL caveats matter and both get asked; see NULL handling.

Referential actions on a foreign key decide what happens when the parent goes:

ON DELETE RESTRICT / NO ACTION   -- refuse (the default)
ON DELETE CASCADE                -- delete the children too
ON DELETE SET NULL               -- orphan them, column must be nullable

CASCADE is convenient and dangerous — one delete can silently remove a large subtree.

Use DECIMAL, never FLOAT, for money: binary floating point cannot represent 0.10 exactly, so sums drift. That is a favourite follow-up.

Transactions

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;    -- or ROLLBACK

ACID in one line each: Atomicity — all or nothing; Consistency — constraints hold before and after; Isolation — concurrent transactions do not see each other's in-progress work; Durability — a committed change survives a crash. Being able to recite that, plus the isolation levels (read uncommitted, read committed, repeatable read, serializable) and the anomalies each prevents, covers the usual follow-ups.

Note that DDL is often implicitly committed — in MySQL and Oracle, an ALTER TABLE inside a transaction commits everything before it. PostgreSQL is the notable engine with transactional DDL.

Mistakes that cost the interview

• `UPDATE` or `DELETE` with no `WHERE`.
• Positional `INSERT` without a column list.
• `SELECT` then `INSERT` as an upsert — a race condition.
• Expecting `TRUNCATE` to fire triggers or be rollback-safe.
• `FLOAT` for currency.
• `CASCADE` without saying what it will remove.

Practise these on the SQL sheet.