1
00:00:00,210 --> 00:00:00,690
Okay.

2
00:00:00,690 --> 00:00:06,660
So we now have one more video, one new bit of information before we move on to the exercises and wrap

3
00:00:06,660 --> 00:00:09,720
up this section and go on to join tables.

4
00:00:09,720 --> 00:00:11,310
Very important and big topic.

5
00:00:11,580 --> 00:00:15,610
But before we do that, we have this video which is on something called case statements.

6
00:00:15,630 --> 00:00:18,330
This is a pretty well exciting is relative.

7
00:00:18,360 --> 00:00:22,410
This is very different than things that we've seen in this section.

8
00:00:22,440 --> 00:00:26,880
It has to do with logic, but it's kind of on a bigger scale.

9
00:00:26,910 --> 00:00:29,050
It allows us to make decisions.

10
00:00:29,070 --> 00:00:33,600
So if you're familiar with programming languages, that's something I've been saying a lot in this section.

11
00:00:33,600 --> 00:00:38,400
But if you are, you've probably come across K statements or conditional statements which allow you

12
00:00:38,400 --> 00:00:43,360
to add logic in to do things like conditionally print something out.

13
00:00:43,380 --> 00:00:49,140
It's a very simple example, but if a number is greater than than five, print one thing.

14
00:00:50,130 --> 00:00:51,870
Otherwise print something else.

15
00:00:52,200 --> 00:00:54,500
Well, the same thing holds true in my SQL.

16
00:00:54,510 --> 00:00:59,160
We use these case statements, which I'll show you in just a moment to do things like this.

17
00:01:00,360 --> 00:01:06,390
So over here I have a genre category or a genre column that is not in our database.

18
00:01:07,080 --> 00:01:12,150
And what I've what I've gone and done is basically said print out title and release here, which are

19
00:01:12,150 --> 00:01:16,550
very straightforward, but then take that release year.

20
00:01:16,560 --> 00:01:21,900
And if it's greater than 2000, genre should be equal to modern lit.

21
00:01:22,410 --> 00:01:26,450
If it's less than 2000, then it should be 20th century lit.

22
00:01:26,460 --> 00:01:34,590
Very, very simple genres that we could easily expand that maybe genre is the wrong word, maybe period

23
00:01:34,590 --> 00:01:37,920
is better or error, but I called it genre.

24
00:01:37,950 --> 00:01:42,000
It's a lot of work to change these slides now, so we'll just call it that.

25
00:01:42,180 --> 00:01:47,010
But again, what what's happening here is we have a conditional statement.

26
00:01:47,040 --> 00:01:50,130
This depends on this.

27
00:01:50,460 --> 00:01:57,270
So if we change release Year of the Namesake to be 1990, this would also change to be 20th century

28
00:01:57,270 --> 00:01:57,660
lit.

29
00:01:58,350 --> 00:02:04,800
So it's adding in logic a new layer of complexity that we haven't seen how to do to make these decisions.

30
00:02:04,800 --> 00:02:09,090
And we're not limited just to to of course, we could break it up into a whole bunch of things and I'll

31
00:02:09,090 --> 00:02:13,500
show you a few more examples, but let's first identify how this works.

32
00:02:13,500 --> 00:02:15,990
And I warn you, there's kind of a lot of syntax.

33
00:02:17,230 --> 00:02:17,490
Okay.

34
00:02:17,560 --> 00:02:18,640
So here it is.

35
00:02:18,970 --> 00:02:21,040
This is what results in what we just saw.

36
00:02:21,580 --> 00:02:27,100
So the beginning and end are the same select title and release here from books.

37
00:02:27,580 --> 00:02:30,700
But then we have this whole case thing for separate lines.

38
00:02:30,730 --> 00:02:34,960
They don't have to be separate, of course, like anything in my SQL, but it makes it a lot easier

39
00:02:34,960 --> 00:02:36,970
to see what's related to what.

40
00:02:37,690 --> 00:02:38,950
First thing that we have.

41
00:02:38,990 --> 00:02:40,270
I'll just is case.

42
00:02:40,270 --> 00:02:42,460
That's the word that says this is a case statement.

43
00:02:42,460 --> 00:02:51,220
It's going to consist of a couple of points of logic that will spit one thing out and that thing ends

44
00:02:51,220 --> 00:02:52,000
right here.

