1
00:00:04,880 --> 00:00:10,970
If you don't already have SQL Server Management Studio open, go ahead and open it now and select the

2
00:00:10,970 --> 00:00:14,840
Battery Co database and we'll open a new query window.

3
00:00:14,840 --> 00:00:21,230
In this lecture we're going to look at using the in between and light clauses with the select statement

4
00:00:21,230 --> 00:00:23,780
to further refine our search parameters.

5
00:00:24,780 --> 00:00:30,330
The in clause determines if a value is included in a list of values between.

6
00:00:30,330 --> 00:00:37,290
Determines if a value is within a range, and like determines if a value matches a given string pattern.

7
00:00:37,530 --> 00:00:39,480
Let's look at how they're used.

8
00:00:39,660 --> 00:00:45,600
In a recent assignment, we used a Where clause with relational operators to filter results for employees

9
00:00:45,600 --> 00:00:47,370
who lived in certain states.

10
00:00:47,370 --> 00:00:53,100
It's okay if there are a few states that this can soon become unwieldly if we have to keep adding states

11
00:00:53,100 --> 00:00:56,430
to the list, and that is where the in clause comes in.

12
00:00:56,430 --> 00:01:01,170
The in clause allows you to determine if a value is in a list.

13
00:01:01,170 --> 00:01:05,040
Here's an example of using the in clause with chars or var chars.

14
00:01:05,040 --> 00:01:11,640
So this query gives us similar results, but is much more compact and easy to use.

15
00:01:11,640 --> 00:01:16,950
It's very easy to just add a state to the list and it's much more compact.

16
00:01:16,950 --> 00:01:18,780
And here's another example.

17
00:01:18,780 --> 00:01:23,040
This time we're going to use the in clause with a list of numbers.

18
00:01:23,040 --> 00:01:29,370
So you can use most data types, but everything in the list needs to be the same data type.

19
00:01:29,370 --> 00:01:34,650
So here we're looking for a set of vendors where a vendor ID is in the list.

20
00:01:34,650 --> 00:01:41,340
And here's a third example where we are looking for order dates within a list of date times.

21
00:01:41,340 --> 00:01:47,190
Notice that dates in SQL are treated like strings and we can just execute this.

22
00:01:47,190 --> 00:01:50,610
But what if we need to see if a value lies within a range?

23
00:01:50,610 --> 00:01:53,880
This is where the between clause can come in handy.

24
00:01:53,880 --> 00:01:59,370
Here's an example checking to see if an order date is between two dates.

25
00:01:59,790 --> 00:02:02,970
This is a really efficient way to get this done.

26
00:02:03,580 --> 00:02:10,600
We can also use the between clause to check to see if a price is between two price points.

27
00:02:10,600 --> 00:02:15,580
And finally, we can even use the between statement using strings.

28
00:02:15,580 --> 00:02:21,760
Here we are filtering a list of employees whose last name lies between Barker and Chen.

29
00:02:22,330 --> 00:02:25,720
So this is a quick way to pick out a group of employees.

30
00:02:25,720 --> 00:02:30,220
Now let's take a look at some examples using the like keyword.

31
00:02:31,930 --> 00:02:39,490
The light clause determines if a value matches a string pattern, like uses wildcard characters to determine

32
00:02:39,490 --> 00:02:41,950
if each string matches a pattern.

33
00:02:41,950 --> 00:02:49,120
We use a percent sign to indicate any string of zero or more characters, an underscore to look for

34
00:02:49,120 --> 00:02:55,750
a single character, and we can use square brackets to find if the string contains characters in a range

35
00:02:55,750 --> 00:02:56,890
or in a set.

36
00:02:56,890 --> 00:02:59,290
So let's take a look at a few examples.

37
00:02:59,680 --> 00:03:06,160
Here's an example of using like to find a list of employees whose last name begin with the characters

38
00:03:06,190 --> 00:03:10,600
ba and are any length longer than at least two characters.

39
00:03:10,600 --> 00:03:17,650
So executing this, we only have two employees whose last name start with those letters.

40
00:03:18,450 --> 00:03:20,910
Now, here's one that's a little tricky.

41
00:03:21,060 --> 00:03:27,060
What we want to do is find inventory items that are triple A batteries.

42
00:03:27,060 --> 00:03:32,340
But we don't want quadruple a batteries or double A batteries.

43
00:03:32,340 --> 00:03:34,770
And this query will work.

44
00:03:34,770 --> 00:03:40,830
You might think, well, we could just do this and look for a, a, a with a space at the end.

45
00:03:40,830 --> 00:03:42,600
But that doesn't work.

46
00:03:42,600 --> 00:03:49,650
And that is because the percent character can be any character, including that fourth A.

47
00:03:49,650 --> 00:03:56,010
So four A's meets this condition and that's why it lists your four A batteries.

48
00:03:56,010 --> 00:04:02,130
So if you're just looking for the string a, a a, then you have to make sure that you put your space

49
00:04:02,130 --> 00:04:06,630
before and after it, just like is in the result set.

50
00:04:06,630 --> 00:04:12,540
So sometimes when you're doing like you have to do a little bit of experimentation to find out the exact

51
00:04:12,540 --> 00:04:16,710
string pattern that you want to use to get your desired results.

52
00:04:16,710 --> 00:04:21,600
Now let's look at an example where we're looking for specific characters.

53
00:04:21,600 --> 00:04:28,290
This query looks for customers whose last name contains an M at the very start of the name, and then

54
00:04:28,290 --> 00:04:30,600
any character which is your underscore.

55
00:04:30,600 --> 00:04:34,530
And then N is going to be the third letter of the last name.

56
00:04:34,530 --> 00:04:38,400
And then there can be any length string with any characters after that.

57
00:04:38,400 --> 00:04:44,850
So if we execute this, you can see that we have three customers that meet this.

58
00:04:44,850 --> 00:04:52,470
If we don't have the percent sign, then we don't have any customers who have a last name that is exactly

59
00:04:52,470 --> 00:04:57,360
three characters long, with an M at the front and an N at the back.

60
00:04:57,360 --> 00:04:58,470
Here's an example.

61
00:04:58,470 --> 00:05:07,080
Using ranges, this query will select all customers whose last name begins in letters A through F inclusive.

62
00:05:07,080 --> 00:05:11,190
And so if we run this, then here are all our customer names.

63
00:05:11,190 --> 00:05:14,400
And you can see that it is A through F inclusive.

64
00:05:14,400 --> 00:05:17,700
So it includes the A's and includes the F's.

65
00:05:17,700 --> 00:05:23,250
And for our final example we can reverse that using the not character.

66
00:05:23,250 --> 00:05:30,690
And so this query will list all customers whose last name begins with letters that are not in the range

67
00:05:30,690 --> 00:05:37,500
of A through F, and looking at these, they begin with G and go all the way to Z.

68
00:05:38,890 --> 00:05:47,080
Next up is an assignment where you'll be doing some exercises using in between and like clauses with

69
00:05:47,080 --> 00:05:48,610
your SQL select statements.

70
00:05:48,640 --> 00:05:50,740
I'll see you in the next assignment.