WHERE | BUS 104 – SQL Exam

Still feeling nervous for your exam? Check out the SQL Cram Kit for practice questions that emulate the difficulty that you'll face on your exam. Each question includes a thorough step-by-step explanation where I walk you through the entire problem solving process!

WHERE explained

Congratulations guys! You've just scored a date with the girl of your dreams. Problem is, all your shirts are raggedy and old. You need a desperate upgrade before making a first impression on your date.

So, you hop online and go to CoolGuyShirts.com to find the perfect fit. You browse through the 6 shirt options and notice a filter on the left-hand side.

You'll utilize this filter to quickly determine what shirt you want to buy. First things first, you only want a shirt if it's got a 4-Star & Up rating. You need some high-class stuff!

Next, you know you look good in red. So, you select the option to only see red shirts.

But then you realize that blue might look good with your pants. So, you select that option as well.

Perfect, now we've got 2 options to choose from.

In this situation, we utilized the filter to select 4-Star & Up shirts that are red or blue. We also essentially preformed a WHERE statement.

We took all of the shirts and defined conditions that each shirt must fit in order to be shown in the results.

WHERE statements are used to define conditions that each record must fit to be shown in the results.

How to code a WHERE

Here is the template for WHERE statements:

WHERE condition(s);

Let's break down how we can write these conditions.

Every condition is typically structured like so:

WHERE field comparison-operator value;

Let's apply this template to the first filter we decided: that our shirts had to be 4 Stars & Up.

CoolGuyShirts.com contained the following shirts:

In SQL table form, it looks like so:

So the field of our first condition, stating shirts must be 4 Stars & Up, is star_rating.

WHERE star_rating comparison-operator value;

Now, for the comparison operator. Let's step back here and understand what comparison operators are and how to use them.

Comparison Operators

A comparison operator is used to determine if a record meets a condition. It compares values and returns either true or false.

Here's the list of the most common comparison operators, a description of them, and an example.

Now that we know what comparison operators are, which one should we use to state the shirt must have a star_rating value of 4 or greater?

We'll use the ">=" comparison operator…

WHERE star_rating >= value;

…with a value of 4.

WHERE star_rating >= 4;

When you read this in English terms, it makes sense. We only want shirts in which the star rating…

WHERE star_rating >= 4;

…is greater than or equal to…

WHERE star_rating >= 4;

…4.

WHERE star_rating >= 4;

So, when we applied this WHERE condition to CoolGuyShirts.com, we got the following results:

Notice how the rows that don't have a star_rating value of greater than or equal to 4 were filtered out.

Now, let's handle the next condition that we selected: the shirt must be red.

But, we've already got a condition in our WHERE statement. How can we have multiple conditions?

With logical operators!

WHERE condition1 logical-operator condition2 logical-operator condition3…;

Logical Operators

Logical operators are used to conjunct multiple conditions in a WHERE statement and state conditions in more "English" terms.

The two most common logical operators that you'll use are AND and OR.

So, here's our current WHERE statement. First step, what will we place in the logical operator?

WHERE star_rating >= 4 logical-operator condition;

The AND logical operator. We want our shirts to have a star_rating value of greater than or equal to 4 and be red.

WHERE star_rating >= 4 AND condition;

Now, for our condition stating shirts must be red. Let's break down this condition into a field, comparison operator, and value.

WHERE star_rating >= 4 AND field comparison-operator value;

What's our field going to be? color, since this is where the color of the shirt is stored.

WHERE star_rating >= 4 AND color comparison-operator value;

What's our comparison operator going to be? Well, we want color to be, or equal, red. So, we'll place the "=" comparison operator here.

WHERE star_rating >= 4 AND color = value;

Now, what's the value going to be?

If you're thinking "red", be careful. Refer back to the table of shirts and notice the capitalization used.

It's not "red", it's "Red".

SQL pays attention to capitalization, so you need to as well!

WHERE star_rating >= 4 AND color = 'Red';

Perfect, let's spell this out in English terms and ensure it makes sense.

Our shirt must have a 4-Star & Up rating…

WHERE star_rating >= 4 AND color = 'Red';

…and…

WHERE star_rating >= 4 AND color = 'Red;

…the color of the shirt…

WHERE star_rating >= 4 AND color = 'Red';

…must equal…

WHERE star_rating >= 4 AND color = 'Red';

…red.

WHERE star_rating >= 4 AND color = 'Red';

When we applied this filter on the CoolGuyShirts.com website, we got the following results:

This caused only shirts with a star_rating value greater than or equal to 4 and a color equal to "Red" to remain in the results.

Okay, moving onto our last condition. Remember how after we filtered for only red shirts, we also included blue shirts?

We'll add on another condition here: the shirt's color must equal blue.

But, how will we accomplish this without messing up our current conditions?

Let's re-read our filtering to get a better idea.

We want to filter for shirts that have a 4-Star & Up rating and have a color of either red or blue.

It helps to break this down into two main parts, (1) the star rating and (2) the shirt color.

We want to filter for shirts that (1) have a 4-Star & Up rating and (2) have a color of either red or blue.

We've already accomplished the first part of this, that the shirt must have a 4-Star & Up rating.

WHERE star_rating >= 4 AND color = 'Red';

And we're almost done with the second part. We've stated that the shirt must have a color of red…

WHERE star_rating >= 4 AND color = 'Red';

…but we have not included blue yet. How will we do this?

With parentheses!

We'll group the two parts of the question with parentheses. Think about it this way:

We want to filter for shirts that (have a 4-star or above rating) and (have a color of either red or blue).

Or, in SQL terms…

WHERE (condition stating that shirt is 4-Star & Up) AND (conditions stating the shirt must be either red or blue);

As we've stated, we've already got the first part down.

