1
00:00:01,230 --> 00:00:08,940
Comparison operators are fairly straightforward, but matching the operators, for example, like and

2
00:00:08,940 --> 00:00:12,690
I like deserve additional explanation.

3
00:00:12,690 --> 00:00:19,080
So both let you find the variety of values that include characters matching a specified pattern, which

4
00:00:19,080 --> 00:00:26,370
is handy if you don't know exactly what you are searching for or if you are rooting out misspelled words.

5
00:00:26,370 --> 00:00:37,650
So to use like and I like you specify a pattern to match using one or both of the symbols, which is

6
00:00:37,650 --> 00:00:42,570
the first symbol is present sign and the second symbol first symbol is present sign.

7
00:00:42,570 --> 00:00:46,350
And the second symbol is a wildcard.

8
00:00:46,380 --> 00:00:52,170
Matching just one character and present side means the matching one or more characters.

9
00:00:52,170 --> 00:01:02,400
So, for example, if you are trying to find a baker we will use here from teachers like Be.

10
00:01:05,770 --> 00:01:07,420
We can also use like.

11
00:01:09,350 --> 00:01:10,070
A key.

12
00:01:12,300 --> 00:01:13,800
Or we can also use.

13
00:01:17,180 --> 00:01:17,780
Acre.

14
00:01:18,050 --> 00:01:18,650
Just.

15
00:01:18,810 --> 00:01:21,230
Underscore acre.

16
00:01:22,180 --> 00:01:28,060
Or we can also use the pay underscore error here.

17
00:01:28,790 --> 00:01:37,490
So the difference is the lag operator, which is a part of the ANSI SQL standard, is case sensitive.

18
00:01:37,520 --> 00:01:44,600
The I like operator, which is a PostgreSQL only implementation, is case insensitive.

19
00:01:44,630 --> 00:01:47,990
So here we will use this.

20
00:01:50,270 --> 00:01:51,050
Explanation.

21
00:01:51,050 --> 00:01:52,310
So select.

22
00:01:52,730 --> 00:01:55,520
Select First name.

23
00:01:56,360 --> 00:01:57,830
From teachers.

24
00:01:59,780 --> 00:02:04,460
Teachers wear first name first.

25
00:02:05,910 --> 00:02:08,700
Name like Sam.

26
00:02:09,790 --> 00:02:12,760
Sum and percentage character.

27
00:02:12,970 --> 00:02:19,570
And here, this is another query here, and we will select the first name.

28
00:02:21,500 --> 00:02:29,780
So actually, let's first run this or actually first name, write this and then run write features.

29
00:02:33,550 --> 00:02:34,510
A teachers.

30
00:02:35,920 --> 00:02:39,220
And their first name.

31
00:02:40,410 --> 00:02:41,280
I like.

32
00:02:42,550 --> 00:02:43,870
We can also do right like that.

33
00:02:43,900 --> 00:02:44,680
I like.

34
00:02:45,410 --> 00:02:45,920
Some.

35
00:02:48,530 --> 00:02:49,190
Run this.

36
00:02:53,190 --> 00:02:53,670
That's it.

37
00:02:54,180 --> 00:02:55,140
Let's delete this firstly.

38
00:02:56,130 --> 00:02:57,300
We'll get same result.

39
00:02:57,330 --> 00:02:57,930
No.

40
00:02:58,730 --> 00:03:06,290
So over the years I have gravitated toward using I like and wild card operators, but to make sure I'm

41
00:03:06,290 --> 00:03:08,870
not inadvertently excluding results from searches.

42
00:03:08,870 --> 00:03:11,270
So particularly when vetting data.

43
00:03:11,270 --> 00:03:18,950
So I don't ensure that whoever typed the names of people, places or products or other proper nouns

44
00:03:19,190 --> 00:03:20,810
always remembers to capitalize them.

45
00:03:20,810 --> 00:03:27,740
And if one of the goals of interviewing data is to understand its quality using a case, insensitive

46
00:03:27,740 --> 00:03:31,280
search will help you to find variations.

47
00:03:31,280 --> 00:03:33,620
So in this case, let's actually delete this.

48
00:03:35,970 --> 00:03:36,900
Here it is.

49
00:03:37,230 --> 00:03:39,390
And here, as you can see, we are getting results.

50
00:03:39,390 --> 00:03:41,730
But with, like, without.

51
00:03:41,760 --> 00:03:42,510
I like.

52
00:03:46,060 --> 00:03:47,560
We will get nothing.

53
00:03:47,830 --> 00:03:48,640
That's it.

54
00:03:49,590 --> 00:03:56,700
So because like and like search for patterns performance on a large databases can slow so we can improve

55
00:03:56,700 --> 00:04:01,530
using indexes which I will cover in next lectures.

56
00:04:01,620 --> 00:04:09,450
So you can also combine operators with and and or here.

57
00:04:09,630 --> 00:04:16,200
So comparison operators become even more useful when we combine them, right?

58
00:04:16,200 --> 00:04:20,070
So to do this, we connect them using a logical operators and.

59
00:04:21,480 --> 00:04:22,260
And or.

60
00:04:23,670 --> 00:04:27,140
Or along with if needed, parentheses.

61
00:04:27,150 --> 00:04:31,860
So here we will use the select Select here.

