Expressions and calculations
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Calculating in SELECT
A SELECT doesn't only read columns: it can also compute values. You can use the arithmetic operators +, -, * and / directly on columns.
Here is the Codémon table we'll use in this chapter:
Let's double the power of every Codémon:
SELECT name, power, power * 2
FROM codemon;Each row gets a new value computed from its own power. The table itself is not modified: an expression only exists in the result of the query.
SELECT as a calculator
You can even run a SELECT without any table: SELECT 7 * 6; simply returns 42. Handy for quick checks!
Exercise 2 — Naming computed columns
Look at the column header in the previous result: it is literally power * 2. Not very friendly! An expression behaves like any other column, so you can give it an alias with AS:
SELECT name, power * 2 AS doubled_power
FROM codemon;You can combine several columns in one expression, and use as many expressions as you want in the same query:
SELECT name,
power + level AS total,
power - level AS gap
FROM codemon;Parentheses matter
Operators follow the usual math priority: * and / are applied before + and -. So power + level * 2 is not the same as (power + level) * 2. When in doubt, add parentheses.
Exercise 3 — Predict the result
Given this table:
What value does the diff column contain for Aquachu?
SELECT name, power - level AS diff
FROM codemon;Hint : Aquachu has a power of 75 and a level of 15. The expression subtracts the level from the power, row by row.
Exercise 4 — Complete the calculation
Complete the query so it shows each Codémon's name and its power multiplied by 3, in a column named boosted.
Hint : Multiplication uses the * symbol, and a computed column receives its name with AS.
Exercise 5 — Division surprises
Division has a trap. When both operands are whole numbers, SQLite performs an integer division: the decimal part is simply dropped.
SELECT name, power, power / 2 AS half
FROM codemon;Aquachu's half power shows 37, not 37.5. To get a decimal result, make one of the operands a decimal number:
SELECT name, power, power / 2.0 AS half
FROM codemon;The % operator returns the remainder of a division. It's a classic way to know whether a number is even (remainder 0) or odd (remainder 1):
SELECT name, level, level % 2 AS remainder
FROM codemon;Other databases
Integer division also happens in PostgreSQL and SQL Server. MySQL and Oracle are exceptions: there, 75 / 2 gives 37.5. Writing / 2.0 gives the same result everywhere.
Exercise 6 — Match the expressions
Match each expression with the value it returns.
75 / 275 / 2.075 % 2(70 + 80) / 2Exercise 7 — Working with text
Expressions aren't only for numbers. The || operator concatenates text values, which means it glues them together:
SELECT name || ' the ' || type || ' Codémon' AS title
FROM codemon;Text written between single quotes is a literal: a fixed value that is identical on every row. You can also select a literal on its own:
SELECT name, 'Codémon' AS kind, level
FROM codemon;Numbers are automatically converted to text when you concatenate them:
SELECT name || ' (level ' || level || ')' AS label
FROM codemon;Single quotes for text
In SQL, text literals use single quotes: 'fire'. Double quotes are reserved for column and table names. Mixing them up is one of the most common beginner mistakes.
CONCAT in other databases
|| is the standard SQL operator, supported by SQLite, PostgreSQL and Oracle. MySQL and SQL Server use the CONCAT() function instead.
Exercise 8 — Build the label
Which query produces the text Pyrochu (fire) for Pyrochu?
SELECT name || ' (' || type || ')' FROM codemon;SELECT name + ' (' + type + ')' FROM codemon;SELECT name, ' (', type, ')' FROM codemon;SELECT CONCAT name, type FROM codemon;Hint : Concatenation uses ||, and text literals go between single quotes. Separating values with commas creates separate columns, not one text.
Exercise 9 — Battle report
The Codédex needs a battle report for every Codémon.
Write a query that returns, for each Codémon:
1. its name
2. its power plus its level, in a column named score
3. its name followed by its type between parentheses, like Aquachu (water), in a column named label
-- Write your query below
SELECT
FROM codemon;