Session 3 — Computed columns, aliases, and text functions

A SELECT list does not have to name existing columns — it can compute new ones. You will write arithmetic expressions (price * qty), concatenate text with ||, and use functions like UPPER, LENGTH, TRIM, SUBSTRING, and LIKE patterns. Aliases (AS) give computed columns readable names, and you will learn the crucial rule: WHERE cannot see aliases, ORDER BY can — because filtering happens before the expression is evaluated, sorting after.

Plan: Core ideas → worked expressions → Quiz.

Time: about 55–65 minutes if you recompute every derived column by hand.

Session 3 — Computed columns, aliases, and text functions

The one-paragraph story

The SELECT list does not have to name existing columns — it can compute new ones. You can multiply two columns (price * qty), join text together, and apply built-in functions that trim, uppercase, or search strings. A computed column is just an expression evaluated once per result row, and you can give it a readable name with an alias (AS). Aliases matter twice: they make the result readable, and they give later clauses something to refer to (though the exact rules for using an alias in WHERE versus ORDER BY differ). This session turns the bookshop data into derived values — line totals, upper-cased names, trimmed strings — and builds the habit of writing expressions that are easy to read and verify.

---

Expressions in the SELECT list

An expression is anything that evaluates to a value per row: a column name, a constant, arithmetic, a function call, or any combination.

SELECT title,
       price * 2 AS double_price
FROM books;

For every book row, the second column is the number price * 2. Arithmetic operators work on numbers: +, -, *, /; parentheses group them, so (price + 2) * 3 differs from price + 2 * 3.

Division deserves a caution. In many engines, 7 / 2 uses integer division and yields 3, not 3.5. When you need a decimal, make at least one operand decimal — e.g. price / 2.0 — or use the engine's explicit cast. This single gotcha causes more "why is my average wrong" moments than anything else in Session 4.

---

Concatenation: putting text together

Most engines join text with ||:

SELECT name || ' — ' || country AS author_badge
FROM authors;

produces values like 'Alina Voss — Germany'. (Some engines use a CONCAT(a, b) function; the || operator is the most portable in modern SQL.)

NULLs spread. In standard SQL, anything combined with NULL becomes NULL: NULL || 'suffix' is NULL, not 'suffix'. If a column may be NULL (Session 5 shows how LEFT JOINs create them), concatenation silently empties the result. The guard is COALESCE(col, 'fallback'), which returns the first non-NULL value:

SELECT COALESCE(name, 'unknown') AS display_name FROM authors;

---

Useful text functions

