After SELECT and FROM, the next SQL clause you're most likely to use as a data scientist is WHERE.

With just a SELECT expression, we can specify which columns we want to select, as well as transform the column values using aliases, built-in functions, and other expressions.

However if we want to filter the rows that we want to select, we also need to include a WHERE clause.

You will be able to:

• Retrieve a subset of records from a table using a WHERE clause
• Filter results using conditional operators such as BETWEEN, IS NULL, and LIKE
• Apply an aggregate function to the result of a filtered query

For this section of the lesson, we'll use the Northwind database, the ERD (entity-relationship diagram) of which is shown below:

Below, we connect to a SQLite database using the Python sqlite3 library (documentation here), then display the contents of the employees table:

import pandas as pd
import sqlite3 
conn = sqlite3.connect('data.sqlite')
pd.read_sql("""
SELECT *
  FROM employees;
""", conn)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

When filtering data using WHERE, you are trying to find rows that match a specific condition. The simplest condition involves checking whether a specific column contains a specific value. In SQLite, this is done using =, which is similar to == in Python:

pd.read_sql("""
SELECT *
  FROM employees
 WHERE lastName = "Patterson";
""", conn)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

Note that we are selecting all columns (SELECT *) but are no longer selecting all rows. Instead, we are only selecting the 3 rows where the value of lastName is "Patterson".

SQL is essentially doing something like this:

# Selecting all of the records in the database
result = pd.read_sql("SELECT * FROM employees;", conn)
# Create a list to store the records that match the query
employees_named_patterson = []
# Loop over all of the employees
for _, data in result.iterrows():
    # Check if the last name is "Patterson"
    if data["lastName"] == "Patterson":
        # Add to list
        employees_named_patterson.append(data)

# Display the result list as a DataFrame
pd.DataFrame(employees_named_patterson)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

Except SQL is designed specifically to perform these kinds of queries efficiently! Even if you are pulling data from SQL into Python for further analysis, SELECT * FROM <table>; is very rarely the most efficient approach. You should be thinking about how to get SQL to do the "heavy lifting" for you in terms of selecting, filtering, and transforming the raw data!

You can also combine WHERE clauses with SELECT statements other than SELECT * in order to filter rows and columns at the same time. For example:

pd.read_sql("""
SELECT firstName, lastName, email
  FROM employees
 WHERE lastName = "Patterson";
""", conn)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

WHERE clauses are especially powerful when combined with more-complex SELECT statements. Most of the time you will want to use aliases (with AS) in the SELECT statements to make the WHERE clauses more concise and readable.

If we wanted to select all employees with 5 letters in their first name, that would look like this:

pd.read_sql("""
SELECT *, length(firstName) AS name_length
  FROM employees
 WHERE name_length = 5;
""", conn)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

Or, to select all employees with the first initial of "L", that would look like this:

pd.read_sql("""
SELECT *, substr(firstName, 1, 1) AS first_initial
  FROM employees
 WHERE first_initial = "L";
""", conn)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

Important note: Just like in Python, you can compare numbers in SQL just by typing the number (e.g. name_length = 5) but if you want to compare to a string value, you need to surround the value with quotes (e.g. first_initial = "L"). If you forget the quotes, you will get an error, because SQL will interpret it as a variable name rather than a hard-coded value:

pd.read_sql("""
SELECT *, substr(firstName, 1, 1) AS first_initial
  FROM employees
 WHERE first_initial = L;
""", conn)

---------------------------------------------------------------------------

OperationalError                          Traceback (most recent call last)

~/opt/anaconda3/envs/learn-env/lib/python3.8/site-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   2017         try:
-> 2018             cur.execute(*args, **kwargs)
   2019             return cur


OperationalError: no such column: L


The above exception was the direct cause of the following exception:


DatabaseError                             Traceback (most recent call last)

<ipython-input-7-083650d31057> in <module>
----> 1 pd.read_sql("""
      2 SELECT *, substr(firstName, 1, 1) AS first_initial
      3   FROM employees
      4  WHERE first_initial = L;
      5 """, conn)


~/opt/anaconda3/envs/learn-env/lib/python3.8/site-packages/pandas/io/sql.py in read_sql(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize)
    562 
    563     if isinstance(pandas_sql, SQLiteDatabase):
