1
00:00:00,090 --> 00:00:05,310
So in the last video at the end, I mentioned that we're about to move on to bigger and better operators.

2
00:00:05,310 --> 00:00:06,660
And this is the first of those.

3
00:00:06,660 --> 00:00:07,650
It's called logical.

4
00:00:07,650 --> 00:00:11,610
And you'll also see it as just to ampersand.

5
00:00:12,420 --> 00:00:16,460
So what it does, it's sort of a step up in operators.

6
00:00:16,470 --> 00:00:22,200
It allows us to actually chain together multiple pieces of logical information, multiple operators

7
00:00:22,200 --> 00:00:22,860
together.

8
00:00:22,860 --> 00:00:27,300
And basically it necessitates that all things are true.

9
00:00:27,330 --> 00:00:28,740
All conditions are true.

10
00:00:28,770 --> 00:00:30,060
Now, that sounds like gibberish.

11
00:00:30,060 --> 00:00:31,530
So let's put it in practice.

12
00:00:31,530 --> 00:00:32,400
Let's see what I mean.

13
00:00:32,400 --> 00:00:33,540
So here's an example.

14
00:00:34,170 --> 00:00:39,780
Let's say I wanted to do something like this select books written by Dave Eggers that are also published

15
00:00:39,780 --> 00:00:41,220
after the year 2010.

16
00:00:42,090 --> 00:00:49,440
So individually these are each straightforward tasks a colour coded them to show you select books written

17
00:00:49,440 --> 00:00:50,100
by Dave Eggers.

18
00:00:50,100 --> 00:00:51,120
We know how to do that.

19
00:00:51,360 --> 00:00:58,410
We also know how to do select all books published after the year 2010 using greater than what we just

20
00:00:58,410 --> 00:00:58,890
saw.

21
00:00:58,980 --> 00:01:02,730
However, to put these together, we need to use logical.

22
00:01:02,760 --> 00:01:04,739
And so here's the first part.

23
00:01:04,769 --> 00:01:11,760
Select books written by Dave Eggers would look like this select star from books where author name equals

24
00:01:11,760 --> 00:01:12,450
Eggers.

25
00:01:13,840 --> 00:01:20,210
And then just like books published after the year 2010, it look like this select star from books where

26
00:01:20,210 --> 00:01:22,660
it released year is greater than 2010.

27
00:01:23,440 --> 00:01:32,220
So to put them together we use and so as I mentioned, we can either use the word or the text A and

28
00:01:32,380 --> 00:01:34,090
or two ampersand.

29
00:01:35,110 --> 00:01:42,670
And it looks like this select star from books where author l name equals eggers and released year is

30
00:01:42,670 --> 00:01:43,840
greater than 2010.

31
00:01:44,260 --> 00:01:45,070
And that's it.

32
00:01:45,070 --> 00:01:50,350
So basically we just take the two where clauses and shove them together with the word and.

33
00:01:50,800 --> 00:01:55,540
And what this means is that both parts have to be true in order for it to be selected.

34
00:01:55,720 --> 00:01:57,250
So it will start it.

35
00:01:57,250 --> 00:01:57,820
We'll check.

36
00:01:57,820 --> 00:01:59,530
Is this author's last name Eggers?

37
00:02:00,010 --> 00:02:02,950
If it is, then it will move on and say, okay.

38
00:02:02,950 --> 00:02:05,420
And was it released after 2010?

39
00:02:05,440 --> 00:02:08,560
If it wasn't, then it discards it and it doesn't select it.

40
00:02:08,590 --> 00:02:12,280
However, if both parts are true, then we see the results.

41
00:02:12,280 --> 00:02:13,330
So let's try it.

42
00:02:14,470 --> 00:02:21,790
Okay, so let's start off by selecting title author l name and release here.

43
00:02:22,120 --> 00:02:24,040
And I'm just working in this document.

