1
00:00:00,090 --> 00:00:05,970
So greater than less than not equal to and equal to our technically what we call comparison operators.

2
00:00:05,970 --> 00:00:07,670
They compare two values, right?

3
00:00:07,680 --> 00:00:09,570
Is this less than this other thing?

4
00:00:09,570 --> 00:00:11,310
Is this not equal to the other thing?

5
00:00:11,310 --> 00:00:13,140
Is this equal greater and so on.

6
00:00:13,230 --> 00:00:18,060
But now we're actually going to take a look at an example of a logical operator.

7
00:00:18,060 --> 00:00:23,100
Now, if you have any experience with other programming languages, you probably are familiar with logical

8
00:00:23,100 --> 00:00:25,680
and in logical or logical not.

9
00:00:25,680 --> 00:00:28,440
But if you're not, don't worry at all.

10
00:00:28,440 --> 00:00:30,000
It's simple enough to pick up.

11
00:00:30,000 --> 00:00:36,480
And it is one of the most useful pieces of my SQL syntax when writing complicated queries.

12
00:00:36,480 --> 00:00:38,460
So we'll take it step by step from the beginning.

13
00:00:38,460 --> 00:00:44,100
Imagine that I wanted to write a query to select all the books written by Dev Dave Eggers that were

14
00:00:44,100 --> 00:00:46,440
published after the year 2010.

15
00:00:46,800 --> 00:00:51,390
Well, there's two basic pieces of information here that we're filtering by.

16
00:00:51,390 --> 00:00:57,390
We're selecting by author where author is equal to Dave Eggers or just Eggers, maybe for last name.

17
00:00:57,840 --> 00:01:05,610
And then secondarily, we're also finding books released after 2010 so we can write those two queries

18
00:01:05,610 --> 00:01:07,290
individually, right?

19
00:01:07,290 --> 00:01:12,660
We can select the books written by Dave Eggers, select star from books where author last name is equal

20
00:01:12,660 --> 00:01:17,910
to Eggers, and we can select the books published after the year 2010.

21
00:01:17,910 --> 00:01:21,270
Select star from books we released here is greater than 2010.

22
00:01:22,360 --> 00:01:27,340
And if I ran this here we can see there's three Dave Eggers books.

23
00:01:27,340 --> 00:01:34,330
And then if I ran this one here to find the books released after 2010, there's five books there.

24
00:01:34,420 --> 00:01:36,730
And what I want is the overlap.

25
00:01:36,970 --> 00:01:43,450
I want the books that were released after 2010 and have author last name of Eggers, which is these

26
00:01:43,450 --> 00:01:47,410
two books here, A Hologram for the King and the Circle.

27
00:01:47,710 --> 00:01:49,290
And that's what this slide shows here.

28
00:01:49,300 --> 00:01:54,010
I want author l name equal to Eggers and released year greater than 2010.

29
00:01:54,040 --> 00:01:55,960
Both of those need to be true.

30
00:01:56,020 --> 00:01:57,490
I want those books.

31
00:01:57,490 --> 00:02:04,090
And the way that we do that is by using and capital A and doesn't have to be capitalized, but A and

32
00:02:04,120 --> 00:02:09,370
will join together two or more different pieces of logic.

33
00:02:09,370 --> 00:02:11,440
So here's our first condition.

34
00:02:11,530 --> 00:02:17,020
Author Last name is equal to Eggers, and at the same time release year is greater than 2010.

35
00:02:17,560 --> 00:02:25,270
So in order for a book to be included in the results of this query to be selected, this has to come

36
00:02:25,270 --> 00:02:27,790
out to be true and this has to come out to be true.

37
00:02:27,790 --> 00:02:31,060
If only one of them is true, then that book doesn't count.

38
00:02:31,060 --> 00:02:32,170
It's not included.

39
00:02:32,290 --> 00:02:32,680
Right.

40
00:02:32,680 --> 00:02:38,860
If the author last name is Eggers, but release here is 2005, then this thing is not true.

41
00:02:38,860 --> 00:02:40,810
The whole statement is untrue.

42
00:02:40,960 --> 00:02:42,520
So why don't we try writing this?

43
00:02:42,520 --> 00:02:43,120
Let's.

44
00:02:43,300 --> 00:02:45,070
Let's do it in a separate file.

45
00:02:45,070 --> 00:02:50,560
So instead of select star, let's do title author, last name and released here.

46
00:02:51,490 --> 00:02:52,660
From books.

47
00:02:53,350 --> 00:02:58,150
And then I'll do my first piece where let's do it based off of released.

48
00:02:58,150 --> 00:03:02,770
Your first or released year is equal, or rather is greater than 2010.

