Filter content of a TABLE in SQL

In this example, we will display the content of a table but we will filter out the results. Since we are working in the notebook, we will load the sql extension in order to manipulate the database. The database mydatabase.db is a SQLite database already created before the example.

#load the extension
%load_ext sql 
#connect to the database
%sql sqlite:///mydatabase.db 

'Connected: @mydatabase.db'

Filter content matching exactly a condition

We want to extract all the entries in a dataframe that match a certain condition, in order to do so, we will use the following command :

SELECT * FROM TABLE WHERE column="condition"

In our example, we will filter all the entries in the tutyfrutty table whose color is yellow

%sql SELECT * FROM tutyfrutty WHERE color="yellow"

 * sqlite:///mydatabase.db
Done.

Complex conditions

You can build more complex conditions by using the keywords OR and AND

In the following example, we will filter all entries that are either yellow or red

%sql SELECT * FROM tutyfrutty WHERE color="yellow" OR color="red"

 * sqlite:///mydatabase.db
Done.

Note : when combining multiple conditions with AND and OR, be careful to use parentesis where needed

Conditions matching a pattern

You can also use the LIKE keyword in order to find all entries that match a certain pattern. In our example, we want to find all fruits begining with a "l". In order to do so, we will use the LIKE keyword and the wildcard "%" meaning any string

%sql SELECT * FROM tutyfrutty WHERE fruit LIKE "l%"

 * sqlite:///mydatabase.db
Done.

Numerical conditions

When we are working with numerical data, we can use the GREATER THAN > and SMALLER THAN < operators

%sql SELECT * FROM tutyfrutty WHERE kcal < 47

 * sqlite:///mydatabase.db
Done.

If we want the condition to be inclusive we can use the operator <= (alternatively >=)

%sql SELECT * FROM tutyfrutty WHERE kcal <= 47

 * sqlite:///mydatabase.db
Done.