SQLite - SPLessons

SQLite Clauses

Chapter 11

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

SQLite Clauses

SQLite Clauses

shape Clause

SQLite Clauses are defined as a set of rules, that make one to understand the concepts of SQLite command in Database.

SQLite Clauses are very similar to SQL clause, except some functional operations.

Where Clause

shape Description

To specify conditions in SQLite command use where clause. i.e, Select "*" from the table name will show all the rows from the table and Where clause will specify the condition for the result set in the table.

shape Syntax

Select <column_name1>,<column_name2>,…….<column_nameN> from <table_name> where <condition> ;

column_name => All the columns in the table

table_name => Any accurate table.

condition => condition is a logic to get a specific record.

shape Examples

By viewing the below example, the concept of where clause easily understands.

sqlite> create table employee25(emp_id int,e_name varchar2(20),dept_no int,salar
y int,age int,commission varchar2(20));
sqlite> insert into employee25 values(1,'Mike',10,12000,25,'Null');
sqlite> insert into employee25 values(2,'James',20,13000,26,'Null');
sqlite> insert into employee25 values(3,'Nike',30,14000,27,'Not Null');
sqlite> insert into employee25 values(4,'Jack',40,15000,28,'Null');
sqlite> insert into employee25 values(5,'Haddiee',50,16000,29,'Null');
sqlite> select * from employee25;
1|Mike|10|12000|25|Null
2|James|20|13000|26|Null
3|Nike|30|14000|27|Not Null
4|Jack|40|15000|28|Null
5|Haddiee|50|16000|29|Null
sqlite> select * from employee25 where age>=28;
4|Jack|40|15000|28|Null
5|Haddiee|50|16000|29|Null

Above example tells that, when where clause operation is performed on a column name like age>=28, then it automatically displays the entire details whose age>=28.

Order by Clause

shape Description

To arrange data of a table either in ascending order or descending order based on single column use order by clause i.e, Select “*” from the table name will show all the rows from the table and  order by clause is used to arrange the specific column in ascending or descending order.

shape Syntax

Select <column_name1>,<column_name2>,<column_name3>………<column_nameN> from <table_name> ORDER BY <column_name> DESC;

table_name => Any accurate table.

column_name => The condition that one can perform on a column by using Order by clause.

shape Examples

By viewing the below example, the concept of Order by clause easily understands.

sqlite> SELECT * FROM EMPLOYEE25;
1|Mike|10|12000|25|Null
2|James|20|13000|26|Null
3|Nike|30|14000|27|Not Null
4|Jack|40|15000|28|Null
5|Haddiee|50|16000|29|Null
sqlite> select * from employee25 order by salary ASC;
1|Mike|10|12000|25|Null
2|James|20|13000|26|Null
3|Nike|30|14000|27|Not Null
4|Jack|40|15000|28|Null
5|Haddiee|50|16000|29|Null
sqlite> SELECT * FROM EMPLOYEE25 ORDER BY SALARY DESC;
5|Haddiee|50|16000|29|Null
4|Jack|40|15000|28|Null
3|Nike|30|14000|27|Not Null
2|James|20|13000|26|Null
1|Mike|10|12000|25|Null

The above example tells that, when order by clause operation is performed on a column name like salary, then it automatically displays the entire details of employees in descending order and ascending order.

Groupby Clause

shape Description

To divide the data of a table into groups based on single column use group by clause.

shape Syntax

Select <column_name>,Group <function1>,Group <function2> from <table_name> Group by <column_name>

table_name > Any accurate table.

column_name => The condition that one can perform on a column by using Group by clause.

shape Examples

By viewing the below example, the concept of group by clause easily understands.

sqlite> select * from employee25;
1|Mike|10|12000|25|Null
2|James|20|13000|26|Null
3|Nike|30|14000|27|Not Null
4|Jack|40|15000|28|Null
5|Haddiee|50|16000|29|Null
sqlite> select e_name,max(salary)from employee25 group by e_name;
Haddiee|16000
Jack|15000
James|13000
Mike|12000
Nike|14000

