1
00:00:00,300 --> 00:00:00,930
All right.

2
00:00:00,930 --> 00:00:07,650
So we have one more video here or at least one more tool for the toolbox before we get to the exercise

3
00:00:07,650 --> 00:00:08,850
at the end of this section.

4
00:00:09,000 --> 00:00:11,400
And that tool is something called like.

5
00:00:11,640 --> 00:00:14,010
And I'll start off by saying, like, it's very useful.

6
00:00:14,220 --> 00:00:19,680
It basically allows us to perform better searching of our data.

7
00:00:19,800 --> 00:00:25,950
So if you think of when we saw how to use where, where would allow us to find exact matches so I could

8
00:00:25,950 --> 00:00:33,720
do things like select star from books where title is exactly Harry Potter and the Sorcerer's Stone.

9
00:00:34,230 --> 00:00:36,960
But what if I wanted to select all Harry Potter books?

10
00:00:37,500 --> 00:00:43,860
I would want to do something like Select Star from books where title contains Harry Potter or starts

11
00:00:43,860 --> 00:00:46,140
with Harry Potter or something like that.

12
00:00:46,140 --> 00:00:47,940
And we don't have a way of doing that yet.

13
00:00:48,030 --> 00:00:53,790
Until now with like so like allows us to do things like this.

14
00:00:54,540 --> 00:00:59,970
Imagine someone comes into your bookstore if we have a bookshop and they say, There's a book I'm looking

15
00:00:59,970 --> 00:01:03,300
for, but I can't remember the title.

16
00:01:04,120 --> 00:01:12,220
I think the author's first name is David or maybe it's Dan or or Dave, and you could go over to your

17
00:01:12,220 --> 00:01:18,250
system as a really helpful bookstore employee and you could just search for, let's say, books written

18
00:01:18,250 --> 00:01:23,920
by authors whose first name starts with DA, which I guess is a very broad category.

19
00:01:23,920 --> 00:01:30,340
But you could still do that if you searched for authors at like a Barnes Noble or a popular bookshop

20
00:01:30,340 --> 00:01:31,300
or Amazon.

21
00:01:31,510 --> 00:01:35,290
You can do that type of search and it will work.

22
00:01:35,320 --> 00:01:37,450
It's just not that common.

23
00:01:38,320 --> 00:01:39,880
But we could do the same thing for books.

24
00:01:39,880 --> 00:01:44,890
Like I said, Harry Potter is a good example where we want to search for all the Harry Potter books,

25
00:01:44,890 --> 00:01:49,300
not just the exact one word for word matched or character for character matched.

26
00:01:49,300 --> 00:01:51,010
So that's where where comes in.

27
00:01:51,010 --> 00:01:54,010
Now, I warn you, it's going to look a little bit odd when I show it to you.

28
00:01:54,700 --> 00:01:56,290
But that's okay.

29
00:01:56,560 --> 00:01:58,090
It's not that complicated.

30
00:01:58,090 --> 00:01:59,920
There's just some weird symbols here.

31
00:02:00,820 --> 00:02:04,810
So here's an example of using like where?

32
00:02:04,810 --> 00:02:11,020
So this is ignoring the first part of the query, the select star from or the update or the delete or

33
00:02:11,020 --> 00:02:11,890
whatever it is.

34
00:02:12,160 --> 00:02:23,500
So blank where author f name like and then in quotes I have this percent sign d a percent sign.

35
00:02:24,570 --> 00:02:30,180
So I'll tell you, first of all, what this is going to do is find where the author's first name has

36
00:02:30,180 --> 00:02:31,950
the letters, DEA in it.

37
00:02:33,090 --> 00:02:37,230
And these little things here, those percentage symbols kind of like that animation there.

38
00:02:37,650 --> 00:02:40,050
Those are known as wild cards.

39
00:02:40,050 --> 00:02:42,150
So that's basically the way that we specify.

40
00:02:42,150 --> 00:02:47,100
We want to find author first names where there's something, anything at all.

41
00:02:47,100 --> 00:02:47,790
It's a wild card.

42
00:02:47,790 --> 00:02:54,930
So any characters and then these two exact characters, characters, DEA and then anything afterwards.

43
00:02:55,650 --> 00:03:02,850
So it's basically a way of saying whatever, blank, anything, DEA and then whatever, anything else

44
00:03:02,850 --> 00:03:03,690
afterwards.

45
00:03:04,530 --> 00:03:10,440
And if we try using this right now, actually, you'll see what happens if we go over here and we do

46
00:03:10,440 --> 00:03:15,300
a select and let's just do a title and author f name.

47
00:03:16,480 --> 00:03:27,010
From books where and then we'll do author f name like and then this is where we need those wild card

48
00:03:27,010 --> 00:03:30,670
symbols and we'll put D a in between.

49
00:03:30,670 --> 00:03:35,120
So once again, this is saying anything followed by D A Exactly.

50
00:03:35,140 --> 00:03:42,130
D So it can't be D and then s and then A It has to be de and then anything afterwards.

51
00:03:42,610 --> 00:03:44,410
So if I hit enter now.

52
00:03:46,030 --> 00:03:47,370
You'll see that it works.

53
00:03:47,380 --> 00:03:48,580
We get these authors.

54
00:03:48,580 --> 00:03:52,540
Dave, Dave, Dave, David, David, Dan and Frida.

55
00:03:53,290 --> 00:03:54,200
And you might notice.

56
00:03:54,220 --> 00:03:56,440
Well, Frida, that doesn't start with DA.

57
00:03:56,440 --> 00:03:57,760
And you would be right.

58
00:03:58,240 --> 00:04:04,810
It ends with DA, but it still meets this queries or it still meets the conditions for this query,

