1
00:00:00,090 --> 00:00:05,220
Next up, we're going to talk about case statements, which can be a little overwhelming when you look

2
00:00:05,220 --> 00:00:08,010
at the syntax, but they're incredibly useful.

3
00:00:08,310 --> 00:00:12,520
And once you understand how they work, they're very easy to use.

4
00:00:12,540 --> 00:00:20,760
We can use case statements to make decisions about values to have different outcomes based off of values.

5
00:00:20,790 --> 00:00:27,180
An example of this would be something like selecting the title and the release here from books.

6
00:00:27,330 --> 00:00:34,920
But then this third column is a new column I've made up when I was selecting called Genre, and it has

7
00:00:34,920 --> 00:00:39,120
different values depending on the value of release here.

8
00:00:39,360 --> 00:00:44,040
So for every book released after 2000, it says modern lit.

9
00:00:44,670 --> 00:00:51,870
Otherwise, it says 20th century lit for all the 1900 books, 1981, 1945, all that.

10
00:00:52,260 --> 00:00:59,280
So the way that this works is we set up a case statement that says when the release year is greater

11
00:00:59,280 --> 00:01:04,379
than or equal to 2000, then the corresponding value should be modern lit.

12
00:01:05,220 --> 00:01:08,490
Otherwise use 20th century lit.

13
00:01:08,580 --> 00:01:10,310
Now I just breezed through the syntax error.

14
00:01:10,320 --> 00:01:15,450
We're going to type it out, go slowly, and then we have end to end the case.

15
00:01:15,660 --> 00:01:23,100
So all of this code right here is used to generate this genre, modern lit or 20th century lit.

16
00:01:23,220 --> 00:01:25,620
And in this case it's just an either or.

17
00:01:25,980 --> 00:01:29,640
You either are modern lit or you're 20th century lit.

18
00:01:29,640 --> 00:01:35,180
But we can get more complicated, and I'll show you in a moment, but let's try writing this ourself.

19
00:01:35,190 --> 00:01:42,570
So I would start by selecting whatever we want Title released year, and then our next column we're

20
00:01:42,570 --> 00:01:47,160
going to select is going to be the result of a case statement.

21
00:01:47,160 --> 00:01:50,070
So I like to do case and end together.

22
00:01:50,280 --> 00:01:53,010
And then I'll just say from books just to get that out of the way.

23
00:01:53,010 --> 00:01:54,180
So I don't forget that.

24
00:01:54,330 --> 00:02:02,070
And then in here between case and end, so where I put my, when something, then something clauses

25
00:02:02,310 --> 00:02:09,120
so when released year if I'm just doing the example from the slides is greater than or equal to the

26
00:02:09,120 --> 00:02:16,470
year 2000, then the corresponding value for this column we're making should be modern lit.

27
00:02:17,720 --> 00:02:20,680
And then we write else that will be our.

28
00:02:20,690 --> 00:02:25,790
Otherwise we want the value to be what, 20th century lit?

29
00:02:27,930 --> 00:02:29,070
Just like that.

30
00:02:29,280 --> 00:02:33,060
And then I can give it an alias as genre.

31
00:02:33,060 --> 00:02:37,230
If I don't do that, the name of that field is going to be this massive thing.

32
00:02:37,230 --> 00:02:40,650
So I'm going to give it an alias there and let's try running it.

33
00:02:41,550 --> 00:02:46,560
And then we go For all the books greater than release, you're greater than or equal to 2000.

34
00:02:46,560 --> 00:02:53,220
We get Modern lights, everything in the nineties, eighties and whatever before 2000 is 20th century

35
00:02:53,220 --> 00:02:53,590
lit.

36
00:02:53,730 --> 00:02:56,040
So that's our first example of a case statement.

37
00:02:56,040 --> 00:02:56,910
Here's another one.

38
00:02:56,910 --> 00:02:59,490
This is a little bit more complicated here.

39
00:02:59,490 --> 00:03:05,490
We've got a stock rating, a visualization of how much we have left in stock of each book.

40
00:03:06,000 --> 00:03:07,980
Remember, we have a stock quantity.

41
00:03:08,130 --> 00:03:16,020
So what I've done here is use the between operator to write a when, then when stock quantity is between

