SQL DML — SELECT, INSERT, UPDATE, DELETE: Question bank

Prerequisites worth a glance first: WHERE clause and Predicates, Aggregate Functions & GROUP BY, Transactions — ACID, COMMIT, ROLLBACK, and the parent parent DML note.

True or false — justify

Every answer starts with True/False and then the real reason — the reason is the point.

An UPDATE with no WHERE clause updates a random subset of rows.

False. A missing WHERE means the predicate is effectively "always true", so it hits every single row — nothing random about it, which is exactly why it's dangerous.

DELETE FROM employees; and DROP TABLE employees; leave the database in the same state.

False. DELETE empties the rows but the table structure (columns, types, constraints) survives; DROP removes the table itself, so afterwards SELECT * FROM employees errors "no such table".

SELECT can change data in the table.

False. SELECT only reads — it produces a result set to look at and never modifies the stored rows; that read-only nature is why it's safe to run freely.

You can reference a SELECT-list alias inside the WHERE clause of the same query.

False. WHERE is logically evaluated before SELECT (FROM → WHERE → … → SELECT), so the alias doesn't exist yet when WHERE runs.

WHERE salary > 60000 and HAVING salary > 60000 are interchangeable.

False. WHERE filters individual rows before grouping; HAVING filters ==groups after GROUP BY== and can use aggregates like COUNT(*). On an ungrouped query they may coincide, but their stage in execution differs.

WHERE salary = NULL correctly finds employees whose salary is unknown.

False. Any comparison with NULL using = yields UNKNOWN, never TRUE, so no rows match. You must write ==WHERE salary IS NULL==.

A multi-row INSERT that fails on the third row still inserts the first two.

False (normally). A single INSERT statement is atomic: if any tuple violates a constraint, the whole statement is rejected and none of the rows land.

UPDATE employees SET salary = salary * 1.10 uses the new salary for the multiplication.

False. The right-hand salary refers to the row's current value before this update; each row is computed once from its old value.

Running a wrong UPDATE is always unrecoverable.

False. If you're inside an open transaction and have not committed, ROLLBACK undoes it. It becomes unrecoverable only after COMMIT (see Transactions — ACID, COMMIT, ROLLBACK).

TRUNCATE TABLE t is just a faster DELETE FROM t and can always be rolled back.

False. TRUNCATE deallocates pages wholesale (fast, no per-row WHERE) and in many engines is not rollback-able and skips row triggers — so it's blunt, not merely quick.

Spot the error

Read the statement, name what's wrong (or say "nothing").

SELECT dept, name, AVG(salary) FROM employees GROUP BY dept;

::: name is neither in GROUP BY nor wrapped in an aggregate. A dept bucket holds many names, so SQL can't pick one — most engines reject this. Group by all bare columns or aggregate them.

SELECT name, AVG(salary) AS pay FROM employees WHERE pay > 50000 GROUP BY name;

::: WHERE pay > 50000 references the alias pay and an aggregate, but WHERE runs before both grouping and the SELECT alias exist. Filter aggregates with HAVING AVG(salary) > 50000 instead.

INSERT INTO employees VALUES ('Eve', 5, 'Sales', 42000);

::: No column list, so values must match table order (id, name, dept, salary) — but 'Eve' and 5 are swapped. Naming columns (INSERT INTO employees (id,name,dept,salary) ...) makes order mistakes impossible.

UPDATE employees SET salary = 0 WHERE dept = 'Eng'
DELETE FROM employees WHERE id = 4;

::: Missing semicolon between two statements — the parser can't tell where one ends. Also a hidden trap: without a ; boundary you might run only part of what you intended.

DELETE FROM employees WHERE dept <> 'Eng';

::: Nothing syntactically wrong, but the trap: rows where dept IS NULL are not deleted, because NULL <> 'Eng' evaluates to UNKNOWN, not TRUE. If you meant "everyone not in Eng", NULL depts slip through.

SELECT name FROM employees WHERE salary > 60000 LIMIT 2 ORDER BY salary DESC;

::: ORDER BY must come before LIMIT. As written it's a syntax error; even conceptually, sorting has to happen before you cut to the top N, else "top 2" is meaningless.

Why questions

Answer with the mechanism, not just a restatement.

Why does naming columns in an INSERT make it "future-proof"?

Because values bind to named targets, not to positions. If someone later ALTERs the table to add a column (see SQL DDL — CREATE, ALTER, DROP), a positional insert breaks or misaligns, while a named one still fills exactly the columns you listed.

Why can HAVING use COUNT(*) but WHERE cannot?

COUNT(*) is a property of a group, and groups only exist after GROUP BY has run. WHERE acts one stage earlier, on individual rows, where no group and no count yet exist.

Why is previewing a risky UPDATE with the same WHERE in a SELECT a good habit?

The SELECT shows you exactly which rows the predicate matches without changing anything. If that set looks wrong (too many, too few), you catch it before any data is touched.

Why does DML run inside a transaction give you a "safety net" that DDL often doesn't?

DML changes (rows) are logged and can be ROLLBACK-ed before COMMIT. Many DDL statements (like DROP, TRUNCATE) auto-commit or aren't transactional, so there's nothing to undo afterwards.

Why does SET salary = salary + bonus behave correctly even though salary appears on both sides?

SQL reads the old value of every referenced column first, then writes the result. There's no step-by-step mutation mid-row, so the right side always sees pre-update values.

Why do we say "the DB executes SELECT in a weird order"?

Because the written order (SELECT first) is not the logical order (FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT). Knowing the true order derives rules like "no alias in WHERE" instead of memorizing them.

Edge cases

The scenarios the four verbs quietly invite.

What does DELETE FROM employees WHERE 1 = 0; do?

The predicate is never true, so zero rows are deleted — a valid no-op. It's the harmless mirror image of a missing WHERE (which matches everything).

What does an aggregate like AVG(salary) do with a NULL salary in the group?

Aggregate functions skip NULLs. AVG divides the sum of known salaries by the count of non-null salaries, so an unknown salary doesn't drag the average toward zero.

What does SELECT COUNT(*) vs COUNT(salary) return when some salaries are NULL?

COUNT(*) counts all rows including NULL-salary ones; COUNT(salary) counts only rows where salary is not NULL. The gap between them tells you how many salaries are unknown.

What happens if you INSERT a row whose id duplicates an existing PRIMARY KEY?

It's rejected — the primary key enforces uniqueness (see SQL DDL — CREATE, ALTER, DROP). No row is added and, in a multi-row insert, the whole statement fails atomically.

What does UPDATE employees SET dept = 'Eng' WHERE dept = 'Eng'; change on disk?

Logically nothing (values are already 'Eng'), but it still matches those rows and may fire row triggers and generate log entries — a "no-op" update isn't always free.

What if WHERE compares two columns and both can be NULL, e.g. WHERE a = b?

If either side is NULL the result is UNKNOWN, so the row is excluded even when both are NULL. "Both unknown" is not treated as "equal"; use IS NOT DISTINCT FROM or explicit IS NULL checks if you need that.

On an empty table, what does SELECT COUNT(*) FROM employees; return versus SELECT AVG(salary) FROM employees;?

COUNT(*) returns 0 (a defined count of nothing), but AVG(salary) returns NULL — there are no values to average, so the answer is "undefined/unknown", not zero.

Active Recall

Recall One-line self-test

No-WHERE rule in five words ::: No WHERE means every row. The safest DML verb, and why ::: SELECT — it only reads, never mutates stored data. The one comparison that never matches NULL ::: = NULL; use IS NULL instead. Which clause filters groups, not rows ::: HAVING (runs after GROUP BY; WHERE runs before).