WHERE (star_rating >= 4) AND (color = 'Red' logical-operator condition);

And we're almost done with the second part…

WHERE (star_rating >= 4) AND (color = 'Red' logical-operator condition);

…we just need to include the condition stating the shirt can be blue as well.

WHERE (star_rating >= 4) AND (color = 'Red' logical-operator condition);

So, what will the logical operator be here?

It will not be AND, since a shirt cannot be both red and blue.

Instead, we'll use OR. The shirt can either be red or blue.

WHERE (star_rating >= 4) AND (color = 'Red' OR condition);

Writing the condition stating the shirt must be blue is extremely similar to the condition stating the shirt must be red.

WHERE (star_rating >= 4) AND (color = 'Red' OR color = 'Blue');

Perfect, now we've included that the shirt can be blue as well. Let's re-read through this to ensure it makes sense.

Our shirt must have a 4-Star & Up rating…

WHERE (star_rating >= 4) AND (color = 'Red' OR color = 'Blue');

…and…

WHERE (star_rating >= 4) AND (color = 'Red' OR color = 'Blue');

…the color of the shirt…

WHERE (star_rating >= 4) AND (color = 'Red' OR color = 'Blue');

…must be red…

WHERE (star_rating >= 4) AND (color = 'Red' OR color = 'Blue');

…or…

WHERE (star_rating >= 4) AND (color = 'Red' OR color = 'Blue');

…blue.

WHERE (star_rating >= 4) AND (color = 'Red' OR color = 'Blue');

When we applied this filter on the CoolGuyShirts.com website, we got the following results:

It brought back one of the shirts we had previously filtered out, marked with green.

This is because previously, we had stated we only wanted red shirts, but now we're saying blue are fine as well.

Let's wrap up our query by writing our SELECT and FROM statements.

Finishing the query

Concerning our SELECT statement, we'll use "*". We want to see each field in the shirts table (price, star_rating, and color).

SELECT *
FROM database_name.table_name
WHERE (star_rating >= 4) AND (color = 'Red' OR color = 'Blue');

Concerning our database_name, let's just say that CoolGuyShirts.com has a database called coolguyshirts…

SELECT *
FROM coolguyshirts.table_name
WHERE (star_rating >= 4) AND (color = 'Red' OR color = 'Blue');

…with a table called shirts containing each shirt.

SELECT *
FROM coolguyshirts.shirts
WHERE (star_rating >= 4) AND (color = 'Red' OR color = 'Blue');

When we execute this query, we get the following results:

Notice how this is the exact same as above, except all the red rows are filtered out and not shown in the results?

Here in lies the purpose of WHERE statements. To filter out records that don't meet certain conditions.

If you'd like to move on now to GROUP BY | SQL in 30 Minutes, feel free to. Otherwise, you can continue on and gain some more logical operators for your SQL arsenal.

Bonus Content

There are some more logical operators that I omitted for the sake of a faster learning process.

You don't need these logical operators to code in SQL, but they definitely help make life easier on you.

Now, let's apply one of these bonus logical operators to our CoolGuyShirts.com query. Which one do you think we'll use on the highlighted part of our code below?

SELECT *
FROM coolguyshirts.shirts
WHERE (star_rating >= 4) AND (color = 'Red' OR color = 'Blue');

With this line of code, what we're trying to say is that the shirt color is included in "Red" or "Blue".

So, we'll use the IN operator!

SELECT *
FROM coolguyshirts.shirts
WHERE (star_rating >= 4) AND (color IN ('Red', 'Blue'));

Now, we can remove the parentheses since now we're not combining ANDs and ORs in one WHERE statement.

SELECT *
FROM coolguyshirts.shirts
WHERE star_rating >= 4 AND color IN ('Red', 'Blue');

Our query looks a little nicer on the eye now! Plus, it makes more sense at a glance.

The shirts must have a 4-Star Rating & Up...

SELECT *
FROM coolguyshirts.shirts
WHERE star_rating >= 4 AND color IN ('Red', 'Blue');

...and...

SELECT *
FROM coolguyshirts.shirts
WHERE star_rating >= 4 AND color IN ('Red', 'Blue');

...must be red or blue.

SELECT *
FROM coolguyshirts.shirts
WHERE star_rating >= 4 AND color IN ('Red', 'Blue');

SQL Follow-along Guide

It's no secret you retain info better when you write it down. That's why I created the SQL Follow-along Guide for you!

As you come upon key concepts highlighted in yellow, like this sentence here, you can fill-in-the-blanks on your Follow-along Guide so that you remember all the important stuff for later.

You can obtain the SQL Follow-along Guide for FREE by entering your email below!

Now, we're ready to move onto learning how to make mathematical calculations in a query with GROUP BY!

Concepts

Each exam concept broken down with relatable situations to your life!

	Tools	SQL Follow-along Guide (FREE)
	Lesson	What is SQL?
	Lesson	SELECT & FROM
	Lesson	JOIN
	Lesson	WHERE
	Lesson	GROUP BY
	Lesson	HAVING
	Lesson	ORDER BY
	Lesson	Troubleshooting errors

Practice Problems

Include step-by-step explanations through each part of the problem!

I want my Cram Kit!

"The SQL Cram Kit was the best thing I could’ve done before my exam. It takes practice and more practice to be good at coding and that is what I needed."

Andrew Green
October 29, 2020

⭐⭐⭐⭐⭐

Rating: 5 out of 5.

Adam Wise

I’m a Miami University (OH) 2021 alumni who majored in Information Systems. At Miami, I tutored students in Python, SQL, JavaScript, and HTML for 2+ years. I’m a huge fantasy football fan, Marvel nerd, and love hanging out with my friends here in Chicago where I currently reside.