1 00:00:00,090 --> 00:00:05,970 So greater than less than not equal to and equal to our technically what we call comparison operators. 2 00:00:05,970 --> 00:00:07,670 They compare two values, right? 3 00:00:07,680 --> 00:00:09,570 Is this less than this other thing? 4 00:00:09,570 --> 00:00:11,310 Is this not equal to the other thing? 5 00:00:11,310 --> 00:00:13,140 Is this equal greater and so on. 6 00:00:13,230 --> 00:00:18,060 But now we're actually going to take a look at an example of a logical operator. 7 00:00:18,060 --> 00:00:23,100 Now, if you have any experience with other programming languages, you probably are familiar with logical 8 00:00:23,100 --> 00:00:25,680 and in logical or logical not. 9 00:00:25,680 --> 00:00:28,440 But if you're not, don't worry at all. 10 00:00:28,440 --> 00:00:30,000 It's simple enough to pick up. 11 00:00:30,000 --> 00:00:36,480 And it is one of the most useful pieces of my SQL syntax when writing complicated queries. 12 00:00:36,480 --> 00:00:38,460 So we'll take it step by step from the beginning. 13 00:00:38,460 --> 00:00:44,100 Imagine that I wanted to write a query to select all the books written by Dev Dave Eggers that were 14 00:00:44,100 --> 00:00:46,440 published after the year 2010. 15 00:00:46,800 --> 00:00:51,390 Well, there's two basic pieces of information here that we're filtering by. 16 00:00:51,390 --> 00:00:57,390 We're selecting by author where author is equal to Dave Eggers or just Eggers, maybe for last name. 17 00:00:57,840 --> 00:01:05,610 And then secondarily, we're also finding books released after 2010 so we can write those two queries 18 00:01:05,610 --> 00:01:07,290 individually, right? 19 00:01:07,290 --> 00:01:12,660 We can select the books written by Dave Eggers, select star from books where author last name is equal 20 00:01:12,660 --> 00:01:17,910 to Eggers, and we can select the books published after the year 2010. 21 00:01:17,910 --> 00:01:21,270 Select star from books we released here is greater than 2010. 22 00:01:22,360 --> 00:01:27,340 And if I ran this here we can see there's three Dave Eggers books. 23 00:01:27,340 --> 00:01:34,330 And then if I ran this one here to find the books released after 2010, there's five books there. 24 00:01:34,420 --> 00:01:36,730 And what I want is the overlap. 25 00:01:36,970 --> 00:01:43,450 I want the books that were released after 2010 and have author last name of Eggers, which is these 26 00:01:43,450 --> 00:01:47,410 two books here, A Hologram for the King and the Circle. 27 00:01:47,710 --> 00:01:49,290 And that's what this slide shows here. 28 00:01:49,300 --> 00:01:54,010 I want author l name equal to Eggers and released year greater than 2010. 29 00:01:54,040 --> 00:01:55,960 Both of those need to be true. 30 00:01:56,020 --> 00:01:57,490 I want those books. 31 00:01:57,490 --> 00:02:04,090 And the way that we do that is by using and capital A and doesn't have to be capitalized, but A and 32 00:02:04,120 --> 00:02:09,370 will join together two or more different pieces of logic. 33 00:02:09,370 --> 00:02:11,440 So here's our first condition. 34 00:02:11,530 --> 00:02:17,020 Author Last name is equal to Eggers, and at the same time release year is greater than 2010. 35 00:02:17,560 --> 00:02:25,270 So in order for a book to be included in the results of this query to be selected, this has to come 36 00:02:25,270 --> 00:02:27,790 out to be true and this has to come out to be true. 37 00:02:27,790 --> 00:02:31,060 If only one of them is true, then that book doesn't count. 38 00:02:31,060 --> 00:02:32,170 It's not included. 39 00:02:32,290 --> 00:02:32,680 Right. 40 00:02:32,680 --> 00:02:38,860 If the author last name is Eggers, but release here is 2005, then this thing is not true. 41 00:02:38,860 --> 00:02:40,810 The whole statement is untrue. 42 00:02:40,960 --> 00:02:42,520 So why don't we try writing this? 43 00:02:42,520 --> 00:02:43,120 Let's. 44 00:02:43,300 --> 00:02:45,070 Let's do it in a separate file. 45 00:02:45,070 --> 00:02:50,560 So instead of select star, let's do title author, last name and released here. 46 00:02:51,490 --> 00:02:52,660 From books. 47 00:02:53,350 --> 00:02:58,150 And then I'll do my first piece where let's do it based off of released. 48 00:02:58,150 --> 00:03:02,770 Your first or released year is equal, or rather is greater than 2010. 49 00:03:04,570 --> 00:03:07,210 And then afterwards I'm going to say and. 50 00:03:07,930 --> 00:03:14,290 I also want to know where author your last name is exactly equal to Eggers. 51 00:03:15,520 --> 00:03:16,990 So let's run this query. 52 00:03:17,910 --> 00:03:18,750 And there we go. 53 00:03:19,350 --> 00:03:20,970 Only two rows that matched. 54 00:03:20,970 --> 00:03:25,350 Both of them have author last name of eggers and release year is greater than 2010. 55 00:03:26,170 --> 00:03:27,550 So that's our first example. 56 00:03:28,360 --> 00:03:30,250 Here's a more complicated example. 57 00:03:30,250 --> 00:03:32,510 We can chain on as many as we want. 58 00:03:32,530 --> 00:03:37,660 I can keep going and say, Arthur, last name is Eggers and release year is greater than 2010. 59 00:03:37,660 --> 00:03:41,930 And also the title is like this string. 60 00:03:41,950 --> 00:03:44,140 It includes novel in the middle. 61 00:03:44,140 --> 00:03:48,100 So if I do that, let me just build up this query over here. 62 00:03:49,620 --> 00:03:55,260 So do the same thing as before, But we'll add on another and I can keep going as many times as I want. 63 00:03:55,290 --> 00:04:00,750 Now, all three of these pieces, these three different comparisons all have to be true. 64 00:04:01,170 --> 00:04:02,820 Released Release year Greater than 2010. 65 00:04:03,180 --> 00:04:08,760 The last name is exactly Eggers and the title is like novel, meaning it contains not novel somewhere 66 00:04:08,760 --> 00:04:11,730 in there because we've got the percent signs on either side. 67 00:04:12,120 --> 00:04:14,160 So all three of those have to be true. 68 00:04:14,160 --> 00:04:17,100 And there's only one match a hologram for the King. 69 00:04:17,130 --> 00:04:21,959 It contains novel author Last name Eggers released here is greater than 2010. 70 00:04:21,959 --> 00:04:23,880 2012 is greater than 2010. 71 00:04:24,360 --> 00:04:26,340 So this is incredibly useful. 72 00:04:26,340 --> 00:04:33,150 We can combine all of the different tools to different comparisons that we've learned so far and make 73 00:04:33,150 --> 00:04:34,800 more complicated queries. 74 00:04:35,220 --> 00:04:39,870 Now let's let's actually dial it back for a moment and talk about how and works. 75 00:04:40,020 --> 00:04:41,940 So there's a left and a right side. 76 00:04:43,290 --> 00:04:49,950 And in order for and to evaluate to true this whole thing to be true, the left side has to be true 77 00:04:49,950 --> 00:04:51,930 and the right side has to be true. 78 00:04:52,050 --> 00:04:58,050 Now that makes sense just based off of how we think about this and what we mean generally when we say 79 00:04:58,050 --> 00:04:58,440 the word. 80 00:04:58,440 --> 00:05:04,560 And if I was just reading this release here is greater than 2010 and author last name is Eggers. 81 00:05:04,650 --> 00:05:10,380 We know in order for and to be true, both of those have to be true, right? 82 00:05:10,380 --> 00:05:17,910 If we had a book that was released in 2010, but author last name was Smith, well, the left side would 83 00:05:17,910 --> 00:05:18,750 be true. 84 00:05:19,350 --> 00:05:22,260 Well, actually, it wouldn't be true because I said greater than 2010. 85 00:05:22,260 --> 00:05:23,790 So let's say it was 2011. 86 00:05:23,820 --> 00:05:24,960 Greater than 2010. 87 00:05:24,960 --> 00:05:26,070 So that's true. 88 00:05:26,070 --> 00:05:29,130 But then the right side would be false. 89 00:05:29,130 --> 00:05:32,190 And that means the entire thing is false. 90 00:05:32,340 --> 00:05:35,100 So I can actually prove that this is how it works. 91 00:05:35,100 --> 00:05:38,700 If we just do something simple like this, select, remember how we can do this. 92 00:05:38,700 --> 00:05:40,440 Three is greater than one. 93 00:05:40,620 --> 00:05:43,650 If it is true, we get one as the answer. 94 00:05:43,650 --> 00:05:48,150 And if I did, three is greater than four, we get zero for false. 95 00:05:48,270 --> 00:05:50,280 So I can do this select. 96 00:05:50,280 --> 00:05:52,620 Let's do something we know is going to be true. 97 00:05:53,340 --> 00:05:54,990 One is greater than zero. 98 00:05:55,650 --> 00:05:56,350 That is true. 99 00:05:56,370 --> 00:06:03,000 We get one and let's say eight is equal to eight. 100 00:06:03,450 --> 00:06:04,770 Stupid, but true. 101 00:06:04,980 --> 00:06:07,350 So the whole thing evaluates to true. 102 00:06:07,770 --> 00:06:09,720 But if I change this a little bit. 103 00:06:09,720 --> 00:06:11,690 So now the right side is false. 104 00:06:11,700 --> 00:06:13,500 The whole thing evaluates to false. 105 00:06:13,500 --> 00:06:15,030 Remember, zero means false. 106 00:06:15,270 --> 00:06:16,650 So this is true. 107 00:06:16,680 --> 00:06:17,850 This is false. 108 00:06:17,850 --> 00:06:21,690 When we have and in the middle that means the whole thing is false. 109 00:06:21,720 --> 00:06:26,520 The only way for end to be true is when both sides are true. 110 00:06:26,880 --> 00:06:28,750 So let's try one more example. 111 00:06:28,770 --> 00:06:31,180 Why don't we do something sort of silly? 112 00:06:31,200 --> 00:06:40,050 Let's find the books that have a title that are that is at least, let's say, 15 characters, so long 113 00:06:40,080 --> 00:06:44,160 titles and also a large number of pages. 114 00:06:44,490 --> 00:06:46,330 So we'll start with the first bit. 115 00:06:46,350 --> 00:06:48,510 This is a little trickier than what we've done so far. 116 00:06:48,540 --> 00:06:53,640 How do I know how many characters are in the title and how do I compare that to 15? 117 00:06:53,790 --> 00:06:55,210 Well, I can do something like this. 118 00:06:55,230 --> 00:07:00,030 We're going to use a string function, remember char length. 119 00:07:00,030 --> 00:07:03,060 So we'll do something like select char length. 120 00:07:04,290 --> 00:07:11,940 Of the title from books, but that's just going to print to me or select the value of the character 121 00:07:11,940 --> 00:07:14,100 length, the number of characters in the title. 122 00:07:14,220 --> 00:07:15,600 Maybe not that useful. 123 00:07:16,050 --> 00:07:18,750 What I want to do is select the title. 124 00:07:20,330 --> 00:07:25,640 And then from books where the character length of the title. 125 00:07:27,000 --> 00:07:28,710 Let's say it's greater than 30. 126 00:07:29,340 --> 00:07:34,050 So now I'm getting the titles of the books that have long titles. 127 00:07:34,470 --> 00:07:35,640 And there we are. 128 00:07:35,670 --> 00:07:36,970 So these are long titles. 129 00:07:36,970 --> 00:07:39,070 So we have nothing to do with end at the moment. 130 00:07:39,090 --> 00:07:44,400 But now I want to add in this condition, this additional stipulation that says not only do I want the 131 00:07:44,400 --> 00:07:48,270 ones that have long titles, but they also have to be long books. 132 00:07:49,080 --> 00:07:50,940 So let's just do it on a separate line. 133 00:07:51,960 --> 00:07:56,250 And pages, I don't know, pages is greater than 500. 134 00:07:56,520 --> 00:07:57,550 Do we have any? 135 00:07:57,570 --> 00:07:58,890 I think there's at least one. 136 00:07:59,100 --> 00:08:00,180 There's two of them. 137 00:08:00,270 --> 00:08:04,740 Why don't we also display pages or retrieve pages so we can verify? 138 00:08:06,660 --> 00:08:12,990 Okay, So we've got a title over here, The Amazing Adventures of Kavalier and Clay that has to be greater 139 00:08:12,990 --> 00:08:14,220 than 30 characters. 140 00:08:14,490 --> 00:08:19,890 And again, we're not just directly comparing title, but we're comparing the character length of the 141 00:08:19,890 --> 00:08:20,460 title. 142 00:08:20,700 --> 00:08:24,540 And then pages has to be greater than 500 at the same time. 143 00:08:24,540 --> 00:08:29,130 So there's other books that are more than 500 pages, but they don't have long titles. 144 00:08:29,130 --> 00:08:35,730 And there's other long titles like what we talk About when we talk about love that don't have 500 plus 145 00:08:35,730 --> 00:08:36,419 pages. 146 00:08:36,630 --> 00:08:42,240 So we're getting the intersection, only the rows that have this true and this true. 147 00:08:42,630 --> 00:08:43,530 So that's logical. 148 00:08:43,530 --> 00:08:50,310 And we can use it to create really complex queries and keep adding on if we wanted to, you know, different 149 00:08:50,310 --> 00:08:53,610 conditions and make things more and more specific.