SQLite Expressions

  Total Views : 44
  Comments : 0
 
  Keywords : SQLite query expressions with example, SQLite binary and unary expressions with examples, SQLite boolean & number expressions with example, How to use sqlite expressions with example

Here we will learn sqlite expressions with example and different type of sqlite expressions (binary expressions, unary expressions, boolean expressions, numeric expressions, date expressions) with examples.

SQLite Expressions

In SQLite expression is a combination of one or more explicit value, constants, variables, operators and functions that the SQLite interprets and computes to produce value.

 

Like other programming language, 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 in 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:

SQLite Binary Expressions

The SQLite Binary operators take two values as parameter expressions and combine or compare them in some way that produces an output value.

Syntax of SQLite Binary Expressions

Following is the syntax of SQLite Binary Expressions.

 

left-expr binary-operator right-expr

Following are the different type of binary expression operators available in SQLite.

 

OperatorName 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

SQLite Binary Expression Examples

Following are the some of SQLite binary expression examples.

 

sqliteSELECT FROM emp_master WHERE salary 10000;

 

emp_id      first_name  last_name   salary

----------  ----------  ----------  ----------

2           Shweta      Jariwala    19200

3           Vinay       Jariwala    35000

 

sqliteSELECT FROM emp_master WHERE salary 10000;

 

emp_id      first_name  last_name   salary

----------  ----------  ----------  ----------

1           Honey       Patel       10000

 

sqliteSELECT FROM emp_master WHERE salary == 10000;

 

emp_id      first_name  last_name   salary

----------  ----------  ----------  ----------

1           Honey       Patel       10000

 

sqliteSELECT FROM emp_master WHERE salary<>10000;

 

emp_id      first_name  last_name   salary

----------  ----------  ----------  ----------

2           Shweta      Jariwala    19200

3           Vinay       Jariwala    35000

 

sqliteSELECT FROM emp_master WHERE emp_id >1 and salary 24000;

 

emp_id      first_name  last_name   salary

----------  ----------  ----------  ----------

3           Vinay       Jariwala    35000

 

sqliteUPDATE emp_master set salary salary + 100 WHERE emp_id 1;

 

sqliteSELECT FROM emp_master where emp_id 1;

 

emp_id      first_name  last_name   salary

----------  ----------  ----------  ----------

1           Honey       Patel       10100

SQLite Unary Expressions

In SQLite unary expressions or operators are the simplest type 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.

Syntax of SQLite Unary Expressions

Following is the syntax of SQLite Unary Expressions.

 

unary-operator expr

Following are the unary expression operators support in SQLite.

 

OperatorName of Operator
- Sign negation
+ Positive sign
~ Bit inversion
NOT Logic inversion

SQLite Unary Expression Examples

Following are the some of SQLite unary expression examples.

 

sqliteSELECT (-6+9as 'Unary Expression';

 

Unary Expression

----------------

3

 

sqliteSELECT ~9;

 

~9

----------

-10

 

sqliteSELECT 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.

SQLite Boolean Expressions

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.

 

Following are the operators which supports in SQLite unary expression operators.

 

OperatorName of Operator
< Less than
<= Less than equal to
> Greater than
>= Greater than equal to
= Equal to
<>, != Not equal to

SQLite Boolean Expressions Example

Following are the examples of using SQLite Boolean expression.

 

sqliteSELECT FROM emp_master WHERE salary 10000;

 

emp_id      first_name  last_name   salary

----------  ----------  ----------  ----------

1           Honey       Patel       10000

 

sqliteSELECT*FROM emp_master WHERE salary == 10000;

 

emp_id      first_name  last_name   salary

----------  ----------  ----------  ----------

1           Honey       Patel       10000

 

sqliteSELECT FROM emp_master WHERE salary <> 10000;

 

emp_id      first_name  last_name   salary

----------  ----------  ----------  ----------

2           Shweta      Jariwala    19200

3           Vinay       Jariwala    35000

 

sqliteSELECT FROM emp_master WHERE emp_id 1 and salary 24000;

 

emp_id      first_name  last_name   salary

----------  ----------  ----------  ----------

3           Vinay       Jariwala    35000

SQLite Numeric Expression

In SQLite Numeric Expressions return a single numeric value instead of an entire row and usually perform calculations.

SQLite Numeric Expression Examples

Following are examples of using SQLite Numeric Expressions.

 

sqliteSELECT4;

 

6+4

----------

10

 

sqliteSELECT 58/4;

 

58/4

----------

14

 

sqliteSELECT ((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.

 

Following examples illustrate how we can use SQLite aggregate function to build a numeric expression against table.

 

sqliteSELECT count(*) FROM emp_master;

 

count(*)

----------

3

 

sqliteSELECT sum(salaryFROM emp_master;

 

sum(salary)

-----------

64200

 

sqliteSELECT avg(salaryFROM emp_master;

 

avg(salary)

-----------

21400.0

SQLite Date Expression

As the name suggests, SQLite Date Expressions return date/time values. These expressions are used to modify system date time value.

 

  • Date() - Returns the current date in YYYY-MM-DD format.
  • CURRENT_TIMESTAMP - Returns the current timestamp.
  • Datetime()- Returns the current Date and time.

SQLite Date Expression Examples

Following are the examples of using SQLite Date expression.

 

sqliteSELECT CURRENT_TIMESTAMP;

 

CURRENT_TIMESTAMP

-------------------

2016-07-29 07:32:26

 

sqliteSELECT date();

 

date()

----------

2016-07-29

 

sqliteSELECT datetime();

 

datetime()

-------------------

2016-07-29 08:09:14

This is how we can use SQLite Expressions in queries based on our requirements.

 

PREVIOUS

SQLite Operators
 

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User