SQL SELECT, WHERE, ORDER BY and LIMIT: Querying Your First Table

A park volunteer sends you one question: "Which easy trails can I take my dog on this weekend, longest first?" In a spreadsheet you would filter two columns and sort a third. SQL is the same instruction written as a sentence the database can execute, and once you can write that sentence you can answer most everyday data questions without exporting anything.

A table is a set of rows with the same shape

A relational table has named columns, each with a type, and any number of rows. There is no "first row" in the table itself; rows are a set, and the order you see on screen is whatever the database chose unless you ask for a specific one. A primary key is a column (here trail_id) whose value identifies exactly one row.

SQL is declarative: you describe the rows you want, and the database decides how to find them. Every example in this lesson ran against a real SQLite database, so the results shown are exact. The statements below create a small trails table; the schema syntax is standard enough that it works with only minor changes in PostgreSQL and MySQL.

CREATE TABLE trails (
  trail_id       INTEGER PRIMARY KEY,
  name           TEXT    NOT NULL,
  region         TEXT    NOT NULL,
  length_km      REAL    NOT NULL,
  climb_m        INTEGER NOT NULL,
  difficulty     TEXT    NOT NULL,
  dogs_ok        INTEGER NOT NULL,   -- 1 = dogs allowed, 0 = not allowed
  last_inspected TEXT                -- ISO date; NULL = never inspected
);

INSERT INTO trails VALUES
  (1,  'Heron Marsh Loop',  'Lowlands',   4.2,   30, 'easy',     1, '2025-05-02'),
  (2,  'Quarry Steps',      'Lowlands',   2.8,  140, 'moderate', 1, '2025-04-18'),
  (3,  'Kestrel Ridge',     'Highlands', 11.5,  820, 'hard',     0, '2025-03-30'),
  (4,  'Millrace Path',     'Lowlands',   6.1,   55, 'easy',     1, NULL),
  (5,  'Cairn Traverse',    'Highlands', 14.0, 1130, 'hard',     0, '2025-06-11'),
  (6,  'Birchwood Circuit', 'Midlands',   7.4,  210, 'moderate', 1, '2025-05-20'),
  (7,  'Silent Falls Walk', 'Midlands',   3.3,   95, 'easy',     0, '2025-02-09'),
  (8,  'Tarn Edge',         'Highlands',  8.9,  460, 'moderate', 1, NULL),
  (9,  'Old Canal Towpath', 'Lowlands',   9.6,   20, 'easy',     1, '2025-06-01'),
  (10, 'Gorse Hill',        'Midlands',   5.0,  305, 'moderate', 0, '2025-05-27');

Run SELECT * to see everything the table holds. The star means "all columns"; it is handy for exploring and a habit worth dropping in application code, for reasons covered under common mistakes.

SELECT * FROM trails;

trail_id | name              | region    | length_km | climb_m | difficulty | dogs_ok | last_inspected
---------+-------------------+-----------+-----------+---------+------------+---------+---------------
       1 | Heron Marsh Loop  | Lowlands  |       4.2 |      30 | easy       |       1 | 2025-05-02
       2 | Quarry Steps      | Lowlands  |       2.8 |     140 | moderate   |       1 | 2025-04-18
       3 | Kestrel Ridge     | Highlands |      11.5 |     820 | hard       |       0 | 2025-03-30
       4 | Millrace Path     | Lowlands  |       6.1 |      55 | easy       |       1 | NULL
       5 | Cairn Traverse    | Highlands |      14.0 |    1130 | hard       |       0 | 2025-06-11
       6 | Birchwood Circuit | Midlands  |       7.4 |     210 | moderate   |       1 | 2025-05-20
       7 | Silent Falls Walk | Midlands  |       3.3 |      95 | easy       |       0 | 2025-02-09
       8 | Tarn Edge         | Highlands |       8.9 |     460 | moderate   |       1 | NULL
       9 | Old Canal Towpath | Lowlands  |       9.6 |      20 | easy       |       1 | 2025-06-01
      10 | Gorse Hill        | Midlands  |       5.0 |     305 | moderate   |       0 | 2025-05-27

Notice the two NULL entries in last_inspected. A NULL is not an empty string or a zero. It marks a value that is missing or unknown, and it behaves differently from every ordinary value, as a later section shows.

SELECT picks columns, WHERE picks rows, ORDER BY arranges them

Here is the volunteer's question as a query. SELECT lists the columns to return, FROM names the table, WHERE keeps only the rows for which the condition is true, and ORDER BY ... DESC sorts the survivors from largest to smallest.

SELECT name, length_km
FROM trails
WHERE difficulty = 'easy' AND dogs_ok = 1
ORDER BY length_km DESC;

name              | length_km
------------------+----------
Old Canal Towpath |       9.6
Millrace Path     |       6.1
Heron Marsh Loop  |       4.2

Only three rows qualify. It helps to know the order in which the database logically processes the clauses, because it explains several error messages you will meet.

Combining conditions without surprises

AND binds tighter than OR, exactly like multiplication over addition. A condition written without parentheses may therefore mean something different from what you read into it. Compare the two queries below, which differ only in two parentheses.