59
00:04:04,810 --> 00:04:07,900
which is anything da anything.

60
00:04:08,500 --> 00:04:19,990
So in this case, it's free, ID or free is meeting this and then we have DA and then this wild card.

61
00:04:19,990 --> 00:04:22,060
There's nothing afterwards and that's perfectly fine.

62
00:04:22,900 --> 00:04:27,310
This is not saying there has to be something after the and the or something before.

63
00:04:27,340 --> 00:04:32,590
As you can see, we're matching D a V, so there's nothing before it.

64
00:04:32,590 --> 00:04:33,520
That's fine.

65
00:04:33,550 --> 00:04:36,460
Then Da and then V afterwards.

66
00:04:36,730 --> 00:04:39,040
So basically this is all about pattern matching.

67
00:04:39,340 --> 00:04:46,960
And if we wanted to refine this so that we only get author first names that start exactly with Dave

68
00:04:47,290 --> 00:04:50,710
or sorry exactly with DA, we can do it like this.

69
00:04:50,980 --> 00:04:56,740
All we do is leave off that first wild card, that first percentage sign, and that says, I want to

70
00:04:56,740 --> 00:05:02,380
find author first names where they start with DA and then anything afterwards.

71
00:05:02,680 --> 00:05:08,230
So it could be Dana, David, Dave, Dan, Darwin, whatever.

72
00:05:08,530 --> 00:05:12,940
So if we just make that one small change and we delete that percentage sign.

73
00:05:14,660 --> 00:05:16,280
You can see Frida is gone.

74
00:05:16,290 --> 00:05:20,660
I'm sorry, Frida, because Frida no longer fits the pattern that we're matching.

75
00:05:21,660 --> 00:05:24,150
Also, it's important to note that it is case insensitive.

76
00:05:24,150 --> 00:05:29,880
As you can see, this is lowercase D and a and it still matches all these uppercase D's and lowercase

77
00:05:29,880 --> 00:05:30,360
A's.

78
00:05:31,230 --> 00:05:36,900
So if I wanted to do something slightly different, let's say search all books where the title has the

79
00:05:36,900 --> 00:05:38,880
word the somewhere in there.

80
00:05:39,690 --> 00:05:40,530
It's pretty simple.

81
00:05:40,530 --> 00:05:47,340
It's just a select title from books where title like.

82
00:05:47,910 --> 00:05:50,010
And if I just say the.

83
00:05:51,150 --> 00:05:54,060
That's only going to give me books where it's exactly the.

84
00:05:54,360 --> 00:05:55,590
And that's not what we want.

85
00:05:55,590 --> 00:05:58,830
So we want something followed by the.

86
00:05:59,700 --> 00:06:04,860
And if we just leave it like this, that's not going to give us anything, because this is specifying

87
00:06:04,860 --> 00:06:09,070
we want books where the title has anything and then ends with the letters THG.

88
00:06:09,450 --> 00:06:10,770
That's not really what we want.

89
00:06:11,580 --> 00:06:14,730
So we'll add another wild card at the end.

90
00:06:14,880 --> 00:06:22,080
And now that gives us all the books that have a title that has something, then tag and then something

91
00:06:22,080 --> 00:06:22,950
else afterwards.

92
00:06:23,490 --> 00:06:27,360
So you can see we get The Namesake a hologram for the King, The Circle, The Amazing Adventures of

93
00:06:27,360 --> 00:06:28,290
Kavalier and Clay.

94
00:06:28,320 --> 00:06:30,870
Consider the Lobster and then Lincoln in the Bardo.

95
00:06:31,310 --> 00:06:32,530
Really good book, by the way.

96
00:06:32,550 --> 00:06:34,050
I know I've said that like ten times.

97
00:06:34,440 --> 00:06:35,190
It's a good book.

98
00:06:36,030 --> 00:06:38,760
So that's the basics of using.

99
00:06:38,760 --> 00:06:42,410
Like, these percentage signs are very common.

100
00:06:42,420 --> 00:06:46,360
I know it looks kind of odd, but this is really, really important.

101
00:06:46,380 --> 00:06:48,350
Think of when you're searching.

102
00:06:48,360 --> 00:06:49,200
I don't know.

103
00:06:49,860 --> 00:06:50,610
You're on.

104
00:06:50,610 --> 00:06:55,530
If you use Reddit, if you're ever searching for things on Reddit behind the scenes, like is being

105
00:06:55,530 --> 00:07:02,550
employed or if you're searching on whatever site you search on, there's some form of variation of this,

106
00:07:02,550 --> 00:07:07,800
like going on where your query, whatever you search for is not being.

107
00:07:07,830 --> 00:07:09,030
It's not only matching.

108
00:07:09,030 --> 00:07:10,130
Exactly.

109
00:07:10,140 --> 00:07:16,410
So when I search for an Amazon, I type in let's let's just go with my let's say Lord of the Rings.

110
00:07:16,410 --> 00:07:17,490
I search Lord of the Rings.

111
00:07:17,490 --> 00:07:21,960
It's not only going to give me books that have a title exactly of Lord of the Rings.

112
00:07:21,990 --> 00:07:25,050
It's going to give me books that have Lord of the Rings in the title somewhere.

113
00:07:25,200 --> 00:07:30,660
Obviously, their search is much, much more complicated, where it also searches things like through

114
00:07:30,660 --> 00:07:33,900
comments and reviews and related books.

115
00:07:33,900 --> 00:07:37,800
And there's this whole complicated graph network of how the search works.

116
00:07:37,800 --> 00:07:42,450
But the core idea, the simplest search that you could implement uses like.