So we now have one more video one new bit of information before we move on to the exercises and wrap
up this section and go on to join tables.
Very important big topic.
But before we do that we have this video which is on this thing called Case statements.
This is a pretty well exciting is relative.
This is very different than things that we've seen in this section.
It has to do with logic but it's kind of on a bigger scale.
It allows us to make decisions.
So if you're familiar with programming languages it's something I've been saying a lot in this section
but if you are you've probably come across case statements or conditional statements which allow you
to add logic in to do things like conditionally print something out.
It's a very simple example but if a number is greater than some than 5 print one thing.
Otherwise print something else.
Well the same thing holds true in my essay.
Q Well we use these case statements all of which I'll show you in just a moment.
To do things like this.
So over here I have a genre category Herge on a column that is not in our database.
And what I've what I've gone and done it's basically said print out title and released year which are
very straightforward but then take that release year.
And if it's greater than 2000 genre should be equal to modern one if it's less than 2000 than it should
be 20th century but very very simple genres that we could easily expand that or maybe genres and wrong
word maybe period is better or era but I call that genre.
It's a lot of work to change sides now so we'll just call it that.
But again what what's happening here is we have a conditional statement.
This depends on this.
So if we change Release Year of the namesake to be 1990 This would also change to be 20th century.
So it's adding in logic a new layer of complexity that we haven't seen how to do to make these decisions.
And we're not limited just to of course we could break it up into a whole bunch of things and I'll show
you a few more examples.
But let's first identify how this works.
And I warn you there's kind of a lot of syntax.
OK so here it is.
This is what results in what we just saw.
So the beginning and end are the same.
Select title and release year from books.
But then we have this whole case thing four separate lines.
They don't have to be separate Of course like anything in my school.
But it makes it a lot easier to see what's related to what first thing that we have in this case.
That's a word that says this is a case that is going to consist of a couple of points of logic that
will spit one thing out and that thing ends right here.
And then I have an AS.
So I don't have to have as genre.
Remember that's called an alias.
But if I left that off it will look really really gross when it printed out in the table because instead
of genre here it would say this entire thing which would make the table gigantic It would go way off
the page and would ruin our formatting.
So usually we want to use as.
But then the more important part is what we have right here.
So Case statements take the following form.
There's always a when at least one.
And then there's an ounce as well as the opening case in the closing end.
So let's step through what this says when released year is greater than or equal to 2000 then return
modern.
But when I say return it's a programming term but basically means spit out.
Think of it as just making new genre equal to modern.
So that happens when released here is greater than or equal to 2000.
Then we have an else which is otherwise we're going to return 20th century late.
So this is basically saying if it's greater than 2000 if it was released in the 2000s then make genre
modern.
Otherwise it was released in the 9900.
So we're going to print out 20th century for a genre and that's how we end up with this.
So we have this bit of conditional logic where genre changes depending on the value of release year.
So let's actually try this now.
Run it and you can see we get the namesake.
2003 is modern but interpretor maladies however is from 1996.
So we get 20th century lit and I will show you if I get rid of the as
it's a nightmare because if you look at what we have in our headers here we have title release year
and then we have case when released greater than 2000 than modern else blah blah blah blah blah.
So you definitely want to use as one you can.
OK so that's one example.
But I do think this one benefits from a couple because it's a bit weird especially if you don't come
from a programming background so don't panic if you're not comfortable with this type of logic.
But if you are it's very similar if you're not.
It's kind of a new big new idea.
So here's another one.
What I've done here printed out title and then start quantity and then I'm using stock quantity to then
print a graphical representation of how much stock we have.
So one star is things that are lower in-stock two star has a medium of stock and three stars means we
have a lot of stock and I can't remember the exact numerical breakdown where that happens.
I believe if it is your to 50 copies we have one star.
If it's 50 to 100 We have two stars.
And then if it's 100 or more we have three stars.
So it looks like this let me show you select title and start quantity from books again beginning and
end is the same.
Then we have our case and then the end of the case with this alias stock.
And so that's just going to give us stock right here.
And then the important part.
So when the stock quantity is between 0 and 50 and we don't have to choose between we could use you
know what we saw earlier are logical and say when it is greater than or equal to zero and star quantity
is less than 50 then blah blah blah.
But it's much easier to use between.
When stock quantity is between zero and 50 then stock it won't start.
So that's how we end up with one star.
But if that's not true then it moves on to the next one.
So if stock quantity is one hundred.
This is false.
So it ignores it and it tries this well stock quantity between 50 one in 100.
If it is then stock is going to be two stars.
And if it's greater I would say it's 153.
Well this is false.
And this is false.
So it goes to this last one else has no other choices.
Otherwise.
If nothing else above was true well then this one is three stars.
So goes through and it does that for every single one.
So we end up with one two and three stars.
So hopefully you're getting the idea here.
Pretty powerful.
It allows us to make decisions or do have different outcomes depending on our data.
One thing I definitely need to point out because I can see this is being confusing.
You don't have to have stock quantity printed out in order to reference it.
In your case statement or back here I don't have to work with Release Year appear in order to work with
it.
In that case statement.
So let's actually copy this one over and run it
make some space.
Here we go.
OK.
So we see the same thing.
But just to show you are not going to use stock quantity appear just you know like the title and our
stock visualizer and there we go.
So I just wanted to show you don't have to have stock quantity referenced in order to use it inside
of your case.
And another point I should make is you're not limited in this number of how many conditions we could
have.
So if we wanted to we could have another one here.
When stock quantity is between what would you 101 and 150 let's say then it's three stars.
Otherwise it's higher than that.
So I will say four stars.
So let's try that.
And that's actually ad stock quantity back.
And it makes sense to be able to see that and what's copied over and run it this time.
And now we have one star two star got four stars for 154 and three stars for 104.
So my point is that you're not limited.
Another thing that that trips me up personally is I'm inclined to put commas here just because most
things in my school are separated by comments if you have a list of values or a list of anything that
will break it.
So no commas there.
Troopship all the time.
OK.
So one other thing I did want to address is that we could make it shorter if we don't use between.
And the way that we can do that is by taking advantage of the execution order of things.
So let me show you.
Here's another the same exact outcome is this line of code where we have one two and three stars.
However it's much shorter because what's happening here.
So if it's less than or equal to 50 then we're done.
We get one star if that's not true that means already it's greater than 50.
So if this line is running we know for sure start quantity is greater than 50.
They go in order.
So we don't have to necessarily add this between because we know it's definitely greater than 50 so
we can just take advantage of it and just say OK well is it less than or equal to 100 which is the equivalent
of saying Is it between 50 and 100 if it is then it's two stars.
But if it's not less than or equal to one hundred then that means it's greater than 100.
And so if we get three stars.
So this is more just showing you a way of cleaning it up if you wanted to.
So you don't have to choose between.
And you can see if we get the exact same outcome one two and three stars we don't have to use those
longer between statements although some people would argue between is easier to read.
It's more semantic more understandable when you look at it rather than working with this definitely
up for debate.
So that wraps up the section on case statements which hopefully you see what I mean when I say they
are both very useful but possibly a little intimidating especially syntactically there's a lot there.
So definitely you know take some time on the next couple of exercises.
We're going to have a few that require Do you require you to write Case statements.
Hopefully you'll get some practice there and you'll see how powerful they are.
With that said we're moving on to exercises.