🗄 SQL & Databases · Chapter 1

SELECT, WHERE, ORDER BY — your first question

SQL is not a programming language. You describe the answer you want and the database works out how to get it.

Study this lesson free in the app →

The basics

SQL is the language for asking a database questions. A SELECT query describes the rows and columns you want, and the database decides how to fetch them.

The problem it solves: your ride data lives in tables with millions or billions of rows. You need answers like "the ten biggest Shillong fares this month" without writing loops, reading files or choosing indexes yourself. You describe the answer; the database's optimiser picks the route.

The mental model: ordering at a restaurant. You say "paneer butter masala, no chilli" — not which shelf the paneer is on. The second idea that unlocks SQL: you write clauses in one order but the database runs them in another — FROM → WHERE → SELECT → ORDER BY → LIMIT. SELECT runs near the end, which explains most beginner errors.

Name your columns, filter the date, and LIMIT while you explore.

Explained

SELECT ride_id, fare
FROM rides
WHERE city = 'Shillong' AND fare > 100
ORDER BY fare DESC
LIMIT 10;

Read it aloud: give me the ride id and fare, from the rides table, where the city is Shillong and the fare is over 100, biggest first, top ten.

You never said how to find them. No loop, no index lookup, no file reading. You described the result and the database chose the strategy — and that's the whole idea.

Ordering at a restaurant

You don't walk into a kitchen and say "go to the fridge, take the second shelf, find the paneer…". You say "paneer butter masala, no chilli."

You described what you want. The kitchen decided the route.

That's declarative. Python is a recipe — do this, then this. SQL is an order — here's the dish, you figure it out.

The consequence matters: two people can write very different-looking SQL for the same answer, and the database may execute both identically. It's not following your instructions; it's reading your description and making its own plan.

The six clauses, and the order they run in

You write them in this order:

SELECT   columns you want
FROM     which table
WHERE    which rows to keep
GROUP BY how to bucket them        (next lesson)
HAVING   which buckets to keep     (next lesson)
ORDER BY how to sort the result
LIMIT    how many rows to return

But the database runs them in a different order:

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

That one difference explains most beginner errors. SELECT happens near the end — which is why:

• you can't use a column alias you defined in SELECT inside WHERE (it doesn't exist yet)
• but you can use it in ORDER BY (which runs after)

SELECT fare * 1.05 AS fare_with_tax
FROM rides
WHERE fare_with_tax > 100      -- ✗ error: no such column
ORDER BY fare_with_tax;        -- ✓ fine

Learn the execution order now and you will pre-empt a dozen confusing errors later.

Watch the query execute

FROM → WHERE → SELECT → ORDER BY → LIMIT. Every confusing SQL error becomes obvious once you know which step you're in.

WHERE: the operators you need

-- comparison
WHERE fare > 100
WHERE fare >= 100 AND fare <= 500
WHERE fare BETWEEN 100 AND 500     -- same thing, inclusive both ends
WHERE status != 'cancelled'        -- or <> in strict standard SQL

-- sets
WHERE city IN ('Shillong', 'Guwahati')
WHERE city NOT IN ('Delhi')

-- text matching
WHERE ride_id LIKE 'ride_9%'       -- % = any number of characters
WHERE ride_id LIKE 'ride_90_1'     -- _ = exactly one character
WHERE lower(city) = 'shillong'     -- case-insensitive comparison

-- missing values  (never use = for this)
WHERE fare IS NULL
WHERE fare IS NOT NULL

-- dates
WHERE event_time >= '2026-07-01'
  AND event_time <  '2026-08-01'   -- half-open: safer than BETWEEN

-- combining
WHERE city = 'Shillong'
  AND (status = 'completed' OR fare > 0)   -- brackets matter with OR

Two habits worth forming immediately: IS NULL, never = NULL, and half-open date ranges (>= start AND < end) so a timestamp at 23:59:59 on the last day isn't silently dropped.

Why does this return nothing?

The rides table has 40,000 rows. About 300 have no fare recorded.

SELECT count(*) FROM rides WHERE fare = NULL;

It returns 0. No error, no warning.

The query that ran for six hours

A new analyst runs this against a 400-million-row table:

SELECT * FROM ride_events;

The client hangs. They add LIMIT 100 and it returns instantly.

Two things happened, both worth internalising on day one.

SELECT * reads every column. In a warehouse with columnar storage, asking for 3 of 40 columns can be ten times cheaper than asking for all of them. SELECT * opts out of that saving entirely.

No WHERE means no pruning. If the table is partitioned by date, a query with a date filter reads one day; without one it reads every day ever stored.

The habits that follow:

• Name your columns. SELECT * is for exploring, never for anything saved or scheduled
• Always filter the partition column — usually a date — even when exploring
• LIMIT while exploring, always. It costs nothing and prevents this

On a laptop database none of this matters. On a cloud warehouse, SELECT * without a filter is a query that costs real money and appears on someone's bill.

Written order ≠ execution order. FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

IS NULL, never = NULL.

Name your columns. LIMIT while exploring. Filter the date.

Words to know

In two minutes

• SQL is declarative: you describe the result, the database chooses the plan.
• Written order is SELECT…FROM…WHERE…ORDER BY…LIMIT; execution order is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.
• Because SELECT runs late, its aliases cannot be used in WHERE but can be used in ORDER BY.
• WHERE keeps only rows where the condition is true; comparisons with NULL are unknown, so = NULL returns nothing — use IS NULL.
• Filter dates with half-open ranges (>= start AND < next start); BETWEEN is inclusive and drops most of the last day on timestamps.
• LIMIT without ORDER BY gives arbitrary rows; AND binds tighter than OR, so bracket your ORs.
• On a warehouse, name your columns and filter the partition column — SELECT * with no WHERE scans and bills for everything.

Test yourself

There are 9 questions, 3 puzzles and 7 recall cards for this lesson in the app — the recall cards come back on later days, just before you would forget.