Column Expressions and Aliases

Computing inside SELECT

A plain SELECT name, salary hands you back exactly what is stored — nothing more. But the answers people actually want are rarely stored directly: monthly pay, a formatted label, a total after tax. You could pull the raw rows and recompute those in your application, yet that means shipping more data than you need and duplicating the same formula in every place that reads the table.

SQL lets you compute inside the SELECT list instead. The arithmetic operators +, -, *, and / work the way you expect, and the result becomes a new column in the output:

SELECT name, salary, salary / 12 AS monthly_salary
FROM employees;

The expression salary / 12 is evaluated once per row, and AS monthly_salary gives that computed column a readable name.

Compute a monthly salary column on the fly.

SELECT name, salary, salary / 12 AS monthly_salary
FROM employees
LIMIT 5;

Precedence and parentheses

SQL follows the same precedence you learned in school: multiplication and division bind tighter than addition and subtraction. So salary + bonus * 0.1 adds only a tenth of the bonus, because the multiply happens first.

When you need a different grouping, parentheses override the order — (salary + bonus) * 0.1 adds the bonus first, then takes a tenth. Reach for parentheses whenever the intended order is not obvious; they cost nothing and they make the query self-documenting.

flowchart LR
  F["FROM employees"] --> W["WHERE (optional)"]
  W --> S["SELECT<br/>columns + expressions"]
  S --> O["ORDER BY (optional)"]
  style S fill:#0e7490,color:#fff

The database evaluates expressions for every row after it knows which table and rows are involved.

The integer-division trap

Here is a trap that silently gives wrong answers. The salary column in our data is an integer, and in SQLite dividing one integer by another performs integer division — it throws the fractional part away. 910000 / 12 is not 75833.33, it is 75833.

That is rarely what you want for money. Force real-number division by making one operand a fraction: salary * 1.0 / 12 multiplies by 1.0 first, which turns the value into a real, and the division that follows keeps its decimals. Multiplying by 1.0 is the one-line habit that keeps your rates and averages honest.

flowchart LR
  A["salary = 910000<br/>(integer)"] -->|"÷ 12"| B["75833.33 mathematically"]
  B --> C["SQLite truncates<br/>→ 75833"]
  A -->|"× 1.0 ÷ 12"| D["75833.33 kept"]
  style C fill:#b45309,color:#fff
  style D fill:#0e7490,color:#fff

Integer ÷ integer truncates; multiplying by 1.0 first keeps the decimals.

Force real division so the daily rate keeps its decimals.

SELECT name, salary, salary * 1.0 / 365 AS daily_rate
FROM employees
LIMIT 5;

Functions inside expressions

Arithmetic is only the start. SQL also has scalar functions you can drop into the same expressions: ROUND(x, 2) rounds to two decimals, UPPER(name) uppercases the text, and ABS(x) strips any minus sign. They compose just like arithmetic — ROUND(salary * 1.0 / 12, 2) gives a monthly figure rounded to the nearest cent.

The rule is the same as before: evaluate per row, name the result with AS, and the computed value appears as a fresh column. Anything you could write on a calculator, you can write here.

Joining text with ||

In SQLite the concatenation operator is two vertical bars, ||. It stitches values and literal strings together, so you can build human-readable output directly in the query:

SELECT name || ' - ' || role AS greeting
FROM employees;

For Ada Nilsen that yields Ada Nilsen - Engineer. You can chain as many pieces as you like — columns, quoted literals, even numbers, which SQLite converts to text for you along the way.

SELECT name || ' - ' || role AS greeting
FROM employees
LIMIT 5;

Aliases earn their keep in ORDER BY

Once a computed column has a name, you can sort by it — ORDER BY monthly_salary DESC reads far better than re-typing the whole expression. SQLite lets you put an alias straight into ORDER BY, because sorting happens after the SELECT list is built.

If you want a header that contains a space or mixed case, wrap the alias in double quotes: AS "Monthly Pay". Single quotes are for string values; double quotes are for names. Mixing those up is a common source of confusing errors.

Exercise

List each employee's name and their salary after a 10% raise. Alias the computed column as raised_salary.

SELECT name, salary * 1.1 AS raised_salary
FROM employees
;

SELECT name, salary * 1.1 AS raised_salary FROM employees;

Exercise

Return a single column for each employee formatted as 'Name - Role' (for example, 'Ada Nilsen - Engineer'). Alias it as greeting.

SELECT name || ' - ' || role AS greeting
FROM employees
;

SELECT name || ' - ' || role AS greeting FROM employees;

Exercise

This query was meant to show each employee's monthly salary to the cent, but because both operands are integers it silently truncates to a whole number. Repair it so the decimals survive.

SELECT name, salary / 12 AS monthly_salary
FROM employees
;

SELECT name, salary * 1.0 / 12 AS monthly_salary FROM employees;

Exercise

Build one column called role_tag for each employee, shaped exactly like [Engineer] Ada Nilsen — a literal [, then the role, a literal ] , then the name. (Order matters: this is not the same arrangement as the example above.)

SELECT
  -- concatenate a literal, role, a literal, then name
FROM employees
;

SELECT '[' || role || '] ' || name AS role_tag FROM employees;

Exercise

Which query correctly calculates a 5% bonus on every employee's salary?

Recap

Compute inside SELECT with + - * /; name each result with AS.
Precedence follows school maths — use parentheses to override it.
Integer divided by integer truncates; multiply by 1.0 to keep decimals.
|| joins text and literals, but any NULL makes the whole result NULL.
Aliases are display labels; double-quote a name, single-quote a value.

Next you'll summarise whole columns down to single numbers with aggregates.

Column Expressions and Aliases

Computing inside SELECT

Precedence and parentheses

The integer-division trap

Functions inside expressions

Joining text with ||

Aliases earn their keep in ORDER BY

Recap

Checkpoint quiz

Go deeper — technical resources