49
00:03:04,570 --> 00:03:07,210
And then afterwards I'm going to say and.

50
00:03:07,930 --> 00:03:14,290
I also want to know where author your last name is exactly equal to Eggers.

51
00:03:15,520 --> 00:03:16,990
So let's run this query.

52
00:03:17,910 --> 00:03:18,750
And there we go.

53
00:03:19,350 --> 00:03:20,970
Only two rows that matched.

54
00:03:20,970 --> 00:03:25,350
Both of them have author last name of eggers and release year is greater than 2010.

55
00:03:26,170 --> 00:03:27,550
So that's our first example.

56
00:03:28,360 --> 00:03:30,250
Here's a more complicated example.

57
00:03:30,250 --> 00:03:32,510
We can chain on as many as we want.

58
00:03:32,530 --> 00:03:37,660
I can keep going and say, Arthur, last name is Eggers and release year is greater than 2010.

59
00:03:37,660 --> 00:03:41,930
And also the title is like this string.

60
00:03:41,950 --> 00:03:44,140
It includes novel in the middle.

61
00:03:44,140 --> 00:03:48,100
So if I do that, let me just build up this query over here.

62
00:03:49,620 --> 00:03:55,260
So do the same thing as before, But we'll add on another and I can keep going as many times as I want.

63
00:03:55,290 --> 00:04:00,750
Now, all three of these pieces, these three different comparisons all have to be true.

64
00:04:01,170 --> 00:04:02,820
Released Release year Greater than 2010.

65
00:04:03,180 --> 00:04:08,760
The last name is exactly Eggers and the title is like novel, meaning it contains not novel somewhere

66
00:04:08,760 --> 00:04:11,730
in there because we've got the percent signs on either side.

67
00:04:12,120 --> 00:04:14,160
So all three of those have to be true.

68
00:04:14,160 --> 00:04:17,100
And there's only one match a hologram for the King.

69
00:04:17,130 --> 00:04:21,959
It contains novel author Last name Eggers released here is greater than 2010.

70
00:04:21,959 --> 00:04:23,880
2012 is greater than 2010.

71
00:04:24,360 --> 00:04:26,340
So this is incredibly useful.

72
00:04:26,340 --> 00:04:33,150
We can combine all of the different tools to different comparisons that we've learned so far and make

73
00:04:33,150 --> 00:04:34,800
more complicated queries.

74
00:04:35,220 --> 00:04:39,870
Now let's let's actually dial it back for a moment and talk about how and works.

75
00:04:40,020 --> 00:04:41,940
So there's a left and a right side.

76
00:04:43,290 --> 00:04:49,950
And in order for and to evaluate to true this whole thing to be true, the left side has to be true

77
00:04:49,950 --> 00:04:51,930
and the right side has to be true.

78
00:04:52,050 --> 00:04:58,050
Now that makes sense just based off of how we think about this and what we mean generally when we say

79
00:04:58,050 --> 00:04:58,440
the word.

80
00:04:58,440 --> 00:05:04,560
And if I was just reading this release here is greater than 2010 and author last name is Eggers.

81
00:05:04,650 --> 00:05:10,380
We know in order for and to be true, both of those have to be true, right?

82
00:05:10,380 --> 00:05:17,910
If we had a book that was released in 2010, but author last name was Smith, well, the left side would

83
00:05:17,910 --> 00:05:18,750
be true.

84
00:05:19,350 --> 00:05:22,260
Well, actually, it wouldn't be true because I said greater than 2010.

85
00:05:22,260 --> 00:05:23,790
So let's say it was 2011.

86
00:05:23,820 --> 00:05:24,960
Greater than 2010.

87
00:05:24,960 --> 00:05:26,070
So that's true.

88
00:05:26,070 --> 00:05:29,130
But then the right side would be false.

89
00:05:29,130 --> 00:05:32,190
And that means the entire thing is false.

90
00:05:32,340 --> 00:05:35,100
So I can actually prove that this is how it works.

91
00:05:35,100 --> 00:05:38,700
If we just do something simple like this, select, remember how we can do this.

92
00:05:38,700 --> 00:05:40,440
Three is greater than one.

93
00:05:40,620 --> 00:05:43,650
If it is true, we get one as the answer.

94
00:05:43,650 --> 00:05:48,150
And if I did, three is greater than four, we get zero for false.

95
00:05:48,270 --> 00:05:50,280
So I can do this select.

96
00:05:50,280 --> 00:05:52,620
Let's do something we know is going to be true.

97
00:05:53,340 --> 00:05:54,990
One is greater than zero.

98
00:05:55,650 --> 00:05:56,350
That is true.