-- What was probably meant: easy trails in either region
SELECT name, region, difficulty
FROM trails
WHERE region = 'Highlands' OR region = 'Lowlands' AND difficulty = 'easy';

-- With the parentheses the author meant
SELECT name, region, difficulty
FROM trails
WHERE (region = 'Highlands' OR region = 'Lowlands') AND difficulty = 'easy';

name              | region    | difficulty
------------------+-----------+-----------
Heron Marsh Loop  | Lowlands  | easy
Kestrel Ridge     | Highlands | hard
Millrace Path     | Lowlands  | easy
Cairn Traverse    | Highlands | hard
Tarn Edge         | Highlands | moderate
Old Canal Towpath | Lowlands  | easy

name              | region   | difficulty
------------------+----------+-----------
Heron Marsh Loop  | Lowlands | easy
Millrace Path     | Lowlands | easy
Old Canal Towpath | Lowlands | easy

The first query returned all three Highlands trails, including two hard ones, because region = 'Highlands' stands alone on the left of the OR. Only the Lowlands branch was also required to be easy. The second query returns exactly the three easy Lowlands trails, since no easy trail exists in the Highlands. Whenever a WHERE clause mixes AND with OR, add parentheses even if they are redundant; the next reader will thank you.

The same query is shorter with IN for a list of values and BETWEEN for an inclusive range. LIKE matches patterns, where % stands for any run of characters. Be aware that SQLite's LIKE ignores case for ASCII letters, while PostgreSQL's does not (it offers ILIKE for that), so pattern queries can behave differently between databases.

SELECT name, length_km
FROM trails
WHERE length_km BETWEEN 5 AND 9
  AND region IN ('Midlands', 'Highlands')
ORDER BY region, length_km;

name              | length_km
------------------+----------
Tarn Edge         |       8.9
Gorse Hill        |       5.0
Birchwood Circuit |       7.4

NULL is not equal to anything, not even NULL

Asking whether an unknown value equals something yields "unknown", and WHERE keeps only rows where the condition is true. So a comparison with NULL never succeeds, and the query returns nothing without any error. You must use IS NULL or IS NOT NULL.

SELECT name FROM trails WHERE last_inspected = NULL;

SELECT name FROM trails WHERE last_inspected IS NULL;

name
----

name
-------------
Millrace Path
Tarn Edge

The first query printed only a header, an empty result, even though two trails were never inspected. The second finds them. This is one of the most common beginner bugs because nothing warns you.

Sorting, limiting and paging

LIMIT n returns at most n rows, and OFFSET m skips the first m. Together they produce pages. The sort decides which rows count as first, so always pair them with an ORDER BY, and add a tiebreaker column (here name) so equal values come back in a stable order. The next block also computes a derived column and shows a trap with integer division.

SELECT name, climb_m, length_km,
       ROUND(climb_m / length_km, 1) AS m_per_km
FROM trails
ORDER BY m_per_km DESC
LIMIT 3;

SELECT name, length_km
FROM trails
ORDER BY length_km DESC, name
LIMIT 3 OFFSET 3;

SELECT 7 / 2 AS int_div, 7 / 2.0 AS real_div;

name           | climb_m | length_km | m_per_km
---------------+---------+-----------+---------
Cairn Traverse |    1130 |      14.0 |     80.7
Kestrel Ridge  |     820 |      11.5 |     71.3
Gorse Hill     |     305 |       5.0 |     61.0

name              | length_km
------------------+----------
Tarn Edge         |       8.9
Birchwood Circuit |       7.4
Millrace Path     |       6.1

int_div | real_div
--------+---------
      3 |      3.5

The first result lists the three steepest trails by metres of climb per kilometre. The second shows page two of the longest trails, three per page. The third line is the trap: when both operands are integers, SQLite (like PostgreSQL) divides as integers and discards the fraction, so 7 / 2 is 3. Make either side a decimal when you want a fractional result. Deep OFFSET values also get slow on large tables, because the database still has to walk past the skipped rows, a topic picked up in Database Indexing.

Common mistakes

• `WHERE col = NULL`. Symptom: an empty result when you know matching rows exist. Use IS NULL.
• Mixing AND with OR without parentheses. Symptom: extra rows that clearly break your intent, like the hard Highlands trails above.
• `LIMIT` without `ORDER BY`. Symptom: the "top 3" changes between runs or after an unrelated insert, and pages overlap. Always sort first.
• Double quotes around text values. Standard SQL uses single quotes for strings and double quotes for identifiers. SQLite will often quietly treat a double-quoted word as a string when no such column exists, but PostgreSQL fails with column "easy" does not exist.
• **SELECT * in application code.** When someone adds a column, your code receives more data than it expected, and queries move more bytes than they need. Name the columns you use.
• Integer division. Symptom: percentages that are all 0 or whole numbers. Multiply by 1.0 or write 100.0 as one operand.

How this shows up in real projects and interviews

Most of day-to-day SQL is exactly this: a filtered, sorted, limited read, usually behind a search page or an admin screen. Interviewers use these clauses to check habits rather than trivia: do you handle NULL, do you sort before limiting, can you explain why a query returned too many rows. For the next step, learn to summarise many rows into a few in SQL GROUP BY, HAVING and aggregates.