44
00:02:24,040 --> 00:02:29,080
I'm just going to copy and paste in, but I'm just putting a record of it here to make it easier for

45
00:02:29,080 --> 00:02:31,120
me to edit as I go through.

46
00:02:31,780 --> 00:02:38,650
So select title author l name and released here from books where and we're just checking where author

47
00:02:38,650 --> 00:02:41,470
last name equals Eggers.

48
00:02:41,500 --> 00:02:43,870
Just like that, there's semicolon.

49
00:02:46,170 --> 00:02:51,570
And you can see we have these three hologram for the King, The Circle, and my favorite heartbreaking

50
00:02:51,570 --> 00:02:52,950
work of staggering genius.

51
00:02:53,460 --> 00:02:54,900
And then we have for least a year.

52
00:02:54,930 --> 00:02:59,460
So for some reason, someone comes into our bookstore and says, you know, I really like Dave Eggers,

53
00:02:59,460 --> 00:03:03,470
but I only like his later stuff, which I would disagree with.

54
00:03:03,480 --> 00:03:09,360
I think his earlier stuff is better, but let's say someone says that, well, then we would do something

55
00:03:09,360 --> 00:03:12,330
like Select and I'll actually just copy this.

56
00:03:14,320 --> 00:03:20,260
And change the where clause to where released year is greater than 2010, which we already know how

57
00:03:20,260 --> 00:03:20,830
to do.

58
00:03:21,310 --> 00:03:22,390
This should be review.

59
00:03:22,900 --> 00:03:31,360
Now this gives us all books released after 2010 and then to combine them is where we use logical and.

60
00:03:32,530 --> 00:03:33,490
And.

61
00:03:36,370 --> 00:03:39,280
Release this year greater than 2010 OC.

62
00:03:39,990 --> 00:03:42,000
So let's check that it works.

63
00:03:43,020 --> 00:03:48,120
You can see we only get two results because both of these things have to match.

64
00:03:48,120 --> 00:03:54,030
So author has to be exactly Eggers case insensitive and release here has to be greater than the year

65
00:03:54,030 --> 00:03:54,840
2010.

66
00:03:56,250 --> 00:04:02,430
And just to show you, we can replace this with double ampersand and it works the exact same way.

67
00:04:03,530 --> 00:04:04,310
There we go.

68
00:04:05,450 --> 00:04:09,230
So that's the basics of and we'll use it all the time.

69
00:04:09,470 --> 00:04:15,200
It's important when I mean, it's any time you need to chain together things if you're trying to search

70
00:04:15,200 --> 00:04:17,050
based off of two criteria.

71
00:04:17,060 --> 00:04:21,480
So a common example might be something like select star from product.

72
00:04:21,500 --> 00:04:26,570
Let's say we work in a toy store where brand equals Lego.

73
00:04:27,530 --> 00:04:35,360
But when you're on a store online store, often there's like an option that will say, show out of stock

74
00:04:35,360 --> 00:04:37,880
items or don't show out-of-stock items.

75
00:04:37,880 --> 00:04:49,400
So we could do something behind the scenes, like where brand is Lego and in stock equals true.

76
00:04:50,370 --> 00:04:55,350
That way, we wouldn't be showing the customer any Lego products that are also out of stock, which

77
00:04:55,350 --> 00:04:57,210
is frustrating when you see that.

78
00:04:57,840 --> 00:05:03,270
So that's a pretty common use cases when you're working with a value, a boolean, yes or no value like

79
00:05:03,270 --> 00:05:08,040
in stock or is active user or something like that.

80
00:05:09,210 --> 00:05:16,530
So the last thing that I'll address here is if we go back to the sort of problem, set things like one

81
00:05:16,530 --> 00:05:17,820
less than five.

82
00:05:18,900 --> 00:05:22,470
But rather than just doing one on their own, I'm going to give you a couple here.

83
00:05:22,680 --> 00:05:27,060
We're going to chain them together with and and I'll give you a first example here.

