Session 2 — SELECT basics: columns, WHERE, ORDER BY, LIMIT

You will write your first real queries. The SELECT statement has a fixed clause order — SELECT columns, FROM table, WHERE condition, ORDER BY sort, LIMIT cut — and every later feature plugs into that skeleton. You will filter rows with comparison and logical operators, sort with ASC/DESC and multi-column ORDER BY, truncate results with LIMIT, and remove duplicates with DISTINCT, tracing every result set row by row.

Plan: Core ideas → worked queries → Quiz.

Time: about 60–70 minutes if you trace every filter by hand before checking the quiz.

Session 2 — SELECT basics: columns, WHERE, ORDER BY, LIMIT

The one-paragraph story

SELECT is the heart of SQL: it turns a table into the result set you care about by choosing columns, filtering rows, and ordering what remains. The clause order matters — SELECT which columns, FROM which table, WHERE which condition, ORDER BY which columns, LIMIT how many rows — and it is the fixed skeleton that every later feature (joins, grouping, subqueries) plugs into. This session writes your first real queries on the bookshop schema: picking columns, filtering with comparison and logical operators, sorting with ORDER BY and DESC, and truncating with LIMIT. You will trace every result set by hand.

---

The SELECT skeleton

The simplest query reads all columns of a table:

SELECT * FROM authors;

* means "every column". Result: the whole authors table. Almost every query you write will instead name specific columns:

SELECT name, country FROM authors;

Only the two named columns come back — the shape of the result set is decided by the column list, and the contents by the rows the query keeps.

The clause order, once and for all (in this session, only the first five clauses are used):

SELECT   columns / expressions
FROM     table(s)
WHERE    row condition          -- keeps rows, removes others
GROUP BY grouping columns       -- Session 4
HAVING   group condition        -- Session 4
ORDER BY sort columns           -- sorts the result set
LIMIT    number of rows         -- truncates the result set

SQL executes the clauses in a specific mental order: FROM decides the source, WHERE filters rows, then grouping (if any), then SELECT computes the output columns, then ORDER BY sorts, then LIMIT cuts. Getting this order right is the single most useful habit for debugging a query — most surprises are "I filtered after I should have" or "I sorted before I grouped".

---

WHERE: keeping only the rows you want

WHERE is the filter. It is written after FROM, before ORDER BY, and it removes every row that does not satisfy the condition:

SELECT title, price
FROM books
WHERE price > 20;

Comparison operators: =, <> (not equal), >, >=, <, <=. Text comparisons use quotes: WHERE genre = 'mystery'. Combine conditions with AND, OR, and negate with NOT:

SELECT title FROM books
WHERE genre = 'fiction' AND price < 25;

AND requires both conditions; OR requires either. When you mix them, parentheses say which binds first — WHERE genre = 'fiction' OR (genre = 'history' AND price < 30) reads "fiction, or a cheap history", not "fiction-or-history, and cheap".

Rows that fail the condition disappear. If a filter keeps zero rows, the result set is empty — that is a valid, sometimes very informative answer ("nothing matches").

---

ORDER BY: sorting the result set

Tables have no guaranteed order, so if you want a readable sequence you sort explicitly:

SELECT title, price
FROM books
ORDER BY price DESC;

ASC (default, smallest first) and DESC (largest first) choose the direction. Sort by several columns to break ties — the first column is the primary sort, later ones refine:

SELECT title, author_id, published_year
FROM books
ORDER BY author_id, published_year DESC;

Here rows group by author, and within each author the newest book comes first. Sorting happens after WHERE and after the SELECT columns are computed, which matters once expressions appear (Session 3).

---

LIMIT: cutting the result set

LIMIT n keeps only the first n rows of the sorted result:

SELECT title, price
FROM books
ORDER BY price DESC
LIMIT 3;

"The three most expensive books." LIMIT is almost always paired with ORDER BY, because without an order the "first" rows are arbitrary.

---

DISTINCT: unique values

SELECT DISTINCT removes duplicate rows from the result, keeping each distinct combination of the selected columns once:

SELECT DISTINCT genre FROM books;

Returns each genre name once. DISTINCT applies to the whole selected row (all listed columns together), not one column at a time.

---

Building a query step by step

Take a concrete question: "List the three most recent history books, cheapest first."

1. FROM books — start with all book rows.
2. WHERE genre = 'history' — keep only history.
3. SELECT title, price — pick the columns.
4. ORDER BY published_year DESC — most recent first.
5. LIMIT 3 — keep three.

SELECT title, price
FROM books
WHERE genre = 'history'
ORDER BY published_year DESC
LIMIT 3;