45
00:02:52,180 --> 00:02:55,810
And then I have an as so I don't have to have as genre.

46
00:02:55,990 --> 00:03:01,540
Remember that's called an alias, but if I left that off, it would look really, really gross when

47
00:03:01,540 --> 00:03:08,620
it printed out in the table, because instead of genre here, it would say this entire thing, which

48
00:03:08,620 --> 00:03:10,330
would make the table gigantic.

49
00:03:10,330 --> 00:03:13,900
It would go way off the page and would ruin our formatting.

50
00:03:13,990 --> 00:03:16,090
So usually want to use as.

51
00:03:16,780 --> 00:03:19,990
But then the more important part is what we have right here.

52
00:03:20,410 --> 00:03:23,230
So case statements take the following form.

53
00:03:23,230 --> 00:03:30,070
There's always a when at least one, and then there's an else as well as the opening case and the closing

54
00:03:30,070 --> 00:03:30,490
end.

55
00:03:30,490 --> 00:03:38,740
So let's step through what this says when released here is greater than or equal to 2000, then return

56
00:03:38,740 --> 00:03:39,910
modern lit.

57
00:03:39,910 --> 00:03:43,900
And when I say return, it's a programming term but basically means spit out.

58
00:03:45,190 --> 00:03:49,180
Think of it as just making genre equal to modern lit.

59
00:03:49,750 --> 00:03:53,470
So that happens when release year is greater than or equal to 2000.

60
00:03:55,290 --> 00:04:01,050
Then we have an else, which is otherwise we're going to return to 20th century lit.

61
00:04:01,380 --> 00:04:07,350
So this is basically saying if it's greater than 2000, if it was released in the 2000, then make genre

62
00:04:07,350 --> 00:04:08,280
modern lit.

63
00:04:08,460 --> 00:04:10,590
Otherwise it was released in the 1900s.

64
00:04:10,590 --> 00:04:15,900
So we're going to print out 20th century lit for genre and that's how we end up with this.

65
00:04:16,320 --> 00:04:22,620
So we have this bit of conditional logic where genre changes depending on the value of release year.

66
00:04:23,520 --> 00:04:25,680
So let's actually try this now.

67
00:04:29,200 --> 00:04:29,920
Run it.

68
00:04:30,850 --> 00:04:33,580
And you can see we get the namesake.

69
00:04:33,610 --> 00:04:35,410
2003 is modern lit.

70
00:04:36,400 --> 00:04:39,040
Interpreter Maladies, however, is from 1996.

71
00:04:39,310 --> 00:04:45,160
And so we get 20th century lit and I will show you if I get rid of the as.

72
00:04:48,440 --> 00:04:54,650
It's a nightmare because if you look at what we have as our headers here, we have title release here

73
00:04:54,650 --> 00:04:59,240
and then we have case when released greater than 2000, then modern else blah blah blah, blah, blah.

74
00:04:59,270 --> 00:05:02,180
So you definitely want to use as when you can.

75
00:05:03,590 --> 00:05:04,970
So that's one example.

76
00:05:05,300 --> 00:05:11,150
But I do think this one benefits from a couple because it's a bit weird, especially if you don't come

77
00:05:11,150 --> 00:05:12,260
from a programming background.

78
00:05:12,260 --> 00:05:13,160
So don't panic.

79
00:05:13,520 --> 00:05:15,770
If you're not comfortable with this type of logic.

80
00:05:15,770 --> 00:05:18,080
But if you are, it's very similar.

81
00:05:18,410 --> 00:05:21,130
If you're not, it's kind of a new a big new idea.

82
00:05:21,140 --> 00:05:22,280
So here's another one.

83
00:05:22,700 --> 00:05:26,690
What I've done here, print it out title and then stock quantity.

84
00:05:27,260 --> 00:05:31,040
And then I'm using stock quantity to then print it.

85
00:05:31,040 --> 00:05:33,870
Graphical representation of how much stock we have.

86
00:05:33,890 --> 00:05:37,460
So one star is things that are lower in stock.

87
00:05:37,460 --> 00:05:41,990
Two star has a medium level of stock in three stars means we have a lot of stock.

88
00:05:42,470 --> 00:05:46,040
And I can't remember the exact numerical breakdown where that happens.

89
00:05:46,040 --> 00:05:49,370
I believe if it's 0 to 50 copies, we have one star.

