Design a relational database schema and write SQL statements to create tables, insert data, query with joins, and update or delete rows

What this dot point is asking

NESA wants you to model data as relational tables, declare primary and foreign keys, and write the standard SQL operations: CREATE, SELECT (with WHERE, JOIN, ORDER BY, aggregates), INSERT, UPDATE, DELETE.

The answer

Schema design

Each table represents one kind of thing. Columns are attributes. Rows are individual records.

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  username TEXT NOT NULL UNIQUE,
  email TEXT NOT NULL UNIQUE,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE notes (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_notes_user ON notes(user_id);

Key concepts:

• Primary key: uniquely identifies a row. Usually an integer surrogate key.
• Foreign key (REFERENCES): a column whose value must exist as a primary key in another table. Enforces referential integrity.
• NOT NULL: the column must always have a value.
• UNIQUE: no two rows can share this value.
• DEFAULT: a value used when none is supplied.
• INDEX: speeds up queries that filter or join on the column.

SELECT

SELECT id, title, created_at
FROM notes
WHERE user_id = 12
ORDER BY created_at DESC
LIMIT 20;

JOIN

Combine rows from two tables on a matching column:

SELECT n.id, n.title, u.username
FROM notes AS n
INNER JOIN users AS u ON u.id = n.user_id
WHERE n.created_at > '2026-01-01';

Variants:

• INNER JOIN: only rows that match in both tables.
• LEFT JOIN: every row from the left table, plus matches from the right (NULL if no match).
• RIGHT JOIN / FULL JOIN: less common in HSC examples.

Aggregates and GROUP BY

SELECT user_id, COUNT(*) AS note_count
FROM notes
GROUP BY user_id
HAVING COUNT(*) > 5
ORDER BY note_count DESC;

Common aggregate functions: COUNT, SUM, AVG, MIN, MAX.

INSERT

INSERT INTO notes (user_id, title, body)
VALUES (12, 'First note', 'This is the body.');

UPDATE

UPDATE notes
SET title = 'Renamed'
WHERE id = 42 AND user_id = 12;

Always include a WHERE clause. UPDATE notes SET title = 'X' without WHERE updates every row.

DELETE

DELETE FROM notes WHERE id = 42 AND user_id = 12;

Same warning as UPDATE: never run without WHERE except deliberately.

Transactions

Group multiple statements into one atomic operation:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

If anything fails, ROLLBACK undoes the partial change. This is the ACID "A" (atomicity) at work.

ACID

Relational databases guarantee:

• Atomicity: a transaction either fully succeeds or fully fails.
• Consistency: constraints (NOT NULL, UNIQUE, foreign keys) are never violated.
• Isolation: concurrent transactions do not see each other's intermediate state.
• Durability: once committed, the data survives crashes.

Calling SQL from application code

Always use parameterised queries:

import sqlite3

with sqlite3.connect("app.db") as conn:
    cur = conn.execute(
        "SELECT id, title FROM notes WHERE user_id = ? ORDER BY id DESC",
        (user_id,),
    )
    notes = cur.fetchall()

Never interpolate user input with f-strings - that is the SQL injection door.

Worked schema

A booking site needs users, events and bookings.

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  password_hash TEXT NOT NULL
);

CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  starts_at TEXT NOT NULL,
  capacity INTEGER NOT NULL CHECK (capacity > 0)
);

CREATE TABLE bookings (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  event_id INTEGER NOT NULL REFERENCES events(id),
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (user_id, event_id)
);

The UNIQUE constraint prevents a user double-booking the same event. A typical query:

SELECT e.title, e.starts_at
FROM bookings AS b
INNER JOIN events AS e ON e.id = b.event_id
WHERE b.user_id = 12
ORDER BY e.starts_at;