Stage 3 · Build

Relational Databases (PostgreSQL)

SQL Fundamentals

SELECT, INSERT, UPDATE, DELETE, JOINs, GROUP BY, HAVING, and CTEs.

8 min readDatabases for SREBuild

Query Anatomy

SQL queries follow a strict execution order that differs from the order you write them. The FROM clause executes first, then WHERE, GROUP BY, HAVING, SELECT, and finally ORDER BY. Understanding this order prevents logical errors.

Basic CRUD operations


-- Create
INSERT INTO users (email, name) VALUES ('carol@example.com', 'Carol Davis');

-- Read
SELECT id, email, name FROM users WHERE is_active = true;

-- Update
UPDATE users SET name = 'Carol M. Davis' WHERE id = 3;

-- Delete
DELETE FROM users WHERE id = 3;

Always use WHERE clauses with UPDATE and DELETE. Running these without WHERE affects every row in the table.

Never run UPDATE or DELETE without WHERE

Accidentally omitting a WHERE clause updates or deletes every row. In production, wrap destructive operations in transactions so you can rollback. Some ORMs add a safety WHERE 1=1 pattern.

Filtering and Sorting

WHERE clause patterns


-- Exact match
SELECT * FROM orders WHERE status = 'shipped';

-- Range queries (use indexes efficiently)
SELECT * FROM orders WHERE created_at >= '2024-01-01'
  AND created_at < '2024-02-01';

-- Pattern matching
SELECT * FROM users WHERE email LIKE '%@example.com';

-- IN for multiple values
SELECT * FROM products WHERE category_id IN (1, 3, 7);

-- NULL check
SELECT * FROM users WHERE deleted_at IS NULL;

-- Sorting
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20;

The range query on created_at is index-friendly. LIKE '%pattern' with a leading wildcard cannot use a B-tree index and will scan the entire table.

JOIN Types

JOINs combine rows from two or more tables based on a related column. The type of join determines which rows are included in the result.

Join Type	Behavior	Use Case
INNER JOIN	Only matching rows from both tables	Default — only want complete records
LEFT JOIN	All rows from left, matching from right	Users with optional orders
RIGHT JOIN	All rows from right, matching from left	Rare — rewrite as LEFT JOIN
FULL OUTER JOIN	All rows from both tables	Finding orphaned records

JOIN examples


-- Users with their orders (INNER JOIN)
SELECT u.name, o.id AS order_id, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id;

-- All users, even those without orders (LEFT JOIN)
SELECT u.name, COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

-- Find users who have never ordered
SELECT u.name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

The last query uses LEFT JOIN with IS NULL to find rows in one table that have no matching rows in another — a common pattern for finding orphaned or inactive records.

Aggregation with GROUP BY

Aggregate functions


-- Count orders per user
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id;

-- Average order value by status
SELECT status, AVG(total) AS avg_total, COUNT(*) AS count
FROM orders
GROUP BY status
HAVING COUNT(*) > 10;

HAVING filters after aggregation, while WHERE filters before. Use WHERE to reduce the dataset first, then HAVING to filter aggregated results.

WHERE vs HAVING

WHERE filters individual rows before grouping. HAVING filters groups after aggregation. A common mistake is using HAVING where WHERE would be more efficient — WHERE reduces the data before the expensive GROUP BY operation.

Subqueries and CTEs

Common Table Expressions (CTEs) make complex queries readable. They act as named temporary result sets that you can reference in subsequent queries.

CTE example


WITH monthly_revenue AS (
    SELECT
        date_trunc('month', created_at) AS month,
        SUM(total) AS revenue
    FROM orders
    WHERE status != 'cancelled'
    GROUP BY 1
),
revenue_growth AS (
    SELECT
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) AS prev_month
    FROM monthly_revenue
)
SELECT
    month,
    revenue,
    ROUND(((revenue - prev_month) / prev_month) * 100, 1) AS growth_pct
FROM revenue_growth
WHERE prev_month IS NOT NULL
ORDER BY month DESC;

This query chains CTEs to compute monthly revenue and then calculate month-over-month growth. The LAG window function accesses the previous row without a self-join.

Window Functions

Window functions perform calculations across a set of rows related to the current row, without collapsing them into a single output row like GROUP BY does.

Window function patterns


-- Rank users by total spending
SELECT
    u.name,
    SUM(o.total) AS total_spent,
    RANK() OVER (ORDER BY SUM(o.total) DESC) AS spend_rank
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

-- Running total of orders
SELECT
    id,
    total,
    created_at,
    SUM(total) OVER (ORDER BY created_at) AS running_total
FROM orders;

-- Percentile of response times
SELECT
    endpoint,
    response_ms,
    PERCENT_RANK() OVER (PARTITION BY endpoint ORDER BY response_ms) AS percentile
FROM api_logs;

PARTITION BY divides the window into groups (like GROUP BY but without collapsing). ORDER BY within the window determines the row ordering for calculations like RANK and running totals.

Use window functions instead of self-joins

Window functions like LAG, LEAD, ROW_NUMBER, and RANK replace many patterns that previously required self-joins or subqueries. They are typically more readable and performant.

Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.