2026-04-09

Help Me Understand SQL With Examples

If you can read and write a bit of English, you can read and write SQL. It is a small, declarative language for asking questions about tables of data. This guide walks through the parts you will use every day, with runnable-style examples.

A quick mental model

  • A database holds tables. A table is rows and columns.
  • A query answers: which rows do I want, which columns, and how should they be combined or summarized.
  • You describe the result you want; the database figures out how to get it.

Sample schema and data

You can run these in SQLite, PostgreSQL, or MySQL with minor tweaks.

-- Users, products, orders, and order items
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price_cents INTEGER NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  status TEXT NOT NULL DEFAULT 'pending',
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE order_items (
  order_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL,
  unit_price_cents INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

INSERT INTO users (id, name, email) VALUES
  (1, 'Ava', 'ava@example.com'),
  (2, 'Ben', 'ben@example.com'),
  (3, 'Cara', 'cara@example.com');

INSERT INTO products (id, name, price_cents) VALUES
  (10, 'Keyboard', 5000),
  (11, 'Mouse', 2500),
  (12, 'Monitor', 19999);

INSERT INTO orders (id, user_id, status) VALUES
  (100, 1, 'paid'),
  (101, 1, 'paid'),
  (102, 2, 'pending');

INSERT INTO order_items (order_id, product_id, quantity, unit_price_cents) VALUES
  (100, 10, 1, 5000),
  (100, 11, 2, 2500),
  (101, 12, 1, 19999),
  (102, 11, 1, 2500);

Reading data: SELECT, WHERE, ORDER BY, LIMIT

-- Pick columns
SELECT id, name FROM users;

-- Rename columns with AS for readability
SELECT id AS user_id, email AS contact FROM users;

-- Filter rows with WHERE (use AND, OR, IN, BETWEEN, LIKE)
SELECT * FROM products WHERE price_cents BETWEEN 2000 AND 20000;
SELECT * FROM users WHERE email LIKE '%@example.com';

-- Sort and limit
SELECT * FROM orders WHERE status = 'paid' ORDER BY created_at DESC LIMIT 5;

Tips:

  • Use single quotes for text literals.
  • Percent sign in LIKE is a wildcard. Case sensitivity depends on the database.

Aggregate and group: COUNT, SUM, AVG, GROUP BY, HAVING

Order totals come from order_items. Let us compute totals and summarize.

-- Total for each order
SELECT
  oi.order_id,
  SUM(oi.quantity * oi.unit_price_cents) AS order_total_cents
FROM order_items oi
GROUP BY oi.order_id;

-- How many orders per user
SELECT o.user_id, COUNT(*) AS order_count
FROM orders o
GROUP BY o.user_id;

-- Users with more than 1 paid order
SELECT o.user_id, COUNT(*) AS paid_orders
FROM orders o
WHERE o.status = 'paid'
GROUP BY o.user_id
HAVING COUNT(*) > 1;  -- HAVING filters after grouping

Remember: WHERE filters rows before grouping; HAVING filters groups after aggregation.

Combine tables: JOINs you will actually use

-- Which user placed each order (only matching rows)
SELECT o.id AS order_id, u.name
FROM orders o
JOIN users u ON u.id = o.user_id;

-- Include users with no orders (LEFT JOIN)
SELECT u.name, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
ORDER BY u.name;

-- Order line details across three tables
SELECT o.id AS order_id, u.name AS customer, p.name AS product,
       oi.quantity, oi.unit_price_cents,
       oi.quantity * oi.unit_price_cents AS line_total
FROM order_items oi
JOIN orders o   ON o.id = oi.order_id
JOIN users u    ON u.id = o.user_id
JOIN products p ON p.id = oi.product_id
ORDER BY o.id, p.name;

-- Anti-join: users who have never ordered
SELECT u.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

Rule of thumb: start from the table that represents the thing you ultimately want to list, then join to what you need to describe it.

Subqueries and CTEs (WITH)

Use a CTE to make complex steps readable.

-- Compute order totals, then pick top customers by lifetime spend
WITH order_totals AS (
  SELECT oi.order_id, SUM(oi.quantity * oi.unit_price_cents) AS total_cents
  FROM order_items oi
  GROUP BY oi.order_id
), customer_totals AS (
  SELECT o.user_id, SUM(ot.total_cents) AS lifetime_cents
  FROM orders o
  JOIN order_totals ot ON ot.order_id = o.id
  GROUP BY o.user_id
)
SELECT u.name, ct.lifetime_cents
FROM customer_totals ct
JOIN users u ON u.id = ct.user_id
ORDER BY ct.lifetime_cents DESC
LIMIT 5;

-- Correlated subquery: latest order per user
SELECT u.id, u.name,
       (
         SELECT MAX(o.created_at)
         FROM orders o
         WHERE o.user_id = u.id
       ) AS last_order_at
FROM users u;

CTEs are great for readability and reuse of intermediate results in one query.

Window functions: analytics without collapsing rows

Window functions compute over a partition of rows while keeping row detail.

-- Rank each user's orders by recency
SELECT o.id AS order_id, o.user_id,
       ROW_NUMBER() OVER (PARTITION BY o.user_id ORDER BY o.created_at DESC) AS rn
FROM orders o;

-- Running spend per user across time
WITH order_totals AS (
  SELECT o.id, o.user_id, o.created_at,
         SUM(oi.quantity * oi.unit_price_cents) AS total_cents
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY o.id, o.user_id, o.created_at
)
SELECT user_id, id AS order_id, created_at,
       total_cents,
       SUM(total_cents) OVER (
         PARTITION BY user_id ORDER BY created_at
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total_cents
FROM order_totals
ORDER BY user_id, created_at;

Modifying data: INSERT, UPDATE, DELETE

-- Insert a new order and its items (often done within a transaction)
INSERT INTO orders (id, user_id, status) VALUES (200, 3, 'paid');
INSERT INTO order_items (order_id, product_id, quantity, unit_price_cents)
VALUES (200, 10, 1, 5000), (200, 11, 1, 2500);

-- Update product price
UPDATE products SET price_cents = 2600 WHERE id = 11;

-- Increase all monitor prices by 5 percent
UPDATE products
SET price_cents = CAST(price_cents * 1.05 AS INTEGER)
WHERE name = 'Monitor';

-- Delete a pending order (and its items if you set up cascading or delete items first)
DELETE FROM order_items WHERE order_id = 102;
DELETE FROM orders WHERE id = 102 AND status = 'pending';

Transactions: make multiple changes safely

BEGIN;  -- or START TRANSACTION
  UPDATE products SET price_cents = price_cents - 500 WHERE id = 10;
  UPDATE products SET price_cents = price_cents + 500 WHERE id = 11;
COMMIT;  -- or ROLLBACK on error

Transactions ensure all steps succeed or none are applied.

Indexes: speed up common lookups

Use indexes on columns you filter or join on frequently.

-- Speed common queries by user and recent time
CREATE INDEX idx_orders_user_created_at ON orders (user_id, created_at);

-- Lookup products by name quickly
CREATE INDEX idx_products_name ON products (name);

Practical checks:

  • Create indexes after you know real query patterns.
  • Composite indexes are ordered; (user_id, created_at) helps filters on user_id and sorts by created_at.
  • Too many indexes slow down writes.

NULLs: three-valued logic

  • Use IS NULL and IS NOT NULL to test.
  • Comparisons with NULL yield unknown, not true or false.
  • COALESCE(a, b, c) returns the first non-null.
SELECT COALESCE(NULL, NULL, 'fallback') AS val;  -- returns fallback

-- Beware NOT IN with NULLs; prefer NOT EXISTS
SELECT u.*
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Query execution order (conceptual)

  • FROM and JOIN
  • WHERE
  • GROUP BY
  • HAVING
  • SELECT
  • ORDER BY
  • LIMIT and OFFSET

Write with this flow in mind. For example, aliases from SELECT are not available in WHERE but are available in ORDER BY in many systems.

Real world patterns

  • Pagination
SELECT *
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;  -- page 3 of 20 per page
  • Top N by group (latest order per user)
WITH ranked AS (
  SELECT o.*, ROW_NUMBER() OVER (
    PARTITION BY o.user_id ORDER BY o.created_at DESC
  ) AS rn
  FROM orders o
)
SELECT * FROM ranked WHERE rn = 1;
  • Upsert (insert or update if exists) is vendor specific. Learn your database syntax for conflict handling.

Practical debugging tips

  • Start with SELECT * and remove columns as you understand the shape.
  • Build from inside out: test subqueries and CTEs independently.
  • Add predicates one by one; check row counts as you go.
  • Explain plans: use EXPLAIN to see which indexes are used and join order.
  • Parameterize inputs to avoid SQL injection; never string-concatenate user input.

Exercises (try now)

  1. List users and their total paid spend, highest first.

  2. For each product, show total quantity sold and total revenue.

  3. Find orders that include more than one distinct product.

  4. Show users who placed an order in the last 7 days but had no orders in the 30 days before that.

  5. Compute the 7-day moving average of daily revenue.

A tiny cheat sheet

  • Pick: SELECT col1, col2 FROM table
  • Filter: WHERE condition
  • Sort and limit: ORDER BY col DESC LIMIT n
  • Aggregate: GROUP BY cols with COUNT, SUM, AVG, MIN, MAX
  • Post-aggregate filter: HAVING condition
  • Combine: JOIN ... ON ... (INNER, LEFT)
  • Subquery/CTE: WITH name AS ( ... ) SELECT ...
  • Window: func(...) OVER (PARTITION BY ... ORDER BY ...)
  • Change data: INSERT, UPDATE, DELETE
  • Safety: BEGIN; ... COMMIT; and CREATE INDEX for speed

Keep practicing with real questions from your own data. SQL rewards clarity: name things, use CTEs to break tasks into steps, and prefer simple, well-indexed predicates over cleverness.