84
00:05:27,060 --> 00:05:29,400
So one greater than five and seven.

85
00:05:31,150 --> 00:05:32,950
Equal to nine.

86
00:05:34,150 --> 00:05:36,520
So what does this whole thing evaluate to?

87
00:05:39,100 --> 00:05:40,880
Remember, both sides have to be true.

88
00:05:40,900 --> 00:05:42,190
So let's take either side.

89
00:05:42,190 --> 00:05:43,090
We'll take this one.

90
00:05:43,090 --> 00:05:44,620
Is one less than five?

91
00:05:44,650 --> 00:05:46,120
Yes, that is true.

92
00:05:47,170 --> 00:05:49,150
Is seven equal to nine?

93
00:05:49,360 --> 00:05:51,060
No, that's false.

94
00:05:51,070 --> 00:05:53,650
And that immediately nullifies or falsifies.

95
00:05:53,650 --> 00:05:55,300
Excuse me, the entire thing.

96
00:05:55,480 --> 00:05:57,250
Both sides have to be true.

97
00:05:57,700 --> 00:05:58,960
Just to double check.

98
00:06:00,010 --> 00:06:01,030
Let's give it a shot.

99
00:06:02,020 --> 00:06:02,920
Select.

100
00:06:04,900 --> 00:06:06,940
And you can see zero false.

101
00:06:07,630 --> 00:06:12,580
So just like before, I'm going to take a moment, fill out a couple of problems, and hopefully you'll

102
00:06:12,580 --> 00:06:13,930
stick around and give them a shot.

103
00:06:14,860 --> 00:06:15,930
All right, I'm back.

104
00:06:15,940 --> 00:06:19,570
So I have three simple problems, hopefully simple that I typed out.

105
00:06:20,110 --> 00:06:20,580
Go ahead.

106
00:06:20,620 --> 00:06:23,050
Give them a shot and I'll be back.

107
00:06:24,460 --> 00:06:24,850
All right.

108
00:06:24,850 --> 00:06:25,340
What do you know?

109
00:06:25,360 --> 00:06:26,010
I'm back.

110
00:06:26,020 --> 00:06:27,610
So let's go over these quickly.

111
00:06:28,000 --> 00:06:30,800
The first one, remember, both sides have to be true.

112
00:06:30,820 --> 00:06:32,540
So start with the first one.

113
00:06:32,560 --> 00:06:33,460
Negative ten.

114
00:06:33,460 --> 00:06:34,620
Greater than -20.

115
00:06:34,630 --> 00:06:35,440
Is that true?

116
00:06:35,650 --> 00:06:38,170
Yes, it's larger than -20.

117
00:06:38,770 --> 00:06:42,130
How about this is zero less than or equal to zero?

118
00:06:42,460 --> 00:06:44,730
Yes, because zero is equal to zero.

119
00:06:44,740 --> 00:06:46,910
Therefore, we have true and true.

120
00:06:46,930 --> 00:06:49,450
So this whole thing is true.

121
00:06:50,110 --> 00:06:50,950
Let's try it out.

122
00:06:52,930 --> 00:06:53,380
Yep.

123
00:06:53,380 --> 00:06:54,280
We get one.

124
00:06:54,720 --> 00:06:54,880
Okay.

125
00:06:54,970 --> 00:06:55,780
Moving on.

126
00:06:55,870 --> 00:06:59,670
We have is -40 less than or equal to zero.

127
00:06:59,680 --> 00:07:02,230
And yes, I know I had a negative sign here.

128
00:07:02,230 --> 00:07:02,980
I apologize.

129
00:07:02,980 --> 00:07:04,090
That was an accident.

130
00:07:05,080 --> 00:07:05,890
That's wrong.

131
00:07:06,400 --> 00:07:09,730
So hopefully you didn't spend too much time panicking about that.

132
00:07:09,730 --> 00:07:11,770
Is -40 less than or equal to zero?

