SQL Query Examples: Master Architect Cheat Sheet

"Code is ephemeral; Data is eternal."

This is the definitive, categorized reference for every major pattern covered in the SQL Masterclass. Use this as your "Battle Map" when designing, debugging, or optimizing high-scale database systems.

1. Performance Tuning & Observability

The "Explain" Command (Module 20)

Use this to see how the engine plans to execute your query.

EXPLAIN (ANALYZE, BUFFERS, TIMING) 
SELECT * FROM orders WHERE user_id = 500;

• Note: Look for Seq Scan (Bad) vs Index Scan (Good). Check Shared Hit to see how much data came from RAM.

Index Bloat Detection (Module 28)

Find indexes that are taking up space without being used.

SELECT 
    schemaname, relname, 
    pg_size_pretty(pg_relation_size(relid)) AS size,
    idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(relid) DESC;

2. Modern Data: JSONB & Search

Deep JSONB Search (Module 21)

Retrieve values from nested JSON structures with index safety.

SELECT name FROM products 
WHERE metadata @> '{"material": "Organic Cotton"}';

• Architecture Note: Ensure you have a GIN Index on the metadata column for $O(1)$ search complexity.

Full-Text Vector Search (Module 26)

Building a "Google-like" search experience.

SELECT title, ts_rank(search_vector, query) as relevance
FROM articles, to_tsquery('english', 'Architecture & Performance') as query
WHERE search_vector @@ query
ORDER BY relevance DESC;

3. High-Scale Architecture Patterns

Recursive CTEs (Module 22)

Traversing org charts or social graphs.

WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id FROM employees WHERE id = 1
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

Window Functions for Analytics (Module 15)

Calculating running totals or ranking without collapsing rows.

SELECT 
    order_date, 
    amount,
    SUM(amount) OVER (ORDER BY order_date) as running_total
FROM sales;

4. Operational Engineering

Zero-Downtime Indexing (Module 27)

Build an index on a production table without locking writes.

CREATE INDEX CONCURRENTLY idx_user_email ON users (email);

Safety: The Lock Timeout (Module 27)

Stop a migration from bringing down your site if a table is busy.

SET lock_timeout = '2000ms';
ALTER TABLE transactions ADD COLUMN status_v2 text;

5. Security & Isolation

Row-Level Security (RLS) (Module 23)

Enforce data isolation at the engine level.

ALTER TABLE user_data ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON user_data
USING (tenant_id = current_setting('app.current_tenant')::int);

Atomic Financial Transfers (Module 29)

Ensuring the "Truth" of a ledger.

BEGIN;
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

6. The "Daily" Staples (Quick Reference)

Summary: The Architect's Code

1. Never Use SELECT *: It wastes network bandwidth and spills from the CPU cache.
2. Explicit Joins: Always use JOIN ... ON instead of the comma-style join.
3. Audit the Cost: If a query is run more than $1,000$ times a day, it deserves an index.
4. Transactionality: Surround every multi-step change with BEGIN and COMMIT.
5. Schema is Code: Version every change in a migration file.

You have now mastered the 32 components of the SQL Masterclass. You are ready to build the next generation of data-driven systems. Go build the future.

Frequently Asked Questions

Q: What is the most efficient way to check if any row exists matching a condition?

Use EXISTS: SELECT EXISTS (SELECT 1 FROM orders WHERE customer_id = 42 AND status = 'pending'). This returns a boolean and short-circuits as soon as the first matching row is found — it does not count all matches or fetch any data beyond confirming existence. Alternatives like SELECT COUNT(*) > 0 scan all matching rows unnecessarily. EXISTS is also correct when the subquery might return NULLs (unlike IN, which has NULL-related edge cases). For application code, EXISTS is the idiomatic SQL way to answer "does at least one row match this condition?"

Q: How do I update rows in one table based on values from another table?

Use UPDATE ... FROM in PostgreSQL: UPDATE orders SET status = 'shipped' FROM shipments WHERE orders.id = shipments.order_id AND shipments.shipped_at IS NOT NULL. In MySQL, use a multi-table UPDATE: UPDATE orders o JOIN shipments s ON o.id = s.order_id SET o.status = 'shipped' WHERE s.shipped_at IS NOT NULL. In standard SQL, use a correlated subquery in the SET clause or a WHERE EXISTS. The UPDATE ... FROM syntax is the most readable and performant for PostgreSQL.

Q: What is the difference between DELETE and TRUNCATE?

DELETE removes rows one at a time, fires triggers, respects foreign key checks, generates WAL records per row, and can be rolled back. TRUNCATE removes all rows by deallocating the table's data pages, is vastly faster for large tables, does not fire row-level triggers, resets sequences if RESTART IDENTITY is specified, and in PostgreSQL is also transactional (can be rolled back). Use DELETE when you need to remove specific rows, need triggers to fire, or might roll back. Use TRUNCATE when clearing an entire table completely and speed matters — it is orders of magnitude faster than DELETE with no WHERE clause on large tables.

Part of the SQL Mastery Course — engineering the cheat sheet.