00:00What's going on, everybody? Welcome back to another video. Today, we are continuing our
00:04PostgreSQL series, and what we're going to be focusing on is the where statement. Now,
00:08within the where statement, there's a lot of things that we can do. We're going to be focusing
00:12on conditional statements as well as logical statements as well. If you want the data that
00:17we are working with, you haven't been following along this series yet, I'm going to leave a link
00:21to this right here. This is just our script in order to create our tables and insert our data
00:27into them. So, let's come right over here. Before we actually start writing anything,
00:32I do want to show you this really quick. We're going to look at two different things.
00:36We're going to start with comparison operators. Comparison operators are just a way to say,
00:43hey, I want something to be greater than something else, or I want this value to be equal to this
00:49value. It's a way where we can filter our data, and this is what we use right here. So, these
00:54little
00:54less than, greater than, less than or equal to, greater than or equal to, an equal sign where
01:01one value is equal to another value. We have not equal and not equal. These are the exact same
01:06thing, just written differently. These are what we're going to be using in this lesson. You can
01:11pause here and kind of look over this if you'd like, but that's what we're going to be using in
01:15this lesson. Now, let's take a look at our data really quick. We have our character info. So, we have
01:21Luke Skywalker. He's a human, and he has kind of a high net worth. He has $150,000 of net
01:27worth.
01:27Maybe I want to look at all the people who have a high net worth, and let's go ahead and
01:31write this
01:32out. So, we're going to say where, that's going to be our keyword to start filtering our rows of data,
01:38where the estimated underscore net underscore worth, and we're going to do greater than. So, I look at
01:45this, a really easy way to remember this is the alligator mouth right here is pointing towards
01:50the bigger one. It's trying to eat the bigger one. And so, that's just how I learned it in school.
01:54But we're saying it's greater than, and let's do 100,000. So, we're going to do 100, 1, 2, 3.
01:59So,
02:00we're selecting all the rows from the character info table, where the estimated net worth is greater
02:06than 100,000. Let's go ahead and run this. And as you can see, we now have a lot less
02:11rows,
02:11because we filtered out a lot of rows where it was less than 100,000. And so, now, you can
02:18see we
02:18only have rows of data where the estimated net worth is greater than 100,000. Now, we're just using
02:25greater than. We aren't using greater than or equal to. So, if someone had exactly 100,000,
02:31they would not be included in this output. Let's do this, for example. We know this is 150,000.
02:36Let's go up here and replace this. This is 150,000. That's Luke Skywalker. When we run this,
02:44you're going to notice that Luke Skywalker is no longer in our output, because he is exactly 150,000,
02:51and we're looking for greater. But if we do this right here, which is greater than or equal to,
02:57now, he will be included because it is equal to 150,000. Let's run this. And now, we have Luke
03:04Skywalker in our output. Now, just like we did greater than, we can change this to less than.
03:10So, now, we're going to look at all the opposite. Essentially, people make 150,000 or less. Let's go
03:16ahead and run this. As you can see, these people make 150,000 or less. Poor R2-D2 and C
03:22-3PO. They make
03:23almost nothing. Oh, geez. And there's Jar Jar Binks making only 500. That's sad. You'd think he'd have
03:30more, but maybe it's from the, you know, first movie that he was in. I think later on, he was
03:35worth a lot more. But that is neither here nor there. Now, let's pull back this script, because
03:41right now, we're using greater than or less than, but we can also use equal to. So, right in here,
03:46we have has both arms. Now, surprisingly, a lot of characters in Star Wars lose their limbs.
03:52As I was making this, I was kind of going through, and I was thinking, wow, you know, Luke,
03:56he lost his arm. Darth Vader, he lost his arm. Let's see who all lost their arm, or has them
04:02both, depending on how we write this. So, we're going to say has underscore both arms. Let me
04:08spell that right. And we can say is equal to. Now, if we want to specify a value, which is
04:14text, we need to put it in quotes. So, I'm going to say no, N for no. So, has both
04:20arms equals
04:21no. If it equals no, they'll be in our output. So, now, you can see here has both arms. So,
04:27it's not really going to be a range of anything. Now, we're looking for a hard value. It has
04:31to equal exactly this value. Again, we can switch this. We can say where it's yes, and
04:37now, it'll pull up all the people who have yes. Now, right here, we're saying equal to,
04:42and that's the one I use a lot, but you can do the opposite of that, which is not equal
04:47to. All we'd have to do for this one is add an exclamation point. So, now, we're going
04:52to say where they have both arms is not equal to yes. So, let's go ahead and run this. And
04:58in our output, it's all of the no's. So, now, we're just saying where it's not a value, but
05:02it could have lots of values in its output. For example, if we came in here, and we said
05:08where the species is not equal to, and we'll do human. So, for this one, we're saying we don't
05:16want it to be human in the output, but there could be a lot of other species, like Wookie,
05:21unknown for Yoda, Droid, Droid, Zabrak, and Gungan, right? These are different species that
05:27we have that aren't human. If we say just like this, which is greater than or equal
05:32to, makes this kind of like bracket-looking thing, we can say the exact same thing. It'll
05:37give us the exact same output, but I tend to write it like this because this is actually
05:43a way that it's written in other programming languages that I use. I typically find myself
05:47writing it like this, but either one is perfectly fine. Anyone who knows SQL will understand
05:51the difference. Now, the next thing that I want to look at, and I'm going to pull this
05:54up, is logical operators. These work 100% in conjunction with the comparison operators.
06:02And this comes directly from the PostgreSQL site. I just took a screenshot of it. But we have
06:06and, or, and not. We can have multiple conditions and multiple comparison operators being used at
06:12the same time. So let's use the and and or first. And let's say we want them to not be
06:18human, and we also want them to be really rich. And so now we're going to say, and their estimated
06:26net worth is greater than 100,000. And notice for numerical values or decimal, we don't have
06:35to use quotes because it's understood what we're looking for. At least PostgreSQL knows what
06:40we're doing. So now we're filtering it down. Now we have Chewbacca and Darth Maul. They are rich,
06:44and they are not human. And so we can filter down our data using a lot of different conditions.
06:50This is one condition. This is another condition. And something to note, this is just, you may not
06:56be interested in this, but I think it's interesting, is what it's doing is PostgreSQL is evaluating
07:01this expression, this condition, and it determines whether it's true or false, which is a Boolean
07:07value. And so it says, is the species not human? If that is true, then return it. Then it says,
07:14and,
07:15is this condition also true? If that is also true, it will return it, and then it gives us our
07:20output.
07:21Now, let's replace this with an or. Or means only one of these conditions have to be true.
07:29And means both conditions have to be true, like we saw. So, or species is not going to be human.
07:36So
07:37everyone who's not a human will be in this output. And then the estimated net worth is greater than
07:43100,000. Everyone who meets that condition will also be in the output. Let's go ahead and run this.
07:50So now we have essentially everybody, everyone in our output. Because Leia Organa, although she is a
07:57human, she's very wealthy. She's probably one of the most wealthy people in this entire data set.
08:03And so because one of these conditions was met, she is in our output. Now I'm going to show you
08:07the
08:07last logical operator, which is not. And I almost never use this. It just confuses me, if I'm being
08:14honest, when I have it in a big, you know, queries and things like that. So I typically don't use
08:18this
08:18at all. But this is something you can do. And I'm going to show it to you just so that
08:22you,
08:22you know, can see it. So we're going to say where the species is not human, we're going to keep
08:27that
08:27one the same, or, and then we're going to say not estimated net worth of greater than 100, which
08:34means the opposite if it's less than 100. Let's go ahead and run this. Now it looks like all of
08:39our
08:39humans are actually too wealthy. Let's add another zero here and run this just for demonstration purposes.
08:45So we're looking for where the species is not human. So we have a lot of non-humans. And then
08:52we're saying, or, so if this condition is also met, they could be in our output, or their estimated
08:58worth is not greater than, I think that's 1 million now. And so we're doing the opposite of this. Now
09:10we're looking for people's estimated net worth to be less than 1 million. And so it just does the
09:15opposite. Again, I don't use this that much because if I'm being honest, it confuses me.
09:20It works perfectly fine. You can do it. I've seen other people use it, but I just like to stay
09:26with
09:26the positive things or just use comparison operators that I guess make more sense in my mind.
09:33That's just me being honest, but we can run this. And so this really is a lot of what you're
09:38going to
09:38do in the where statement. Now, as we get into things that are more advanced, like subqueries,
09:44subqueries would go in a where statement, but they're a lot more advanced. And by the time we
09:48get to subqueries, you're going to really understand how to write, you know, base queries
09:52like what we're doing. But understanding comparison operators and understanding logical operators
09:57are 100% things that you will use all the time. So this is a really fundamental thing that you
10:02need
10:02to know in SQL. I really hope that this lesson was helpful. If you learned anything,
10:06be sure to like, and subscribe, and I will see you in the next lesson.
Comments