SQL: Comparison Filtering

Snack-Sized Data Basics
SQL - Comparison Filtering

Using the quick set up steps , let's take a closer look at comparison filtering.

As we've seen, comparison filtering checks if a column's value is equal to, different from, greater than, or less than a specific value. It uses =, !=, <, >, <= and >= to compare values.
Let's go through some examples.

Comparison Filtering with Numbers
Let's say we want to find the first and last names of all students with zero absences. We can do so as followed.

SELECT first_name, last_name

FROM students
WHERE absences = 0;

To get the first and last names of all students with more than two absences, the following sql query can be used.

SELECT first_name, last_name

FROM students
WHERE absences > 2;

Alternatively, to get the first and last names of all students with two or less absences, the sql query would be as followed.

SELECT first_name, last_name

FROM students
WHERE absences <= 2;

If we wanted to, to get the first and last names of all students that do not have two absences, we use != , which stands for not equal to.

SELECT first_name, last_name

FROM students
WHERE absences != 2;

Comparison Filtering with Dates

We can also use comparison filtering with dates. For example, if we wanted to find all students born before 2015, we can use the following query. Note that the date needs to be surrounded by single quote.

SELECT first_name, last_name

FROM students
WHERE date_of_birth < '2015-01-01';

We can also do so by extracting the year from the data of birth using the sql keyword EXTRACT(YEAR FROM date. Note that the quotation marks we put around the date earlier is not needed after extracting the year.

SELECT first_name, last_name

FROM students
WHERE EXTRACT(YEAR FROM date_of_birth) = 2015

Similarly, we can extract the month as followed.

SELECT first_name, last_name

FROM students
WHERE EXTRACT(MONTH FROM date_of_birth) = 3

You guessed it! We can also extract the day as followed.

SELECT first_name, last_name

FROM students
WHERE EXTRACT(DAY FROM date_of_birth) = 30

Now head on over to sql to sqliteonline to try them out!

Question:

What query would you use to retrieve the first names, last names, and birth months of students born in the year 2014?

Answer:

SELECT first_name, last_name, EXTRACT(MONTH FROM date_of_birth)
FROM students

WHERE EXTRACT(YEAR FROM date_of_birth) = 2014;

Snack-Sized Data Basics SQL - Comparison Filtering

Snack-Sized Data Basics
SQL - Comparison Filtering