90
00:05:49,550 --> 00:05:51,740
If it's 50 to 100, we have two stars.

91
00:05:51,740 --> 00:05:54,500
And then if it's 100 or more, we have three stars.

92
00:05:55,760 --> 00:05:57,530
So it looks like this.

93
00:05:58,970 --> 00:05:59,870
So let me show you.

94
00:06:00,200 --> 00:06:03,480
Select title and stock quantity from books.

95
00:06:03,500 --> 00:06:05,450
Again, beginning and end is the same.

96
00:06:05,690 --> 00:06:11,060
Then we have our case and then the end of the case with this alias stock.

97
00:06:11,300 --> 00:06:13,520
And so that's just going to give us stock right here.

98
00:06:14,480 --> 00:06:15,680
And then the important part.

99
00:06:16,400 --> 00:06:24,170
So when stock quantity is between zero and 50 and we don't have to use between, we could use what we

100
00:06:24,170 --> 00:06:30,440
saw earlier are logical and we could say when it's greater than or equal to zero and stock quantity

101
00:06:30,440 --> 00:06:33,200
is less than 50, then blah blah blah.

102
00:06:34,010 --> 00:06:35,960
But it's much easier to use between.

103
00:06:36,260 --> 00:06:41,930
So when stock quantity is between zero and 50, then stock is one star.

104
00:06:43,190 --> 00:06:45,170
So that's how we end up with one star.

105
00:06:46,610 --> 00:06:49,550
But if that's not true, then it moves on to the next one.

106
00:06:49,880 --> 00:06:53,330
So if stock quantity is 100, this is false.

107
00:06:53,750 --> 00:06:56,510
So it ignores it and it tries this.

108
00:06:56,510 --> 00:06:59,540
Well, it's stock quantity between 51 and 100.

109
00:06:59,810 --> 00:07:03,890
If it is, then stock is going to be two stars.

110
00:07:04,370 --> 00:07:07,220
And if it's greater, let's say it's 153.

111
00:07:07,520 --> 00:07:09,680
Well, this is false and this is false.

112
00:07:10,010 --> 00:07:12,200
So it goes to this last one else.

113
00:07:12,230 --> 00:07:13,190
It has no other choice.

114
00:07:13,190 --> 00:07:14,150
This is otherwise.

115
00:07:14,150 --> 00:07:18,980
If nothing else above was true, well then this one is three stars.

116
00:07:20,060 --> 00:07:22,340
So it goes through and it does that for every single one.

117
00:07:22,640 --> 00:07:25,430
So we end up with one, two and three stars.

118
00:07:25,430 --> 00:07:26,840
So hopefully you're getting the idea here.

119
00:07:26,840 --> 00:07:27,890
It's pretty powerful.

120
00:07:27,890 --> 00:07:33,050
It allows us to make decisions or do have different outcomes depending on our data.

121
00:07:33,860 --> 00:07:39,380
One thing I definitely need to point out, because I can see this as being confusing you don't have

122
00:07:39,380 --> 00:07:46,130
to have stock quantity printed out in order to reference it in your case statement or back here, I

123
00:07:46,130 --> 00:07:51,050
don't have to work with release here up here in order to work with it in the case statement.

124
00:07:52,130 --> 00:07:54,710
So let's actually copy this one over and run it.

125
00:07:57,700 --> 00:07:58,690
Make some space.

126
00:07:59,530 --> 00:08:00,190
Here we go.

127
00:08:01,020 --> 00:08:03,160
Okay, so we see the same thing.

128
00:08:04,250 --> 00:08:07,610
But just to show you, I'm not going to use stock quantity up here.

129
00:08:07,760 --> 00:08:14,300
I'm just going to select the title and our stock visualizer and there we go.

130
00:08:14,690 --> 00:08:20,480
So I just wanted to show you, you don't have to have stock quantity referenced in order to use it inside

131
00:08:20,480 --> 00:08:21,320
of your case.

132
00:08:22,130 --> 00:08:26,450
And another point I should make is you're not limited in this number of how many conditions we could

133
00:08:26,450 --> 00:08:26,850
have.

134
00:08:26,870 --> 00:08:29,590
So if we wanted to, we could have another one here.

135
00:08:29,600 --> 00:08:39,320
When stock quantity is between 50, what would you 101 and 150, let's say then it's three stars.

