Database programming

DDL, data definition language, changes the structure of the database — tables, columns, keys. DML, data manipulation language, reads and changes the rows inside tables that already exist. The test is shape versus contents.

CREATE makes a new table with its columns, types and keys; ALTER changes an existing structure, such as adding a column; DROP removes a table entirely, along with everything in it.

SELECT reads rows matching a condition; INSERT INTO adds a new row; UPDATE SET changes values in existing rows; DELETE removes rows from a table.

DELETE removes rows and the table stays, ready for more data — it is DML. DROP removes the table itself, with its structure and all its contents — it is DDL.

DDL is run rarely, by a database administrator, when the design genuinely changes. DML is run constantly, by the application, every time somebody uses the system.

It names the two columns that must match — almost always a foreign key and the primary key it points at. Without it, every row of one table pairs with every row of the other.

FROM and JOIN decide which rows exist, WHERE filters them, GROUP BY collapses them, HAVING filters the groups, SELECT picks the columns, and ORDER BY sorts. SELECT is written first but applied near the end.

WHERE filters individual rows before grouping. HAVING filters groups after grouping, and is the only place a condition on a count or total can go, because that value does not exist until GROUP BY has run.

% stands for any run of characters. 'Sm%' matches anything starting Sm, '%son' anything ending son, and '%ann%' anything containing ann.

When the join can legitimately produce the same row more than once — a student belonging to two clubs would otherwise appear twice in a list of names.

INSERT INTO adds a new row; UPDATE SET changes values in rows that already exist; DELETE removes rows. All three are DML, so the table's structure is untouched.

It applies to every row in the table, with no warning, no confirmation and no undo once committed. The safe habit is to write the WHERE clause first and test it with a SELECT.

It is a separate sorted structure the database can go straight to, instead of reading every row. But every INSERT, UPDATE or DELETE on an indexed column must update that structure as well as the row.

After many changes an index's entries no longer sit in an efficient order, so it gradually stops helping. Reorganising tidies it in place; rebuilding constructs it from scratch. Both are usually scheduled for quiet hours.

With indexes present, every one of the millions of inserted rows updates every index individually. Building each index once at the end is a single sorted pass, which is far cheaper.

**COUNT**, **SUM**, **AVG**, **MIN**, **MAX** — each collapsing many rows into one value.

**WHERE** filters rows **before** grouping. **HAVING** filters groups **after**. A condition on an aggregate can only be HAVING.

**COUNT(*)** counts rows. **COUNT(column)** counts rows where that column is not null — the difference between 1 and 0 after a LEFT JOIN.

**AVG, SUM, MIN and MAX ignore them** — 10 rows with 3 nulls means AVG divides by 7. COUNT(*) is the exception.

Every column in SELECT that is **not inside an aggregate**, or there is no single value to show for the group.

A **stored query with a name**, used like a table. It holds **no data of its own**.

It stores the **query**, not the result, so every read runs it afresh — there is nothing to go stale.

Grant rights on the **view only**. With no permission on the underlying table, a user cannot reach a column the view does not select.

**No.** The underlying query runs on every read. A **materialised view** stores the result and is fast, but is only current to the last refresh.

Sometimes — a simple view over one table often yes. One with a **join, GROUP BY or aggregate** usually not: there is no single underlying row to change.

A group of statements treated as **one indivisible unit** — COMMIT makes them permanent, ROLLBACK undoes them all.

**Atomicity** (all or none), **Consistency** (rules still hold), **Isolation** (no interference), **Durability** (survives a crash).

**Isolation** — achieved by locking the row, so the second transaction waits and then reads the updated balance.

That the change has reached **non-volatile storage** before COMMIT returns — which is why a commit waits for the disc.

Each transaction holds what the other wants, so neither can proceed. The database **aborts one**, which then retries.