42
00:03:16,020 --> 00:03:24,900
zero and 50, then do a single star and then we can add a second case of when, then when stock quantity

43
00:03:24,900 --> 00:03:29,790
is between 51 and 100, then do this and we can keep going.

44
00:03:29,790 --> 00:03:31,710
So why don't we try recreating that one?

45
00:03:32,850 --> 00:03:34,290
We'll do the same thing.

46
00:03:35,130 --> 00:03:41,820
Select title and then stock quantity from books.

47
00:03:42,530 --> 00:03:45,180
And then our next column after stock quantity.

48
00:03:45,200 --> 00:03:50,120
Don't forget you need a comma, just as if you were selecting another column will be a case.

49
00:03:52,060 --> 00:03:56,230
End and we'll say when stuck.

50
00:03:57,510 --> 00:04:02,400
Quantity is between zero.

51
00:04:02,400 --> 00:04:06,840
And we could even get more detail, let's say between zero and 20.

52
00:04:08,040 --> 00:04:11,580
I don't know if we have any that go that low between zero and 20.

53
00:04:11,610 --> 00:04:12,150
Do we?

54
00:04:13,800 --> 00:04:14,220
All right.

55
00:04:14,220 --> 00:04:16,810
Maybe we'll say between zero and and 40.

56
00:04:16,829 --> 00:04:17,120
I don't know.

57
00:04:17,130 --> 00:04:20,910
We'll figure out some range when we're between zero and 40, then.

58
00:04:21,120 --> 00:04:24,030
The value for this field will be a single star.

59
00:04:24,930 --> 00:04:32,460
And then I'm just going to duplicate this a couple of times when stock quantity is between 41 and let's

60
00:04:32,460 --> 00:04:38,640
say these aren't going to be super consistent intervals, but we'll say 70, we'll do two stars.

61
00:04:38,760 --> 00:04:44,100
And then if we're between 71 and 100, we'll do three stars.

62
00:04:44,430 --> 00:04:55,770
And then if you're between 101 and 140, then we'll do four stars and then else will do five stars.

63
00:04:57,870 --> 00:05:00,570
So it's kind of long, definitely.

64
00:05:00,810 --> 00:05:05,400
But this is powerful that we can assign these different values conditionally.

65
00:05:05,400 --> 00:05:08,350
So one, two, three or four or five stars.

66
00:05:08,370 --> 00:05:14,220
Now, it's really important to know with case statements, only one of these is ever going to run.

67
00:05:14,220 --> 00:05:18,690
Meaning if this is true, then all of this is done.

68
00:05:18,990 --> 00:05:25,170
Now, if this was false and this was false and then this one is true, we end up with three stars and

69
00:05:25,170 --> 00:05:25,800
then that's it.

70
00:05:25,800 --> 00:05:26,550
Nothing else.

71
00:05:26,550 --> 00:05:29,190
We only get one value out.

72
00:05:29,190 --> 00:05:34,530
And then, of course, if none of these are true, then we rely on that else.

73
00:05:34,530 --> 00:05:36,690
That's the catch all otherwise.

74
00:05:36,960 --> 00:05:42,750
And then I'll call this as I don't know what we call it in the example stock.

75
00:05:42,750 --> 00:05:43,260
Sure.

76
00:05:44,130 --> 00:05:45,600
So let's try running this.

77
00:05:47,580 --> 00:05:50,430
Oh, and I got to make sure I don't have commas there.

78
00:05:51,090 --> 00:05:53,220
These are all going to be for one column.

79
00:05:53,250 --> 00:05:54,600
It's an easy mistake to make.

80
00:05:54,630 --> 00:06:00,000
Also, if you really want to format this, you could make it look nice and indent it properly.

81
00:06:00,360 --> 00:06:06,060
Sure, I click that beautify button, I'm going to paste it in or run it from the workbench or from

82
00:06:06,060 --> 00:06:07,320
whatever tool we're using.

83
00:06:07,320 --> 00:06:08,340
And here we go.

84
00:06:08,430 --> 00:06:11,040
We see one star for The Namesake.

85
00:06:11,040 --> 00:06:17,970
We only have 32 copies, but we have five stars for Lincoln in the Bardo because we have 1000 copies,

