Next up, we're going to talk about case statements, which can be a little overwhelming when you look
at the syntax, but they're incredibly useful.
And once you understand how they work, they're very easy to use.
We can use case statements to make decisions about values to have different outcomes based off of values.
An example of this would be something like selecting the title and the release here from books.
But then this third column is a new column I've made up when I was selecting called Genre, and it has
different values depending on the value of release here.
So for every book released after 2000, it says modern lit.
Otherwise, it says 20th century lit for all the 1900 books, 1981, 1945, all that.
So the way that this works is we set up a case statement that says when the release year is greater
than or equal to 2000, then the corresponding value should be modern lit.
Otherwise use 20th century lit.
Now I just breezed through the syntax error.
We're going to type it out, go slowly, and then we have end to end the case.
So all of this code right here is used to generate this genre, modern lit or 20th century lit.
And in this case it's just an either or.
You either are modern lit or you're 20th century lit.
But we can get more complicated, and I'll show you in a moment, but let's try writing this ourself.
So I would start by selecting whatever we want Title released year, and then our next column we're
going to select is going to be the result of a case statement.
So I like to do case and end together.
And then I'll just say from books just to get that out of the way.
So I don't forget that.
And then in here between case and end, so where I put my, when something, then something clauses
so when released year if I'm just doing the example from the slides is greater than or equal to the
year 2000, then the corresponding value for this column we're making should be modern lit.
And then we write else that will be our.
Otherwise we want the value to be what, 20th century lit?
Just like that.
And then I can give it an alias as genre.
If I don't do that, the name of that field is going to be this massive thing.
So I'm going to give it an alias there and let's try running it.
And then we go For all the books greater than release, you're greater than or equal to 2000.
We get Modern lights, everything in the nineties, eighties and whatever before 2000 is 20th century
lit.
So that's our first example of a case statement.
Here's another one.
This is a little bit more complicated here.
We've got a stock rating, a visualization of how much we have left in stock of each book.
Remember, we have a stock quantity.
So what I've done here is use the between operator to write a when, then when stock quantity is between
zero and 50, then do a single star and then we can add a second case of when, then when stock quantity
is between 51 and 100, then do this and we can keep going.
So why don't we try recreating that one?
We'll do the same thing.
Select title and then stock quantity from books.
And then our next column after stock quantity.
Don't forget you need a comma, just as if you were selecting another column will be a case.
End and we'll say when stuck.
Quantity is between zero.
And we could even get more detail, let's say between zero and 20.
I don't know if we have any that go that low between zero and 20.
Do we?
All right.
Maybe we'll say between zero and and 40.
I don't know.
We'll figure out some range when we're between zero and 40, then.
The value for this field will be a single star.
And then I'm just going to duplicate this a couple of times when stock quantity is between 41 and let's
say these aren't going to be super consistent intervals, but we'll say 70, we'll do two stars.
And then if we're between 71 and 100, we'll do three stars.
And then if you're between 101 and 140, then we'll do four stars and then else will do five stars.
So it's kind of long, definitely.
But this is powerful that we can assign these different values conditionally.
So one, two, three or four or five stars.
Now, it's really important to know with case statements, only one of these is ever going to run.
Meaning if this is true, then all of this is done.
Now, if this was false and this was false and then this one is true, we end up with three stars and
then that's it.
Nothing else.
We only get one value out.
And then, of course, if none of these are true, then we rely on that else.
That's the catch all otherwise.
And then I'll call this as I don't know what we call it in the example stock.
Sure.
So let's try running this.
Oh, and I got to make sure I don't have commas there.
These are all going to be for one column.
It's an easy mistake to make.
Also, if you really want to format this, you could make it look nice and indent it properly.
Sure, I click that beautify button, I'm going to paste it in or run it from the workbench or from
whatever tool we're using.
And here we go.
We see one star for The Namesake.
We only have 32 copies, but we have five stars for Lincoln in the Bardo because we have 1000 copies,
two stars for Amazing Adventures of Kavalier and Clay.
We have 68 copies.
So there's actually another way we could have rewritten this that is even a little more succinct compared
to all of these betweens because my SQL will go in order evaluating these.
Is this true?
And if it is true, then it's done.
If it's not true, then it moves on.
Is this one true?
No.
Okay.
How about this one?
And it goes in order.
We can take advantage of that order and write just a simple less than or greater than, and use the
order to act as our between logic.
So I'll show you what I mean.
I could rewrite this like this stock quantity is less than or equal to 40.
If that's true, then we'll use one star.
Otherwise we'll try this next example, which is if you're less than or equal to 70, we'll do two stars.
Otherwise less than or equal to what we did.
100.
Next, we'll try 100.
And I already forgot what it was, 140.
So again, this relies on the fact that only one of these is ever going to be true.
So if we started here and this is true, great, we're done.
But let's say that the value is actually 80.
So stock quantity is 80.
This is false.
But if we make it to this point, Mike knows for sure that it's not less than 40.
So is it less than 70?
No.
What about here?
Is it less than 100?
Yes, it's 80.
So that means we're between 70 and 100.
We've set up this sort of bracketing system that just relies on the order.
So it's going to work just the same as the first example.
It's just a little less clunky.
We don't have to use between X and Y all over the place.
So that's pretty much it.
For K statements, the syntax can be hard to remember.
It's clunky, it's a lot, but it's also really powerful.
So there's no shame in just looking at the slides or going to the docs copy and pasting other people's
examples and adapting them to your use case.
Just remember your when then pairs go within case and end and then after when.
Then as many as we want we can have our otherwise clause the else and you can have as many one then
as you want, but only one of them is ever going to be or only one value is what will be returned from
the case statement for each row.