SQL reads, writes, joins, and aggregates

Start with the question

Use the study fixture from Tables, data types, and constraints. Open psql against study. Fully qualified names, such as study.entries, make the target schema and table explicit.

Read a SELECT from its purpose: FROM chooses the input relation, WHERE keeps matching rows, the select list chooses output columns/expressions, and ORDER BY makes their order explicit. SQL is declarative: you state the result you want, while PostgreSQL chooses an execution plan.

SELECT entry_id, account_id, amount_cents
FROM study.entries
WHERE amount_cents < 0
ORDER BY created_at, entry_id;

SELECT entry_id FROM study.entries WHERE note IS NULL ORDER BY entry_id;

The first query selects the -250-cent row; the second selects the two unannotated rows. note = NULL is not a null test. Filtering happens before ordering; LIMIT without a deterministic order is not a reliable “latest” query. Comparisons↗, sorting↗.

Keep accounts with no entries

SELECT a.account_id, a.label,
       count(e.entry_id) AS entry_count,
       coalesce(sum(e.amount_cents), 0) AS balance_cents
FROM study.accounts AS a
LEFT JOIN study.entries AS e ON e.account_id = a.account_id
GROUP BY a.account_id, a.label
ORDER BY a.account_id;

Expected balances are 750, 2000, and 0, with counts 2, 1, and 0. An inner join excludes the empty account. count(*) would count its null-extended join row; count(e.entry_id) counts matching entries. WHERE filters input rows and HAVING filters groups. Moving a right-table filter from ON to WHERE can accidentally remove empty accounts. Joins↗, aggregates↗, table expressions↗.

A window calculation lets you compute a running total while retaining each entry:

SELECT account_id, entry_id, amount_cents,
       sum(amount_cents) OVER (
         PARTITION BY account_id ORDER BY created_at, entry_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_cents
FROM study.entries
ORDER BY account_id, created_at, entry_id;

For account 1, the running values are 1000 then 750. This is a query over the fixture, not evidence of MicroBank's accounting implementation. Window functions↗.

Practice a reversible write

BEGIN;
INSERT INTO study.accounts VALUES (4, 'temporary') RETURNING account_id;
UPDATE study.accounts SET label = 'temporary-renamed'
WHERE account_id = 4 RETURNING account_id, label;
DELETE FROM study.accounts WHERE account_id = 4 RETURNING account_id;
ROLLBACK;

Check the affected rows. Without WHERE, an UPDATE or DELETE can affect every row in the table. RETURNING exposes what changed within this transaction; rollback means those changes are not durable.

Applications should bind values through their database driver's parameter API. Never concatenate user input into SQL. This SQL-level example separates the query from a value:

PREPARE account_entries(bigint) AS
SELECT entry_id, amount_cents FROM study.entries
WHERE account_id = $1 ORDER BY entry_id;
EXECUTE account_entries(1);
DEALLOCATE account_entries;

Driver placeholder syntax varies; value parameters do not substitute table names. Prepared statements↗.

Checkpoint

Write a query for accounts whose total exceeds 1000 cents using HAVING, and one for accounts with no entries. Predict the rows before running either. Explain why an HTTP 200 response alone cannot establish that the corresponding row was committed.

Compare your checkpoint answers

Run these in the same study session after attempting them yourself:

SELECT account_id, sum(amount_cents) AS balance_cents
FROM study.entries
GROUP BY account_id
HAVING sum(amount_cents) > 1000
ORDER BY account_id;

SELECT a.account_id, a.label
FROM study.accounts AS a
WHERE NOT EXISTS (
  SELECT 1 FROM study.entries AS e WHERE e.account_id = a.account_id
)
ORDER BY a.account_id;

Expected: account 2 with 2000 cents, then account 3 labelled demo-empty. The first filters groups after aggregation. The second asks whether a matching entry exists, so it does not need to count a null-extended join row. An HTTP response can describe acceptance or another application-defined stage; durable commit must be part of the actual contract and verification. Continue to transactions to see why statement success and commit are separate.