So in the last video at the end, I mentioned that we're about to move on to bigger and better operators.
And this is the first of those.
It's called logical.
And you'll also see it as just to ampersand.
So what it does, it's sort of a step up in operators.
It allows us to actually chain together multiple pieces of logical information, multiple operators
together.
And basically it necessitates that all things are true.
All conditions are true.
Now, that sounds like gibberish.
So let's put it in practice.
Let's see what I mean.
So here's an example.
Let's say I wanted to do something like this select books written by Dave Eggers that are also published
after the year 2010.
So individually these are each straightforward tasks a colour coded them to show you select books written
by Dave Eggers.
We know how to do that.
We also know how to do select all books published after the year 2010 using greater than what we just
saw.
However, to put these together, we need to use logical.
And so here's the first part.
Select books written by Dave Eggers would look like this select star from books where author name equals
Eggers.
And then just like books published after the year 2010, it look like this select star from books where
it released year is greater than 2010.
So to put them together we use and so as I mentioned, we can either use the word or the text A and
or two ampersand.
And it looks like this select star from books where author l name equals eggers and released year is
greater than 2010.
And that's it.
So basically we just take the two where clauses and shove them together with the word and.
And what this means is that both parts have to be true in order for it to be selected.
So it will start it.
We'll check.
Is this author's last name Eggers?
If it is, then it will move on and say, okay.
And was it released after 2010?
If it wasn't, then it discards it and it doesn't select it.
However, if both parts are true, then we see the results.
So let's try it.
Okay, so let's start off by selecting title author l name and release here.
And I'm just working in this document.
I'm just going to copy and paste in, but I'm just putting a record of it here to make it easier for
me to edit as I go through.
So select title author l name and released here from books where and we're just checking where author
last name equals Eggers.
Just like that, there's semicolon.
And you can see we have these three hologram for the King, The Circle, and my favorite heartbreaking
work of staggering genius.
And then we have for least a year.
So for some reason, someone comes into our bookstore and says, you know, I really like Dave Eggers,
but I only like his later stuff, which I would disagree with.
I think his earlier stuff is better, but let's say someone says that, well, then we would do something
like Select and I'll actually just copy this.
And change the where clause to where released year is greater than 2010, which we already know how
to do.
This should be review.
Now this gives us all books released after 2010 and then to combine them is where we use logical and.
And.
Release this year greater than 2010 OC.
So let's check that it works.
You can see we only get two results because both of these things have to match.
So author has to be exactly Eggers case insensitive and release here has to be greater than the year
2010.
And just to show you, we can replace this with double ampersand and it works the exact same way.
There we go.
So that's the basics of and we'll use it all the time.
It's important when I mean, it's any time you need to chain together things if you're trying to search
based off of two criteria.
So a common example might be something like select star from product.
Let's say we work in a toy store where brand equals Lego.
But when you're on a store online store, often there's like an option that will say, show out of stock
items or don't show out-of-stock items.
So we could do something behind the scenes, like where brand is Lego and in stock equals true.
That way, we wouldn't be showing the customer any Lego products that are also out of stock, which
is frustrating when you see that.
So that's a pretty common use cases when you're working with a value, a boolean, yes or no value like
in stock or is active user or something like that.
So the last thing that I'll address here is if we go back to the sort of problem, set things like one
less than five.
But rather than just doing one on their own, I'm going to give you a couple here.
We're going to chain them together with and and I'll give you a first example here.
So one greater than five and seven.
Equal to nine.
So what does this whole thing evaluate to?
Remember, both sides have to be true.
So let's take either side.
We'll take this one.
Is one less than five?
Yes, that is true.
Is seven equal to nine?
No, that's false.
And that immediately nullifies or falsifies.
Excuse me, the entire thing.
Both sides have to be true.
Just to double check.
Let's give it a shot.
Select.
And you can see zero false.
So just like before, I'm going to take a moment, fill out a couple of problems, and hopefully you'll
stick around and give them a shot.
All right, I'm back.
So I have three simple problems, hopefully simple that I typed out.
Go ahead.
Give them a shot and I'll be back.
All right.
What do you know?
I'm back.
So let's go over these quickly.
The first one, remember, both sides have to be true.
So start with the first one.
Negative ten.
Greater than -20.
Is that true?
Yes, it's larger than -20.
How about this is zero less than or equal to zero?
Yes, because zero is equal to zero.
Therefore, we have true and true.
So this whole thing is true.
Let's try it out.
Yep.
We get one.
Okay.
Moving on.
We have is -40 less than or equal to zero.
And yes, I know I had a negative sign here.
I apologize.
That was an accident.
That's wrong.
So hopefully you didn't spend too much time panicking about that.
Is -40 less than or equal to zero?
That's true.
It's smaller than zero.
And I use the English version A and D ten greater than 40.
Well, that's actually false.
Which makes the whole thing false, unfortunately.
So let's try it.
And we get zero, which means false.
Finally, is 54 less than or equal to 54?
That's true.
54 is equal to 54 and is a equal to a uppercase lowercase a equal to uppercase.
By now you should remember they are equivalent.
So there's true on both sides.
And we get true.
All right.
So the last thing I want to highlight about and is that we're not limited to just two components like
we saw here where author L name is Eggers and release year is greater than 2010.
We could do three, we could do 20 if we wanted to.
There's probably an upper limit at some point, but typically if you're working with anything more than
really more than three, there's there are other ways of doing it.
Or you may need to change your table structure, which we'll get to once we talk about tables and more
depth than we talk about joins in particular.
But in this case, I could do something like this where author last name is Eggers and release years
2010 and title contains the word novel.
Don't ask why, but let's say we have a needy customer who wants Dave Eggers book only likes his later
work and wants it to be a novel in the way that she knows or he knows it's a novel is that it contains
a word novel in the title.
Well, if we go back to what we had here, let's just copy this and duplicate it and add another condition,
and I'll do them on separate lines now.
So I'll do ampersand for that one, but then I'll do A and D for the last condition, which is title
like.
And all that we want is percent novel, percent semicolon.
So let's try that.
And there we go.
We get a hologram for the king, a novel because.
Okay, first part author name is Eggers.
Release year is greater than 2010.
2012 is.
And title contains the word novel.
So all three are met.
So this is the only result we get.
So that was just to show you that it's not limited to two conditions.
Next up, we're going to talk about another kind of sister or brother operator, which is logical or.