WEBVTT 00:00.120 --> 00:05.290 So in the last video at the end I mentioned that we're about to move on to bigger and better operators. 00:05.490 --> 00:11.880 And this is the first of those it's called logical and you'll also see it as just two ampersands. 00:12.480 --> 00:16.440 So what it does it's sort of a step up in operators. 00:16.440 --> 00:22.690 It allows us to actually chain together multiple pieces of logical information multiple operators together 00:23.010 --> 00:28.730 and basically it necessitates that all things are true all conditions are true. 00:28.950 --> 00:30.120 Now that sounds like jibberish. 00:30.120 --> 00:32.400 So let's put it in practice let's see what I mean. 00:32.460 --> 00:33.610 So here's an example. 00:34.260 --> 00:36.260 Let's say I want to do something like this. 00:36.300 --> 00:41.140 Select books written by Dave Eggers that are also published after the year 2010. 00:42.210 --> 00:49.410 So individually these are each straightforward tasks a color coded them to show you select books written 00:49.410 --> 00:50.090 by Dave Eggers. 00:50.100 --> 00:51.180 We know how to do that. 00:51.480 --> 00:58.350 We also know how to do select all books published after the year 2010 using greater than what we just 00:58.350 --> 00:58.900 saw. 00:59.160 --> 01:02.730 However to put these together we need to use the logical. 01:02.760 --> 01:04.680 And so here's the first part. 01:04.710 --> 01:08.460 It's like books written by Dave Eggers that look like this. 01:08.560 --> 01:16.060 It's like start from books where author name equals Eggers and then just like books published after 01:16.060 --> 01:18.630 the year 2010 will look like this. 01:18.700 --> 01:22.950 Select star from books where release year is greater than 2010. 01:23.560 --> 01:27.460 So to put them together we use and. 01:28.050 --> 01:28.470 OK. 01:28.480 --> 01:36.740 So as I mentioned we can either use the word or the text A&E or to ampersands and it looks like this. 01:37.330 --> 01:43.840 So Nexstar from books where author name equals Aggers and released here is greater than 2010. 01:44.370 --> 01:45.090 And that's it. 01:45.220 --> 01:51.340 So basically we just take the two where clauses and shove them together with the word and and what this 01:51.340 --> 01:55.740 means is that both parts have to be true in order for it to be selected. 01:55.870 --> 01:56.890 So it will start. 01:57.220 --> 01:58.830 Well check this author's last name. 01:58.840 --> 02:02.960 Eggers if it is then it will move on say OK. 02:03.010 --> 02:05.410 And was it released after 2010. 02:05.560 --> 02:08.540 If it wasn't then it discards it and it doesn't like it. 02:08.710 --> 02:12.270 However if both parts are true then we see the results. 02:12.490 --> 02:13.870 So let's try it. 02:14.380 --> 02:24.100 OK so let's start off by selecting the title author Al name and released here working in this document. 02:24.160 --> 02:29.380 We're going to copy and paste in but I'm just putting a record of it here to make it easier for me to 02:29.380 --> 02:31.310 edit as I go through. 02:31.750 --> 02:38.620 So select title author Al name and or at least year from books where and we're just checking where author 02:38.620 --> 02:41.410 last name equals Aggers. 02:41.590 --> 02:49.550 Just like that there's some in Colin and you can see we have these three hologram for the king the circle 02:49.880 --> 02:53.500 and my favorite Heartbreaking Work of Staggering Genius. 02:53.600 --> 02:55.070 And then we have for least year. 02:55.100 --> 02:59.690 So for some reason someone comes into our bookstore and says You know I really like Dave Eggers but 03:00.050 --> 03:03.500 I only like his later stuff which I would disagree with. 03:03.500 --> 03:04.720 I think his earlier stuff was better. 03:04.740 --> 03:06.860 But let's say someone says that. 03:07.010 --> 03:10.640 Well then we would do something like select. 03:10.830 --> 03:19.090 And I'd like to just copy this and change the WHERE clause to where at least here is greater than 2010 03:19.360 --> 03:25.480 which we already know how to do it should we review how this gives us all books released after 2010 03:25.930 --> 03:33.160 and then to combine them is where we use the logical and and 03:36.340 --> 03:38.380 release year greater than 2010. 03:38.720 --> 03:39.370 OK. 03:40.090 --> 03:43.060 So let's check that it works. 03:43.060 --> 03:48.120 You can see we only get two results because both of these things have to match. 03:48.160 --> 03:53.980 So author has to be exactly Aggers case insensitive and really here has to be greater than the year 03:54.040 --> 03:55.400 2010. 03:56.320 --> 04:03.580 And just to show you we can replace this with double ampersands and it works the exact same way. 04:03.580 --> 04:04.480 There we go. 04:04.960 --> 04:05.450 OK. 04:05.560 --> 04:09.500 So that's the basics of and we'll use it all the time. 04:09.550 --> 04:14.680 It's important when you I mean at any time you need to change together things if you're trying to you 04:14.680 --> 04:17.050 know search based off of true to criteria. 04:17.110 --> 04:22.720 So common example might be something like It's like star from products that say we work in a toy store 04:23.380 --> 04:26.930 where Brand equals Lego. 04:27.640 --> 04:34.540 But when you're on a store off online store often there's like an option that will say you know show 04:34.770 --> 04:37.970 stock items or don't show out-of-stock items. 04:38.020 --> 04:50.110 So we kid do you think behind the scenes like where brand is Lego and in-stock equals true. 04:50.440 --> 04:56.110 That way we wouldn't be showing the customer any Lego products that are also out-of-stock which is frustrating 04:56.410 --> 04:57.790 when you see that. 04:57.850 --> 05:03.190 So that's a pretty common use cases when you're working with a value a boolean yes or no value like 05:03.240 --> 05:04.720 in stock. 05:04.760 --> 05:08.130 X is active user or something like that. 05:08.460 --> 05:09.020 OK. 05:09.340 --> 05:16.840 So the last thing that I'll address here is if we go back to the sort of problem set things like 1 less 05:16.840 --> 05:23.130 than 5 but rather than just doing one on their own I'm going to give you a couple here we're going to 05:23.130 --> 05:29.210 chain them together with and and I'll give you a first example here's one greater than five and seven 05:31.170 --> 05:33.480 equal to nine. 05:34.240 --> 05:36.200 So what does this whole thing evaluate to 05:39.100 --> 05:40.860 remember both sides have to be true. 05:40.930 --> 05:44.580 So let's take either side and take this one is one less than five. 05:44.800 --> 05:46.340 Yes that is true. 05:47.220 --> 05:49.450 Is 7 equal to 9. 05:49.450 --> 05:51.280 No that's false. 05:51.280 --> 05:55.550 And that immediately nullifies falsifies excuse me the entire thing. 05:55.630 --> 05:59.860 Both sides have to be true just to double check. 06:00.020 --> 06:02.050 Let's give it a shot. 06:02.080 --> 06:07.030 Select and you can see zero force. 06:07.380 --> 06:07.670 OK. 06:07.710 --> 06:08.600 Just like before. 06:08.850 --> 06:13.410 I mean take a moment fill out a couple of problems and hopefully you'll stick around and give them a 06:13.410 --> 06:14.790 shot. 06:14.790 --> 06:15.280 All right. 06:15.290 --> 06:15.970 I'm back. 06:16.100 --> 06:19.870 So I have three simple problems hopefully simple that I don't. 06:20.100 --> 06:24.460 Go ahead give them a shot and I'll be back. 06:24.460 --> 06:25.320 All right what you know. 06:25.330 --> 06:26.020 I'm back. 06:26.110 --> 06:28.060 So let's go over these quickly. 06:28.060 --> 06:28.960 The first one. 06:28.990 --> 06:30.800 Remember both sides have to be true. 06:30.970 --> 06:34.580 So start with the first negative 10 greater than negative 20. 06:34.580 --> 06:35.490 Is that true. 06:35.800 --> 06:36.370 Yes. 06:36.550 --> 06:38.780 It's larger than negative 20. 06:38.830 --> 06:39.730 How about this. 06:39.950 --> 06:42.120 Is zero less than or equal to zero. 06:42.610 --> 06:44.830 Yes because zero is equal to zero. 06:44.860 --> 06:47.110 Therefore we have true and true. 06:47.110 --> 06:49.670 So this whole thing is true. 06:50.140 --> 06:54.330 Let's try to Yep we get one. 06:54.640 --> 06:54.940 OK. 06:54.940 --> 06:56.900 Moving on we have. 06:57.070 --> 06:59.780 Negative 40 less than or equal to zero. 06:59.890 --> 07:02.210 And yes I know I had a negative sign here. 07:02.260 --> 07:03.010 I apologize. 07:03.010 --> 07:04.880 That was an accident. 07:05.080 --> 07:06.140 That's wrong. 07:06.550 --> 07:11.140 So hopefully you didn't spend too much time panicking about that is negative 40 less than or equal to 07:11.140 --> 07:11.860 zero. 07:11.860 --> 07:12.540 That's true. 07:12.630 --> 07:13.940 It's smaller than zero. 07:14.320 --> 07:19.830 And I use the English version A&E 10 greater than 40. 07:19.840 --> 07:21.470 Well that's actually false. 07:21.910 --> 07:24.570 Which makes the whole thing false unfortunately. 07:24.880 --> 07:31.070 So let's try it and we get zero which means false. 07:31.210 --> 07:34.240 Finally it's 50 for less than or equal to 54. 07:34.240 --> 07:35.040 That's true. 07:35.110 --> 07:40.320 Fifty four is equal to 54 and is equal to a upper case. 07:40.320 --> 07:44.810 Laura could say you cold upper case by now you should remember they are equivalent. 07:44.900 --> 07:47.560 So there's true on both sides. 07:49.070 --> 07:50.220 And we get true. 07:50.660 --> 07:51.330 All right. 07:51.420 --> 07:57.180 So the last thing I want to highlight about and is that we're not limited to just two components like 07:57.180 --> 07:58.130 we saw here. 07:58.380 --> 08:03.170 You know where author Al name is Eggers and released years graduate than 2010. 08:03.480 --> 08:04.370 We could do three. 08:04.470 --> 08:06.490 We could do 20 if we wanted to. 08:06.810 --> 08:11.880 There's probably an upper limit at some point but typically if you're working with anything more than 08:12.290 --> 08:16.770 really more than three there's there are other ways of doing it or you may need to change your table 08:16.770 --> 08:22.410 structure which we'll get to when we talk about tables and more death and we talk about joints in particular. 08:22.710 --> 08:30.360 But in this case I could do something like this where author last name is Aggers and release years 2010 08:30.870 --> 08:35.360 and Title contains the word novel. 08:35.690 --> 08:36.540 Don't ask why. 08:36.560 --> 08:42.770 But let's say we have a needy customer who wants Dave Eggers book only likes his later work and wants 08:42.810 --> 08:48.200 to be a novel and the way that she knows or he knows it's novel is that it contains the word novel in 08:48.200 --> 08:49.000 the title. 08:49.370 --> 08:58.830 Well if we go back to what we had here let's just copy this and duplicate it and add a nother condition. 08:59.580 --> 09:05.330 I'll do them on separate lines now so I'll do ampersands for that one but then I'll do a and D for the 09:05.420 --> 09:17.420 last condition which is title like and all that we want is present novel present semi-colon. 09:17.870 --> 09:20.520 So let's try that and there we go. 09:20.550 --> 09:27.520 We get a hologram for the King novel because OK first part author name is Aggers. 09:27.600 --> 09:29.210 At least here is greater than 2010. 09:29.220 --> 09:34.090 2012 is and Title contains the word novel so all three are met.