62
00:04:31,860 --> 00:04:36,720
All from teachers where.

63
00:04:37,810 --> 00:04:38,370
Where?

64
00:04:38,380 --> 00:04:40,700
School here.

65
00:04:40,720 --> 00:04:43,000
Myers Middle.

66
00:04:44,000 --> 00:04:46,130
Middle school.

67
00:04:49,410 --> 00:04:56,250
And and salary or salary is less than 40,000.

68
00:04:58,660 --> 00:05:01,570
And we can also use another query here.

69
00:05:02,930 --> 00:05:05,270
So first, actually, let's run this.

70
00:05:05,560 --> 00:05:06,980
This is the first run.

71
00:05:08,980 --> 00:05:11,290
You can also do select.

72
00:05:13,720 --> 00:05:18,490
Select all from teachers.

73
00:05:20,360 --> 00:05:22,280
And where?

74
00:05:22,490 --> 00:05:23,720
Last name?

75
00:05:24,380 --> 00:05:25,430
Last.

76
00:05:26,370 --> 00:05:28,680
Last name is.

77
00:05:30,290 --> 00:05:31,280
The call.

78
00:05:32,430 --> 00:05:34,930
Paul and or.

79
00:05:35,580 --> 00:05:36,240
Or not.

80
00:05:36,240 --> 00:05:38,250
And last name.

81
00:05:39,970 --> 00:05:40,870
His brush.

82
00:05:42,350 --> 00:05:44,680
Let's write another query here.

83
00:05:44,690 --> 00:05:45,830
Select.

84
00:05:47,770 --> 00:05:50,140
From teachers.

85
00:05:51,190 --> 00:05:53,320
Where school.

86
00:05:56,400 --> 00:05:57,420
If the.

87
00:05:59,020 --> 00:05:59,970
Rules.

88
00:06:03,380 --> 00:06:06,470
Age high school and we will use and.

89
00:06:08,980 --> 00:06:10,240
Salary.

90
00:06:11,930 --> 00:06:14,450
Less than 38,000.

91
00:06:15,290 --> 00:06:15,920
Or.

92
00:06:17,910 --> 00:06:20,660
Salary is greater than 40,000.

93
00:06:23,090 --> 00:06:23,480
That's it.

94
00:06:23,630 --> 00:06:25,460
So we will copy this and we will.

95
00:06:27,170 --> 00:06:28,940
We will export these one by one.

96
00:06:29,780 --> 00:06:30,590
Let's export.

97
00:06:30,680 --> 00:06:31,900
Let's run first the.

98
00:06:33,740 --> 00:06:34,160
This.

99
00:06:35,050 --> 00:06:37,810
As you can see, we got this here.

100
00:06:37,900 --> 00:06:43,960
So the first query here, we write, we excluded users and.

101
00:06:45,570 --> 00:06:47,220
Uh, in the where clause.

102
00:06:48,630 --> 00:06:56,550
To find teachers who work at Myers Middle School and have a salary less than $40,000.

103
00:06:56,910 --> 00:07:05,250
So because we connect the two conditions using and both must be true for a row to meet the criteria

104
00:07:05,250 --> 00:07:10,100
in the where clause and be returned in the query result.

105
00:07:10,110 --> 00:07:13,260
And now let's run the second example.

106
00:07:15,860 --> 00:07:18,230
Yeah, let's run the second example.

107
00:07:18,230 --> 00:07:20,690
And with this second example.

108
00:07:20,840 --> 00:07:21,690
That's it.

109
00:07:21,710 --> 00:07:22,670
So.

110
00:07:23,680 --> 00:07:32,210
Uh, we are using or to search for any teacher whose last name matches Cole or brush.

111
00:07:32,230 --> 00:07:39,640
So when we connect finding, when we connect conditions using or only one of the conditions must be

112
00:07:39,640 --> 00:07:44,200
true for a row to meet the criteria of the where clause.

113
00:07:44,770 --> 00:07:48,250
So now let's get to the final example here.

114
00:07:51,230 --> 00:08:01,940
So the final example looks for a teachers at Roosevelt whose salaries are either less than 38,000 or

115
00:08:01,940 --> 00:08:07,890
greater than 40,000 when and when we place the statements inside parentheses.

116
00:08:07,910 --> 00:08:12,140
These are evaluated as a group before being combined with other criteria.

117
00:08:12,140 --> 00:08:19,250
So in this case, the school name must be exactly if the Roosevelt High School and the salary must be

118
00:08:19,250 --> 00:08:28,820
either must be either less or higher than specified for a row to meet the criteria of where class.

119
00:08:28,820 --> 00:08:38,780
So if we use both and and or in a class but don't use any parentheses, the database will evaluate the

120
00:08:38,810 --> 00:08:42,170
end condition first and then the or condition.

121
00:08:42,170 --> 00:08:48,800
So in the in the final example, in this example, that means we'd see different result if we omitted

122
00:08:48,800 --> 00:08:49,730
parentheses.

123
00:08:49,730 --> 00:08:57,390
So here the database would look for rows where the school name is Roosevelt and the salary is less than

124
00:08:57,390 --> 00:08:58,260
38,000.

125
00:08:59,330 --> 00:08:59,840
That's it.

126
00:09:00,050 --> 00:09:03,770
As you can see here, we see if the Roosevelt Middle School here.