Description

Login
Back

Beginner · 12 min

Expressions and aliases

Calculate readable results while controlling numeric division.

Sample database

CREATE TABLE groups (id INTEGER PRIMARY KEY, label TEXT NOT NULL);
CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT, group_id INTEGER REFERENCES groups(id));
CREATE TABLE sessions (id INTEGER PRIMARY KEY, member_id INTEGER NOT NULL REFERENCES members(id), topic TEXT NOT NULL, minutes INTEGER NOT NULL CHECK(minutes > 0), completed INTEGER NOT NULL CHECK(completed IN (0, 1)), day TEXT NOT NULL);
INSERT INTO groups VALUES (1, 'Morning'), (2, 'Evening');
INSERT INTO members VALUES (1, 'Ada', 'Oslo', 1), (2, 'Bo', 'Rome', 1), (3, 'Cy', NULL, 2), (4, 'Dee', 'Oslo', 2);
INSERT INTO sessions VALUES
  (1, 1, 'Travel', 20, 1, '2026-01-01'),
  (2, 1, 'Food', 10, 0, '2026-01-02'),
  (3, 2, 'Travel', 30, 1, '2026-01-01'),
  (4, 2, 'Music', 15, 1, '2026-01-03'),
  (5, 3, 'Music', 25, 0, '2026-01-02'),
  (6, 3, 'Travel', 15, 1, '2026-01-04');

Calculate a derived value

Expressions calculate values without changing stored rows. Use a clear alias for a calculated column. Selecting only the required rows keeps the output small and makes the calculation easy to check.

SELECT id, minutes * 2 AS doubled FROM sessions WHERE id <= 2 ORDER BY id;

Output

id | doubled
1 | 40
2 | 20

Avoid accidental integer division

In SQLite, dividing two integers performs integer division. A real operand preserves a fractional result. Database systems have different numeric type rules, so choose types deliberately for money and measurements.

SELECT minutes / 60.0 AS hours FROM sessions WHERE id = 3;

Output

hours
0.5

Put it into practice

  1. Read the sample tables and predict the result before running the query.
  2. Solve the task, compare the returned rows, and explain why the solution works.

Try it yourself

Code runs on this device. When you are signed in, drafts sync to your account.

Run queries on a fresh sample database. Table changes last for this run only; your query draft is saved separately. Results show column names followed by rows. NULL means a missing value.

Apply what you learned

Return the duration of session 3 in hours, preserving the fraction and naming the column hours.

Show solution
SELECT minutes / 60.0 AS hours FROM sessions WHERE id = 3;

The real divisor produces 0.5 instead of truncating the result to zero.

Practice