SQL Practical Guide: Query, Model, and Protect Your Data

What SQL is for

SQL is the language for asking precise questions of relational data. Use it to answer: Which transactions are uncategorised? What automations failed this month? Which device has changed state most often?

SQL is declarative: you describe the result you want; the database works out an efficient way to retrieve it.

The four operations: CRUD

Start every unfamiliar query with SELECT. Mutating data deserves a transaction and a backup mindset.

Read queries: the everyday toolkit

SELECT id, name, state
FROM devices
WHERE enabled = true
ORDER BY name;

SELECT *
FROM transactions
WHERE occurred_at >= DATE '2026-07-01'
  AND category = 'Groceries'
ORDER BY occurred_at DESC
LIMIT 50;

Prefer explicit columns over SELECT * in reusable queries. It makes the result contract obvious and prevents surprises when schemas change.

Filter, group, aggregate

SELECT
  category,
  COUNT(*) AS transactions,
  ROUND(SUM(amount), 2) AS total_spent
FROM transactions
WHERE occurred_at >= DATE '2026-07-01'
  AND direction = 'expense'
GROUP BY category
HAVING SUM(amount) > 100
ORDER BY total_spent DESC;

Order of thought: FROM rows → WHERE filters rows → GROUP BY groups them → aggregates calculate → HAVING filters groups → ORDER BY sorts final results.

Joins: connect related tables

A primary key uniquely identifies a row; a foreign key references a row in another table.

SELECT
  t.occurred_at,
  t.amount,
  c.name AS category_name
FROM transactions AS t
JOIN categories AS c ON c.id = t.category_id
WHERE t.occurred_at >= CURRENT_DATE - INTERVAL '30 days';

SELECT t.id, t.description
FROM transactions t
LEFT JOIN categories c ON c.id = t.category_id
WHERE c.id IS NULL;

Insert, update, delete safely

INSERT INTO categories (name, kind)
VALUES ('Hardware', 'expense');

UPDATE devices
SET enabled = false
WHERE id = 42;

DELETE FROM import_rows
WHERE imported_at < CURRENT_DATE - INTERVAL '90 days';

Before UPDATE or DELETE, run the same WHERE with SELECT first. Never omit WHERE unless you truly mean every row.

Transactions: your undo boundary

BEGIN;
UPDATE budgets SET limit_amount = 550 WHERE name = 'Groceries';
SELECT name, limit_amount FROM budgets WHERE name = 'Groceries';
ROLLBACK; -- use COMMIT only after review

Use BEGIN / COMMIT for multi-step changes. If anything fails, ROLLBACK preserves consistency.

Model data well

CREATE TABLE devices (
  id INTEGER PRIMARY KEY,
  entity_id TEXT NOT NULL UNIQUE,
  room TEXT NOT NULL,
  enabled BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Constraints are correctness tools

CREATE TABLE readings (
  id INTEGER PRIMARY KEY,
  device_id INTEGER NOT NULL REFERENCES devices(id),
  recorded_at TIMESTAMP NOT NULL,
  temperature_c NUMERIC(4,1) NOT NULL CHECK (temperature_c > -60 AND temperature_c < 100)
);

Put rules that must always be true in the database too—not only in application code.

Parameterise every external value

Never build SQL by concatenating user input:

# Good: driver sends values separately from SQL syntax
cursor.execute(
    "SELECT * FROM devices WHERE entity_id = %s",
    (entity_id,),
)

Parameterisation protects against SQL injection and quoting bugs. It is non-negotiable for any value not hard-coded by you.

SQLite, MariaDB, and PostgreSQL

The core SQL here transfers between all three. Differences appear in JSON functions, date syntax, auto-increment IDs, and administration commands—read the target database documentation before relying on dialect-specific features.

Query performance basics

1. Use EXPLAIN before guessing why a query is slow.
2. Index columns used often in WHERE, JOIN, and ordered ranges.
3. Avoid returning thousands of unused rows; paginate with LIMIT.
4. Avoid N+1 queries: fetch related data with a join rather than one query per row.
5. Measure on realistic data, not an empty development database.

Useful commands

# SQLite
sqlite3 app.db
.tables
.schema devices
.mode column
.headers on

# MariaDB container, interactive client
docker exec -it firefly-db mariadb -u firefly -p firefly_iii

# Make a compressed MariaDB backup (credentials supplied securely)
docker exec firefly-db mariadb-dump --single-transaction database_name | gzip > backup.sql.gz

Common mistakes

Practice exercises

1. Make a SQLite database of household devices and query rooms with offline devices.
2. Import a sample CSV into a staging table, then validate before moving it to a final table.
3. Write a monthly spending summary grouped by category.
4. Use a LEFT JOIN to list entries missing an expected relationship.

Reference links

• PostgreSQL tutorial
• SQLite documentation
• MariaDB documentation