Sqlite - Filter

You can filter your output so you can return only a few records that meet certain criteria.

• Introduction
• where
• and
• or
• in
• between
• like
• `is null
• Task: Sales People
• Task: Population
• Pending

Introduction

You already know the basic syntax of the select statement and how to retrieve columns from one table in your database, but very often you don’t need all records from a table.

The SQL where clause allows you to filter your results.

This clause introduces certain conditions, like:

• quantity < 100
• price between 100 and 500
• customer_name = 'John Smith'

Sample Database

The Sakila sample database is a fictitious database designed to represent a DVD rental store. The tables of the database include film, film_category, actor, customer, rental, payment and inventory among others.

Download sakila database file from this link: sakila.db

where

The select statement returns all rows from one or more columns in a table.

To retrieve rows that satisfy a specified condition, you use a where clause.

select <columns>
from <table>
where <condition>
order by <column>;

In this syntax,

• You place the where clause right after the from clause of the select statement.
• The where clause uses the condition to filter the rows returned from the select clause.
• The condition is a boolean expression that evaluates to true, false, or unknown.

The query returns only rows that satisfy the condition in the where clause.

In other words, the query will include only rows that cause the condition to evaluate to true in the result set.

SQLite evaluates the where clause after the from clause, but before the select and order by clause:

If you use column aliases in the select clause, you cannot use them in the where clause.

Besides the select statement, you can use the where clause in the update and delete statement to specify rows to update and delete.

To form the condition in the where clause, you use comparison and logical operators:

Tasks

Let’s practice with some examples of using the where clause.

We will use the customer table from the sample database for demonstration.

select first_name, last_name
from customer
where first_name = 'JAMIE'

select first_name, last_name
from customer
where first_name = 'JAMIE' and last_name = 'RICE'

select first_name, last_name
from customer
where last_name = 'RODRIGUEZ' or first_name = 'ADAM'

select first_name, last_name
from customer
where first_name IN ('ANN', 'ANNE', 'ANNIE')

select first_name, last_name
from customer
where first_name like 'ANN%'

select first_name as name, length(first_name) as name_length
from customer
where first_name like 'A%' and length(first_name) between 3 and 5
order by name_length

select first_name, last_name
from customer
where first_name like 'BRA%' and last_name != 'MOTLEY'

and

A boolean value can have one of three values: true, false, and null.

SQLite uses true and 1 to represent true false and 0 to represent false.

A boolean expression is an expression that evaluates to a boolean value.

For example, the expression 1 = 1 is a boolean expression that evaluates to true:

select 1 = 1 as result

The 1 in the output indicates the value of true.

The and operator is a logical operator that combines two boolean expressions.

The and operator:

• Returns true only if both expressions are true.
• It returns false if one of the expressions is false.
• Otherwise, it returns null.

The following table shows the results of the and operator when combining true, false, and null.

Note that the order of the expressions doesn’t matter, for example, both true and null and null and true will evaluate to null.

select true and null as result

In practice, you often use the and operator in a where clause to ensure that all specified expressions must be true for a row to be included in the result set.

Tasks

We’ll use the film table from the sample database.

select title, length, rental_rate
from film
where length > 180 and rental_rate < 1

or

The or operator:

• Returns true only if any of the expressions is true.
• It returns false if both expressions are false.
• Otherwise, it returns null.

The following table shows the results of the or operator when combining true, false, and null.

Note that the order of the expressions doesn’t matter, for example both false or null and null or false will evaluate to null.

In practice, you usually use the or operator in a where clause to ensure that either of the specified expressions must be true for a row to be included in the result set.

select title, rental_rate
from film
where rental_rate = 0.99 or rental_rate = 2.99

in

The in operator returns true if the value is equal to any value in the list such as value1 and value2.

The list of values can be a list of literal values, including numbers and strings.

Functionally, the in operator is equivalent to combining multiple boolean expressions with the or operators:

value = value1 or value = value2 or ...

Tasks

select film_id, title
from film
where film_id in (1,2,3)

select film_id, title
from film
where film_id = 1 or film_id = 2 or film_id =3

select first_name, last_name
from actor
where last_name in ('ALLEN', 'CHASE', 'DAVIS')
order by first_name

select payment_id, amount, strftime('%Y-%m-%d', payment_date) as date
from payment
where strftime('%Y-%M-%d', payment_date) in ('2005-02-15', '2005-02-16')

select film_id, title
from film
where film_id not in (1,2,3)

between

The between operator allows you to check if a value falls within a range of values.

The basic syntax of the between operator is as follows:

value between low and high;

If the value is greater than or equal to the low value and less than or equal to the high value, the between operator returns true; otherwise, it returns false.

You can rewrite the between operator by using the greater than or equal (>=) and less than or equal to ( <= ) operators and the logical and operator:

value >= low and value <= high

If you want to check if a value is outside a specific range, you can use the not between operator as follows:

value not between low and high

The following expression is equivalent to the expression that uses the not between operators:

value < low or value > high

Tasks

select payment_id, amount
from payment
where payment_id between 14503 and 14505

select payment_id, amount
from payment
where payment_id not between 14503 and 14505

select strftime('%Y-%m-%d', payment_date) as date, customer_id, payment_id, amount
from payment
where
    strftime('%Y-%m-%d', payment_date) between '2005-02-15' and '2007-02-20'
    and amount > 10
order by date

like

You can use the like operator to match the first names of customers with a string using the following query:

select first_name, last_name
from customer
where first_name like 'Jen%'

The expression consists of the first_name, the like operator and a literal string that contains a percent sign (%). The string 'Jen%' is called a pattern.

The query returns rows whose values in the first_name column begin with Jen and are followed by any sequence of characters. This technique is called pattern matching.

You construct a pattern by combining literal values with wildcard characters and using the like or not like operator to find the matches.

SQLite offers two wildcards:

• Percent sign (%) matches any sequence of zero or more characters.
• Underscore sign (_) matches any single character.

The like operator returns true if the value matches the pattern.

To negate the like operator, you use the not operator.

If the pattern does not contain any wildcard character, the like operator behaves like the equal (=) operator.

Tasks

select first_name, last_name
from customer
where first_name like '%er%'
order by first_name

select first_name, last_name
from customer
where first_name like '_er%'
order by first_name

select first_name, last_name
from customer
where first_name not like 'Jen%'
order by first_name

`is null

