Change rows and tables safely

SQL has two families of verbs, and it helps to keep the names straight because the project checklist uses them. DML, data manipulation, is INSERT, UPDATE and DELETE (and SELECT): it changes the rows, and the tables stay as they were. DDL, data definition, is CREATE, ALTER and DROP: it changes the tables themselves, their columns, types and constraints.

A database you can rebuild is a database you can experiment on. The browser gives you that for free: each change-mode exercise opens a fresh copy, runs your one statement, and shows you what the copy looks like after. The desktop shell gives it to you too, as long as you keep the build script that made the database.

Before any UPDATE or DELETE, run a SELECT with the same WHERE. It shows you the exact rows the change will touch, it shows you how many, and it costs nothing. The tea raise's preview is SELECT id, name, price FROM menu_items WHERE category = 'Tea': seven rows. If the preview shows 45, the WHERE is wrong, and you found out before anything moved.

Then name the rows that must stay the same. 'Only tea changes' means 'coffee, pastries, sandwiches, smoothies and seasonal items do not', and that is a second SELECT you can run after the change to prove it. A change you cannot describe as 'these rows, and not those' is a change you are not ready to make.

Counts are not enough. changes() tells you how many rows a statement touched, and seven is the right number for the tea raise. Seven is also the number you get if you raise seven of the wrong items. Look at the rows, not only the count.

INSERT INTO menu_items (name, category, size, price) VALUES ('Chai Latte', 'Tea', 'Large', 5.50) adds one row. Three habits live in that one statement. Name the columns: SQL lets you skip the column list and supply every value in table order, and every script that does so breaks the day someone adds a column. Leave id out: INTEGER PRIMARY KEY hands out the next number, and the row gets 46 because 45 was the last. Read changes() afterwards, or look at the row: one row, the one you meant.

INSERT INTO menu_items (name, category, size, price)
VALUES ('Chai Latte', 'Tea', 'Large', 5.50);

A column you do not name gets its DEFAULT if the table has one, and NULL if it does not. calories, is_seasonal and description are not in the list above, so the new row has NULL in all three. That is fine when NULL means 'not recorded yet' and wrong when a report will treat it as zero.

Every constraint the table was created with is a rule about which inserts fail. A menu item with no name is refused: NOT NULL constraint failed: menu_items.name. An employee whose shop_id names a shop that does not exist is refused, once foreign keys are on: FOREIGN KEY constraint failed. The message names the rule that fired, which is why you paste error messages into reports as text: the wording is the diagnosis. A refused insert is not a failure of the exercise. It is the table doing its job, and you will write inserts that must fail on purpose in Lab 10.

UPDATE menu_items SET price = ROUND(price * 1.05, 2) WHERE category = 'Tea' raises seven prices. SET takes one or more column = value pairs, and the value can be an expression that uses the row's current values. WHERE picks the rows. Leave the WHERE off and the statement is still valid SQL, it still reports success, and it changes every row in the table. There is no confirmation step. The WHERE is the statement.

Read changes() immediately after. It is the number of rows the last statement touched, seven for the tea raise, 45 for the version without a WHERE. A zero means the WHERE matched nothing, which is a result to notice, not to accept.

An UPDATE has to satisfy every constraint an insert does. Set a price to NULL and the engine refuses: NOT NULL constraint failed, and the row is untouched.

DELETE FROM orders WHERE order_date < '2026-01-20' removes whole rows, 71 of them. The same two rules apply: preview with the same WHERE first, and read what remains. 204 orders minus 71 is 133, and SELECT COUNT(*), MIN(order_date) FROM orders afterwards should say 133 and 2026-01-20. If it says 122, your boundary ate the 20th.

DELETE with no WHERE empties the table, row by row, with no undo in the browser and no undo on the desktop either, unless you were inside a transaction. The disposable copy means you can try it once to see what 'no rows' looks like.

ALTER TABLE shops ADD COLUMN manager_name TEXT adds a column to every existing row at once. Each row gets NULL in it, or the DEFAULT if you give one: ALTER TABLE employees ADD COLUMN is_active INTEGER NOT NULL DEFAULT 1 gives every employee a 1. A NOT NULL column with no DEFAULT is refused, because the existing rows would have nothing to hold, and so is a UNIQUE column: the engine cannot invent distinct values for rows that already exist.

DROP TABLE menu_items_old removes the table and every row in it, in one statement, with no undo. IF EXISTS makes a rerun harmless, which is why build scripts start with DROP TABLE IF EXISTS only when the author means 'start over'. With foreign keys on, a parent table that other tables point at refuses to be dropped: DROP TABLE shops fails while employees and orders reference it. Before you type DROP, say which table must never be dropped and why.

The browser engine is an older SQLite. It can add a column and drop a table. It cannot rename a column or drop one; the desktop SQLite you use for the labs can do both. For anything an ALTER cannot do, such as changing a column's type or adding a constraint to an existing column, the pattern on both engines is the rebuild: create the new table with the right definition, INSERT ... SELECT the rows across, drop the old table, rename the new one.

A coaching booking at Harbor Snow is two writes: insert the booking, debit the guest's prepaid balance. If the second one fails, the first one is a booking nobody paid for. A transaction makes the two succeed or fail together: BEGIN, both writes, check changes() and the balance, then COMMIT when it is right or ROLLBACK when it is not.

The part people get wrong: a failing statement inside a transaction does not undo the ones before it. When the debit is refused by the balance's CHECK, the transaction is still open and the booking insert is still pending. Nothing is committed and nothing is undone until you say which. The error is information; ROLLBACK is the decision.

The browser playground runs one statement at a time on a copy, so it cannot hold a transaction open. Transactions are a desktop skill: open the Harbor Snow kit in the sqlite3 shell, enter each statement separately, and watch the balances between them.