--> 564         return pandas_sql.read_query(
    565             sql,
    566             index_col=index_col,


~/opt/anaconda3/envs/learn-env/lib/python3.8/site-packages/pandas/io/sql.py in read_query(self, sql, index_col, coerce_float, params, parse_dates, chunksize, dtype)
   2076 
   2077         args = _convert_params(sql, params)
-> 2078         cursor = self.execute(*args)
   2079         columns = [col_desc[0] for col_desc in cursor.description]
   2080 


~/opt/anaconda3/envs/learn-env/lib/python3.8/site-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   2028 
   2029             ex = DatabaseError(f"Execution failed on sql '{args[0]}': {exc}")
-> 2030             raise ex from exc
   2031 
   2032     @staticmethod


DatabaseError: Execution failed on sql '
SELECT *, substr(firstName, 1, 1) AS first_initial
  FROM employees
 WHERE first_initial = L;
': no such column: L

Below we select all order details where the price each, rounded to the nearest integer, is 30 dollars:

pd.read_sql("""
SELECT *, CAST(round(priceEach) AS INTEGER) AS rounded_price_int
  FROM orderDetails
 WHERE rounded_price_int = 30;
""", conn)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

We can use the strftime function to select all orders placed in January of any year:

pd.read_sql("""
SELECT *, strftime("%m", orderDate) AS month
  FROM orders
 WHERE month = "01";
""", conn)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

We can also check to see if any orders were shipped late (shippedDate after requiredDate, i.e. the number of days late is a positive number):

pd.read_sql("""
SELECT *, julianday(shippedDate) - julianday(requiredDate) AS days_late
  FROM orders
 WHERE days_late > 0;
""", conn)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

That was the last query in this lesson using the Northwind data, so let's close that connection:

conn.close()

In all of the above queries, we used the = operator to check if we had an exact match for a given value. However, what if you wanted to select the order details where the price was at least 30 dollars? Or all of the orders that don't currently have a shipped date?

We'll need some more advanced conditional operators for that.

Some important ones to know are:

• != ("not equal to")
  • Similar to not combined with == in Python
• > ("greater than")
  • Similar to > in Python
• >= ("greater than or equal to")
  • Similar to >= in Python
• < ("less than")
  • Similar to < in Python
• <= ("less than or equal to")
  • Similar to <= in Python
• AND
  • Similar to and in Python
• OR
  • Similar to or in Python
• BETWEEN
  • Similar to placing a value between two values with <= and and in Python, e.g. (2 <= x) and (x <= 5)
• IN
  • Similar to in in Python
• LIKE
  • Uses wildcards to find similar strings. No direct equivalent in Python, but similar to some Bash terminal commands.

For this section as the queries get more advanced we'll be using a simpler database called pets_database.db containing a table called cats.

The cats table is populated with the following data:

Below we make a new database connection and read all of the data from this table:

conn = sqlite3.connect('pets_database.db')
pd.read_sql("SELECT * FROM cats;", conn)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

In this exercise, you'll walk through executing a handful of common and handy SQL queries that use WHERE with conditional operators. We'll start by giving you an example of what this type of query looks like, then have you type a query specifically related to the cats table.

For the =, !=, <, <=, >, and >= operators, the query looks like:

SELECT column(s)
  FROM table_name
 WHERE column_name operator value;

Note: The example above is not valid SQL, it is a template for how the queries are constructed

Type this SQL query between the quotes below to select all cats who are at least 5 years old:

SELECT *
  FROM cats
 WHERE age >= 5;

pd.read_sql("""

""", conn)

This should return:

If you wanted to select all rows with values in a range, you could do this by combining the <= and AND operators. However, since this is such a common task in SQL, there is a shorter and more efficient command specifically for this purpose, called BETWEEN.

A typical query with BETWEEN looks like:

SELECT column_name(s)
  FROM table_name
 WHERE column_name BETWEEN value1 AND value2;

Note that BETWEEN is an inclusive range, so the returned values can match the boundary values (not like range() in Python)

Let's say you need to select the names of all of the cats whose age is between 1 and 3. Type this SQL query between the quotes below to select all cats who are in this age range:

SELECT *
  FROM cats
 WHERE age BETWEEN 1 AND 3;

pd.read_sql("""

""", conn)

This should return:

NULL in SQL represents missing data. It is similar to None in Python or NaN in NumPy or pandas. However, we use the IS operator to check if something is NULL, not the = operator (or IS NOT instead of !=).

To check if a value is NULL (or not), the query looks like:

SELECT column(s)
  FROM table_name
 WHERE column_name IS (NOT) NULL;

You might have noticed when we selected all rows of cats, some owner IDs were NaN, then in the above query they are None instead. This is a subtle difference where Python/pandas is converting SQL NULL values to NaN when there are numbers in other rows, and converting to None when all of the returned values are NULL. This is a subtle difference that you don't need to memorize; it is just highlighted to demonstrate that the operators we use in SQL are similar to Python operators, but not quite the same.

If we want to select all cats that don't currently belong to an owner, we want to select all cats where the owner_id is NULL.

Type this SQL query between the quotes below to select all cats that don't currently belong to an owner:

SELECT *
  FROM cats
 WHERE owner_id IS NULL;

pd.read_sql("""

""", conn)

This should return:

The LIKE operator is very helpful for writing SQL queries with messy data. It uses wildcards to specify which parts of the string query need to be an exact match and which parts can be variable.

When using LIKE, a query looks like:

SELECT column(s)
  FROM table_name
 WHERE column_name LIKE 'string_with_wildcards';

The most common wildcard you'll see is %. This is similar to the * wildcard in Bash or regex: it means zero or more characters with any value can be in that position.

So for example, if we want all cats with names that start with "M", we could use a query containing M%. This means that we're looking for matches that start with one character "M" (or "m", since this is a case-insensitive query in SQLite) and then zero or more characters that can have any value.

Type this SQL query between the quotes below to select all cats with names that start with "M" (or "m"):

SELECT *
  FROM cats
 WHERE name LIKE 'M%';

pd.read_sql("""

""", conn)

This should return:

Note that we also could have used the substr SQL built-in function here to perform the same task:

SELECT *
  FROM cats
 WHERE substr(name, 1, 1) = "M";

Unlike in Python where:

There should be one-- and preferably only one --obvious way to do it. (Zen of Python)

there will often be multiple valid approaches to writing the same SQL query. Sometimes one will be more efficient than the other, and sometimes the only difference will be a matter of preference.

The other wildcard used for comparing strings is _, which means exactly one character, with any value.

For example, if we wanted to select all cats with four-letter names where the second letter was "a", we could use _a__.

Type this SQL query between the quotes below to select all cats with names where the second letter is "a" and the name is four letters long:

SELECT *
  FROM cats
 WHERE name LIKE '_a__';

pd.read_sql("""

""", conn)

This should return:

Again, we could have done this using length and substr, although it would be much less concise:

SELECT *
  FROM cats
 WHERE length(name) = 4 AND substr(name, 2, 1) = "a";

These examples are a bit silly, but you can imagine how this technique would help to write queries between multiple datasets where the names don't quite match exactly! You can combine % and _ in your string to narrow and expand your query results as needed.

Now, let's talk about the SQL aggregate function COUNT.

SQL aggregate functions are SQL statements that can get the average of a column's values, retrieve the minimum and maximum values from a column, sum values in a column, or count a number of records that meet certain conditions. You can learn more about these SQL aggregators here and here.

For now, we'll just focus on COUNT, which counts the number of records that meet a certain condition. Here's a standard SQL query using COUNT:

SELECT COUNT(column_name)
  FROM table_name
 WHERE conditional_statement;

Let's try it out and count the number of cats who have an owner_id of 1. Type this SQL query between the quotes below:

SELECT COUNT(owner_id)
  FROM cats
 WHERE owner_id = 1;

pd.read_sql("""

""", conn)

This should return:

We are now familiar with this syntax:

SELECT name
  FROM cats;

However, you may not know that this can be written like this as well:

SELECT cats.name
  FROM cats;

Both return the same data.

SQLite allows us to explicitly state the tableName.columnName you want to select. This is particularly useful when you want data from two different tables.

Imagine you have another table called dogs with a column containing all of the dog names:

CREATE TABLE dogs (
	id   INTEGER PRIMARY KEY,
	name TEXT
);

INSERT INTO dogs (name)
VALUES ("Clifford");

If you want to get the names of all the dogs and cats, you can no longer run a query with just the column name. SELECT name FROM cats,dogs; will return Error: ambiguous column name: name.

Instead, you must explicitly follow the tableName.columnName syntax.

SELECT cats.name, dogs.name
  FROM cats, dogs;

You may see this in the future. Don't let it trip you up!

conn.close()

In this lesson, you saw how to filter the resulting rows of a SQL query using a WHERE clause that checked whether a given column was equal to a specific value. You also got a basic introduction to aggregate functions by seeing an example of COUNT, and dove deeper into some conditional operators including BETWEEN and LIKE.