Expressions in SQLite

SQLite Expressions To evaluate the value, a combination of one or more values, operators and SQL functions can be used. These combinations are written in query language in SQLite and are called SQLite expressions. They are mostly used with the SQLite SELECT statement.

Syntax:

SELECT column_1, column_2, column_N   
FROM table_name   
WHERE [CONDITION | EXPRESSION];

Types of SQLite expressions: There are three types of SQLite expressions: Boolean, Numeric and Date.

SQLite Boolean Expression: Used to fetch the data based on matching the single value. Syntax:

SELECT column_1, column_2, column_N   
FROM table_name   
WHERE EXPRESSION;

Example: TEACHERS Table:

ID	NAME	SUBJECT
1	Jim	English
2	John	Geology
3	Watson	French
4	Holmes	Chemistry
5	Tony	Physics

Example:

SELECT * 
FROM TEACHERS 
WHERE ID = 5;

Output:

ID	NAME	SUBJECT
5	Tony	Physics

Explanation: In the above example, the “TEACHERS” is an already existing table. Here the result is returned after matching a single value.

SQLite Numeric Expressions: Used to perform any mathematical operations in the query.

Syntax:

SELECT numerical_expression as  OPERATION_NAME  
[FROM table_name WHERE CONDITION] ;

Example:

SELECT (20 * 10) AS MULTIPLICATION;

Output:

Explanation: In the above example, we performed a multiplication operation.

Aggregate data calculation functions: Built-in functions like avg(), sum(), count(), etc. are known as aggregate data calculation functions, and are used in numeric expressions.

Example: TEACHERS Table:

ID	NAME	SUBJECT
1	Jim	English
2	John	Geology
3	Watson	French
4	Holmes	Chemistry
5	Tony	Physics

Example:

SELECT COUNT(*) AS "COUNT_NUM" 
FROM TEACHERS;

Output:

COUNT_NUM
5

Explanation: In the above example, the “TEACHERS” is an already existing table. Here, we have used an aggregate function, COUNT, thus the count of the total number of records in the table is returned in the result.

SQLite Date Expression: Used to fetch the current system date and time values.

Syntax: