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 two ampersands.
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 jibberish.
So let's put it in practice let's see what I mean.
So here's an example.
Let's say I want 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 color 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 the logical.
And so here's the first part.
It's like books written by Dave Eggers that look like this.
It's like start from books where author name equals Eggers and then just like books published after
the year 2010 will look like this.
Select star from books where release year is greater than 2010.
So to put them together we use and.
OK.
So as I mentioned we can either use the word or the text A&E or to ampersands and it looks like this.
So Nexstar from books where author name equals Aggers and released here 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.
Well check this author's last name.
Eggers if it is then it will move on say OK.
And was it released after 2010.
If it wasn't then it discards it and it doesn't like it.
However if both parts are true then we see the results.
So let's try it.
OK so let's start off by selecting the title author Al name and released here working in this document.
We're 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 Al name and or at least year from books where and we're just checking where author
last name equals Aggers.
Just like that there's some in Colin 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 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 was better.
But let's say someone says that.
Well then we would do something like select.
And I'd like to just copy this and change the WHERE clause to where at least here is greater than 2010
which we already know how to do it should we review how this gives us all books released after 2010
and then to combine them is where we use the logical and and
release year greater than 2010.
OK.
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 Aggers case insensitive and really here has to be greater than the year
2010.
And just to show you we can replace this with double ampersands and it works the exact same way.
There we go.
OK.
So that's the basics of and we'll use it all the time.
It's important when you I mean at any time you need to change together things if you're trying to you
know search based off of true to criteria.
So common example might be something like It's like star from products that say we work in a toy store
where Brand equals Lego.
But when you're on a store off online store often there's like an option that will say you know show
stock items or don't show out-of-stock items.
So we kid do you think 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.
X is active user or something like that.
OK.
So the last thing that I'll address here is if we go back to the sort of problem set things like 1 less
than 5 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's 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 and take this one is one less than five.
Yes that is true.
Is 7 equal to 9.
No that's false.
And that immediately nullifies 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 force.
OK.
Just like before.
I mean 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 don't.
Go ahead give them a shot and I'll be back.
All right what 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 negative 10 greater than negative 20.
Is that true.
Yes.
It's larger than negative 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 to Yep we get one.
OK.
Moving on we have.
Negative 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 negative 40 less than or equal to
zero.
That's true.
It's smaller than zero.
And I use the English version A&E 10 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 it's 50 for less than or equal to 54.
That's true.
Fifty four is equal to 54 and is equal to a upper case.
Laura could say you cold upper case 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.
You know where author Al name is Eggers and released years graduate 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 when we talk about tables and more death and we talk about joints in particular.
But in this case I could do something like this where author last name is Aggers 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
to be a novel and the way that she knows or he knows it's novel is that it contains the 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 a nother condition.
I'll do them on separate lines now so I'll do ampersands 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 present novel present semi-colon.
So let's try that and there we go.
We get a hologram for the King novel because OK first part author name is Aggers.
At least here is greater than 2010.
2012 is and Title contains the word novel so all three are met.