Expressions and calculations

The full text of this chapter, to read or revisit. To practise, do the exercises.

▶ Do the exercises

Exercise 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;
Options
• 60
• 90
• 1125
• An error: you can't subtract two columns

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.

Items to match
• 75 / 2
• 75 / 2.0
• 75 % 2
• (70 + 80) / 2
Definitions
• 37
• 37.5
• 1
• 75

Exercise 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?

Options
• 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

Starter code
-- Write your query below SELECT FROM codemon;
Back to the course