Filtering

What is filtering?

Filtering is the process of selecting rows based on conditions. For example, you might want to select all rows where the value in a column is greater than 10.

The WHERE function is used to filter rows based on conditions. The WHERE function takes a condition as an argument and returns only the rows that satisfy the condition.

Syntax

 SELECT *
FROM table_name
WHERE condition; 

The condition is a logical expression that evaluates to true or false. If the condition evaluates to true, the row is included in the result set; if the condition evaluates to false, the row is excluded from the result set.

Filtering numeric values

We’ll start by filtering rows based on numeric values. We’ll use the following operators to filter rows based on conditions:

Greater than

We use the > operator to filter rows where the value in a column is greater than a specified value.

Syntax

 SELECT *
FROM table_name
WHERE column_name > value; 

Example

We have a table called students with the following data:

 SELECT *
FROM students
WHERE age > 21; 

This query will return all students who are older than 21.

Output

Exercise

4.1 Run the following query to select all students who older than 21

4.2 Write a query to select students who are older than 20

Greater than or equal to

We use the >= operator to filter rows where the value in a column is greater than or equal to a specified value.

Syntax

 SELECT *
FROM table_name
WHERE column_name >= value; 

Example

 SELECT *
FROM students
WHERE age >= 21; 

This query will return all students who are 21 or older.

Output

Exercise

4.3 Run the following query to select all students who are 21 or older

4.4 Write a query to select students who are 20 or older

Less than

Syntax

 SELECT *
FROM table_name
WHERE column_name < value; 

Example

 SELECT *
FROM students
WHERE age < 21; 

This query will return all students who are younger than 21.

Output

Exercise

4.5 Run the following query to select all students who are younger than 21

4.6 Write a query to select students who are younger than 22

Less than or equal to

Syntax

 SELECT *
FROM table_name
WHERE column_name <= value; 

Example

 SELECT *
FROM students
WHERE age <= 21; 

This query will return all students who are 21 or younger.

Output

Exercise

4.7 Run the following query to select all students who are 21 or younger

4.8 Write a query to select students who are 22 or younger

Equal to

Syntax

 SELECT *
FROM table_name
WHERE column_name = value; 

Example

 SELECT *
FROM students
WHERE age = 21; 

This query will return all students who are 21 years old.

Output

Exercise

4.9 Run the following query to select all students who are 21 years old

4.10 Write a query to select students who are 22 years old

Filtering string values

We can filter rows based on string values. We’ll use the following operators to filter rows based on conditions:

Equal to

Syntax

 SELECT *
FROM table_name
WHERE column_name = 'value'; 

Example

 SELECT *
FROM students
WHERE major = 'Mathematics'; 

This query will return all students who are majoring in Mathematics.

Output

Exercise

4.11 Run the following query to select all students who are majoring in Mathematics

4.12 Write a query to select students who are majoring in Biology

Not equal to

Syntax

 SELECT *
FROM table_name
WHERE column_name != 'value'; 

Example

 SELECT *
FROM students
WHERE major != 'Mathematics'; 

This query will return all students who are not majoring in Mathematics.

Output

Exercise

4.13 Run the following query to select all students who are NOT majoring in Mathematics

4.14 Write a query to select students who are NOT majoring in Biology

LIKE

The LIKE operator is used to match a pattern. The % symbol is used as a wildcard character to match any sequence of characters.

Syntax

 SELECT *
FROM table_name
WHERE column_name LIKE 'pattern'; 

Example

 SELECT *
FROM students
WHERE major LIKE 'Math%'; 

This query will return all students who are majoring in a field that starts with “Math”.

Output

Exercise

4.15 Run the following query to select all students who are majoring in a field that starts with “Math”

4.16 Write a query to select students who are majoring in a field that starts with “Bio”

IN and NOT IN

The IN and NOT IN operators are used to match any value in a list of values.

Syntax

 SELECT *
FROM table_name
WHERE column_name IN ('value1', 'value2', ...); 

 SELECT *
FROM table_name
WHERE column_name NOT IN ('value1', 'value2', ...); 

Example

IN

 SELECT *
FROM students
WHERE major IN ('Mathematics', 'Biology'); 

This query will return all students who are majoring in Mathematics or Biology.

Output

NOT IN

 SELECT *
FROM students
WHERE major NOT IN ('Mathematics', 'Biology'); 

This query will return all students who are not majoring in Mathematics or Biology.

Output

Exercise

4.17 Run the following query to select all students who are majoring in Mathematics or Biology

4.18 Write a query to select students who are NOT majoring in Computer Science or Mathematics