A small toolkit (names are standard; check your engine's exact list):

• UPPER(s) / LOWER(s) — change case.
• LENGTH(s) — number of characters.
• TRIM(s) — remove leading/trailing whitespace.
• SUBSTRING(s FROM a FOR b) (or SUBSTR) — extract a slice.
• LIKE — pattern matching: WHERE title LIKE '%clock%' matches any title containing "clock", where % means "any run of characters" and _ means "exactly one character".

SELECT title FROM books
WHERE title LIKE '%river%' OR title LIKE '%circuit%';

Text functions return values exactly like arithmetic does — they are expressions, usable in the SELECT list, in WHERE, and anywhere else an expression fits.

---

Aliases: names for computed columns

Give a computed column a name with AS:

SELECT title,
       price * qty AS line_total
FROM sales JOIN books USING (book_id);   -- joins arrive in Session 5

The alias line_total labels the column in the result set. AS is optional in many engines (price * qty line_total), but writing it makes the query self-documenting.

Where can an alias be used? Because the database computes the SELECT list after filtering, an alias cannot be used in WHERE (the column does not exist yet) but can be used in ORDER BY (sorting happens after computation). So this is fine:

SELECT title, price * 2 AS double_price
FROM books
ORDER BY double_price DESC;

…but this is an error in standard SQL:

-- Error: WHERE runs before the SELECT list is computed
SELECT title, price * 2 AS double_price
FROM books
WHERE double_price > 40;

The mental model: WHERE sees the source table's columns; ORDER BY sees the result's columns.

---

Reading an expression-driven query

Question: "Show each book title and its price after a 10% discount, cheapest discounted price first."

SELECT title,
       price * 0.9 AS discounted
FROM books
ORDER BY discounted ASC;

Steps: FROM all books → SELECT computes price * 0.9 per row and labels it discounted → ORDER BY discounted sorts the result by that computed value. One expression, one alias, one sort — and the query reads top to bottom exactly as planned.

---

Pitfalls to watch for

• WHERE cannot see aliases — it filters the source columns before the SELECT list is computed.
• ORDER BY can see aliases — sorting happens after computation.
• Division is integer-clean only by accident — write decimals explicitly when you mean fractions.
• NULL spreads through expressions — guard with COALESCE whenever a column can be NULL.
• Quotes: single quotes for text literals, double quotes (or none) for identifiers — 'fiction' is a value; "genre" is a column (in engines that accept double-quoted identifiers).

Session 3 — Worked examples

The bookshop data:

authors: (1, Alina Voss, Germany), (2, Mateo Rios, Argentina), (3, Priya Anand, India), (4, Jonas Weber, Germany)

books: (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)

sales: (100, 10, 2, 2024-01-05), (101, 12, 1, 2024-01-05), (102, 10, 3, 2024-01-12), (103, 14, 1, 2024-01-12), (104, 13, 2, 2024-01-19)

Example A — Arithmetic and aliases

SELECT title, price, price * 2 AS double_price FROM books;

| title | price | double_price | |-------|-------|--------------| | The Glass Harbour | 24.99 | 49.98 | | Static Echoes | 19.50 | 39.00 | | River Clock | 22.00 | 44.00 | | Salt Roads | 31.00 | 62.00 | | North of Noon | 27.50 | 55.00 | | Prairie Circuit | 18.00 | 36.00 |

The third column is computed per row. Verify one by hand: 24.99 * 2 = 49.98 ✓.

---

Example B — A line total across two tables

SELECT s.sale_id, s.qty, b.price, s.qty * b.price AS line_total
FROM sales s
JOIN books b ON b.book_id = s.book_id
ORDER BY line_total DESC;

(Table aliases s and b and the JOIN are explained in Sessions 5–6; read the query as "for each sale, bring the book's price along".)

| sale_id | qty | price | line_total | |---------|-----|-------|------------| | 102 | 3 | 24.99 | 74.97 | | 100 | 2 | 24.99 | 49.98 | | 104 | 2 | 31.00 | 62.00 | | 101 | 1 | 22.00 | 22.00 | | 103 | 1 | 27.50 | 27.50 |

Sorted by line_total DESC: 74.97, 62.00, 49.98, 27.50, 22.00. The alias was computed in SELECT, then used in ORDER BY — legal, because sorting runs after the expression is evaluated.

---

Example C — Aliases cannot be used in WHERE

-- Standard SQL error: double_price is not available yet
SELECT title, price * 2 AS double_price
FROM books
WHERE double_price > 40;

Trace it: WHERE filters the source rows — at that moment the only columns are title, price, etc. The expression price * 2 has not been evaluated. The fix is to repeat the expression:

SELECT title, price * 2 AS double_price
FROM books
WHERE price * 2 > 40;

which keeps books 10, 12, 13, 14 (prices 24.99, 22.00, 31.00, 27.50).

---

Example D — Concatenation

SELECT name || ' (' || country || ')' AS badge FROM authors;

| badge | |-------| | Alina Voss (Germany) | | Mateo Rios (Argentina) | | Priya Anand (India) | | Jonas Weber (Germany) |

---

Example E — Text functions

SELECT UPPER(title) AS upper_title,
       LENGTH(title) AS chars
FROM books
WHERE title LIKE '%o%o%';

Which titles contain two 'o's (the pattern %o%o% means "some characters, an o, some characters, another o")? "North of Noon" — yes (o…o). "The Glass Harbour" — no (one 'o' after H… actually "Harbour" has one o; the title has no earlier o). "Static Echoes" — one o. So only "North of Noon" (chars 13) and "River Clock" (one o) fail; check each: North of Noon → 'NORTH OF NOON', 13. "Salt Roads" — one o. "Prairie Circuit" — two o's? "Circuit" has no 'o'. So the only match is North of Noon.

| upper_title | chars | |-------------|-------| | NORTH OF NOON | 13 |

Trace discipline: run %o%o% character by character over each title before trusting the pattern.

---

Example F — COALESCE guards NULLs

Suppose authors.country had a NULL row (Jonas Weber with no country listed). Then

SELECT name || ' — ' || COALESCE(country, 'unknown') AS badge FROM authors;

yields 'Jonas Weber — unknown' instead of silently dropping the suffix. Without COALESCE, 'Jonas Weber' || ' — ' || NULL would be NULL — an invisible empty cell.