List Of All Clauses In SQLite For Defining Specific Condition

Clauses are used to define any specific condition with the commands like select, insert, update, delete, alter.


WHERE CLAUSE IN SQLITE :

Where clause is used to filter out the result, for that define a condition with where clause.If the specified condition is true it return the records. It is used with update, select, alter..etc.

Syntax

select column1, column2, columnN 
from  
where <condition>;

Usage:

sqlite> select * from t1;
Number    Name
 ---      ---
 101      Ram
 102      Ram Kumar
 103      Krishan
 104      Ramesh
sqlite> select name from t1 where num=102;
Name
----
Ram Kumar
sqlite>

AND/ OR Clause

These clause is used to retrieve records or you can say multiple records with the specified condition define which these two clauses.

AND :

With ‘AND’ clause we can define multiple conditions but it  turns true if all specified conditions with it stands true. Like we define a [condition1] and [condition2], will be true if both are condition1 ,  condition2 are true.

Syntax

Where [condition1] AND [condition2] AND [condition3] ... AND [conditionN]

Example:   EMPLOYEE TABLE

Eno     Ename   Esalary   Eaddress
---     -----   -------   ------
101     Rajat   50000.0   Karnal
102     Ranjit  56000.0   Karnal
103     Rahul   66000.0   Karnal
104     Ravi    66000.0   Kaithal
105     Ravina  75000.0   Kaithal

Usage:

sqlite> select Eno from Employee where Eaddress='Karnal';
Eno
---
101
102
103

OR:

With ‘OR’ clause we can define multiple conditions but it returns true if any specified conditions with it is true. Like we define a [condition1] and [condition2], will be true if any of the condition either condition1 ,  condition2 is true.

Syntax

Where [condition1] AND [condition2] OR [condition3] ... OR [conditionN]

Usage:

sqlite> select Eno, Ename from Employee where Eaddress='Karnal' or Esalary=75000;
Eno     Ename
---     ----- 
101     Rajat
102     Ranjit
103     Rahul
105     Ravina

LIKE CLAUSE

Like clause is used to match values using wildcards. The wildcards are the special character which have some meaning for it.
— Percentage “%” – This represents one or more characters.
— Underscore “_” – This represents single character.

Usage:
In following example will return records with Ename ending with alphabet ‘t’.

sqlite> select Esalary from Employee where ename like '%t';
Esalary 
------
50000.0
56000.0

In following will return records as starting with ‘R’ followed by any letter after that ‘v’ than any number of letters.

sqlite> select Eno ,Ename from Employee where Ename like 'R_v%';
Eno     Ename
---     ----
104     Ravi
105     Ravina

GLOB CLAUSE

Glob clause is used to match text values against pattern using unix wildcards. Glob clause is case_ sensitive unlike like clause. The wildcards are the special character which have some meaning for it.
— Asterisk “*” – This represents one or more characters.
— QuestionMark “?” – This represents single character.

Usage:
In following example will return records with Ename ending with alphabet ‘t’.

sqlite> select Esalary from Employee where ename glob '*t';
Esalary 
------
50000.0
56000.0

In following will return records as starting with ‘R’  followed by any letter after that ‘v’ than any number of letters.

sqlite> select Eno ,Ename from Employee where Ename glob 'R?v*';
Eno     Ename
---     ----
104     Ravi
105     Ravina

Important Note: The basic difference between Like and Glob is that Glob is case-sensitive but like operator  is not further Glob uses unix wildcards.

LIMIT & OFFSET CLAUSE

In SQLite limit clause basically limits the output, it only display as much rows as defined in it, let’s see the example.

Usage:
In the below example if you see the limit is set to “3”, then it will display first 3 records.

sqlite> select *  from Employee limit 3 ;
Eno     Ename   Esalary   Eaddress
---     -----   -------   -------
101     Rajat   50000.0   Karnal
102     Ranjit  56000.0   Karnal
103     Rahul   66000.0   Karnal

OFFSET CLAUSE

Using Offset with Limit clause will set a offset that after the defined offset the records will display.

Usage:
In the below query the limit clause will display 2 records as explained above and the offset will leave defined number of records in it.

sqlite> select * from employee limit 2 offset 2;
Eno     Ename   Esalary   Eaddress
---     -----   -------   --------
103     Rahul   66000.0   Karnal
104     Ravi    66000.0   Kaithal

ORDER BY CLAUSE

Order By Clause as the name symbolized it sorts the data is specified order i.e either ascending or descending. By default the order is ascending. You can use multiple columns with order by clause.

Usage:
In the below example order by clause is used which is ordering the records by Eaddress.

sqlite> select Ename, Esalary from employee order by Eaddress ASC;
Ename   Esalary
-----   -------
Ravi    66000.0
Ravina  75000.0
Rajat   50000.0
Ranjit  56000.0
Rahul   66000.0

In below example we used order by clause which is sorting records in descending order.

sqlite> select Ename, Esalary from employee order by Eaddress DESC;
Ename   Esalary
-----   -------
Rajat   50000.0
Ranjit  56000.0
Rahul   66000.0
Ravi    66000.0
Ravina  75000.0

GROUP BY

Group By Clause is used to group put the similar records together. This clause is used with the select query and optionally you can use order by clause with it.

Usage:
In the below example we want to know the no of employees from same location. So we used group by with count function, further we add order by clause which is sorting records according to Eno.

sqlite> select Eaddress, count(Eaddress) as Number_of_Employees from employee group by Eaddress order by Eno;
Eaddress        Number_of_Employees
-------         -------------------
Karnal                 3
Kaithal                2

HAVING CLAUSE

Having clause is used to specify a condition with the group by.
Usage:
In the following example we just count the no of employees from kaithal by grouping them.

sqlite> select Eaddress, count(Eaddress) as Number_of_Employees from employee group by Eaddress having Eaddress='Kaithal';
Eaddress        Number_of_Employees
--------        -------------------
Kaithal                2

DISTINCT / ALL

Distinct as itself explains it display unique data.
Usage:
In below query distinct address records are fetched.

sqlite> select Distinct Eaddress from employee;
Eaddress
------
Karnal
Kaithal

Similarly using ALL, will display all records:

sqlite> select All Eaddress from employee;
Eaddress
--------
Karnal
Karnal
Karnal
Kaithal
Kaithal

Leave a Reply

Your email address will not be published. Required fields are marked *