Different types of Python SQLite Clauses
Where Clause in Python SQLite
The WHERE clause in SQLite is used to provide a criterion while obtaining data from one or more tables.
It returns the specified value from the database if the provided condition is fulfilled, which indicates true. You'll need to utilize the WHERE clause to filter the data and retrieve only the ones you need.
The WHERE clause is utilized not just in SELECT statements, but also in UPDATE, DELETE statements and other statements that will be discussed in later chapters.
Syntax:
SELECT column1, column2, columnN
FROM table_name
WHERE [condition]
Video : https://youtu.be/z66E6QHeQBs
And & OR Clause in Python SQLite
Multiple criteria are compiled using SQLite AND & OR operators to limit down the selected data in an SQLite statement. Conjunctive operators refer to these two operators.
These operators allow you to do several comparisons in the same SQLite statement using various operators.
-
AND Operator
In an SQLite statement's WHERE clause, the AND operator permits many conditions to appear. When utilizing the AND operator, the entire condition is deemed to be true if all of the conditions are true. For example, only when both condition1 and condition2 are true will [condition1] AND [condition2] be true.
Syntax:
SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];
-
OR Operator
In an SQLite statement's WHERE clause, the OR operator is also used to combine numerous criteria. When the OR operator is used, the entire condition is deemed to be true if at least one of the conditions is true. If either condition1 or condition2 is true, [condition1] OR [condition2] will be true.
Syntax:
SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]
Like Clause in Python SQLite
The LIKE operator in SQLite is used to compare text values to a pattern utilizing wildcards. The LIKE operator returns true, which is 1 if the search expression matches the pattern expression. When using the LIKE operator, there are two wildcards that can be utilized.
-
The percent sign (%)
-
The underscore (_)
The % sign can be used to signify zero, one, or several integers or characters. A single number or letter is represented by the underscore. These symbols can be mixed and matched.
Syntax:
Following is the basic syntax of % and _.
SELECT FROM table_name
WHERE column LIKE 'XXXX%'
or
SELECT FROM table_name
WHERE column LIKE '%XXXX%'
or
SELECT FROM table_name
WHERE column LIKE 'XXXX_'
or
SELECT FROM table_name
WHERE column LIKE '_XXXX'
or
SELECT FROM table_name
WHERE column LIKE '_XXXX_'
Limit Clause in Python SQLite
The LIMIT clause in SQLite is used to limit the amount of data returned by a SELECT query.
Syntax:
With LIMIT clause.
SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]
With OFFSET clause.
SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows] OFFSET [row num]
Order By Clause in Python SQLite
The ORDER BY clause in SQLite is used to sort data in ascending or descending order by one or more columns.
Syntax:
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
Group By Clause in Python SQLite
The ORDER BY clause in SQLite is used to sort data by one or more columns in ascending or descending order.
Syntax:
SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN
Python SQLite Having Clause
You can use the HAVING clause to establish conditions that determine which group results appear in the final results.
The WHERE clause applies conditions to the columns that have been chosen, but the HAVING clause applies conditions to the groups that have been generated by the GROUP BY clause.
Syntax:
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
Python SQLite Distinct Clause
The DISTINCT keyword in SQLite is used in combination with the SELECT command to remove all duplicate records and get only unique records.
There can be times when a table has several duplicate records. It makes more sense to fetch just unique records rather than duplicate records when retrieving such records.
Syntax:
SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]
QUIZ!
