SQL DML — SELECT, INSERT, UPDATE, DELETE: Foundations

Before you can read a single SELECT, you must know what each little piece means and what it looks like. Below, every symbol and word the parent note leaned on is rebuilt from nothing, in the order that each one needs the one before it.

0. The grid itself — table, row, column, cell

Look at the figure. The whole rectangle is the table. One horizontal strip (blue) is a row. One vertical strip (pink) is a column. The single box where a row and column cross (yellow) is a cell — that is where an actual value like 50000 lives.

Figure — SQL DML — SELECT, INSERT, UPDATE, DELETE

Why the topic needs this: every DML verb is defined by what part of the grid it touches. INSERT adds a row. DELETE removes a row. UPDATE changes cells inside chosen rows. SELECT shows you chosen rows/columns. You literally cannot say what any verb does without the words row/column.

1. Column names and data types

Each column has a name (a label, like salary) and a type (what kind of value is allowed).

The picture: think of each column as a mailbox with a slot cut to one shape. The INT slot only takes number-cards; the VARCHAR slot takes word-cards. Try to post the wrong shape and the database rejects it.

Why the topic needs this: the parent writes salary INT and name VARCHAR(50). When you later write WHERE dept = 'Eng' the text is in quotes (it's a word-card) but salary > 60000 has no quotes (a number-card). The type explains the quotes.

2. Quotes, the `=` sign, and comparison symbols

The comparison symbols you'll meet. Each one asks a yes/no question about two values:

Symbol	Plain words	How to read it
`=`	equal to	the two values are exactly the same
`<>` or `!=`	not equal to	the two values differ (both spellings mean the same thing)
`>`	greater than	the mouth opens toward the bigger side: `salary > 60000`
`<`	less than	the mouth points at the smaller side: `salary < 60000`
`>=`	greater than or equal to	passes when bigger or a tie: `COUNT(*) >= 2` is true for exactly 2
`<=`	less than or equal to	passes when smaller or a tie: `salary <= 50000` includes exactly 50000

Why the topic needs this: salary > 60000, salary < 60000, COUNT(*) >= 2 all appear in the parent. Each is a yes/no test asked of one row.

3. NULL — the "we don't know" box

Because NULL means "unknown," SQL uses three-valued logic: a predicate can come out TRUE, FALSE, or UNKNOWN. Any comparison against NULL yields UNKNOWN — and a turnstile only lets a row through on TRUE, so UNKNOWN rows are quietly blocked.

Common mistake You cannot test NULL with

= WHERE dept = NULL never matches anything — comparing to an unknown is itself unknown, never TRUE. To test for the blank box you must use the special words:

IS NULL → true when the cell is blank.
IS NOT NULL → true when the cell has a value.

SELECT name FROM employees WHERE dept IS NULL;

Why the topic needs this: the moment a real table has a missing salary or dept, ordinary predicates silently drop those rows. Knowing NULL → UNKNOWN → blocked explains "why did that row vanish?"

4. The predicate — a true/false machine

The picture: imagine a turnstile at the door of every row. The predicate is the guard. TRUE = you pass through; FALSE or UNKNOWN = you're turned away.

Combining and negating predicates:

AND = both guards must say yes (dept = 'Eng' AND salary > 60000).
OR = either guard saying yes is enough.
NOT = flips the answer: NOT (salary > 60000) is TRUE exactly when salary > 60000 is FALSE.

Common mistake Precedence —

AND binds tighter than OR SQL evaluates NOT first, then AND, then OR. So

WHERE dept = 'Eng' OR dept = 'Sales' AND salary > 60000

is read as dept = 'Eng' OR (dept = 'Sales' AND salary > 60000) — probably not what you meant. Add parentheses to force the grouping you want:

WHERE (dept = 'Eng' OR dept = 'Sales') AND salary > 60000

The rule of thumb: when mixing AND and OR, always parenthesise.

Why the topic needs this: the entire WHERE clause is a predicate. WHERE, UPDATE … WHERE, and DELETE … WHERE all reuse this same turnstile idea — see WHERE clause and Predicates.

5. WHERE — the filter, and the terror of leaving it out

This is why the parent chants "No WHERE → everywhere." For SELECT that just shows extra rows (harmless). For UPDATE and DELETE it means you change or erase the whole table. The word WHERE is the single most important safety symbol in the topic.

6. The star `*` and functions like `COUNT`, `AVG`

The picture: a function is a juicer. You drop a column of many numbers in the top; one number drips out the bottom.

Why the topic needs this: SELECT dept, AVG(salary), COUNT(*) uses two juicers. These only make sense once you know that a group of rows can be squeezed together — which needs the next idea.

7. Grouping, HAVING, and aliases

The picture: pour all rows onto a table, then slide them into labelled boxes (Sales box, Eng box). Now AVG runs once per box.

Definition HAVING

HAVING is a predicate turnstile that filters whole buckets after grouping, the way WHERE filters individual rows before grouping.

SELECT dept, COUNT(*) AS n
FROM employees
GROUP BY dept
HAVING COUNT(*) >= 2;

Each dept bucket is asked "do you hold at least 2 rows?" Buckets that answer FALSE are dropped. Because it runs after the juicers, HAVING may test aggregates like COUNT(*) — something WHERE cannot do, since the buckets don't exist yet when WHERE runs.

Why the topic needs this: the parent's GROUP BY dept HAVING COUNT(*) >= 2 and the rule "you can't use an alias in WHERE" both require this bucket picture. WHERE filters rows before bucketing; HAVING filters buckets after. See Aggregate Functions & GROUP BY.

8. The `salary * 1.10` trick — right side reads the current value

Formula Self-referencing assignment

SET salary = salary * 1.10

In SQL, multiply is the star * (not the × you'd write by hand; below we use LaTeX's $\times$ only to describe the arithmetic). Read the line as: new salary $=$ old salary $\times 1.10$ . The right-hand salary is the value before the update; the left-hand salary is where the answer is stored.

Why $\times 1.10$ and not $+ 10%$ ? Multiplying by $1.10$ = keep $100%$ and add $10%$ = $100% + 10% = 110%$ . So $70000 \times 1.10 = 77000$ .

Why the topic needs this: the parent's 10% raise example. Without knowing the right side reads the old value, the arithmetic looks like magic.

9. The semicolon — where one statement ends

Definition Statement terminator

The ==semicolon ;== marks the end of one SQL statement. It lets you send several statements in a row and tells the database where each one stops:

UPDATE employees SET salary = salary + 5000 WHERE dept = 'Sales';
DELETE FROM employees WHERE salary < 60000;

Two statements, two semicolons. Many tools let you omit it for a single statement, but writing it is the safe habit — and it's required when you batch several together.

Why the topic needs this: every multi-statement example in the parent (the forecast-then-verify exercise) relies on ; to separate the steps.

10. Transaction words — BEGIN, COMMIT, ROLLBACK

The picture: a whiteboard you can wipe clean (ROLLBACK) up until you photograph it (COMMIT).

Why the topic needs this: this is the safety net behind the "preview a risky UPDATE" advice — see Transactions — ACID, COMMIT, ROLLBACK.

How these feed the topic

Equipment checklist

Test yourself — cover the right side.

What is a row versus a column?

A row is one record (one employee, read across); a column is one field (the same fact for everyone, read down).

What is a cell?

One row meeting one column — a single value.

What does INT vs VARCHAR(50) allow?

INT = whole numbers; VARCHAR(50) = text up to 50 characters (any symbols, not just letters).

Why is 'Eng' in quotes but 60000 isn't?

'Eng' is a string literal (text); 60000 is an integer, which needs no quotes.

What are the two jobs of = in SQL?

In WHERE it compares (asks true/false); in SET it assigns (stores a value).

Which two spellings both mean "not equal"?

<> and !=.

What do >= and <= add over > and <?

They also pass on a tie (equal counts as a match).

What is NULL, and how do you test for it?

NULL means "unknown / no value"; test with IS NULL / IS NOT NULL, never with =.

What does a comparison against NULL evaluate to, and does the row pass?

UNKNOWN — and the turnstile only passes TRUE, so the row is blocked.

Which binds tighter, AND or OR, and what fixes ambiguity?

AND binds tighter than OR; add parentheses to force the grouping you want.

What is a predicate?

An expression that is TRUE, FALSE, or UNKNOWN for each row — the turnstile guard.

What happens with no WHERE on an UPDATE/DELETE?

Every row passes, so every row is changed or deleted.

What does COUNT(*) return?

The number of rows (in the whole set or per group).

What does GROUP BY dept do before AVG runs?

Sorts rows into one bucket per distinct dept, so AVG runs inside each bucket.

How does HAVING differ from WHERE?

WHERE filters individual rows before grouping; HAVING filters whole buckets after grouping and can test aggregates.

In SET salary = salary * 1.10, what is the right-hand salary, and what does * mean?

The row's current value before the update; * is SQL's multiply operator.

What does the semicolon ; do?

Marks the end of one SQL statement, separating batched statements.

What does ROLLBACK do?

Erases all uncommitted changes in the current transaction, as if they never happened.

Connections

Parent topic
WHERE clause and Predicates — the turnstile guard in full.
Aggregate Functions & GROUP BY — the juicers and buckets.
Transactions — ACID, COMMIT, ROLLBACK — the pencil-draft safety net.
SQL DDL — CREATE, ALTER, DROP — how the grid itself is built.
SQL Joins — combining grids later.