Clauses are used to define any specific condition with the commands like select, insert, update, delete, alter.<\/p>\n
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.<\/p>\n
Syntax<\/span><\/strong><\/p>\n Usage:<\/span><\/strong><\/p>\n These clause is used to retrieve records or you can say multiple records with the specified condition define which these two clauses.<\/p>\n AND :<\/strong><\/span><\/p>\n With ‘AND’ clause we can define multiple conditions but it \u00a0turns true if all specified conditions with it stands true. Like we define a [condition1] and [condition2], will be true if both are\u00a0condition1 , \u00a0condition2 are true.<\/p>\n Syntax<\/span><\/strong><\/p>\n Example: \u00a0 EMPLOYEE TABLE<\/span><\/strong><\/p>\n Usage:<\/span><\/strong><\/p>\n OR:<\/strong><\/span><\/p>\n 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\u00a0condition1 , \u00a0condition2 is true.<\/p>\n Syntax<\/span><\/strong><\/p>\n Usage:<\/span><\/strong><\/p>\n Like clause is used to match values using wildcards. The wildcards are the special character which have some meaning for it. Usage:<\/span><\/strong> In following will return records as starting with ‘R’ followed by any letter after that ‘v’ than any number of letters.<\/p>\n 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. Usage:<\/span><\/strong> In following will return records as starting with ‘R’ \u00a0followed by any letter after that ‘v’ than any number of letters.<\/p>\n Important Note:<\/span> <\/strong>The basic difference between Like and Glob is that Glob is case-sensitive but like operator \u00a0is not further Glob uses unix wildcards.<\/p>\n In SQLite limit clause basically limits the output, it only display as much rows as defined in it, let’s see the example.<\/p>\n Usage:<\/span><\/strong> OFFSET CLAUSE<\/strong><\/span><\/p>\n Using Offset with Limit clause will set a offset that after the defined offset the records will display.<\/p>\n Usage:<\/span><\/strong> 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.<\/p>\n Usage:<\/span><\/strong> In below example we used order by clause which is sorting records in descending order.<\/p>\n 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.<\/p>\n Usage:<\/span><\/strong> Having clause is used to specify a condition with the group by. Distinct as itself explains it display unique data. Similarly using ALL<\/strong><\/span>, will display all records:<\/p>\n 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. … Continue reading List Of All Clauses In SQLite For Defining Specific Condition<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-80","post","type-post","status-publish","format-standard","hentry","category-archive"],"acf":[],"psp_head":"select column1, column2, columnN \r\nfrom \r\nwhere <condition>;<\/pre>\n
sqlite> select * from t1;\r\nNumber Name<\/strong>\r\n --- ---\r\n 101 Ram\r\n 102 Ram Kumar\r\n 103 Krishan\r\n 104 Ramesh\r\nsqlite> select name from t1 where num=102;\r\nName<\/strong>\r\n----\r\nRam Kumar\r\nsqlite>\r\n<\/pre>\n
AND\/ OR Clause<\/strong><\/h4>\n
Where [condition1] AND [condition2] AND [condition3] ... AND [conditionN]\r\n<\/pre>\n
Eno Ename Esalary Eaddress<\/strong>\r\n--- ----- ------- ------\r\n101 Rajat 50000.0 Karnal\r\n102 Ranjit 56000.0 Karnal\r\n103 Rahul 66000.0 Karnal\r\n104 Ravi 66000.0 Kaithal\r\n105 Ravina 75000.0 Kaithal\r\n<\/pre>\n
sqlite> select Eno from Employee where Eaddress='Karnal';\r\nEno<\/strong>\r\n---\r\n101\r\n102\r\n103\r\n<\/pre>\n
Where [condition1] AND [condition2] OR [condition3] ... OR [conditionN]<\/pre>\n
sqlite> select Eno, Ename from Employee where Eaddress='Karnal' or Esalary=75000;\r\nEno Ename<\/strong>\r\n--- ----- \r\n101 Rajat\r\n102 Ranjit\r\n103 Rahul\r\n105 Ravina\r\n<\/pre>\n
LIKE CLAUSE<\/strong><\/h4>\n
\n— Percentage “%”<\/strong><\/span> – This represents one or more characters.
\n— Underscore “_”<\/span><\/strong> – This represents single character.<\/p>\n
\nIn following example will return records with Ename ending with alphabet ‘t’.<\/p>\nsqlite> select Esalary from Employee where ename like '%t';\r\nEsalary<\/strong> \r\n------\r\n50000.0\r\n56000.0\r\n<\/pre>\n
sqlite> select Eno ,Ename from Employee where Ename like 'R_v%';\r\nEno Ename<\/strong>\r\n--- ----\r\n104 Ravi\r\n105 Ravina\r\n<\/pre>\n
GLOB CLAUSE<\/strong><\/h4>\n
\n— Asterisk “*”<\/span><\/strong> – This represents one or more characters.
\n— QuestionMark “?”<\/span><\/strong> – This represents single character.<\/p>\n
\nIn following example will return records with Ename ending with alphabet ‘t’.<\/p>\nsqlite> select Esalary from Employee where ename glob '*t';\r\nEsalary<\/strong> \r\n------\r\n50000.0\r\n56000.0\r\n<\/pre>\n
sqlite> select Eno ,Ename from Employee where Ename glob 'R?v*';\r\nEno Ename<\/strong>\r\n--- ----\r\n104 Ravi\r\n105 Ravina\r\n<\/pre>\n
LIMIT & OFFSET CLAUSE<\/strong><\/h4>\n
\nIn the below example if you see the limit is set to “3”, then it will display first 3 records.<\/p>\nsqlite> select * from Employee limit 3 ;\r\nEno Ename Esalary Eaddress<\/strong>\r\n--- ----- ------- -------\r\n101 Rajat 50000.0 Karnal\r\n102 Ranjit 56000.0 Karnal\r\n103 Rahul 66000.0 Karnal\r\n<\/pre>\n
\nIn the below query the limit clause will display 2 records as explained above and the offset will leave defined number of records in it.<\/p>\nsqlite> select * from employee limit 2 offset 2;\r\nEno Ename Esalary Eaddress<\/strong>\r\n--- ----- ------- --------\r\n103 Rahul 66000.0 Karnal\r\n104 Ravi 66000.0 Kaithal\r\n<\/pre>\n
ORDER BY CLAUSE<\/strong><\/h4>\n
\nIn the below example order by clause is used which is ordering the records by Eaddress.<\/p>\nsqlite> select Ename, Esalary from employee order by Eaddress ASC;\r\nEname Esalary<\/strong>\r\n----- -------\r\nRavi 66000.0\r\nRavina 75000.0\r\nRajat 50000.0\r\nRanjit 56000.0\r\nRahul 66000.0\r\n<\/pre>\n
sqlite> select Ename, Esalary from employee order by Eaddress DESC;\r\nEname Esalary<\/strong>\r\n----- -------\r\nRajat 50000.0\r\nRanjit 56000.0\r\nRahul 66000.0\r\nRavi 66000.0\r\nRavina 75000.0<\/pre>\n
GROUP BY<\/strong><\/h4>\n
\nIn 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.<\/p>\nsqlite> select Eaddress, count(Eaddress) as Number_of_Employees from employee group by Eaddress order by Eno;\r\nEaddress Number_of_Employees<\/strong>\r\n------- -------------------\r\nKarnal 3\r\nKaithal 2\r\n<\/pre>\n
HAVING CLAUSE<\/strong><\/h4>\n
\nUsage:<\/span><\/strong>
\nIn the following example we just count the no of employees from kaithal by grouping them.<\/p>\nsqlite> select Eaddress, count(Eaddress) as Number_of_Employees from employee group by Eaddress having Eaddress='Kaithal';\r\nEaddress Number_of_Employees<\/strong>\r\n-------- -------------------\r\nKaithal 2\r\n<\/pre>\n
DISTINCT \/ ALL<\/strong><\/h4>\n
\nUsage:<\/span><\/strong>
\nIn below query distinct address records are fetched.<\/p>\nsqlite> select Distinct Eaddress from employee;\r\nEaddress<\/strong>\r\n------\r\nKarnal\r\nKaithal\r\n<\/pre>\n
sqlite> select All Eaddress from employee;\r\nEaddress<\/strong>\r\n--------\r\nKarnal\r\nKarnal\r\nKarnal\r\nKaithal\r\nKaithal<\/pre>\n","protected":false},"excerpt":{"rendered":"