86
00:06:17,970 --> 00:06:20,610
two stars for Amazing Adventures of Kavalier and Clay.

87
00:06:20,610 --> 00:06:22,170
We have 68 copies.

88
00:06:22,620 --> 00:06:28,710
So there's actually another way we could have rewritten this that is even a little more succinct compared

89
00:06:28,710 --> 00:06:34,460
to all of these betweens because my SQL will go in order evaluating these.

90
00:06:34,470 --> 00:06:35,550
Is this true?

91
00:06:35,550 --> 00:06:37,410
And if it is true, then it's done.

92
00:06:37,410 --> 00:06:39,660
If it's not true, then it moves on.

93
00:06:39,690 --> 00:06:40,590
Is this one true?

94
00:06:40,590 --> 00:06:41,140
No.

95
00:06:41,160 --> 00:06:41,400
Okay.

96
00:06:41,400 --> 00:06:42,240
How about this one?

97
00:06:42,240 --> 00:06:43,800
And it goes in order.

98
00:06:43,800 --> 00:06:50,580
We can take advantage of that order and write just a simple less than or greater than, and use the

99
00:06:50,580 --> 00:06:53,910
order to act as our between logic.

100
00:06:53,910 --> 00:06:55,110
So I'll show you what I mean.

101
00:06:55,110 --> 00:07:02,670
I could rewrite this like this stock quantity is less than or equal to 40.

102
00:07:03,840 --> 00:07:07,710
If that's true, then we'll use one star.

103
00:07:08,490 --> 00:07:15,390
Otherwise we'll try this next example, which is if you're less than or equal to 70, we'll do two stars.

104
00:07:15,750 --> 00:07:17,940
Otherwise less than or equal to what we did.

105
00:07:17,940 --> 00:07:18,690
100.

106
00:07:20,310 --> 00:07:22,290
Next, we'll try 100.

107
00:07:22,290 --> 00:07:25,530
And I already forgot what it was, 140.

108
00:07:26,130 --> 00:07:31,480
So again, this relies on the fact that only one of these is ever going to be true.

109
00:07:31,500 --> 00:07:34,890
So if we started here and this is true, great, we're done.

110
00:07:34,890 --> 00:07:38,040
But let's say that the value is actually 80.

111
00:07:38,280 --> 00:07:39,720
So stock quantity is 80.

112
00:07:39,750 --> 00:07:40,920
This is false.

113
00:07:40,920 --> 00:07:46,350
But if we make it to this point, Mike knows for sure that it's not less than 40.

114
00:07:46,530 --> 00:07:48,090
So is it less than 70?

115
00:07:48,120 --> 00:07:48,860
No.

116
00:07:48,870 --> 00:07:49,680
What about here?

117
00:07:49,680 --> 00:07:51,300
Is it less than 100?

118
00:07:51,330 --> 00:07:53,380
Yes, it's 80.

119
00:07:53,400 --> 00:07:56,040
So that means we're between 70 and 100.

120
00:07:56,070 --> 00:08:00,330
We've set up this sort of bracketing system that just relies on the order.

121
00:08:00,630 --> 00:08:05,130
So it's going to work just the same as the first example.

122
00:08:05,160 --> 00:08:06,890
It's just a little less clunky.

123
00:08:06,900 --> 00:08:10,380
We don't have to use between X and Y all over the place.

124
00:08:10,800 --> 00:08:11,770
So that's pretty much it.

125
00:08:11,790 --> 00:08:14,730
For K statements, the syntax can be hard to remember.

126
00:08:14,730 --> 00:08:17,670
It's clunky, it's a lot, but it's also really powerful.

127
00:08:17,670 --> 00:08:21,900
So there's no shame in just looking at the slides or going to the docs copy and pasting other people's

128
00:08:21,900 --> 00:08:24,430
examples and adapting them to your use case.

129
00:08:24,450 --> 00:08:31,830
Just remember your when then pairs go within case and end and then after when.

130
00:08:31,830 --> 00:08:37,950
Then as many as we want we can have our otherwise clause the else and you can have as many one then

131
00:08:38,010 --> 00:08:44,700
as you want, but only one of them is ever going to be or only one value is what will be returned from

132
00:08:44,700 --> 00:08:46,710
the case statement for each row.