Querying with SQL: SELECT, WHERE and ORDER BY

Why this matters

Acquiring data usually means writing a query, and most queries are built from three clauses: SELECT chooses the columns, WHERE chooses the rows, and ORDER BY arranges the result. They look simple, and most errors in real queries come from them anyway: a condition that quietly excludes missing values, a result assumed to be in order when it is not, or a DISTINCT that hides a duplicate problem instead of fixing it.

The exam presents queries and asks what they return, or asks which query meets a requirement. This lesson covers the core of the SELECT statement using the course's practice database, shop.db, and ends with a habit that catches most mistakes before a query runs.

The lesson

SELECT, choosing columns and aliasing them

A query starts with SELECT, followed by the columns you want, then FROM and the table they come from:

SELECT customer_id, first_name, last_name, region
FROM customers;

SELECT * returns every column. It is convenient for a first look at a table, and a poor choice in anything that will be reused: it returns more data than needed, and if the table later gains or reorders columns, whatever relies on the result can break or silently change.

Columns can be calculated as well as selected, and given a new name with an alias using AS:

SELECT order_id,
       quantity,
       discount_pct,
       quantity * (100 - discount_pct) / 100.0 AS discounted_units
FROM orders;

An alias makes a result readable and gives a calculated column a name that later steps can refer to. Tables can be aliased too, which becomes essential when a query joins several of them.

WHERE: comparison, ranges, lists, patterns and NULL

WHERE keeps only the rows for which a condition is true.

• Comparison: =, <> (or !=), <, >, <= and >=, such as WHERE region = 'North'. In the practice database that returns 77 customers.
• Combining: AND requires both conditions, OR either, and NOT reverses one. AND is evaluated before OR, so mixed conditions need parentheses: WHERE discount_pct > 0 AND (quantity > 3 OR product_id = 'P001') means something different without them.
• Ranges: BETWEEN is inclusive of both ends. WHERE order_date BETWEEN '2025-03-01' AND '2025-03-31' returns March's 176 orders, and it works on text dates only because they are stored in ISO format, year first, which sorts in date order.
• Lists: IN matches any of several values, as in WHERE region IN ('North', 'East').
• Patterns: LIKE matches text with wildcards: % for any run of characters and _ for exactly one. WHERE email LIKE '%@example.com' finds every address at that domain.

NULL needs separate care. NULL means "unknown" or "missing", and any comparison with it is neither true nor false but unknown, so the row is dropped. WHERE email = NULL returns nothing, even when emails are missing; the correct test is WHERE email IS NULL, or IS NOT NULL for the opposite. The same logic means WHERE region <> 'North' silently excludes rows where region is NULL, which is a common source of totals that do not add up.

ORDER BY and LIMIT, and what an unsorted result promises

ORDER BY sorts the result by one or more columns, ascending by default or descending with DESC:

SELECT product_id, name, unit_price
FROM products
ORDER BY unit_price DESC, name;

The second column breaks ties in the first. Where NULLs appear in the sorted order differs between database systems, so do not rely on it.

Without ORDER BY, a result has no guaranteed order. It often comes back in the order rows were inserted, and that is exactly why the assumption is dangerous: it holds until the table is reorganised, an index changes, or the query runs on a different system, and then "the first row" is a different row. Any query whose meaning depends on order must say so.

LIMIT restricts the number of rows returned. Combined with ORDER BY, it answers "top N" questions: the three most expensive products are the first three rows of the query above. LIMIT without ORDER BY returns an arbitrary set of rows, which is fine for a quick look at data and meaningless as an answer. The syntax varies by system: LIMIT in SQLite, PostgreSQL and MySQL, TOP in SQL Server, and FETCH FIRST in standard SQL and Oracle.

DISTINCT, and when it hides a problem instead of solving one

SELECT DISTINCT removes duplicate rows from a result. It is the right tool when you genuinely want the set of distinct values:

SELECT DISTINCT region FROM customers;

returns the four regions, once each.

It becomes a problem when it is used to make an unexpected duplicate go away. If a query returns each customer twice, the cause is usually upstream: a join that matched more rows than expected, or genuinely duplicated source data. Adding DISTINCT hides the symptom and leaves the cause, and any totals computed from the same logic will still be wrong. Worse, DISTINCT compares whole rows, so two rows that differ in one column, such as two slightly different spellings of the same customer's name, both survive.

The rule is to use DISTINCT when the question is about distinct values, and to investigate, not suppress, duplicates you did not expect.

Reading a query back as a sentence before running it

Most query mistakes are visible before the query runs, if it is read in the order the database evaluates it, which is not the order it is written. The logical order is: FROM (which table), WHERE (which rows), SELECT (which columns), ORDER BY (in what order), then LIMIT (how many).

Reading a query as a sentence in that order exposes errors:

"From orders, keep rows where the date is in March and the discount is over zero or the quantity is over three, show the order id and quantity, sorted by quantity descending, first five."

Said aloud, the ambiguity in "discount or quantity" becomes obvious, and the parentheses can be fixed before a wrong result is produced. Two further checks complete the habit: predict roughly how many rows the query should return before running it, and compare. A query expected to return a few hundred rows that returns zero or two million has told you something is wrong.