133
00:07:11,800 --> 00:07:12,580
That's true.

134
00:07:12,610 --> 00:07:13,840
It's smaller than zero.

135
00:07:14,170 --> 00:07:19,870
And I use the English version A and D ten greater than 40.

136
00:07:19,870 --> 00:07:21,220
Well, that's actually false.

137
00:07:21,790 --> 00:07:24,550
Which makes the whole thing false, unfortunately.

138
00:07:24,700 --> 00:07:25,900
So let's try it.

139
00:07:28,390 --> 00:07:30,430
And we get zero, which means false.

140
00:07:31,150 --> 00:07:34,180
Finally, is 54 less than or equal to 54?

141
00:07:34,210 --> 00:07:35,050
That's true.

142
00:07:35,080 --> 00:07:41,890
54 is equal to 54 and is a equal to a uppercase lowercase a equal to uppercase.

143
00:07:42,250 --> 00:07:44,800
By now you should remember they are equivalent.

144
00:07:44,800 --> 00:07:47,230
So there's true on both sides.

145
00:07:49,020 --> 00:07:50,040
And we get true.

146
00:07:50,670 --> 00:07:51,270
All right.

147
00:07:51,270 --> 00:07:57,210
So the last thing I want to highlight about and is that we're not limited to just two components like

148
00:07:57,210 --> 00:08:02,700
we saw here where author L name is Eggers and release year is greater than 2010.

149
00:08:03,360 --> 00:08:06,390
We could do three, we could do 20 if we wanted to.

150
00:08:06,840 --> 00:08:12,150
There's probably an upper limit at some point, but typically if you're working with anything more than

151
00:08:12,150 --> 00:08:15,390
really more than three, there's there are other ways of doing it.

152
00:08:15,390 --> 00:08:20,460
Or you may need to change your table structure, which we'll get to once we talk about tables and more

153
00:08:20,460 --> 00:08:22,520
depth than we talk about joins in particular.

154
00:08:22,530 --> 00:08:29,820
But in this case, I could do something like this where author last name is Eggers and release years

155
00:08:29,820 --> 00:08:34,289
2010 and title contains the word novel.

156
00:08:35,659 --> 00:08:41,659
Don't ask why, but let's say we have a needy customer who wants Dave Eggers book only likes his later

157
00:08:41,659 --> 00:08:47,330
work and wants it to be a novel in the way that she knows or he knows it's a novel is that it contains

158
00:08:47,330 --> 00:08:48,920
a word novel in the title.

159
00:08:49,160 --> 00:08:57,920
Well, if we go back to what we had here, let's just copy this and duplicate it and add another condition,

160
00:08:59,510 --> 00:09:00,830
and I'll do them on separate lines now.

161
00:09:00,830 --> 00:09:08,240
So I'll do ampersand for that one, but then I'll do A and D for the last condition, which is title

162
00:09:08,390 --> 00:09:09,230
like.

163
00:09:11,150 --> 00:09:17,290
And all that we want is percent novel, percent semicolon.

164
00:09:17,840 --> 00:09:18,950
So let's try that.

165
00:09:19,910 --> 00:09:20,540
And there we go.

166
00:09:20,540 --> 00:09:24,070
We get a hologram for the king, a novel because.

167
00:09:24,470 --> 00:09:27,590
Okay, first part author name is Eggers.

168
00:09:27,590 --> 00:09:29,210
Release year is greater than 2010.

169
00:09:29,210 --> 00:09:30,470
2012 is.

170
00:09:30,590 --> 00:09:32,740
And title contains the word novel.

171
00:09:32,750 --> 00:09:34,250
So all three are met.

172
00:09:34,520 --> 00:09:36,320
So this is the only result we get.

173
00:09:36,800 --> 00:09:40,490
So that was just to show you that it's not limited to two conditions.

174
00:09:41,000 --> 00:09:46,940
Next up, we're going to talk about another kind of sister or brother operator, which is logical or.