SQLite Expressions

An expression is a combination of one or more values, operators, and SQL functions that evaluate to a value.

SQL expressions are similar to formulas, both written in the query language. You can also use specific data sets to query the database.

Syntax

Assume the basic syntax of the SELECT statement is as follows:

SELECT column1, column2, columnN 
FROM table_name 
WHERE [CONDITION | EXPRESSION];

There are different types of SQLite expressions, detailed as follows:

SQLite - Boolean Expressions

SQLite Boolean expressions fetch data based on matching a single value. The syntax is as follows:

SELECT column1, column2, columnN 
FROM table_name 
WHERE SINGLE VALUE MATCHING EXPRESSION;

Assume the COMPANY table has the following records:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

The following example demonstrates the usage of SQLite Boolean expressions:

sqlite> SELECT * FROM COMPANY WHERE SALARY = 10000;
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           James        24          Houston   10000.0

SQLite - Numeric Expressions

These expressions are used to perform any mathematical operations in a query. The syntax is as follows:

SELECT numerical_expression as  OPERATION_NAME
[FROM table_name WHERE CONDITION] ;

Here, numerical_expression is used for a mathematical expression or any formula. The following example demonstrates the usage of SQLite numeric expressions:

sqlite> SELECT (15 + 6) AS ADDITION
ADDITION = 21

There are several built-in functions, such as avg(), sum(), count(), etc., that perform summary data calculations on a table or a specific table column.

sqlite> SELECT COUNT(*) AS "RECORDS" FROM COMPANY; 
RECORDS = 7

SQLite - Date Expressions

Date expressions return current system date and time values. These expressions will be used in various data operations.

sqlite>  SELECT CURRENT_TIMESTAMP;
CURRENT_TIMESTAMP = 2013-03-17 10:43:35
Other Extensions