SQL for Operators

Real-world analogy

What is it?

Operator-grade SQL is enough to support applications, pull reports, and investigate incidents — without becoming a DBA. Any DB-backed app you support will benefit from your ability to read its state.

Real-world relevance

App team says ‘users see stale data.’ You run a SELECT with timestamps on the main table, discover a batch job hasn’t committed since 2 AM. You check the job, find the dependency was missing, escalate with evidence. App team has what they need in 15 minutes.

Key points

• Relational basics — A database has tables (rows/columns). Tables relate via keys (primary key, foreign key). Queries retrieve, filter, aggregate. You don’t need math — just clear logic.
• SELECT, WHERE, ORDER, LIMIT — SELECT columns FROM table WHERE condition ORDER BY col LIMIT n. Use LIMIT/TOP to avoid pulling millions of rows. Always add WHERE before you think about ORDER.
• JOINs — the bread and butter — INNER JOIN: rows matching both tables. LEFT JOIN: all rows from the left, matching from the right (NULLs for unmatched). Practice INNER + LEFT; those cover most reporting needs.
• Aggregation: COUNT, SUM, AVG, GROUP BY — Great for quick ops reports: rows per status, total per day, average per user. GROUP BY columns that are not aggregated; use HAVING to filter after aggregation.
• Read-only mindset — Juniors should default to read-only. DELETE, UPDATE, ALTER, DROP are dangerous. Run them only with explicit approval, in a transaction, with a WHERE clause, ideally in a dev/test first.
• Indexes and why queries are slow — Indexes speed up reads but cost on writes. ‘Full table scan’ on a large table = slow. Suspect missing indexes when the same query is slow in prod but fast in dev.
• Explain plans (awareness) — EXPLAIN tells you how the database plans to execute your query. Juniors should recognize ‘Seq Scan’ / ‘Index Scan’ in output; seniors tune.
• Common operator queries — Find a user by email, count active sessions, list failed jobs last 24h, find orphaned rows, check table sizes, top CPU queries, long-running sessions.

Code example

-- everyday operator queries

-- user lookup
SELECT id, email, status, last_login
FROM users
WHERE email = 'alice@contoso.com';

-- active sessions today
SELECT COUNT(*) AS active_today
FROM sessions
WHERE ended_at IS NULL
  AND started_at >= CURRENT_DATE;

-- failed jobs last 24h
SELECT job_name, status, started_at, error
FROM job_runs
WHERE status = 'FAILED'
  AND started_at >= NOW() - INTERVAL '24 hours'
ORDER BY started_at DESC;

-- orphaned rows
SELECT o.id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

-- long-running sessions (PostgreSQL example)
SELECT pid, usename, state, query, now() - xact_start AS duration
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY duration DESC
LIMIT 20;

Line-by-line walkthrough

1. 1. Everyday operator queries
2. 2. User lookup query
3. 3. Active sessions today
4. 4. Failed jobs 24h
5. 5. Orphaned rows using LEFT JOIN
6. 6. Long-running sessions example
7. 7. Ordering and limiting results

Spot the bug

Junior runs in production:
  UPDATE users SET status = 'disabled';

Explain like I'm 5

Fun fact

Hands-on challenge

More resources

• PostgreSQL tutorial (PostgreSQL)
• SQL Server documentation (Microsoft Learn)
• SQL for Data Analysis (free) (freeCodeCamp)