SQLite - Expressions

An expression is a combination of one or more values, operators, and SQL functions that resolves to a value. SQL expressions are a fundamental part of querying, allowing you to transform, calculate, and filter data directly within the database.

Syntax

Expressions are most commonly used in the SELECT list and the WHERE clause of a query.

SELECT expression_1, expression_2, ...
FROM table_name
WHERE filter_expression;

Let’s explore the different types of SQLite expressions.

SQLite - Boolean Expressions

Boolean expressions evaluate to TRUE, FALSE, or UNKNOWN. They are the cornerstone of the WHERE clause, used to filter rows based on a condition.

Consider an EMPLOYEES table with the following records:

ID | NAME      | AGE | DEPARTMENT  | SALARY
---|-----------|-----|-------------|---------
1  | Alice     | 32  | Engineering | 95000.0
2  | Bob       | 25  | Marketing   | 60000.0
3  | Charlie   | 28  | Engineering | 85000.0
4  | David     | 45  | Marketing   | 72000.0
5  | Eve       | 28  | Sales       | 68000.0

This example uses a boolean expression (SALARY > 70000) to fetch only employees earning more than $70,000.

sqlite> SELECT name, department, salary FROM EMPLOYEES WHERE SALARY > 70000;

NAME    | DEPARTMENT  | SALARY
--------|-------------|---------
Alice   | Engineering | 95000.0
Charlie | Engineering | 85000.0
David   | Marketing   | 72000.0

SQLite - Numeric Expressions

Numeric expressions perform mathematical calculations. You can use them directly in the SELECT list, often with an alias (AS) for a readable column name.

SELECT numerical_expression AS alias_name
[FROM table_name WHERE condition];

Here’s a simple example:

sqlite> SELECT (20 * 5) + 10 AS result;

result
------
110

A more practical example is calculating a projected annual bonus for each employee.

sqlite> SELECT name, salary, salary * 0.1 AS bonus FROM EMPLOYEES;

NAME      | SALARY  | bonus
----------|---------|--------
Alice     | 95000.0 | 9500.0
Bob       | 60000.0 | 6000.0
Charlie   | 85000.0 | 8500.0
David     | 72000.0 | 7200.0
Eve       | 68000.0 | 6800.0

SQLite also includes aggregate functions like AVG(), SUM(), and COUNT() that perform calculations across multiple rows.

sqlite> SELECT COUNT(*) AS total_employees, AVG(salary) AS average_salary FROM EMPLOYEES;

total_employees | average_salary
----------------|-----------------
5               | 76000.0

SQLite - Date and Time Expressions

SQLite provides powerful functions for working with dates and times. These expressions can return the current timestamp or manipulate date/time values.

To get the current timestamp:

sqlite> SELECT CURRENT_TIMESTAMP;

CURRENT_TIMESTAMP
-------------------
2023-10-27 14:50:15

The strftime() function is extremely versatile for formatting and calculating dates. Here, we calculate the date 30 days from now.

sqlite> SELECT date('now', '+30 days');

date('now', '+30 days')
----------------------
2023-11-26