SQL and PostgreSQL Fundamentals for Java Developers

Project 1 stored expenses in a CSV file, and its review table listed the weaknesses: rewriting the whole file for every command, lost updates when two programs run at once, ids that can be reused, and no way to ask questions such as "total per category" without reading everything. A database exists to solve exactly these problems. This article teaches the part of SQL you need for backend work, using PostgreSQL, one of the most widely used open-source relational databases.

Every statement and every result below was run on a real PostgreSQL 18.6 server. The results are shown in the aligned text layout that PostgreSQL's own psql tool uses; they were produced by a small test program running the statements through JDBC, so the layout imitates psql while the data, error messages and command tags come from the server.

• What a relational database is, and how to design simple tables with constraints.
• The core SQL: INSERT, SELECT, UPDATE, DELETE, joins, grouping and aggregates.
• How NULL behaves, and why it surprises people.
• Transactions: what atomicity and isolation mean, shown with two sessions.
• Indexes, query plans, and normalisation.

A relational database stores data in tables. A table has named, typed columns and holds rows, one row per thing (one expense, one category). Tables are linked by keys:

• A primary key uniquely identifies a row in its table (expenses.id).
• A foreign key is a column that refers to the primary key of another table (expenses.category_id refers to categories.id), so a link between two rows is a stored value that the database can check.

SQL (Structured Query Language) is the language for all of it. It is declarative: you describe the result you want, and the database decides how to compute it. The same SQL core works across PostgreSQL, MySQL, Oracle and SQL Server, although each has its own extensions. What follows is PostgreSQL.

You will see statements grouped into three kinds:

(Keywords such as SELECT are shown in upper case by convention. SQL does not care, but readers do.)

The database for the Expense Tracker needs a table of categories and a table of expenses that refers to it. This is the DDL, with a constraint for each rule that must never be broken:

CREATE TABLE categories (    id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    name text NOT NULL UNIQUE);CREATE TABLE expenses (    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    spent_on     date   NOT NULL,    amount_cents bigint NOT NULL CHECK (amount_cents > 0),    category_id  integer NOT NULL REFERENCES categories (id),    description  text   NOT NULL DEFAULT '');

CREATE TABLECREATE TABLE

Each part has a job:

• GENERATED ALWAYS AS IDENTITY makes the database assign a unique, increasing number, so no two rows share an id and no id is reused by accident (unlike Project 1's max + 1).
• PRIMARY KEY means unique and not null, and creates an index on it.
• NOT NULL forbids a missing value. UNIQUE forbids duplicates. CHECK (...) forbids values that break a condition. DEFAULT supplies a value when none is given.
• REFERENCES categories (id) is the foreign key: an expense must point to a category that exists.

Older style. Before PostgreSQL 10, auto-numbered keys were written with the serial type (id serial PRIMARY KEY). It still works and is common in older schemas and tutorials. The standard GENERATED ... AS IDENTITY form is preferred in new code, and GENERATED ALWAYS also stops code from supplying an id by accident.

These constraints are the point: the database refuses bad data no matter which program sends it. A check in Java is a good first line of defence, but it protects only the code that includes it. A constraint protects the data from every program, script and future developer.

INSERT INTO categories (name)VALUES ('FOOD'), ('TRANSPORT'), ('HOUSING'), ('UTILITIES'), ('HEALTH'), ('ENTERTAINMENT'), ('OTHER');INSERT INTO expenses (spent_on, amount_cents, category_id, description)VALUES ('2026-09-01', 120000, 3, 'rent'),       ('2026-09-03', 1250, 1, 'lunch with Sam'),       ('2026-09-05', 4500, 2, 'monthly metro pass'),       ('2026-09-10', 8900, 4, 'electricity'),       ('2026-09-12', 2300, 1, 'groceries'),       ('2026-09-20', 1500, 6, 'cinema'),       ('2026-10-02', 320, 1, 'coffee, and a muffin'),       ('2026-10-03', 4500, 2, 'train ticket');

INSERT 0 7INSERT 0 8

The result INSERT 0 8 is a command tag: the statement ran and inserted 8 rows. (The 0 is a historical placeholder.) Strings use single quotes; double quotes are for identifiers such as column names.

RETURNING gives back values the database generated, such as the new id, so you do not need a second query:

INSERT INTO expenses (spent_on, amount_cents, category_id, description)VALUES ('2026-10-04', 700, 5, 'pharmacy')RETURNING id, spent_on, amount_cents;

 id |  spent_on  | amount_cents----+------------+--------------  9 | 2026-10-04 |          700(1 row)

You will use RETURNING id from Java in the next article.

SELECT id, spent_on, amount_cents, descriptionFROM expensesORDER BY spent_on, id;

 id |  spent_on  | amount_cents |     description----+------------+--------------+----------------------  1 | 2026-09-01 |       120000 | rent  2 | 2026-09-03 |         1250 | lunch with Sam  3 | 2026-09-05 |         4500 | monthly metro pass  4 | 2026-09-10 |         8900 | electricity  5 | 2026-09-12 |         2300 | groceries  6 | 2026-09-20 |         1500 | cinema  7 | 2026-10-02 |          320 | coffee, and a muffin  8 | 2026-10-03 |         4500 | train ticket  9 | 2026-10-04 |          700 | pharmacy(9 rows)

The parts of a query, in the order you write them:

An important detail: without ORDER BY, the database may return rows in any order, and the order can change between runs. If order matters, say so.

SELECT id, amount_cents, descriptionFROM expensesWHERE spent_on >= '2026-09-01' AND spent_on < '2026-10-01'  AND amount_cents BETWEEN 1000 AND 10000ORDER BY amount_cents DESCLIMIT 3;

 id | amount_cents |    description----+--------------+--------------------  4 |         8900 | electricity  3 |         4500 | monthly metro pass  5 |         2300 | groceries(3 rows)

Filtering by a month is written as >= first day AND < first day of next month, a half-open range. It works for dates and timestamps and keeps indexes usable (indexes below). Other useful predicates: IN (1, 2, 3), LIKE 'lun%' (% is any text), ILIKE for case-insensitive matching (a PostgreSQL extension), and AND, OR, NOT.

NULL means no value or unknown, not zero and not the empty string. Because "unknown equals unknown" is itself unknown, comparisons with NULL produce neither true nor false:

SELECT NULL = NULL AS equals_null, NULL IS NULL AS is_null,       NULL = 1 AS compare_with_one, 5 + NULL AS arithmetic;

 equals_null | is_null | compare_with_one | arithmetic-------------+---------+------------------+------------             | t       |                  |(1 row)

NULL = NULL is NULL (shown as an empty cell), while NULL IS NULL is true (t). Anything combined with NULL through arithmetic is NULL. The rules to remember:

• Test with IS NULL / IS NOT NULL, never = NULL.
• WHERE keeps only rows where the condition is true, so rows where it is NULL disappear silently.
• Most aggregates (SUM, AVG, COUNT(column)) ignore NULLs. COUNT(*) counts rows.
• If a column should never be missing, declare it NOT NULL, as we did, so the question never arises.

This is also why Optional and null handling in Java (article 17) matters when mapping rows: a nullable column becomes a value that may be absent.

SELECT count(*) AS expenses, sum(amount_cents) AS total_cents,       min(spent_on) AS first_day, max(spent_on) AS last_dayFROM expenses;

 expenses | total_cents | first_day  |  last_day----------+-------------+------------+------------        9 |      143970 | 2026-09-01 | 2026-10-04(1 row)

GROUP BY computes an aggregate per group. This query answers the CLI's summary command in one statement:

SELECT category_id, count(*) AS n, sum(amount_cents) AS total_centsFROM expensesWHERE spent_on >= '2026-09-01' AND spent_on < '2026-10-01'GROUP BY category_idORDER BY total_cents DESC;

 category_id | n | total_cents-------------+---+-------------           3 | 1 |      120000           4 | 1 |        8900           2 | 1 |        4500           1 | 2 |        3550           6 | 1 |        1500(5 rows)

The rule: every column in SELECT must either appear in GROUP BY or be inside an aggregate function. Forgetting this is the commonest GROUP BY error, and PostgreSQL reports it clearly:

SELECT category_id, description, sum(amount_cents)FROM expensesGROUP BY category_id;

ERROR:  column "expenses.description" must appear in the GROUP BY clause or be used in an aggregate function

WHERE versus HAVING: WHERE filters rows before grouping, HAVING filters groups after aggregating:

SELECT category_id, sum(amount_cents) AS total_centsFROM expensesGROUP BY category_idHAVING sum(amount_cents) > 5000ORDER BY category_id;

 category_id | total_cents-------------+-------------           2 |        9000           3 |      120000           4 |        8900(3 rows)

A category id such as 3 means little to a reader. A join combines rows from two tables where a condition matches, normally a foreign key equal to a primary key:

SELECT e.id, e.spent_on, c.name AS category, e.amount_centsFROM expenses eJOIN categories c ON c.id = e.category_idWHERE e.spent_on < '2026-09-06'ORDER BY e.spent_on;

 id |  spent_on  | category  | amount_cents----+------------+-----------+--------------  1 | 2026-09-01 | HOUSING   |       120000  2 | 2026-09-03 | FOOD      |         1250  3 | 2026-09-05 | TRANSPORT |         4500(3 rows)

e and c are aliases (short names for the tables). JOIN ... ON is an inner join: only rows with a match on both sides appear. That causes a classic surprise. Which categories have no expenses? An inner join cannot show them, because they have nothing to match. A left join keeps every row from the left table and fills the right side with NULL when there is no match:

SELECT c.name AS category, count(e.id) AS expenses, coalesce(sum(e.amount_cents), 0) AS total_centsFROM categories cLEFT JOIN expenses e ON e.category_id = c.idGROUP BY c.nameORDER BY c.name;

   category    | expenses | total_cents---------------+----------+------------- ENTERTAINMENT |        1 |        1500 FOOD          |        3 |        3870 HEALTH        |        1 |         700 HOUSING       |        1 |      120000 OTHER         |        0 |           0 TRANSPORT     |        2 |        9000 UTILITIES     |        1 |        8900(7 rows)

Two details make that query correct. count(e.id) counts only matched rows (it ignores the NULL that the left join produced), whereas count(*) would count 1 for an empty category. And coalesce(x, 0) replaces a NULL with a default: sum over no rows is NULL, not 0.

A common mistake is forgetting the join condition, which produces a cartesian product (every row paired with every other: 9 expenses times 7 categories is 63 rows). Always write ON.

An expense belongs to one category, and a category has many expenses: one-to-many, modelled with a foreign key on the "many" side. If an expense could carry several tags and a tag could be on many expenses, that is many-to-many, which needs a join table with a foreign key to each side:

CREATE TABLE tags (    id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    name text NOT NULL UNIQUE);CREATE TABLE expense_tags (    expense_id bigint  NOT NULL REFERENCES expenses (id) ON DELETE CASCADE,    tag_id     integer NOT NULL REFERENCES tags (id),    PRIMARY KEY (expense_id, tag_id));INSERT INTO tags (name) VALUES ('work'), ('shared');INSERT INTO expense_tags (expense_id, tag_id) VALUES (2, 1), (2, 2), (5, 2);SELECT t.name AS tag, e.descriptionFROM tags tJOIN expense_tags et ON et.tag_id = t.idJOIN expenses e ON e.id = et.expense_idORDER BY t.name, e.description;

CREATE TABLECREATE TABLEINSERT 0 2INSERT 0 3  tag   |  description--------+---------------- shared | groceries shared | lunch with Sam work   | lunch with Sam(3 rows)

The composite primary key (expense_id, tag_id) stops the same tag being attached twice. ON DELETE CASCADE means deleting an expense also deletes its rows in the join table, which is a choice you should make deliberately, because it deletes data without asking. The later JPA articles map exactly this structure.

UPDATE expenses SET amount_cents = 1300, description = 'lunch with Sam (corrected)' WHERE id = 2;DELETE FROM expenses WHERE id = 8;

UPDATE 1DELETE 1

The command tags show the number of rows affected. UPDATE and DELETE without WHERE change every row. This is the most expensive typo in SQL, so make it a habit to write the WHERE first, to run the same condition as a SELECT before changing data, and to use transactions (below) for anything risky.

Constraints show their value when something tries to break a rule. Each of these is a real server error:

INSERT INTO categories (name) VALUES ('FOOD');INSERT INTO expenses (spent_on, amount_cents, category_id) VALUES ('2026-10-05', -5, 1);INSERT INTO expenses (spent_on, amount_cents, category_id) VALUES ('2026-10-05', 500, 99);INSERT INTO expenses (spent_on, amount_cents, category_id) VALUES (NULL, 500, 1);DELETE FROM categories WHERE id = 1;

ERROR:  duplicate key value violates unique constraint "categories_name_key"DETAIL:  Key (name)=(FOOD) already exists.ERROR:  new row for relation "expenses" violates check constraint "expenses_amount_cents_check"DETAIL:  Failing row contains (10, 2026-10-05, -5, 1, ).ERROR:  insert or update on table "expenses" violates foreign key constraint "expenses_category_id_fkey"DETAIL:  Key (category_id)=(99) is not present in table "categories".ERROR:  null value in column "spent_on" of relation "expenses" violates not-null constraintDETAIL:  Failing row contains (12, null, 500, 1, ).ERROR:  update or delete on table "categories" violates foreign key constraint "expenses_category_id_fkey" on table "expenses"DETAIL:  Key (id)=(1) is still referenced from table "expenses".

Each message names the rule that was broken and, usually, the offending value. Notice something else in the DETAIL lines: the failing rows were given ids 10 and 12 although the failed inserts stored nothing. Identity values are never handed back, even when the insert fails or is rolled back, so gaps in the id sequence are normal and you must never rely on ids being consecutive.

The five errors: a duplicate name (UNIQUE), a negative amount (CHECK), a category that does not exist (the foreign key), a missing date (NOT NULL), and deleting a category that expenses still refer to (the foreign key again, which protects against orphaned rows). When Java code gets one of these through JDBC, it arrives as an exception (article 28), and recognising the cause is part of the job.

Often several statements belong together. Moving an expense between categories and recording the change in a log, or creating an order and reducing stock: either everything must happen or nothing. A transaction groups statements so they succeed or fail as one unit. Its guarantees are summarised as ACID:

You open a transaction with BEGIN, finish it with COMMIT (keep the changes) or ROLLBACK (discard them). Outside a transaction block, each statement is its own transaction and commits on its own (autocommit).

BEGIN;INSERT INTO expenses (spent_on, amount_cents, category_id, description)VALUES ('2026-10-05', 999, 1, 'this one is fine');INSERT INTO expenses (spent_on, amount_cents, category_id, description)VALUES ('2026-10-05', 0, 1, 'this one breaks the check');SELECT count(*) AS visible_inside_failed_transaction FROM expenses;ROLLBACK;SELECT count(*) AS rows_after_rollback FROM expenses WHERE spent_on = '2026-10-05';

BEGININSERT 0 1ERROR:  new row for relation "expenses" violates check constraint "expenses_amount_cents_check"DETAIL:  Failing row contains (14, 2026-10-05, 0, 1, this one breaks the check).ERROR:  current transaction is aborted, commands ignored until end of transaction blockROLLBACK rows_after_rollback---------------------                   0(1 row)

The first insert was valid, and the second broke the CHECK. In PostgreSQL, after an error inside a transaction, the transaction is aborted: every later statement is refused until you end it with ROLLBACK. After the rollback, the first insert was discarded too: nothing from that transaction remains. That all-or-nothing behaviour is atomicity.

Two sessions (two connections to the database) show isolation. The lines starting with -- session say which session runs the statements that follow; session A changes a row, and session B reads it before and after A commits:

-- session ABEGIN;UPDATE expenses SET amount_cents = 2000 WHERE id = 5;SELECT amount_cents AS a_sees FROM expenses WHERE id = 5;-- session BSELECT amount_cents AS b_sees_before_commit FROM expenses WHERE id = 5;-- session ACOMMIT;-- session BSELECT amount_cents AS b_sees_after_commit FROM expenses WHERE id = 5;

-- session ABEGINUPDATE 1 a_sees--------   2000(1 row)-- session B b_sees_before_commit----------------------                 2300(1 row)-- session ACOMMIT-- session B b_sees_after_commit---------------------                2000(1 row)

Session B never saw the uncommitted 2000: it saw the old value until A committed. That is the default isolation level in PostgreSQL, Read Committed: a statement sees only data that was committed before the statement began. PostgreSQL's documentation describes four standard levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) and notes that its Read Uncommitted behaves like Read Committed, so a dirty read, seeing another transaction's uncommitted data, cannot happen. Stronger levels trade concurrency for fewer surprises. The interview-level knowledge is:

The lost update is the database form of the race condition from article 19, and the same reasoning applies: read-modify-write sequences need protection. The JPA articles show the optimistic locking approach.

Keep transactions short: they hold locks and resources, and a transaction left open (for example while waiting for a user) blocks others.

Without help, finding rows that match a condition means reading the whole table (a sequential scan). An index is an extra structure, by default a B-tree, that lets the database jump to matching rows, as the index of a book does. To see the effect, generate a bigger table with generate_series and look at the query plan, the database's description of how it will run the query. EXPLAIN shows the plan without running the query (and (COSTS OFF) hides the estimated numbers, which would otherwise change from machine to machine):

CREATE TABLE big_expenses ASSELECT g AS id,       DATE '2025-01-01' + (g % 365) AS spent_on,       100 + (g % 5000) AS amount_centsFROM generate_series(1, 50000) AS g;ANALYZE big_expenses;EXPLAIN (COSTS OFF) SELECT * FROM big_expenses WHERE spent_on = DATE '2025-06-15';

CREATE TABLEANALYZE                QUERY PLAN------------------------------------------- Seq Scan on big_expenses   Filter: (spent_on = '2025-06-15'::date)(2 rows)

Seq Scan with a filter means every row was examined. Now add an index on that column:

CREATE INDEX idx_big_expenses_spent_on ON big_expenses (spent_on);ANALYZE big_expenses;EXPLAIN (COSTS OFF) SELECT * FROM big_expenses WHERE spent_on = DATE '2025-06-15';

CREATE INDEXANALYZE                      QUERY PLAN------------------------------------------------------ Bitmap Heap Scan on big_expenses   Recheck Cond: (spent_on = '2025-06-15'::date)   ->  Bitmap Index Scan on idx_big_expenses_spent_on         Index Cond: (spent_on = '2025-06-15'::date)(4 rows)

The plan now starts from the index. (PostgreSQL chooses a bitmap scan when a condition matches a moderate number of rows. It chooses a plain index scan when the matches are few, and it may legitimately choose a sequential scan when it expects to read most of the table. The planner decides from statistics (that is what ANALYZE refreshes), which is why you check with EXPLAIN instead of assuming.)

Index rules of thumb:

• Primary keys and UNIQUE constraints are indexed automatically. Foreign keys are not in PostgreSQL, so index them yourself when you join or filter on them often (category_id in a big expenses table).
• Index the columns you filter, join and sort on, in the queries that matter.
• Every index has a cost: it takes disk space, and every INSERT, UPDATE and DELETE must maintain it. Do not index everything.
• An index on (a, b) helps queries that filter on a, or on a and b, but generally not on b alone.
• Wrapping an indexed column in a function (WHERE date_trunc('month', spent_on) = ...) usually stops the plain index from being used. This is why the earlier month filter used a half-open range on the bare column.
• Small tables do not need indexes. Measure with EXPLAIN on realistic data volumes.

Normalisation is organising tables so that each fact is stored in one place. See what happens when it is not. Here the category name is copied into every expense row:

CREATE TABLE flat_expenses (    id            integer PRIMARY KEY,    description   text,    category_name text);INSERT INTO flat_expenses VALUES (1, 'lunch', 'FOOD'), (2, 'groceries', 'FOOD'), (3, 'metro', 'TRANSPORT');UPDATE flat_expenses SET category_name = 'FOOD & DRINK' WHERE id = 1;SELECT category_name, count(*) AS rows FROM flat_expenses GROUP BY category_name ORDER BY category_name;

CREATE TABLEINSERT 0 3UPDATE 1 category_name | rows---------------+------ FOOD          |    1 FOOD & DRINK  |    1 TRANSPORT     |    1(3 rows)

The rename was applied to one row only, so the same category now has two spellings: an update anomaly. The same duplication causes insert anomalies (you cannot record a category without an expense) and delete anomalies (deleting the last expense loses the category). The cure is the design you already have: put categories in their own table and refer to them by id.

The normal forms are steps of this idea:

Normalise by default, because it keeps data consistent. Denormalise (deliberately repeat data) only for a measured reason, such as a read-heavy report, and accept the responsibility of keeping the copies in sync.

Store money as integer cents (bigint), as Project 1 did, or as numeric(12, 2). Both are exact. Never use real or double precision for money: they are approximate binary fractions, and sums drift. With numeric, the JDBC type is BigDecimal; with cents, it is long. Whichever you choose, decide once and apply it everywhere. Store the currency too if you ever handle more than one.

• UPDATE or DELETE without WHERE.
• Comparing with = NULL instead of IS NULL.
• Relying on row order without ORDER BY.
• A missing join condition, which multiplies rows.
• Using an inner join and losing rows that have no match (use a left join).
• SELECT * in application code, which breaks when columns change and fetches data you do not need.
• Checking rules only in Java and having no constraints in the database.
• Using float or double precision for money.
• Indexing everything, or nothing, without looking at plans.
• Long-running transactions, and ignoring that PostgreSQL aborts a transaction after an error.
• Building SQL by joining strings with user input, which is SQL injection (article 28).

SELECT c.name, max(e.amount_cents) AS largest_centsFROM categories cJOIN expenses e ON e.category_id = c.idGROUP BY c.nameORDER BY largest_cents DESC;

     name      | largest_cents---------------+--------------- HOUSING       |        120000 UTILITIES     |          8900 TRANSPORT     |          4500 FOOD          |          2000 ENTERTAINMENT |          1500 HEALTH        |           700(6 rows)

SELECT c.nameFROM categories cLEFT JOIN expenses e ON e.category_id = c.idWHERE e.id IS NULLORDER BY c.name;

 name------- OTHER(1 row)

SELECT e.id, e.amount_centsFROM expenses e JOIN categories c ON c.id = e.category_idWHERE c.name = 'FOOD'ORDER BY e.id;

 id | amount_cents----+--------------  2 |         1300  5 |         2000  7 |          320(3 rows)

BEGIN;UPDATE expenses SET amount_cents = amount_cents + 10WHERE category_id = (SELECT id FROM categories WHERE name = 'FOOD');SELECT id, amount_cents FROM expenses WHERE category_id = 1 ORDER BY id;ROLLBACK;

BEGINUPDATE 3 id | amount_cents----+--------------  2 |         1310  5 |         2010  7 |          330(3 rows)ROLLBACK

• Next: JDBC: Talking to a Database from Java connects Java to these tables, safely.
• Then: Project 2: Expense Tracker on PostgreSQL uses this exact schema behind the ExpenseRepository interface.
• Later, for how JPA hides and sometimes hurts this SQL: Relationships, Lazy Loading, N+1 and Transactions.