Week 2: SQL Fundamentals — Querying Relational Data

1. What a Relational Database Actually Is

A relational database organizes data into tables — think of each one as a strict, always-consistent version of an Excel sheet. Each row is one record, each column holds one attribute, and every table has a primary key: a column (or combination of columns) that uniquely identifies each row. A customer_id or order_id is a typical primary key.

For this week, all examples query a small orders table:

orders (table)

order_id | customer_name | region | amount | order_date
---------+---------------+--------+--------+------------
1        | Asha Rao      | North  | 1200   | 2026-01-04
2        | Ben Diaz      | South  | 450    | 2026-01-05
3        | Chen Wei      | North  | 890    | 2026-01-06
4        | Asha Rao      | North  | 300    | 2026-01-09
5        | Dev Patel     | East   | NULL   | 2026-01-10

Every query below runs against this table. Type the CREATE TABLE and INSERT statements into any SQL environment — MySQL Workbench, pgAdmin, DB Fiddle, or even SQLite — to follow along and run every query yourself.

2. SELECT & WHERE: Reading and Filtering Rows

SELECT is how you read data — it never modifies anything. Name the columns you want, the table to read from, and optionally a WHERE clause to filter which rows come back.

sql

-- Every column, every row
SELECT * FROM orders;

-- Just the columns you need
SELECT customer_name, amount FROM orders;

-- Filter rows with WHERE
SELECT * FROM orders
WHERE region = 'North';

-- Combine conditions with AND / OR
SELECT * FROM orders
WHERE region = 'North' AND amount > 500;

-- Match a set of values without repeating OR
SELECT * FROM orders
WHERE region IN ('North', 'East');

-- Range check
SELECT * FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-07';

Never use SELECT * in a query you're actually going to run in production

It's fine for exploring a small table, but it pulls every column whether you need it or not, breaks silently if the table's structure changes later, and makes a query harder for anyone else to read. Name the columns you need.

3. Sorting, Limiting & Removing Duplicates

A plain SELECT returns rows in no guaranteed order. Control that explicitly with ORDER BY, cap how many rows come back with LIMIT, and drop duplicate values with DISTINCT.

sql

-- Highest amount first
SELECT customer_name, amount FROM orders
ORDER BY amount DESC;

-- Sort by region, then amount within each region
SELECT * FROM orders
ORDER BY region ASC, amount DESC;

-- Just the top 2 orders by amount
SELECT * FROM orders
ORDER BY amount DESC
LIMIT 2;

-- The distinct list of regions that appear at all
SELECT DISTINCT region FROM orders;

ORDER BY ... LIMIT together is the standard pattern for "top N" and "bottom N" queries — sort by the metric that matters, then cap the result — and it's dramatically faster than pulling every row and sorting them yourself afterward.

4. Aggregate Functions & a First GROUP BY

Aggregate functions collapse many rows into one number: COUNT, SUM, AVG, MIN and MAX. Used alone, they summarize the whole table. Paired with GROUP BY, they summarize per group — the SQL equivalent of an Excel PivotTable.

sql

-- One number for the whole table
SELECT COUNT(*) AS total_orders, SUM(amount) AS total_revenue
FROM orders;

-- One row PER region -- this is the PivotTable move
SELECT region, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM orders
GROUP BY region;

-- Filter which GROUPS show up (not which rows) with HAVING
SELECT region, SUM(amount) AS total_amount
FROM orders
GROUP BY region
HAVING SUM(amount) > 1000;

WHERE filters rows before grouping; HAVING filters groups after

This is the single most common early SQL mistake: writing WHERE SUM(amount) > 1000 fails because WHERE runs before aggregation exists. Any condition on an aggregate result belongs in HAVING, not WHERE.

5. NULL & Common Pitfalls

NULL means "no value recorded" — it is not zero, not an empty string, and not equal to itself in the way you'd expect. This trips up almost everyone the first time.

sql

-- WRONG -- this never matches, even on genuinely NULL rows
SELECT * FROM orders WHERE amount = NULL;

-- RIGHT -- use IS NULL / IS NOT NULL
SELECT * FROM orders WHERE amount IS NULL;
SELECT * FROM orders WHERE amount IS NOT NULL;

-- Aggregate functions silently skip NULLs
SELECT AVG(amount) FROM orders;
-- Averages only the 4 non-NULL rows, not all 5 -- know this before you trust the number

-- Replace a NULL with a fallback value for display or math
SELECT customer_name, COALESCE(amount, 0) AS amount
FROM orders;

That AVG behavior is worth internalizing: an aggregate over a column with missing data is quietly computed over fewer rows than the table actually has. Always ask what a NULL means in a given column before trusting an average or a percentage built on top of it.

6. Hands-on Exercise

Hands-on

Answer five real questions with five real queries

Set up the orders table from this lesson in any SQL environment (DB Fiddle is the fastest way to start with zero setup), then write a query for each requirement.

Requirements:

CREATE TABLE orders and INSERT the five sample rows shown above (including the NULL amount).
Write a query returning every order from the "North" region, sorted by amount descending.
Write a query returning the total and average order amount per region, using GROUP BY.
Extend that query with HAVING to show only regions whose total exceeds 500.
Write a query that lists every order where amount is NULL, then a second query using COALESCE to show those rows with amount displayed as 0 instead.
Write a query returning the single highest-value order, using ORDER BY and LIMIT.

Hint

If AVG(amount) for a region looks higher than you'd expect, check whether that region has a NULL row — AVG divides by the count of non-NULL rows only, so a missing value quietly shrinks the denominator instead of pulling the average down.

7. Knowledge Check

Four quick questions. Expand each to check your answer.

What makes a column a good primary key for a table?

It must uniquely identify every row in the table, and it should never change once set. A row's order_id is a good primary key; customer_name is not, since two different customers could share a name and one customer's name could still change.

Why does `WHERE amount = NULL` never return any rows, even when some rows genuinely have a NULL amount?

NULL represents an unknown value, so it is never considered equal (or unequal) to anything using = — not even to another NULL. SQL requires the dedicated IS NULL / IS NOT NULL operators to test for it correctly.

Why does `WHERE SUM(amount) > 1000` fail, and what should you use instead?

WHERE filters individual rows before any grouping or aggregation happens, so an aggregate like SUM() doesn't exist yet at that stage of the query. Filtering on an aggregated value requires HAVING, which runs after GROUP BY has produced its summarized rows.

A table has 5 rows but one has a NULL in the amount column. What does `AVG(amount)` actually divide by?

4, not 5. Aggregate functions like AVG, SUM, MIN and MAX silently ignore NULL values, so the average is computed only over the rows that actually have a value — which can make an average look higher or lower than expected if you assumed it covered every row.