Okay.
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 and big topic.
But before we do that, we have this video which is on something 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, that's something I've been saying a lot in this section.
But if you are, you've probably come across K 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 than five, print one thing.
Otherwise print something else.
Well, the same thing holds true in my SQL.
We use these case statements, which I'll show you in just a moment to do things like this.
So over here I have a genre category or a genre column that is not in our database.
And what I've what I've gone and done is basically said print out title and release here, which are
very straightforward, but then take that release year.
And if it's greater than 2000, genre should be equal to modern lit.
If it's less than 2000, then it should be 20th century lit.
Very, very simple genres that we could easily expand that maybe genre is the wrong word, maybe period
is better or error, but I called it genre.
It's a lot of work to change these slides 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
lit.
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 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.
Okay.
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 here from books.
But then we have this whole case thing for separate lines.
They don't have to be separate, of course, like anything in my SQL, but it makes it a lot easier
to see what's related to what.
First thing that we have.
I'll just is case.
That's the word that says this is a case statement.
It's 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 would 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 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 else as well as the opening case and the closing
end.
So let's step through what this says when released here is greater than or equal to 2000, then return
modern lit.
And when I say return, it's a programming term but basically means spit out.
Think of it as just making genre equal to modern lit.
So that happens when release year is greater than or equal to 2000.
Then we have an else, which is otherwise we're going to return to 20th century lit.
So this is basically saying if it's greater than 2000, if it was released in the 2000, then make genre
modern lit.
Otherwise it was released in the 1900s.
So we're going to print out 20th century lit for 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 lit.
Interpreter Maladies, however, is from 1996.
And 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 as our headers here, we have title release here
and then we have case when released greater than 2000, then modern else blah blah blah, blah, blah.
So you definitely want to use as when you can.
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 a big new idea.
So here's another one.
What I've done here, print it out title and then stock quantity.
And then I'm using stock quantity to then print it.
Graphical representation of how much stock we have.
So one star is things that are lower in stock.
Two star has a medium level of stock in 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's 0 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.
So let me show you.
Select title and stock 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 stock quantity is between zero and 50 and we don't have to use between, we could use what we
saw earlier are logical and we could say when it's greater than or equal to zero and stock quantity
is less than 50, then blah blah blah.
But it's much easier to use between.
So when stock quantity is between zero and 50, then stock is one star.
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 100, this is false.
So it ignores it and it tries this.
Well, it's stock quantity between 51 and 100.
If it is, then stock is going to be two stars.
And if it's greater, let's say it's 153.
Well, this is false and this is false.
So it goes to this last one else.
It has no other choice.
This is otherwise.
If nothing else above was true, well then this one is three stars.
So it 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.
It's 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 as 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 here up here in order to work with it in the case statement.
So let's actually copy this one over and run it.
Make some space.
Here we go.
Okay, so we see the same thing.
But just to show you, I'm not going to use stock quantity up here.
I'm just going to select the title and our stock visualizer and there we go.
So I just wanted to show you, 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 50, what would you 101 and 150, let's say then it's three stars.
Otherwise it's higher than that.
So we'll say four stars.
So let's try that.
And that's actually add stock quantity back in.
It makes sense to be able to see that and let's copy it over and run it this time.
And now we have one star, two star.
We've 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 SQL are separated by commas.
If you have a list of values or a list of anything that will break it.
So no commas there.
It trips me up all the time.
Okay.
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 as 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 stock quantity is greater than 50.
They go in order.
So we don't have to necessarily add this between because we know.
All right, it's definitely greater than 50.
So we can just take advantage of that and just say, okay, 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 100, then that means it's greater than 100.
And so 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 use
between.
And you can see 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 this 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 take some time.
On the next couple of exercises, I'm going to have a few that require to 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.