DataslopeExpressions and Aliases
Compute new values in SQLite queries with arithmetic, text concatenation, aliases, and common functions.
A query can do more than repeat stored values. It can compute new values as it reads each row.
Those calculations are called expressions. An expression might multiply price by quantity, join two pieces of text, round a number, or make text uppercase. An alias gives the computed result a clear name.
Expressions create computed columns
Anywhere you can select a column, you can also write a calculation. SQLite evaluates that calculation for each row in the result.
line_total is not stored in the table. SQLite calculates it for the
query result.
Arithmetic operators
SQLite can use familiar arithmetic operators in expressions.
| Operator | Meaning | Example |
|---|---|---|
+ | add | price + 1 |
- | subtract | price - 1 |
* | multiply | price * quantity |
/ | divide | price / 2 |
ROUND(value, 2) rounds to two decimal places. It is handy for money
examples, though real financial systems need more careful rules.
In the expression price * quantity AS line_total, what does the alias do?
It saves line_total as a permanent table column.
It names the computed result column line_total.
It changes multiplication into addition.
It filters rows where line_total is missing.
AS aliases make results readable
Without an alias, a computed column may get a clumsy name based on the
expression. AS lets you choose a friendly output name.
The two computed values are the same. The aliased one is easier to read.
This course uses AS because it makes your intent obvious.
Join text with ||
In SQLite, || concatenates text. "Concatenate" means join pieces of
text together.
The expression joins the category, a colon and space, and the item name.
Common SQLite functions
A function takes input and returns a value. SQLite includes many useful built-in functions. Here are beginner-friendly ones:
| Function | Example | What it does |
|---|---|---|
ROUND(number, places) | ROUND(3.14159, 2) | rounds a number |
LENGTH(text) | LENGTH('cat') | counts characters |
UPPER(text) | UPPER('cat') | makes text uppercase |
LOWER(text) | LOWER('CAT') | makes text lowercase |
substr(text, start, count) | substr('SQLite', 1, 3) | returns part of text |
SQLite's substr starts counting at 1. So substr(item, 1, 4) returns
the first four characters.
Expressions work in ORDER BY and WHERE
You can compute a value to filter rows, sort rows, or both.
The expression price * quantity is calculated for each row. SQLite can
use that calculation to decide which rows pass and where they appear in
the sorted result.
Check your understanding
What is a computed column in a SELECT result?
A value that must already be stored in the table.
A value calculated by an expression for each row.
A column that always changes the table structure.
A filter that removes rows.
What does the SQLite operator || do with text?
Divides one text value by another.
Joins text values into one longer text value.
Compares two values for equality.
Selects every column.
Which function would you use to count the characters in a text value?
ROUND
UPPER
LENGTH
substr
Why use AS total_price after an expression?
To permanently rename a table column.
To make SQLite store the expression for later automatically.
To give the result column a clear name.
To force the result to sort by that column.