136
00:08:40,789 --> 00:08:42,200
Otherwise it's higher than that.

137
00:08:42,200 --> 00:08:43,549
So we'll say four stars.

138
00:08:43,789 --> 00:08:45,140
So let's try that.

139
00:08:47,790 --> 00:08:49,660
And that's actually add stock quantity back in.

140
00:08:49,680 --> 00:08:56,040
It makes sense to be able to see that and let's copy it over and run it this time.

141
00:08:57,450 --> 00:09:00,390
And now we have one star, two star.

142
00:09:00,420 --> 00:09:05,250
We've got four stars for 154 and three stars for 104.

143
00:09:06,150 --> 00:09:08,430
So my point is that you're not limited.

144
00:09:08,460 --> 00:09:14,820
Another thing that that trips me up personally is I'm inclined to put commas here just because most

145
00:09:14,820 --> 00:09:16,830
things in my SQL are separated by commas.

146
00:09:16,830 --> 00:09:20,970
If you have a list of values or a list of anything that will break it.

147
00:09:20,970 --> 00:09:22,350
So no commas there.

148
00:09:22,800 --> 00:09:24,270
It trips me up all the time.

149
00:09:25,110 --> 00:09:25,510
Okay.

150
00:09:26,340 --> 00:09:31,410
So one other thing I did want to address is that we could make it shorter if we don't use between.

151
00:09:31,590 --> 00:09:36,150
And the way that we can do that is by taking advantage of the execution order of things.

152
00:09:36,600 --> 00:09:38,250
So let me show you.

153
00:09:38,400 --> 00:09:44,850
Here's another the same exact outcome as this line of code where we have one, two and three stars.

154
00:09:45,420 --> 00:09:48,480
However, it's much shorter because what's happening here.

155
00:09:48,480 --> 00:09:51,270
So if it's less than or equal to 50, then we're done.

156
00:09:51,270 --> 00:09:52,410
We get one star.

157
00:09:52,950 --> 00:09:56,790
If that's not true, that means already it's greater than 50.

158
00:09:56,910 --> 00:10:02,700
So if this line is running, we know for sure stock quantity is greater than 50.

159
00:10:02,700 --> 00:10:03,840
They go in order.

160
00:10:04,110 --> 00:10:08,700
So we don't have to necessarily add this between because we know.

161
00:10:08,700 --> 00:10:10,650
All right, it's definitely greater than 50.

162
00:10:11,100 --> 00:10:17,010
So we can just take advantage of that and just say, okay, well, is it less than or equal to 100,

163
00:10:17,010 --> 00:10:20,160
which is the equivalent of saying is it between 50 and 100?

164
00:10:20,880 --> 00:10:22,830
If it is, then it's two stars.

165
00:10:22,830 --> 00:10:27,570
But if it's not less than or equal to 100, then that means it's greater than 100.

166
00:10:27,810 --> 00:10:29,430
And so we get three stars.

167
00:10:29,580 --> 00:10:36,180
So this is more just showing you a way of cleaning it up if you wanted to, so you don't have to use

168
00:10:36,180 --> 00:10:36,960
between.

169
00:10:36,960 --> 00:10:40,890
And you can see we get the exact same outcome one, two and three stars.

170
00:10:40,890 --> 00:10:46,650
We don't have to use those longer between statements, although some people would argue between is easier

171
00:10:46,650 --> 00:10:47,130
to read.

172
00:10:47,130 --> 00:10:53,280
It's more semantic, more understandable when you look at it rather than working with this definitely

173
00:10:53,280 --> 00:10:54,060
up for debate.

174
00:10:54,450 --> 00:11:00,720
So that wraps up this section on case statements, which hopefully you see what I mean when I say they

175
00:11:00,720 --> 00:11:05,760
are both very useful but possibly a little intimidating, especially syntactically.

176
00:11:05,760 --> 00:11:06,720
There's a lot there.

177
00:11:06,720 --> 00:11:09,450
So definitely take some time.

178
00:11:09,450 --> 00:11:14,280
On the next couple of exercises, I'm going to have a few that require to require you to write case

179
00:11:14,280 --> 00:11:14,910
statements.

180
00:11:14,910 --> 00:11:18,090
Hopefully you'll get some practice there and you'll see how powerful they are.

181
00:11:18,330 --> 00:11:20,820
With that said, we're moving on to exercises.