Each clause answers one sub-question; reading the query top to bottom is the plan. This decomposition — one clause per requirement — is the way every later session builds more complex queries.

---

Pitfalls to watch for

• WHERE before ORDER BY, always. A condition that looks like a sort ("the expensive ones") is still a filter and belongs in WHERE.
• = is equality, not assignment. WHERE price = 20 selects rows whose price is exactly 20.
• LIMIT without ORDER BY is arbitrary. It is not wrong, but the rows chosen are unspecified.
• Quotes for text, no quotes for numbers. WHERE genre = mystery (no quotes) is an error in most engines; WHERE price = '20' is comparing a number to text.

Session 2 — Worked examples

The bookshop data (from Session 1):

books

| book_id | title | author_id | genre | price | published_year | |---------|-------|-----------|-------|-------|----------------| | 10 | The Glass Harbour | 1 | fiction | 24.99 | 2019 | | 11 | Static Echoes | 1 | fiction | 19.50 | 2021 | | 12 | River Clock | 2 | mystery | 22.00 | 2018 | | 13 | Salt Roads | 3 | history | 31.00 | 2020 | | 14 | North of Noon | 3 | history | 27.50 | 2017 | | 15 | Prairie Circuit | 4 | mystery | 18.00 | 2022 |

Example A — Column selection

SELECT title, price FROM books;

Result set (rows in original order, only two columns):

| title | price | |-------|-------| | The Glass Harbour | 24.99 | | Static Echoes | 19.50 | | River Clock | 22.00 | | Salt Roads | 31.00 | | North of Noon | 27.50 | | Prairie Circuit | 18.00 |

Six rows, two columns. The table itself is unchanged — the result set is a view of it.

---

Example B — Filtering with comparisons

SELECT title, price
FROM books
WHERE price > 20;

Check each row: 24.99 ✓, 19.50 ✗, 22.00 ✓, 31.00 ✓, 27.50 ✓, 18.00 ✗.

| title | price | |-------|-------| | The Glass Harbour | 24.99 | | River Clock | 22.00 | | Salt Roads | 31.00 | | North of Noon | 27.50 |

Four rows survive. Trace discipline: go row by row through the source and cross out failures before writing the result.

---

Example C — Combining conditions

SELECT title, genre, price
FROM books
WHERE (genre = 'fiction' OR genre = 'history') AND price < 30;

Step 1 — the parenthesised OR keeps fiction and history books: 10, 11, 13, 14. Step 2 — AND price < 30 removes 13 (price 31.00). Result:

| title | genre | price | |-------|-------|-------| | The Glass Harbour | fiction | 24.99 | | Static Echoes | fiction | 19.50 | | North of Noon | history | 27.50 |

---

Example D — Sorting

SELECT title, published_year
FROM books
ORDER BY published_year DESC;

Sorting newest first:

| title | published_year | |-------|----------------| | Prairie Circuit | 2022 | | Static Echoes | 2021 | | Salt Roads | 2020 | | The Glass Harbour | 2019 | | River Clock | 2018 | | North of Noon | 2017 |

---

Example E — Sorting by two columns

SELECT title, author_id, published_year
FROM books
ORDER BY author_id, published_year DESC;

Group by author (1, then 2, 3, 4); within each author, newest first:

| title | author_id | published_year | |-------|-----------|----------------| | Static Echoes | 1 | 2021 | | The Glass Harbour | 1 | 2019 | | River Clock | 2 | 2018 | | Salt Roads | 3 | 2020 | | North of Noon | 3 | 2017 | | Prairie Circuit | 4 | 2022 |

---

Example F — LIMIT on a sorted result

SELECT title, price
FROM books
ORDER BY price DESC
LIMIT 3;

The three most expensive books:

| title | price | |-------|-------| | Salt Roads | 31.00 | | North of Noon | 27.50 | | The Glass Harbour | 24.99 |

---

Example G — DISTINCT

SELECT DISTINCT genre FROM books;

| genre | |-------| | fiction | | mystery | | history |

Three rows. SELECT DISTINCT genre, author_id would instead keep distinct pairs (e.g. fiction/1 once, not twice).

---

Example H — A full decomposed query

Question: "The cheapest history book."

1. FROM books
2. WHERE genre = 'history' → keeps 13, 14.
3. ORDER BY price ASC → North of Noon (27.50) first.
4. LIMIT 1.

SELECT title, price
FROM books
WHERE genre = 'history'
ORDER BY price ASC
LIMIT 1;

Result: one row, North of Noon | 27.50. Every clause handled exactly one part of the question.