Here we will learn sqlite expressions with example and different types of sqlite expressions (binary expressions, unary expressions, boolean expressions, numeric expressions, date expressions) with examples.
The SQLite expression is a combination of one or more explicit values, constants, variables, operators, and functions that the SQLite interprets and computes to produce value.
Like other programming languages, SQLite has a fairly flexible expression syntax that can be used to combine and compute values.
Many times an expression is used to define a conditional, such as which rows are returned as a result. In these contexts, an expression need only return a logical true or false value.
Consider the very basic syntax of SQLite SELECT statement as follows:
SELECT columns
FROM table
WHERE expression
Based on operands that the expression takes, there are two types of Expressions in SQLite. They are as follows:
The SQLite Binary operators take two values as parameter expressions and combine or compare them in some way that produces an output value.
Following is the syntax of SQLite Binary Expressions.
left-expr binary-operator right-expr
The following are the different types of binary expression operators available in SQLite.
| Operator | Name of Operator |
|---|---|
| || | String Concatenation |
| * | Multiplication |
| / | Division |
| % | Modulo or Remainder |
| + | Addition |
| - | Subtraction |
| <<, >> | Bit shifts |
| &, | | Binary AND, OR |
| <, <=, =>, > | Greater-than, Less-than |
| =, == | Equal |
| !=, <> | Not Equal |
| AND, OR | Logical AND, OR |
The following are some of SQLite binary expression examples.
sqlite> SELECT * FROM emp_master WHERE salary > 10000;
emp_id first_name last_name salary
---------- ---------- ---------- ----------
2 Shweta Jariwala 19200
3 Vinay Jariwala 35000
sqlite> SELECT * FROM emp_master WHERE salary = 10000;
emp_id first_name last_name salary
---------- ---------- ---------- ----------
1 Honey Patel 10000
sqlite> SELECT * FROM emp_master WHERE salary == 10000;
emp_id first_name last_name salary
---------- ---------- ---------- ----------
1 Honey Patel 10000
sqlite> SELECT * FROM emp_master WHERE salary<>10000;
emp_id first_name last_name salary
---------- ---------- ---------- ----------
2 Shweta Jariwala 19200
3 Vinay Jariwala 35000
sqlite> SELECT * FROM emp_master WHERE emp_id >1 and salary > 24000;
emp_id first_name last_name salary
---------- ---------- ---------- ----------
3 Vinay Jariwala 35000
sqlite> UPDATE emp_master set salary = salary + 100 WHERE emp_id = 1;
sqlite> SELECT * FROM emp_master where emp_id = 1;
emp_id first_name last_name salary
---------- ---------- ---------- ----------
1 Honey Patel 10100
In SQLite, unary expressions or operators are the simplest types of expression operator and it will take a single parameter expression. In all cases, if the parameter expression is NULL, then the operator will also return NULL.
Following is the syntax of SQLite Unary Expressions.
unary-operator expr
The following are the unary expression operators support in SQLite.
| Operator | Name of Operator |
|---|---|
| - | Sign negation |
| + | Positive sign |
| ~ | Bit inversion |
| NOT | Logic inversion |
The following are some of the SQLite unary expression examples.
sqlite> SELECT (-6+9) as 'Unary Expression';
Unary Expression
----------------
3
sqlite> SELECT ~9;
~9
----------
-10
sqlite> SELECT * FROM emp_master WHERE salary IS NOT 10000;
emp_id first_name last_name salary
---------- ---------- ---------- ----------
1 Honey Patel 10100
2 Shweta Jariwala 19300
3 Vinay Jariwala 35100
In SQLite, we have other types of expressions also which are based on what type of result that the expression generates i.e. Boolean, number, etc.
In SQLite Boolean expression is an expression in a programming language that produces a Boolean value when evaluated, i.e. one of true or false.
The following are the operators which support in SQLite unary expression operators.
| Operator | Name of Operator |
|---|---|
| < | Less than |
| <= | Less than equal to |
| > | Greater than |
| >= | Greater than equal to |
| = | Equal to |
| <>, != | Not equal to |
The following are examples of using SQLite Boolean expression.
sqlite> SELECT * FROM emp_master WHERE salary = 10000;
emp_id first_name last_name salary
---------- ---------- ---------- ----------
1 Honey Patel 10000
sqlite> SELECT*FROM emp_master WHERE salary == 10000;
emp_id first_name last_name salary
---------- ---------- ---------- ----------
1 Honey Patel 10000
sqlite> SELECT * FROM emp_master WHERE salary <> 10000;
emp_id first_name last_name salary
---------- ---------- ---------- ----------
2 Shweta Jariwala 19200
3 Vinay Jariwala 35000
sqlite> SELECT * FROM emp_master WHERE emp_id > 1 and salary > 24000;
emp_id first_name last_name salary
---------- ---------- ---------- ----------
3 Vinay Jariwala 35000
In SQLite, Numeric Expressions return a single numeric value instead of an entire row and usually perform calculations.
The following are examples of using SQLite Numeric Expressions.
sqlite> SELECT 6 + 4;
6+4
----------
10
sqlite> SELECT 58/4;
58/4
----------
14
sqlite> SELECT ((8*5) - (6*4));
((8*5)-(6*4))
--------------
16
Each of the examples above returns a numeric value. SQLite also offers several built-in functions to perform the collection of aggregate data calculations against a table or a specific table column like avg(), count(), sum(), etc.
The following examples illustrate how we can use SQLite aggregate function to build a numeric expression against the table.
sqlite> SELECT count(*) FROM emp_master;
count(*)
----------
3
sqlite> SELECT sum(salary) FROM emp_master;
sum(salary)
-----------
64200
sqlite> SELECT avg(salary) FROM emp_master;
avg(salary)
-----------
21400.0
As the name suggests, SQLite Date Expressions return date/time values. These expressions are used to modify system date-time value.
The following are the examples of using SQLite Date expression.
sqlite> SELECT CURRENT_TIMESTAMP;
CURRENT_TIMESTAMP
-------------------
2016-07-29 07:32:26
sqlite> SELECT date();
date()
----------
2016-07-29
sqlite> SELECT datetime();
datetime()
-------------------
2016-07-29 08:09:14
This is how we can use SQLite Expressions in queries based on our requirements.