99
00:05:56,370 --> 00:06:03,000
We get one and let's say eight is equal to eight.

100
00:06:03,450 --> 00:06:04,770
Stupid, but true.

101
00:06:04,980 --> 00:06:07,350
So the whole thing evaluates to true.

102
00:06:07,770 --> 00:06:09,720
But if I change this a little bit.

103
00:06:09,720 --> 00:06:11,690
So now the right side is false.

104
00:06:11,700 --> 00:06:13,500
The whole thing evaluates to false.

105
00:06:13,500 --> 00:06:15,030
Remember, zero means false.

106
00:06:15,270 --> 00:06:16,650
So this is true.

107
00:06:16,680 --> 00:06:17,850
This is false.

108
00:06:17,850 --> 00:06:21,690
When we have and in the middle that means the whole thing is false.

109
00:06:21,720 --> 00:06:26,520
The only way for end to be true is when both sides are true.

110
00:06:26,880 --> 00:06:28,750
So let's try one more example.

111
00:06:28,770 --> 00:06:31,180
Why don't we do something sort of silly?

112
00:06:31,200 --> 00:06:40,050
Let's find the books that have a title that are that is at least, let's say, 15 characters, so long

113
00:06:40,080 --> 00:06:44,160
titles and also a large number of pages.

114
00:06:44,490 --> 00:06:46,330
So we'll start with the first bit.

115
00:06:46,350 --> 00:06:48,510
This is a little trickier than what we've done so far.

116
00:06:48,540 --> 00:06:53,640
How do I know how many characters are in the title and how do I compare that to 15?

117
00:06:53,790 --> 00:06:55,210
Well, I can do something like this.

118
00:06:55,230 --> 00:07:00,030
We're going to use a string function, remember char length.

119
00:07:00,030 --> 00:07:03,060
So we'll do something like select char length.

120
00:07:04,290 --> 00:07:11,940
Of the title from books, but that's just going to print to me or select the value of the character

121
00:07:11,940 --> 00:07:14,100
length, the number of characters in the title.

122
00:07:14,220 --> 00:07:15,600
Maybe not that useful.

123
00:07:16,050 --> 00:07:18,750
What I want to do is select the title.

124
00:07:20,330 --> 00:07:25,640
And then from books where the character length of the title.

125
00:07:27,000 --> 00:07:28,710
Let's say it's greater than 30.

126
00:07:29,340 --> 00:07:34,050
So now I'm getting the titles of the books that have long titles.

127
00:07:34,470 --> 00:07:35,640
And there we are.

128
00:07:35,670 --> 00:07:36,970
So these are long titles.

129
00:07:36,970 --> 00:07:39,070
So we have nothing to do with end at the moment.

130
00:07:39,090 --> 00:07:44,400
But now I want to add in this condition, this additional stipulation that says not only do I want the

131
00:07:44,400 --> 00:07:48,270
ones that have long titles, but they also have to be long books.

132
00:07:49,080 --> 00:07:50,940
So let's just do it on a separate line.

133
00:07:51,960 --> 00:07:56,250
And pages, I don't know, pages is greater than 500.

134
00:07:56,520 --> 00:07:57,550
Do we have any?

135
00:07:57,570 --> 00:07:58,890
I think there's at least one.

136
00:07:59,100 --> 00:08:00,180
There's two of them.

137
00:08:00,270 --> 00:08:04,740
Why don't we also display pages or retrieve pages so we can verify?

138
00:08:06,660 --> 00:08:12,990
Okay, So we've got a title over here, The Amazing Adventures of Kavalier and Clay that has to be greater

139
00:08:12,990 --> 00:08:14,220
than 30 characters.

140
00:08:14,490 --> 00:08:19,890
And again, we're not just directly comparing title, but we're comparing the character length of the

141
00:08:19,890 --> 00:08:20,460
title.

142
00:08:20,700 --> 00:08:24,540
And then pages has to be greater than 500 at the same time.

143
00:08:24,540 --> 00:08:29,130
So there's other books that are more than 500 pages, but they don't have long titles.

144
00:08:29,130 --> 00:08:35,730
And there's other long titles like what we talk About when we talk about love that don't have 500 plus

145
00:08:35,730 --> 00:08:36,419
pages.

146
00:08:36,630 --> 00:08:42,240
So we're getting the intersection, only the rows that have this true and this true.

147
00:08:42,630 --> 00:08:43,530
So that's logical.

148
00:08:43,530 --> 00:08:50,310
And we can use it to create really complex queries and keep adding on if we wanted to, you know, different

149
00:08:50,310 --> 00:08:53,610
conditions and make things more and more specific.

