Intro to SQL - Part 2

Introduction

You now know how to select columns and perform basic counts. This chapter will focus on filtering your results.

In SQL, the WHERE keyword allows you to filter based on both text and numeric values in a table. There are a few different comparison operators you can use:

  • = equal
  • <> not equal
  • < less than
  • > greater than
  • <= less than or equal to
  • >= greater than or equal to

For example, you can filter text records such as title. The following code returns all films with the title ‘Metropolis’:

SELECT title
FROM films
WHERE title = 'Metropolis';

Notice that the WHERE clause always comes after the FROM statement!

Filter

Introduction


Instructions


dbGetQuery(con, "SELECT * FROM ecom WHERE duration > 300")
dbGetQuery(con, "SELECT * FROM ecom WHERE duration > 300")

Filter Text

Introduction


Instructions


dbGetQuery(con, "SELECT * FROM ecom WHERE device == 'mobile'")
dbGetQuery(con, "SELECT * FROM ecom WHERE device == 'mobile'")

Where And

Introduction


Instructions


dbGetQuery(con, "SELECT * FROM ecom WHERE n_visit > 3 AND duration > 100")
dbGetQuery(con, "SELECT * FROM ecom WHERE n_visit > 3 AND duration > 100")

Where And Or - Part 1

What if you want to select rows based on multiple conditions where some but not all of the conditions need to be met? For this, SQL has the OR operator.

For example, the following returns all films released in either 1994 or 2000:

SELECT title
FROM films
WHERE release_year = 1994
OR release_year = 2000

Note that you need to specify the column for every OR condition, so the following is invalid:

SELECT title
FROM films
WHERE release_year = 1994 OR 2000

When combining AND and OR, be sure to enclose the individual clauses in parentheses, like so:

SELECT title
FROM films
WHERE (release_year = 1994 OR release_year = 1995)
AND (certification = 'PG' OR certification = 'R')

Otherwise, due to SQL’s precedence rules, you may not get the results you’re expecting!

Where And Or - Part 2

Introduction


Instructions


dbGetQuery(con, "SELECT * FROM ecom WHERE (n_visit == 5 OR n_visit == 3) 
  AND (device = 'mobile' OR device = 'tablet')")
dbGetQuery(con, "SELECT * FROM ecom WHERE (n_visit == 5 OR n_visit == 3) 
  AND (device = 'mobile' OR device = 'tablet')")

Between - Part 1

As you’ve learned, you can use the following query to get titles of all films released in and between 1994 and 2000:

SELECT title
FROM films
WHERE release_year >= 1994
AND release_year <= 2000

Checking for ranges like this is very common, so in SQL the BETWEEN keyword provides a useful shorthand for filtering values within a specified range. This query is equivalent to the one above:

SELECT title
FROM films
WHERE release_year
BETWEEN 1994 AND 2000

It’s important to remember that BETWEEN is inclusive, meaning the beginning and end values are included in the results!

Between - Part 2

Introduction


Instructions


dbGetQuery(con, "SELECT * FROM ecom WHERE n_visit BETWEEN 1 AND 3
  AND device = 'mobile'")
dbGetQuery(con, "SELECT * FROM ecom WHERE n_visit BETWEEN 1 AND 3
  AND device = 'mobile'")

Where In

Introduction


Instructions


dbGetQuery(con, "SELECT * FROM ecom WHERE n_visit IN (2, 4, 6, 8, 10)")
dbGetQuery(con, "SELECT * FROM ecom WHERE n_visit IN (2, 4, 6, 8, 10)")

Introduction to NULL and IS NULL

In SQL, NULL represents a missing or unknown value. You can check for NULL values using the expression IS NULL. For example, to count the number of missing birth dates in the people table:

SELECT COUNT(*)
FROM people
WHERE birthdate IS NULL

As you can see, IS NULL is useful when combined with WHERE to figure out what data you’re missing.

Sometimes, you’ll want to filter out missing values so you only get results which are not NULL. To do this, you can use the IS NOT NULL operator.

For example, this query gives the names of all people whose birth dates are not missing in the people table.

SELECT name
FROM people
WHERE birthdate IS NOT NULL

NULL and IS NULL

Introduction


Instructions


dbGetQuery(con, "SELECT * FROM ecom WHERE device IS NULL")
dbGetQuery(con, "SELECT * FROM ecom WHERE device IS NULL")

LIKE and NOT LIKE - Part 1

As you’ve seen, the WHERE clause can be used to filter text data. However, so far you’ve only been able to filter by specifying the exact text you’re interested in. In the real world, often you’ll want to search for a pattern rather than a specific text string.

In SQL, the LIKE operator can be used in a WHERE clause to search for a pattern in a column. To accomplish this, you use something called a wildcard as a placeholder for some other values. There are two wildcards you can use with LIKE:

The % wildcard will match zero, one, or many characters in text. For example, the following query matches companies like ‘Data’, ‘DataC’ ‘DataCamp’, ‘DataMind’, and so on:

SELECT name
FROM companies
WHERE name LIKE 'Data%'

The _ wildcard will match a single character. For example, the following query matches companies like ‘DataCamp’, ‘DataComp’, and so on:

SELECT name
FROM companies
WHERE name LIKE 'DataC_mp'

You can also use the NOT LIKE operator to find records that don’t match the pattern you specify.

Got it? Let’s practice!

LIKE and NOT LIKE - Part 2

Introduction


Instructions


dbGetQuery(con, "SELECT * FROM ecom WHERE country LIKE 'P%'")
dbGetQuery(con, "SELECT * FROM ecom WHERE country LIKE 'P%'")

LIKE and NOT LIKE - Part 3

Introduction


Instructions


dbGetQuery(con, "SELECT * FROM ecom WHERE country LIKE '_o%'")
dbGetQuery(con, "SELECT * FROM ecom WHERE country LIKE '_o%'")