The above example tells that, when group by clause operation is performed on a column name like e_name and salary, then it automatically displays the entire details of employees based on maximum salary of employee.

Having Clause

shape Description

To specify a condition with group by clause use having clause.

shape Syntax

Select <column_name>,group <function1>,group <function2> from <table_name> group by column name having <condition>;

table_name => Any accurate table.

condition => condition is a logic to get a specific record.

shape Examples

By viewing the below example, the concept of having clause easily understands.

sqlite> select * from employee25;
1|Mike|10|12000|25|Null
2|James|20|13000|26|Null
3|Nike|30|14000|27|Not Null
4|Jack|40|15000|28|Null
5|Haddiee|50|16000|29|Null
sqlite> select dept_no,max(salary) from employee25 group by dept_no having max(salary)>15000;
50|16000

Above example tells that, when having clause operation is performed on a column name like dept_no and salary having salary greater that 15000, then it automatically displays the  details of employees based on maximum salary.

Distinct Clause

shape Description

To eliminate duplicate values in a column use distinct clause.

shape Syntax

Select DISTINCT <column_ name1><column_name2>…..<column_nameN) from <table_name>;

table_name => Any accurate table.

column_name => The condition that one can perform on a column by using distinct clause.

shape Examples

By viewing the below example, the concept of distinct clause easily understands.

sqlite> select distinct dept_no from employee25 where salary=20000;
20

Above example tells that, when distinct clause operation is performed on a column dept_no, then it displays the values of dept_no=20 who is having maximum salary, and remove the another dept_no=20 values as it contains less salary. 

Glob Clause

shape Description

Beside LIKE operator, GLOB is case delicate and it follows the syntax of UNIX for indicating. The GLOB operator in SQLite Clauses is utilized to coordinate just content qualities against an example utilizing trump cards. In the event that the hunt expression can be coordinated to the example expression, the GLOB operator will pass it as valid, which is 1. The GLOB operators are of two types.They are:

The sign asterisk defines zero or numerous numbers or characters. The ? sign defines a solitary character or number.

Statement Description
WHERE SALARY GLOB ‘*200*’ Searches any qualities that accepts 200 in any location.
WHERE SALARY GLOB ‘400*’ Searches any qualities that begins with 400.
WHERE SALARY GLOB ‘4??’ Searches any qualities that begin with 4 and contains not less than 3 characters long.
WHERE SALARY GLOB ‘?00*’ Search any qualities that have 00 in the second and third locations.
WHERE SALARY GLOB ‘?3*2’ Searches any qualities that have a 3 in the second location and end with a 2.
WHERE SALARY GLOB ‘*3’ Search any qualities that end with 3
WHERE SALARY GLOB ‘4???6’ Search any qualities in a five-digit number that begin with 4 and end with 6.

shape Examples

By viewing the below example, the concept of SQLite Clauses distinct easily understands.

sqlite> select * from employee25;
1|Mike|10|12000|25|Null
2|James|20|13000|26|Null
3|Nike|30|14000|27|Not Null
4|Jack|40|15000|28|Null
5|Haddiee|50|16000|29|Null
6|David|30|18000|30|
7|Annie|20|20000|30|
sqlite> select * from employee25 where age GLOB '*6';
2|James|20|13000|26|Null

Here in the above example entire employee values are displayed based on age whose age ends with 6. i.e.,’*6′.

Summary

shape Key Points

  • SQLite Clauses – Is set of rule,that make one to understand the concepts of SQLite commands in the Database.
  • Where clause – Is used to specify condition in select statement.
  • Order by clause – Is utilized to arrange the table column in ascending or descending order.
  • Group by clause – Is used to divide the data of a table into groups.
  • Having clause – Is used to put condition in group by clause.
  • distant clause – Is used to eliminate duplicate values.
  • GLOB clause – Is used to find the most extreme values.