1 00:00:05,610 --> 00:00:11,070 If you don't already have SQL Server Management Studio open, go ahead and open it now. 2 00:00:11,100 --> 00:00:16,590 If there are any open query windows, it might be a good idea to close them to prevent confusion. 3 00:00:16,620 --> 00:00:19,350 Let's open a new query window. 4 00:00:19,380 --> 00:00:23,850 We're going to be learning about how to use the where and order by clauses. 5 00:00:23,850 --> 00:00:29,130 We are going to create a SQL file to save that will be part of our class resources. 6 00:00:29,130 --> 00:00:30,960 And so let's get started. 7 00:00:30,960 --> 00:00:37,590 The Where clause allows us to restrict results to data that meets specific search conditions. 8 00:00:37,590 --> 00:00:41,910 So we can select everything from customers. 9 00:00:43,130 --> 00:00:44,570 And get all the records. 10 00:00:44,570 --> 00:00:51,170 But let's say that we only want customers that live in the state of Virginia, which is designated by 11 00:00:51,170 --> 00:00:52,010 VA. 12 00:00:52,310 --> 00:01:01,370 We can then put a Where clause, filter the results by the state ID field, and we just use equals VA 13 00:01:01,370 --> 00:01:02,960 and we can hit execute. 14 00:01:02,960 --> 00:01:08,660 And now we have a list of our customers that are in the state of Virginia. 15 00:01:08,690 --> 00:01:18,680 Now if we want to order these by last name first name, then we can use the order by clause and just 16 00:01:18,680 --> 00:01:22,400 put in the names of the columns that we want to order by. 17 00:01:22,880 --> 00:01:26,510 And by default this is going to be ascending. 18 00:01:26,510 --> 00:01:30,830 So we're ordering by the last name column and then the first name column. 19 00:01:30,830 --> 00:01:36,290 So alphabetically chin is going to appear first then dent Doe and Jones. 20 00:01:36,290 --> 00:01:42,020 And then after that is ordered then it is ordered by the first name column. 21 00:01:42,020 --> 00:01:44,720 So you have al John and John Paul. 22 00:01:45,430 --> 00:01:48,100 So I will put my first comment in there. 23 00:01:48,100 --> 00:01:53,890 And then for the next example I am going to use the same thing. 24 00:01:53,890 --> 00:01:58,390 But we will do order by descending order. 25 00:02:00,150 --> 00:02:07,290 And I will use the same query, but we will do the DSC which is descending. 26 00:02:07,290 --> 00:02:11,730 Notice it turns blue and then you can also use ASC, but that's always there. 27 00:02:11,730 --> 00:02:14,730 By default we can execute this. 28 00:02:15,660 --> 00:02:21,360 And now our last names are in descending order instead of ascending. 29 00:02:22,250 --> 00:02:28,430 So to sum up, the Where clause is used to restrict the results to records that meet specific search 30 00:02:28,430 --> 00:02:34,700 conditions rather than all the records, and the order by clause is used to order the results by one 31 00:02:34,700 --> 00:02:35,990 or more columns. 32 00:02:37,010 --> 00:02:41,090 But what if we wanted to include more than one search result? 33 00:02:41,090 --> 00:02:45,290 And what other operators can we use other than equals? 34 00:02:45,940 --> 00:02:52,780 We can use boolean operators to include more than one search criteria using and or, or. 35 00:02:52,810 --> 00:02:57,100 We can also use not to exclude values from the results. 36 00:02:57,430 --> 00:03:02,350 We can also use relational operators to specify the search conditions. 37 00:03:02,350 --> 00:03:07,810 We can use equal to, less than, greater than, less than or equal to. 38 00:03:07,810 --> 00:03:10,510 Greater than or equal to or not equal to. 39 00:03:10,540 --> 00:03:12,220 Let's see how these work. 40 00:03:12,460 --> 00:03:18,040 So in this next example let's take a look at including multiple search conditions using. 41 00:03:18,040 --> 00:03:21,520 And this time we're going to be looking at the employees table. 42 00:03:21,520 --> 00:03:29,110 So first we will just do select star from employees just to get everything in there and take a look 43 00:03:29,110 --> 00:03:30,130 at our data. 44 00:03:30,130 --> 00:03:40,510 Let's only include employees where the state ID is equal to Ma. 45 00:03:40,510 --> 00:03:47,590 And then we just type and that are in department ID equals 100. 46 00:03:48,590 --> 00:03:50,330 And let's execute this. 47 00:03:50,330 --> 00:03:57,350 So this is a list of all employees that are in the same department, the Department 100 and also live 48 00:03:57,350 --> 00:03:59,090 in Massachusetts. 49 00:03:59,090 --> 00:04:06,800 And then of course we can order these by last name and we can execute this. 50 00:04:08,170 --> 00:04:14,470 So for the next example, let's add an or to our multiple search conditions. 51 00:04:14,470 --> 00:04:19,090 And I am just going to use this same select statement. 52 00:04:19,090 --> 00:04:26,380 And let's say that we want all employees who live in the state of Maryland and are either in Department 53 00:04:26,380 --> 00:04:28,930 ID 100 or Department ID 200. 54 00:04:28,930 --> 00:04:31,030 Now here is something interesting. 55 00:04:31,030 --> 00:04:35,650 I can just copy this and I'll just change this to two. 56 00:04:35,950 --> 00:04:38,500 And when we execute this. 57 00:04:40,440 --> 00:04:45,690 We don't quite get the results we expected because we have people that are not in Maryland. 58 00:04:45,690 --> 00:04:54,030 And so what is happening is it's evaluating this as where the state is in Massachusetts and the department 59 00:04:54,030 --> 00:04:58,680 ID is in 100, or department ID is in 200. 60 00:04:58,680 --> 00:05:02,910 So all of your two hundreds are the ones not in Massachusetts. 61 00:05:02,910 --> 00:05:10,750 Well, what if we want to make this show employees that live in Ma and are either in department ID 100 62 00:05:10,750 --> 00:05:18,300 or 200, well, we can just use parentheses to basically enforce the order of precedence on how and 63 00:05:18,300 --> 00:05:19,590 and or are used. 64 00:05:19,590 --> 00:05:27,870 And so now this is signifying that we want records where our employees are in Massachusetts and they're 65 00:05:27,870 --> 00:05:30,390 either in department 100 or 200. 66 00:05:30,390 --> 00:05:38,760 So if we execute this then all of our employees are in Massachusetts and they're in department 100 or 67 00:05:38,760 --> 00:05:39,270 200. 68 00:05:39,270 --> 00:05:43,620 So you can use parentheses to make the results come out the way you want. 69 00:05:43,620 --> 00:05:49,170 And one of the cardinal rules of SQL programming is just because you get results back doesn't mean they're 70 00:05:49,170 --> 00:05:49,650 correct. 71 00:05:49,650 --> 00:05:53,850 So when you're working on queries, just because you get some records back doesn't mean it's right. 72 00:05:53,850 --> 00:05:59,160 You need to look at it, evaluate it, make sure it meets what your expectation is. 73 00:05:59,460 --> 00:06:04,470 And so we'll do one more exercise with multiple search conditions. 74 00:06:04,470 --> 00:06:07,260 And this time we're going to exclude records using not. 75 00:06:07,260 --> 00:06:10,350 So I'm going to build on this query we've been playing with. 76 00:06:10,350 --> 00:06:15,600 And let's say that we want employees where they live in Massachusetts. 77 00:06:15,600 --> 00:06:17,790 They're in 100 or 200. 78 00:06:17,790 --> 00:06:22,020 And I'm just going to go to the next line and then we'll do another. 79 00:06:22,020 --> 00:06:27,300 And and let's say that we don't want anyone living in the city of Milton. 80 00:06:27,300 --> 00:06:33,060 So we just do and not city equals Milton. 81 00:06:33,060 --> 00:06:36,840 So let's select this and hit execute. 82 00:06:36,840 --> 00:06:41,850 And you can see that we don't have anyone here that lives in Milton. 83 00:06:42,390 --> 00:06:48,630 So now let's do some exercises using relational operators in our Where clause. 84 00:06:48,630 --> 00:06:52,380 So we're going to still look at our employees table. 85 00:06:52,380 --> 00:06:58,800 And so let's just start over with select star from employees. 86 00:06:58,800 --> 00:07:05,580 And let's say that we want everyone who's salary is greater than $40,000. 87 00:07:05,580 --> 00:07:09,840 So we'll say where salary greater than. 88 00:07:11,400 --> 00:07:12,780 40,000. 89 00:07:12,930 --> 00:07:14,700 So let's execute that. 90 00:07:14,700 --> 00:07:23,820 And then of course let's order by salary and we'll do descending which should put the highest one on 91 00:07:23,820 --> 00:07:24,540 top. 92 00:07:24,570 --> 00:07:29,850 So Mary Ann Shea is killing it here 138,000. 93 00:07:29,850 --> 00:07:31,980 And then everybody below her is less. 94 00:07:31,980 --> 00:07:33,870 So there's all our salary. 95 00:07:33,870 --> 00:07:42,780 And then we can also do another example where we want everybody who's salary is greater than 40,000. 96 00:07:42,780 --> 00:07:43,920 And we can do. 97 00:07:43,920 --> 00:07:48,600 And department ID does not equal 300. 98 00:07:49,320 --> 00:07:53,250 So let's execute this and our results. 99 00:07:53,250 --> 00:07:57,540 Here is everyone whose salary is greater than 40,000. 100 00:07:57,540 --> 00:08:01,500 And they're in any department other than 300. 101 00:08:01,980 --> 00:08:07,350 So we can use boolean operators to have more than one search condition. 102 00:08:07,350 --> 00:08:10,800 We can use not to exclude records. 103 00:08:10,800 --> 00:08:16,440 And we can use our relational operators to filter our results in different ways. 104 00:08:17,370 --> 00:08:22,590 Now, I haven't done examples with all of the relational operators, but I think you're probably familiar 105 00:08:22,590 --> 00:08:23,250 with them. 106 00:08:23,250 --> 00:08:26,880 We did the equal to we did the greater than we did the not equal to. 107 00:08:26,880 --> 00:08:32,580 So in the next assignment, some of the solutions will use some of these relational operators that we 108 00:08:32,580 --> 00:08:33,270 didn't use. 109 00:08:33,300 --> 00:08:35,130 See you in the next assignment.