To check if a value is null or not, you cannot use the equal to (=) or not equal to (<>) operators.

Instead, you use the is null operator.

The is null operator returns true if the value is null or false otherwise.

The is not null operator returns true if the value is not null or false otherwise.

SQLite offers some useful functions to handle null effectively such as nullif and coalesce.

Task: Sales People

We’ll use the following table that includes information on salespeople:

CREATE TABLE employees (
  id INTEGER UNIQUE NOT NULL,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  salary INTEGER NOT NULL,
  commission_rate REAL NOT NULL,
  commission REAL,
  branch_id INTEGER NOT NULL
);

INSERT INTO employees VALUES
(11, 'Katarina', 'Rostova', 45000, 0.15, 47345.60, 1),
(12, 'Alina', 'Park', 43000, 0.15, 45678.90, 2),
(13, 'Meera', 'Malik', 50000, 0.15, 39045.63, 2),
(17, 'Samar', 'Navabi', 52000, 0.14, 23023.45, 2),
(18, 'Donald', 'Ressler', 40000, 0.14, 41345.75, 2),
(20, 'Elisabeth', 'Keen', 59000, 0.14, 45350.00, 2),
(21, 'Tom', 'Keen', 41000, 0.12, 41560.75, 1),
(22, 'Dembe', 'Zuma', 40000, 0.12, 31540.70, 5),
(23, 'Aram', 'Mojtabai', 50000, 0.12, 29050.65, 2),
(30, 'Kate', 'Kaplan', 54000, 0.10, 25760.45, 5),
(32, 'Marvin', 'Gerard', 55000, 0.10, 22500.00, 5),
(34, 'Raymond', 'Reddington', 60000, 0.10, 17570.80, 5),
(35, 'Harold', 'Cooper', 57000, 0.10, 15450.50, 2),
(37, 'Ian', 'Garvey', 43000, 0.08, NULL, 1),
(38, 'Ivan', 'Stepanov', 41000, 0.08, NULL, 1)

select first_name, last_name, salary from employees
where salary >= 50000
order by salary desc

select first_name, last_name, commission_rate from employees
where commission_rate >= 0.12
order by commission_rate desc

select first_name, last_name, salary, commission from employees
where commission > salary

select * from employees
where commission_rate between 0.12 and 0.14

select first_name, last_name from employees
where last_name < 'Keen'
order by last_name

select first_name, last_name from employees
where last_name like 'K%'
order by last_name

select first_name, last_name from employees
where commission is null
order by last_name

select * from employees
where last_name in ('Kaplan', 'Gerard', 'Zuma')
order by last_name

select *
from employees
where branch_id = 5 and salary >= 50000

select *
from employees
where not branch_id = 2

select *
from employees
where commission > salary and not branch_id = 2

Task: Population

Download population.csv

select "Country/Territory", "2022 Population" as Population
from population
where Continent = "Asia" and "2022 Population" > 100000000
order by Population desc

select *
from population
where "Continent" = "Europe" and "2022 Population" between 10000000 and 15000000

select "Country/Territory", "2022 Population" - "1970 Population" as Difference, "Continent","2022 Population", "1970 Population"
from population
where "1970 Population" > "2022 Population"
order by Difference

Pending

• How to Compare Datetime Values in SQL