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:
200 |
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:
SELECT CURRENT_TIMESTAMP;
Example:
SELECT CURRENT_TIMESTAMP; |
Output:
2019-08-02 12:15:00 |
Explanation:
In the above example, the current timestamp is returned.