1 00:01.220 --> 00:01.670 Now. 2 00:01.670 --> 00:02.990 Are you able to hear me? 3 00:02.990 --> 00:03.470 Right? 4 00:08.540 --> 00:08.930 Oh, yes. 5 00:08.930 --> 00:09.560 Very well. 6 00:11.300 --> 00:11.960 Okay, so. 7 00:11.960 --> 00:13.820 So now today we will see the like. 8 00:13.820 --> 00:14.600 Operator. 9 00:14.630 --> 00:19.370 So like like operator is basically used for the pattern comparison. 10 00:19.760 --> 00:21.380 Pattern comparison. 11 00:21.410 --> 00:25.490 So now let's start with the first query itself. 12 00:25.490 --> 00:29.400 So we have this employee table with us. 13 00:31.440 --> 00:38.520 Now suppose from this employee table what we want we want display the details of the employees whose 14 00:38.520 --> 00:45.120 name starts with s like Sachin Smith in maybe few more. 15 00:45.120 --> 00:45.450 Right. 16 00:45.450 --> 00:48.840 So what we have to do, we have to write like this. 17 00:48.840 --> 00:54.590 Select star from employee where employee name like as percentage. 18 00:56.000 --> 01:00.860 So all this employee are starting with s right Sachin Smith and Scott. 19 01:02.420 --> 01:06.650 So like operator is used for basically a pattern comparison. 20 01:07.160 --> 01:13.310 Now one more example display the details of the employees whose name ends with S. 21 01:13.310 --> 01:15.680 So what changes we have to do. 22 01:15.710 --> 01:18.660 We have to write percentage at the beginning. 23 01:21.090 --> 01:24.120 We have to write percentage at the beginning. 24 01:24.120 --> 01:32.550 So all this employee John Adams and James all our employee are name is ending with s right. 25 01:32.550 --> 01:35.430 So what is the difference over here. 26 01:35.430 --> 01:37.140 Percentage is written at the end. 27 01:37.140 --> 01:40.590 Over here percentage is written at the beginning right. 28 01:42.820 --> 01:47.740 Now suppose in the entire string if you want to find it anywhere. 29 01:47.740 --> 01:48.370 Right. 30 01:48.730 --> 01:57.070 If you want to find it anywhere in the string, then you have to mention that percentage sign in both 31 01:57.070 --> 01:57.760 the side. 32 01:58.930 --> 02:03.370 So over here check this result set. 33 02:05.390 --> 02:06.110 Ramesh. 34 02:06.110 --> 02:06.530 Right. 35 02:06.530 --> 02:07.430 Ramesh in. 36 02:07.580 --> 02:09.740 Ramesh S is in between. 37 02:09.740 --> 02:10.040 Right. 38 02:10.040 --> 02:11.330 In between anywhere. 39 02:12.890 --> 02:24.740 So Ramesh will also display in this case means sometimes this s James in Adams S is at the end and over. 40 02:24.740 --> 02:27.200 Here's is at the beginning and over. 41 02:27.200 --> 02:29.470 Here's is in between somewhere. 42 02:29.770 --> 02:35.710 So in entire string, wherever the S is, there it will be displayed. 43 02:39.880 --> 02:42.160 Now let's see one more example. 44 02:42.160 --> 02:50.140 Display the details of the employees where third character in their name should be a third character 45 02:50.140 --> 02:50.980 in the name. 46 02:51.130 --> 02:54.620 So over here you need to remember one thing. 47 02:54.710 --> 02:59.180 This will be mentioned as one character. 48 02:59.330 --> 03:02.330 Then this will be mentioned as second character. 49 03:03.230 --> 03:06.260 And this will be mentioned as third character. 50 03:10.160 --> 03:10.940 Right. 51 03:12.830 --> 03:13.730 Got it. 52 03:13.790 --> 03:17.730 So this is first character. 53 03:17.730 --> 03:18.720 This is second. 54 03:18.720 --> 03:23.370 And what we need to identify, we need to identify a is a third character. 55 03:23.370 --> 03:25.560 So this A will be a third character. 56 03:25.560 --> 03:34.950 So if we run this result set we will find all the data where A is third character from starting. 57 03:37.470 --> 03:40.650 So in all this but this is the underscore right. 58 03:41.710 --> 03:42.160 Yeah. 59 03:42.160 --> 03:46.210 But underscore will be defined as the first character in like operator. 60 03:46.660 --> 03:47.920 But this is underscore right. 61 03:47.920 --> 03:49.780 What you are mentioning is underscore right. 62 03:50.290 --> 03:51.190 Am I correct or not. 63 03:51.760 --> 03:54.280 Yes it is I'm talking about it's underscore right. 64 03:54.280 --> 03:55.450 If it is underscore. 65 03:55.480 --> 04:04.930 My question is if a data having underscore inside the data itself how it going to, you know specify 66 04:04.930 --> 04:06.750 that operator is underscore. 67 04:11.310 --> 04:19.110 If let's say that Blake is the I is get identified because it's a third character is a as per your example 68 04:19.110 --> 04:21.990 is Blake Blake. 69 04:22.470 --> 04:29.340 My question is if it is, you know, um, it starts with let's say Underscore and Blake. 70 05:06.980 --> 05:07.220 Okay. 71 05:07.220 --> 05:07.880 Now let's see. 72 05:07.910 --> 05:09.350 For this example. 73 05:13.970 --> 05:19.830 Name starts with j or one, two, three, four. 74 05:19.830 --> 05:21.570 Fifth character is J, right? 75 05:21.690 --> 05:22.170 So 76 05:22.170 --> 05:28.740 1212345. 77 05:32.850 --> 05:34.320 It is showing, right? 78 05:35.910 --> 05:36.990 Perfect, perfect. 79 05:36.990 --> 05:37.620 Got it. 80 05:39.900 --> 05:44.180 So it has considered this one as a one character only, right? 81 05:45.080 --> 05:46.970 This one is one character only. 82 05:47.030 --> 05:47.510 Got it? 83 05:47.510 --> 05:47.690 Got it. 84 05:47.690 --> 05:50.660 Now suppose I remove this and mention this. 85 05:50.660 --> 05:55.340 I am not sure whether it will work or not. 86 06:00.680 --> 06:01.370 One. 87 06:01.370 --> 06:02.270 Two three. 88 06:02.270 --> 06:05.090 Four four. 89 06:05.090 --> 06:05.100 12344. 90 06:06.690 --> 06:09.090 No, it is not working right. 91 06:12.180 --> 06:14.520 This record is not coming. 92 06:17.760 --> 06:22.410 I thought this one record will come, but it is displaying entire table. 93 06:30.340 --> 06:32.260 So I'm not sure about, but, uh. 94 06:32.260 --> 06:37.030 But usually we are not identifying anything like this only. 95 06:37.030 --> 06:37.360 Right. 96 06:37.360 --> 06:39.190 Anything like this. 97 06:39.190 --> 06:45.760 If we want to identify such record, which is underscore, then what we are doing, then we we are doing 98 06:45.760 --> 06:47.080 like this only right. 99 06:47.560 --> 06:48.550 Then it will. 100 06:52.780 --> 06:53.680 What is wrong? 101 06:53.790 --> 06:54.600 name like? 102 07:00.030 --> 07:02.070 I don't know how it is working. 103 07:03.360 --> 07:07.830 It should display only one record, but it is still displaying all the records. 104 07:11.340 --> 07:15.390 I want to find in the enum where it is underscore are there? 105 07:15.420 --> 07:22.750 Can anyone tell me how to write this query where it is underscore only like this. 106 07:24.880 --> 07:28.420 So only one record I want from this entire table. 107 07:29.260 --> 07:31.330 Then how we can get it. 108 07:41.270 --> 07:45.650 Was that your statement is not working like percentage underscore. 109 07:45.680 --> 07:46.910 Is it not working? 110 07:48.260 --> 07:48.710 I think. 111 07:53.060 --> 07:53.510 Yeah. 112 07:53.510 --> 07:56.210 In this case it is not working properly. 113 08:04.160 --> 08:05.720 Then it's a fourth character. 114 08:05.730 --> 08:06.570 Is L right? 115 08:06.960 --> 08:07.590 Yeah. 116 08:07.830 --> 08:10.440 Four character or three character of L. 117 08:10.620 --> 08:14.970 I mean sorry three character of underscore and fourth character is also underscore. 118 08:15.210 --> 08:17.340 But no, it will still not work. 119 08:18.480 --> 08:20.610 It is not able to identify. 120 08:24.660 --> 08:27.240 So anyway, let's move forward. 121 08:29.540 --> 08:31.970 We will check it later. 122 08:35.930 --> 08:42.290 Now suppose display the details of the employees where A is third character from the last in their name. 123 08:42.410 --> 08:45.590 So over here we need to reverse your condition. 124 08:45.590 --> 08:48.890 You are mentioning underscore in the beginning over here. 125 08:48.890 --> 08:49.400 Right? 126 08:51.290 --> 08:52.820 Or over here in the beginning. 127 08:52.820 --> 08:56.550 But over here you need to mention that underscore in the last. 128 08:56.550 --> 09:02.070 So one two and three three character should be a third character should be a. 129 09:02.370 --> 09:04.500 So if you write this one. 130 09:07.110 --> 09:10.140 Then from last third character is a right. 131 09:12.120 --> 09:13.380 123. 132 09:14.190 --> 09:14.760 Got it. 133 09:20.590 --> 09:25.060 Now suppose display the details of the employee who joined in 2021. 134 09:25.540 --> 09:32.680 So we have seen in one, one way we can do is like greater than, equal to and less than or equal to 135 09:32.680 --> 09:35.110 right, particular date, higher date. 136 09:35.290 --> 09:36.280 Then between. 137 09:36.280 --> 09:38.500 Also we have seen yesterday between close. 138 09:38.500 --> 09:46.400 Also we can use and one thing this one I think also we have seen we have to mention like 2021. 139 09:46.400 --> 09:54.020 So all these three query will give same result means one one question. 140 09:54.020 --> 09:57.110 We will be able to do with three different ways. 141 09:59.120 --> 10:01.970 One query we will able to write in three different ways. 142 10:01.970 --> 10:07.600 So based upon the situation you can pick up whatever you want to write. 143 10:11.170 --> 10:14.050 So all these three will be give the same result. 144 10:14.140 --> 10:17.050 Now suppose display the employee details file name. 145 10:17.050 --> 10:21.130 Start from a j or s. 146 10:21.610 --> 10:25.480 So over here what it will return. 147 10:25.480 --> 10:26.950 Can anyone told me. 148 10:37.520 --> 10:39.830 Can anyone tell me what it will return? 149 10:41.360 --> 10:42.290 Uh, the first one. 150 10:44.120 --> 10:45.230 Display employee retail. 151 10:45.230 --> 10:45.590 Right. 152 10:48.020 --> 10:48.620 This query. 153 10:52.880 --> 10:58.500 So this, uh, this will return like in in the first statement you mentioned, like, uh, a percentage. 154 10:58.500 --> 10:58.710 Right. 155 10:58.710 --> 11:05.550 So the wherever the name is starting with a, it will return and then you're using or conditions as 156 11:05.550 --> 11:09.840 well e name like J or it is starting with J or it is starting with S. 157 11:12.150 --> 11:13.020 Okay. 158 11:13.020 --> 11:17.220 So let's see whether it will start with a J or S. 159 11:17.490 --> 11:20.530 Everything will be written right now. 160 11:20.530 --> 11:24.280 Same thing we are able to mention with this one also. 161 11:29.380 --> 11:32.470 Same thing we will be able to mention like this. 162 11:35.590 --> 11:37.090 Please be on mute. 163 11:39.550 --> 11:44.970 Select star from EMP where ename like AJ's all together. 164 11:44.970 --> 11:48.120 We have mentioned in single quote and then. 165 11:52.770 --> 11:57.510 So again this both the queries will give us the same result, right? 166 11:59.520 --> 12:02.820 But how come in a square brackets when it is given? 167 12:03.330 --> 12:09.490 Or I think it is a parenthesis right A, j and S without any commas. 168 12:09.790 --> 12:13.750 Does that not mean to that it is a single word? 169 12:13.780 --> 12:14.590 A j yes. 170 12:15.760 --> 12:16.300 No, no. 171 12:16.300 --> 12:20.950 It will pick as a either A or j or S. 172 12:22.330 --> 12:27.790 Basically it is a syntax only syntactical only. 173 12:27.880 --> 12:31.610 This is not indicate that it will start with a JS only. 174 12:31.610 --> 12:33.710 It will not indicate like that. 175 12:33.710 --> 12:42.080 It will indicate either A or J or S, but how that uh interpreter is identifying in that way. 176 12:42.080 --> 12:49.190 Because, you know, if it is, if I let's say that in the, in the, in the, uh, text, I need to 177 12:49.190 --> 12:53.690 find out a l l e l n but I don't want Alan. 178 12:53.690 --> 12:55.630 But I'm not sure whether it is Alan or not. 179 12:55.630 --> 12:57.880 But I'm sure that it starts with the e a l e. 180 12:58.180 --> 13:00.760 So how do I need to mention it that way, then? 181 13:01.750 --> 13:02.350 Yeah. 182 13:02.380 --> 13:06.640 Alan E then you can mention a l e over here itself. 183 13:10.690 --> 13:13.060 No, l l it is double l. 184 13:14.920 --> 13:15.460 Okay. 185 13:15.490 --> 13:17.380 Al l then. 186 13:17.380 --> 13:22.040 Okay, Then it will identify a and L only a. 187 13:22.610 --> 13:29.510 So over here al L is coming right in the first one only itself because its name is starting with S. 188 13:29.510 --> 13:37.490 Sorry starting with a, but there is no employee starting from L in our record. 189 13:38.480 --> 13:39.050 Okay. 190 13:39.050 --> 13:42.780 But also it's showing a also it's showing. 191 13:42.900 --> 13:45.180 Um, you know employee starts with a as well. 192 13:45.180 --> 13:45.570 Right. 193 13:45.570 --> 13:45.900 Yeah. 194 13:45.900 --> 13:48.630 So it may be two two things is coming over there. 195 13:49.710 --> 13:50.160 Yeah. 196 13:50.160 --> 13:57.930 So this is a separate separate thing only you cannot mention like al it is starting with al. 197 13:57.930 --> 14:00.180 It is not like that either. 198 14:00.180 --> 14:02.490 It is are there starting with A or L. 199 14:07.960 --> 14:11.650 I think it's better to specify in a separate air condition. 200 14:11.650 --> 14:13.750 So it is more more clear. 201 14:14.230 --> 14:14.710 Yeah. 202 14:14.710 --> 14:15.940 Instead of if you are not. 203 14:16.660 --> 14:17.170 Yeah. 204 14:17.170 --> 14:20.710 This is I am just letting you know another way. 205 14:20.710 --> 14:25.300 If you are not getting this then you can use this one right? 206 14:26.260 --> 14:32.220 But this is a syntactically I am showing you now how it works. 207 14:32.220 --> 14:38.100 So you just need to just need to understand this is a separate word. 208 14:38.100 --> 14:40.170 This is not like an L. 209 14:40.320 --> 14:43.830 So if you want to like check that one right. 210 14:43.950 --> 14:50.040 Then what you have to do, you have to remove this bracket and then l now let's try. 211 14:53.820 --> 14:57.100 So now your result is also coming right now. 212 14:57.100 --> 14:58.810 Second one is ignoring. 213 15:02.680 --> 15:06.610 So at that time you have to ignore this square bracket. 214 15:08.590 --> 15:09.580 So one thing. 215 15:11.650 --> 15:14.470 You have to experiment in the result. 216 15:16.930 --> 15:22.040 Now display the employee details where name starts with A to H. 217 15:23.690 --> 15:28.610 Means either it is starting from a, b, c d e f g h. 218 15:29.330 --> 15:35.030 So again, maybe this one will be confusing, but you have to write like this a to h. 219 15:35.060 --> 15:36.170 A dash h. 220 15:36.740 --> 15:39.200 So it will give. 221 15:39.200 --> 15:44.400 All the names were starting from a to h a, b, c, f, h. 222 15:49.830 --> 15:57.030 Then you have to mention a to h means a dash h in the square bracket. 223 16:05.010 --> 16:13.340 Now let's see what then one very more must important operator is we can say that is is operator. 224 16:13.400 --> 16:19.850 So now is operator is basically used for to check for for null null value. 225 16:19.880 --> 16:23.570 Suppose let me check the same table. 226 16:24.200 --> 16:27.770 Select star from EMP. 227 16:29.000 --> 16:30.170 Now. 228 16:32.100 --> 16:32.700 where? 229 16:32.730 --> 16:33.030 Come. 230 16:33.030 --> 16:35.460 I want to check where commission equal to null. 231 16:35.460 --> 16:36.180 Right. 232 16:36.180 --> 16:36.600 Where? 233 16:36.600 --> 16:37.980 Commission equal to null. 234 16:38.040 --> 16:44.070 Then let me know what this will return. 235 16:44.070 --> 16:44.430 Where? 236 16:44.430 --> 16:47.550 Commission equal to null. 237 16:48.270 --> 16:49.950 What it will return me. 238 16:53.610 --> 16:55.300 Can anyone tell me? 239 16:57.550 --> 16:58.420 Wherever it is. 240 16:58.420 --> 16:58.840 Blank. 241 16:58.840 --> 16:59.260 Right. 242 17:00.580 --> 17:01.420 From which? 243 17:01.420 --> 17:02.320 How many records? 244 17:02.320 --> 17:02.710 One. 245 17:02.710 --> 17:03.010 Two. 246 17:03.010 --> 17:03.400 Three. 247 17:03.400 --> 17:03.940 Four. 248 17:03.940 --> 17:04.690 Five. 249 17:04.690 --> 17:05.650 Six. 250 17:06.100 --> 17:06.820 Seven. 251 17:06.820 --> 17:07.060 Eight. 252 17:07.060 --> 17:07.330 Nine. 253 17:07.330 --> 17:07.540 Ten. 254 17:07.540 --> 17:08.170 11. 255 17:09.250 --> 17:10.030 I do not know. 256 17:10.030 --> 17:10.780 Wherever it is. 257 17:10.780 --> 17:11.320 Null. 258 17:11.350 --> 17:13.810 Uh, this record you are telling, right? 259 17:13.810 --> 17:14.080 Right? 260 17:14.080 --> 17:15.010 Right, right. 261 17:16.660 --> 17:18.100 So anyone else? 262 17:20.090 --> 17:21.740 Can you go back to the statement again? 263 17:21.770 --> 17:23.930 Uh, this one, it will return 0.00. 264 17:23.930 --> 17:25.190 It will return or not? 265 17:25.190 --> 17:26.270 No no, no. 266 17:26.630 --> 17:27.140 Okay. 267 17:27.140 --> 17:28.370 This is a statement. 268 17:30.740 --> 17:32.900 It will just find the blank values. 269 17:35.090 --> 17:37.370 But was that the second statement executed? 270 17:37.370 --> 17:38.600 But I didn't see that. 271 17:39.410 --> 17:41.150 No, no, I haven't executed it. 272 17:41.150 --> 17:42.970 I am just asking you a question. 273 17:42.970 --> 17:43.870 Then we will. 274 17:44.740 --> 17:44.860 Uh. 275 17:44.860 --> 17:46.150 Then it has to show. 276 17:46.150 --> 17:49.210 Uh, no commission equal to null means it will show only then. 277 17:51.430 --> 17:56.230 Okay, so over here, everyone is wrong. 278 18:04.870 --> 18:07.400 So over here, everyone is wrong. 279 18:07.400 --> 18:08.300 How? 280 18:09.470 --> 18:11.090 Let's see the result. 281 18:14.450 --> 18:15.860 What is the result? 282 18:19.670 --> 18:21.200 The second result is nothing. 283 18:21.200 --> 18:21.650 Nothing? 284 18:21.650 --> 18:22.040 Nothing. 285 18:22.040 --> 18:22.220 Right? 286 18:23.180 --> 18:25.820 Now let me show you one more thing. 287 18:25.820 --> 18:27.230 Not equal to null. 288 18:27.260 --> 18:28.250 Now. 289 18:33.330 --> 18:36.090 Wherever commission is there, it will return that one. 290 18:40.650 --> 18:41.850 Still nothing is coming. 291 18:42.570 --> 18:44.310 So, no, I think, uh harshit. 292 18:44.310 --> 18:45.450 You have to write. 293 18:45.450 --> 18:46.860 His keyword is null. 294 18:46.890 --> 18:50.820 Ya, that is why I want to explain. 295 18:50.820 --> 18:58.390 It is not possible to test for null values with comparison operators such as equal to less than or not, 296 18:58.390 --> 19:00.880 equal to, greater than, greater than or equal to. 297 19:01.120 --> 19:09.430 Each operator is used to use for null comparison, where this one is not possible in is operator. 298 19:09.430 --> 19:14.500 Right now for null comparison, we are using is null or is not null. 299 19:14.500 --> 19:15.670 So. 300 19:18.130 --> 19:20.070 Ignore this two statement. 301 19:20.100 --> 19:21.870 Now let's see. 302 19:21.870 --> 19:27.390 Display the details of the employees whose commission is not yet decided. 303 19:27.390 --> 19:29.730 Whose commission is not yet decided. 304 19:29.760 --> 19:33.960 So what we want, we want null record. 305 19:33.960 --> 19:34.470 Right. 306 19:34.860 --> 19:35.400 Where? 307 19:35.400 --> 19:36.510 Commission equal to null. 308 19:36.510 --> 19:39.180 So in this case, we have to write like this. 309 19:39.360 --> 19:41.250 Where commission is null. 310 19:44.330 --> 19:51.230 Then how can we also define like, uh, can you go back to a statement, uh uh, select star from employee 311 19:51.230 --> 19:56.270 where commission is equal to and uh, in the commas, inverted commas, we can say like blank. 312 19:56.690 --> 19:59.180 I think it will work that that way as well. 313 20:08.360 --> 20:09.020 Is equal to. 314 20:09.050 --> 20:09.380 Yeah. 315 20:19.790 --> 20:20.600 Why? 316 20:20.870 --> 20:24.980 Because this commission column is integer column. 317 20:24.980 --> 20:25.550 Right? 318 20:25.790 --> 20:27.770 Commission column is integer column. 319 20:27.770 --> 20:30.500 And this will indicate for vehicle. 320 20:31.200 --> 20:32.700 for vertical values. 321 20:33.120 --> 20:35.970 This one will be using for the vertical values. 322 20:36.090 --> 20:40.950 Now working in numeric or integer or decimal cannot be compared. 323 20:41.880 --> 20:44.010 So that is why it is giving error. 324 20:44.010 --> 20:46.290 But suppose if any name. 325 20:46.560 --> 20:47.250 Yeah. 326 20:48.810 --> 20:55.200 So you are saying, you know, um, if we have some text values like varchar value, then only we can 327 20:55.200 --> 20:55.890 define this way. 328 20:55.890 --> 20:56.910 Like is equal to like. 329 20:56.910 --> 20:57.180 Yeah. 330 20:57.180 --> 21:02.400 Suppose in the name, if we want to, uh, declare like this. 331 21:02.790 --> 21:07.020 So let's try that one also. 332 21:11.400 --> 21:19.110 Where name is equal to Smith or Ename. 333 21:24.240 --> 21:26.460 Now let's do one update. 334 21:40.440 --> 21:42.270 Let me try this one. 335 21:43.780 --> 21:45.280 One row updated. 336 21:45.550 --> 21:47.380 Now let's try this one. 337 21:48.610 --> 21:50.380 It is still not showing. 338 21:51.580 --> 21:54.280 Now let me try this one. 339 22:03.190 --> 22:03.880 Okay. 340 22:03.880 --> 22:06.190 Where in name? 341 22:17.340 --> 22:18.900 Now it is showing. 342 22:19.680 --> 22:20.310 So. 343 22:20.310 --> 22:22.500 So this empty string and this one. 344 22:22.500 --> 22:23.760 Both are different. 345 22:24.300 --> 22:26.460 This one, it means this one. 346 22:26.460 --> 22:28.170 And this one both are different. 347 22:28.530 --> 22:30.690 So it is not able to compare. 348 22:30.700 --> 22:31.480 Right. 349 22:35.140 --> 22:40.960 So you have to compulsory slave right is null only. 350 22:43.870 --> 22:44.980 For the integer values. 351 22:45.460 --> 22:45.880 Yeah. 352 22:45.880 --> 22:47.680 For integer worker anything. 353 22:48.640 --> 22:51.610 Now let's try where MP name is null. 354 22:53.290 --> 22:54.430 No it will not return. 355 22:55.910 --> 22:59.270 We have to give a name is equal to like in the commas. 356 22:59.270 --> 23:01.040 Blank space are equal to. 357 23:01.310 --> 23:02.030 Equal to. 358 23:02.030 --> 23:02.960 Empty string. 359 23:03.530 --> 23:04.130 Empty string. 360 23:04.130 --> 23:04.490 Right. 361 23:07.490 --> 23:08.060 Then it will. 362 23:08.060 --> 23:08.690 It will return. 363 23:08.690 --> 23:09.740 It means this one. 364 23:09.740 --> 23:10.340 And this one. 365 23:10.340 --> 23:12.290 Both are different, right? 366 23:12.290 --> 23:13.190 Both are different. 367 23:13.190 --> 23:13.430 Right. 368 23:16.670 --> 23:21.270 So what is it actually would be null then, you know. 369 23:21.630 --> 23:22.980 Double quotes means it. 370 23:22.980 --> 23:24.900 It shows that nothing is there. 371 23:25.140 --> 23:26.100 Null. 372 23:26.880 --> 23:27.630 It is nothing. 373 23:27.630 --> 23:28.530 Is there again? 374 23:28.770 --> 23:29.400 No, but. 375 23:29.490 --> 23:32.160 But this is known as empty string. 376 23:33.300 --> 23:34.920 This is known as empty string. 377 23:34.920 --> 23:36.330 And this is known as null. 378 23:38.880 --> 23:41.010 So both are SQL server. 379 23:41.010 --> 23:44.810 Both will handle in different ways. 380 23:45.410 --> 23:46.040 Okay. 381 23:47.330 --> 23:50.030 It's weird to me, you know, generally. 382 23:51.800 --> 23:57.110 So the thumb rule is like, you know, if you want to find any blank values or null values in the text, 383 23:57.110 --> 24:00.770 string or wire values, then you have to give is equal to empty string. 384 24:00.770 --> 24:06.470 If you want to find any blank or null values in the like integer values like some numbers are there 385 24:06.470 --> 24:08.010 and you want to find some blank. 386 24:08.010 --> 24:11.010 Then you have to write is null or is not null. 387 24:11.070 --> 24:12.000 Something like that. 388 24:15.150 --> 24:16.650 Thanks, Deepak. 389 24:16.740 --> 24:17.550 Thanks, Arshad. 390 24:19.500 --> 24:20.100 Yeah. 391 24:21.030 --> 24:27.720 Now this is again the same thing for, uh, from amp table. 392 24:27.720 --> 24:31.200 Show me the records whose manager is not yet decided. 393 24:31.200 --> 24:32.590 So again is null. 394 24:32.590 --> 24:35.440 So this person has no manager, right? 395 24:36.580 --> 24:38.980 So that is why it is showing one result. 396 24:39.190 --> 24:41.230 Now this is the reverse case. 397 24:41.230 --> 24:46.810 So where Chemical Commission is not empty there should be some commission are there. 398 24:46.810 --> 24:47.410 Right. 399 24:47.590 --> 24:51.550 So in that case we are able to write is not null. 400 24:51.550 --> 24:56.090 So all this employee has some commission is there. 401 24:56.090 --> 24:56.630 Right. 402 24:56.630 --> 24:58.040 Whether zero. 403 24:58.040 --> 25:00.110 So that is also fine. 404 25:00.110 --> 25:01.760 Something is there right. 405 25:01.760 --> 25:03.080 It is not null. 406 25:04.490 --> 25:07.160 So zero will also it will be displayed. 407 25:12.800 --> 25:13.280 Okay. 408 25:13.280 --> 25:15.590 Now let's see the order by clause. 409 25:18.530 --> 25:22.840 So there is one thing over here. 410 25:22.840 --> 25:29.350 If we fetch some records from the table without any sorting, then their order is not guaranteed. 411 25:29.350 --> 25:35.800 Sometimes, maybe sometimes lakhs of records are there in the table at that time. 412 25:35.800 --> 25:37.660 Order it. 413 25:37.660 --> 25:42.430 When you run the statement on the first side, then it will give in some order. 414 25:42.550 --> 25:47.720 Then again, if you run the second time then it will give again some record over here. 415 25:47.720 --> 25:48.500 Maybe it will. 416 25:48.500 --> 25:52.460 Not much difference, but it may be happen. 417 25:52.700 --> 25:55.760 So if you want to. 418 25:55.790 --> 26:00.230 But if you want to fetch records in a particular order, we need sorting. 419 26:00.230 --> 26:04.730 So order by means to sort the data into some particular order. 420 26:05.030 --> 26:07.930 And for sorting we are using order by clause. 421 26:08.620 --> 26:11.620 Now one more thing over here is. 422 26:11.920 --> 26:18.280 Order by clause is used to sort the data based on one or more columns or by expression. 423 26:18.340 --> 26:21.490 We can give expression also in the order by clause. 424 26:25.180 --> 26:26.260 Now. 425 26:28.390 --> 26:31.210 Over here two separate things are there. 426 26:31.420 --> 26:33.160 Complete select statements. 427 26:33.160 --> 26:37.570 So whenever we write any select statement what is the order? 428 26:39.640 --> 26:45.280 Means what is the syntax of the basic Select statement like it is coming. 429 26:45.280 --> 26:48.040 Select will come first right then. 430 26:48.040 --> 26:50.770 Or are the column names will come then. 431 26:51.280 --> 26:56.160 Then from clause then all joins will become right. 432 26:56.940 --> 26:58.170 Then what will come? 433 26:58.170 --> 26:59.820 Then where close will come. 434 27:00.240 --> 27:03.060 Then if it is a group by then. 435 27:03.060 --> 27:04.590 Group by close will come. 436 27:04.650 --> 27:07.830 If it is a having, then having close will come. 437 27:12.060 --> 27:13.200 One minute. 438 27:15.570 --> 27:18.630 And at the last and at the last order by will come. 439 27:18.630 --> 27:21.430 Right at the last order by will come. 440 27:21.430 --> 27:25.300 So this is a basic syntax of select statement. 441 27:25.390 --> 27:28.360 But now what is the execution order. 442 27:29.770 --> 27:32.410 Execution order is totally different. 443 27:33.070 --> 27:37.420 We will understand it again in future class. 444 27:38.080 --> 27:41.890 But for now I will show you how execution will take place. 445 27:45.500 --> 27:51.290 So execution will select will not run the first first statement in the query. 446 27:51.890 --> 27:55.850 In internal architecture, select will not run for us. 447 27:55.850 --> 27:59.570 It will start from from join. 448 28:03.560 --> 28:14.590 Then where clause from from then it will come join then where clause group by having and then before 449 28:14.590 --> 28:15.790 this order by. 450 28:19.960 --> 28:23.350 Before this order by it will run the select statement. 451 28:26.440 --> 28:29.440 And then at the last only order by statement. 452 28:29.440 --> 28:31.240 So select will be run over here. 453 28:31.240 --> 28:34.160 And then order by will be run over here. 454 28:34.160 --> 28:38.180 When I will show you one example, you will exactly get to know. 455 28:40.700 --> 28:44.090 Now in the SQL server, what is the default order? 456 28:44.090 --> 28:45.500 Can anyone tell me that? 457 28:45.620 --> 28:52.340 What is the by default order in SQL server should be ascending order? 458 28:52.340 --> 28:54.500 Yeah, it will be in ascending order, right? 459 28:54.500 --> 28:56.190 Not in descending order. 460 28:56.490 --> 28:59.880 So now default order is ascending. 461 29:01.740 --> 29:08.730 Now over here I have written display the details of the employee list wise in ascending order from EMP 462 29:08.730 --> 29:09.360 table. 463 29:09.900 --> 29:13.020 So now let me know. 464 29:16.410 --> 29:19.140 Both query will give same result or not. 465 29:21.430 --> 29:22.420 Uh, it will not. 466 29:30.670 --> 29:31.150 Okay. 467 29:31.150 --> 29:35.380 Now order by ename. 468 29:35.380 --> 29:36.250 Now. 469 29:43.060 --> 29:46.440 Now what query will give me the same result or not? 470 29:47.940 --> 29:48.960 It will not. 471 29:49.890 --> 29:51.060 Let's see. 472 29:53.640 --> 29:54.840 It is giving. 473 29:58.440 --> 30:00.000 Uh, actually speaking, it will not. 474 30:00.030 --> 30:09.130 Reason being is, uh, the order which you, uh, arranged, uh, might be like that or the update which 475 30:09.130 --> 30:12.370 you have, um, you know, updated to the table might be like that. 476 30:14.500 --> 30:19.900 No, no, actually, what what you told me only write in SQL server. 477 30:19.900 --> 30:21.970 By default, order is ascending. 478 30:22.330 --> 30:24.100 By default, order is ascending. 479 30:24.100 --> 30:27.310 So if we mention ascending, then also fine. 480 30:27.310 --> 30:31.060 And if we do not mention ascending then then also fine. 481 30:31.060 --> 30:32.330 Then also it will be ascending, right? 482 30:32.330 --> 30:32.570 Correct? 483 30:32.570 --> 30:32.780 Correct. 484 30:32.780 --> 30:33.290 Got it, got it. 485 30:33.290 --> 30:35.810 So that is why it is giving me the same result. 486 30:36.650 --> 30:39.290 Now we need to change it to descending. 487 30:39.290 --> 30:40.790 Then how do you change it to descending then. 488 30:40.790 --> 30:41.420 Yeah. 489 30:41.420 --> 30:44.630 If we want to change it into descending yeah. 490 30:44.630 --> 30:45.830 Then it will be simple. 491 30:45.830 --> 30:48.020 We need to mention des. 492 30:49.250 --> 30:53.990 So it will give the data into the descending order of employee name. 493 30:54.320 --> 30:54.950 Got it. 494 30:56.280 --> 30:57.930 But now suppose. 495 31:00.270 --> 31:07.080 Display the employee list salary wise in descending order from employee table, and only show their 496 31:07.080 --> 31:08.370 name and salary. 497 31:08.520 --> 31:13.650 So over here only we are showing employee name and salary. 498 31:13.650 --> 31:13.920 Right? 499 31:13.920 --> 31:15.840 In order by salary descending. 500 31:16.050 --> 31:18.510 So which salary is showing first? 501 31:18.510 --> 31:19.470 Over here. 502 31:23.900 --> 31:24.800 The higher salary. 503 31:24.800 --> 31:25.340 Yeah. 504 31:25.340 --> 31:26.420 The highest salary. 505 31:26.420 --> 31:26.930 Right. 506 31:27.170 --> 31:30.350 So over here it will show the highest salary to low. 507 31:30.350 --> 31:31.430 Lowest salary. 508 31:33.440 --> 31:34.610 Now higher date. 509 31:34.610 --> 31:40.670 So display the employee list as per their seniority level from EMP table. 510 31:40.670 --> 31:42.500 So who's senior? 511 31:42.740 --> 31:46.440 Who's higher date is lowest from the table. 512 31:46.440 --> 31:46.950 Right. 513 31:46.950 --> 31:49.260 That is the most senior person right. 514 31:49.530 --> 31:54.510 So in this case we need to mention ascending or nothing. 515 31:54.510 --> 31:54.840 Right. 516 31:54.840 --> 31:58.380 So this is the 1980. 517 31:58.410 --> 32:02.340 This is the highest most senior person in the company. 518 32:03.000 --> 32:05.940 And this is the least senior person in the company right. 519 32:10.120 --> 32:12.010 So this is one more example. 520 32:12.010 --> 32:16.000 Now let's see this in a higher date. 521 32:16.000 --> 32:18.100 You don't want to give an ascending right. 522 32:18.100 --> 32:19.960 Since it is comes automatically right. 523 32:19.960 --> 32:20.320 Yeah. 524 32:20.320 --> 32:22.300 This is also fine if you do not fine. 525 32:22.300 --> 32:22.450 Right. 526 32:22.450 --> 32:23.950 Even if you don't do okay fine. 527 32:23.950 --> 32:24.220 Got it. 528 32:24.220 --> 32:24.670 Got it. 529 32:26.590 --> 32:26.980 Yeah. 530 32:26.980 --> 32:29.050 Then also it is giving same result. 531 32:29.260 --> 32:29.860 Got it. 532 32:30.550 --> 32:38.180 Now display the employee list department wise ascending end with the department salary wise descending. 533 32:38.180 --> 32:45.290 So we can mention two different orders in two different columns in same select statement. 534 32:45.290 --> 32:45.830 Right. 535 32:46.070 --> 32:48.830 So over here what will be the result. 536 32:51.590 --> 32:54.530 First row will have which department number. 537 32:57.280 --> 32:59.500 I think we have ten, 20, 30. 538 32:59.500 --> 33:00.100 Right. 539 33:02.350 --> 33:02.920 It will come. 540 33:02.920 --> 33:04.720 1010 would be the first. 541 33:04.750 --> 33:04.960 Okay. 542 33:04.960 --> 33:06.070 It will be empty. 543 33:06.100 --> 33:07.060 It will come first. 544 33:08.050 --> 33:08.530 Okay. 545 33:08.530 --> 33:09.760 It will now. 546 33:09.760 --> 33:11.290 I think ten will come first. 547 33:11.290 --> 33:12.040 Ten salary. 548 33:12.040 --> 33:12.370 What? 549 33:12.370 --> 33:13.480 It will come first. 550 33:15.070 --> 33:16.090 Least number. 551 33:16.180 --> 33:16.690 No. 552 33:16.690 --> 33:18.160 No salary. 553 33:18.160 --> 33:19.000 Descending. 554 33:19.660 --> 33:20.140 That's what. 555 33:20.140 --> 33:21.140 Least a leased number. 556 33:21.170 --> 33:21.830 Low value. 557 33:21.860 --> 33:22.190 No. 558 33:22.190 --> 33:22.430 No. 559 33:23.120 --> 33:24.110 Descending means highest. 560 33:24.110 --> 33:25.490 Descending means highest. 561 33:27.410 --> 33:30.770 So here salary will become from a maximum. 562 33:30.770 --> 33:31.790 Highest to lowest. 563 33:32.030 --> 33:32.480 Yeah. 564 33:32.870 --> 33:34.130 Number will come from lowest. 565 33:34.310 --> 33:34.640 Yeah, yeah. 566 33:34.640 --> 33:38.390 So all the department wise maximum salary will come first. 567 33:38.480 --> 33:39.860 Then second maximum. 568 33:39.860 --> 33:41.450 Then third maximum like that. 569 33:41.450 --> 33:42.050 Right. 570 33:42.980 --> 33:45.750 So listen Okay. 571 33:49.890 --> 33:53.130 Here descending means highest, not the lowest. 572 33:53.130 --> 33:57.390 So see 10th department five is 5000 is highest. 573 33:57.690 --> 33:58.710 It is two times. 574 33:58.710 --> 33:59.760 So it is come first. 575 33:59.760 --> 34:02.460 Then 2400 is coming second. 576 34:02.460 --> 34:04.680 This is comes third. 577 34:04.680 --> 34:06.690 Like that in 20 this one is highest. 578 34:06.690 --> 34:07.830 So it will come first. 579 34:08.260 --> 34:09.760 then this will come second. 580 34:09.760 --> 34:10.960 Like that, right? 581 34:16.300 --> 34:18.760 Okay, now one more thing. 582 34:19.810 --> 34:21.130 Display. 583 34:22.180 --> 34:28.720 Display the details of the students in order of their total marks in descending order. 584 34:34.740 --> 34:37.680 Okay, now what we need to do over here. 585 34:41.790 --> 34:43.890 You understand the question, right? 586 34:44.040 --> 34:45.420 Display. 587 34:49.290 --> 34:55.530 Display the details of the students in order of the total marks in descending order. 588 34:58.480 --> 35:03.010 Means highest mark of total mark on the top. 589 35:04.090 --> 35:04.840 Sorry. 590 35:06.460 --> 35:06.880 Yeah. 591 35:06.880 --> 35:08.740 We need to sort the marks in descending order. 592 35:08.740 --> 35:09.130 Right. 593 35:09.520 --> 35:10.300 So how. 594 35:10.300 --> 35:12.550 How can you write me a query? 595 35:13.720 --> 35:14.440 Yeah, yeah. 596 35:14.590 --> 35:15.610 Select star from. 597 35:15.610 --> 35:16.840 Can you show me the table? 598 35:19.810 --> 35:20.870 So much. 599 35:21.380 --> 35:22.490 These are the marks, right? 600 35:22.610 --> 35:23.270 Okay. 601 35:23.810 --> 35:24.440 Where? 602 35:24.440 --> 35:26.420 Like we can put maths. 603 35:28.130 --> 35:28.850 Where? 604 35:32.540 --> 35:33.110 Um. 605 35:33.410 --> 35:34.310 Math? 606 35:34.340 --> 35:35.930 That's a column name, right? 607 35:37.490 --> 35:38.300 Okay. 608 35:38.300 --> 35:39.710 This is descending. 609 35:41.750 --> 35:43.310 Just give me a second. 610 35:43.940 --> 35:44.830 Sorry, sorry, Sorry. 611 35:44.980 --> 35:45.910 Order by order. 612 35:45.910 --> 35:46.390 By match. 613 35:46.390 --> 35:46.960 Descending. 614 35:56.020 --> 35:56.950 Then. 615 35:59.110 --> 35:59.680 Again? 616 36:02.140 --> 36:02.410 Yeah. 617 36:02.410 --> 36:03.730 This is only for one subject. 618 36:03.730 --> 36:05.260 If you want to do for multiple. 619 36:05.260 --> 36:07.390 So we can put comma physics and all. 620 36:08.930 --> 36:15.020 So no over here one more thing you need to take care. 621 36:15.200 --> 36:23.120 We have to do like this math plus physic plus. 622 36:25.430 --> 36:28.820 Chemistry descending. 623 36:29.810 --> 36:32.060 Now let me show you. 624 36:43.350 --> 36:44.550 Now let's see. 625 36:44.550 --> 36:45.960 This is the answer. 626 36:50.760 --> 36:52.740 You means manually. 627 36:52.740 --> 36:55.410 You are doing descending for each of the column, right? 628 36:55.410 --> 36:57.280 That is not a proper way, right? 629 37:00.580 --> 37:01.780 Okay, okay. 630 37:02.230 --> 37:09.100 So here you we I am want to tell that we can do expression in the order by as well. 631 37:09.490 --> 37:11.170 This is one expression right. 632 37:11.170 --> 37:13.480 Math plus physics plus chemistry. 633 37:13.480 --> 37:19.810 So we are doing the total of all the three column and then doing descending based upon all these three 634 37:19.810 --> 37:20.440 column. 635 37:20.440 --> 37:20.460 columns. 636 37:23.760 --> 37:25.740 Now let me give one alias. 637 37:26.640 --> 37:29.760 Can anyone tell me why no column name is given over here? 638 37:29.760 --> 37:30.810 No column name. 639 37:31.560 --> 37:33.480 Because we have not given any alias. 640 37:33.480 --> 37:34.260 Column name. 641 37:34.410 --> 37:34.920 Okay. 642 37:34.920 --> 37:36.810 You can put as as total. 643 37:36.810 --> 37:38.190 Total marks something. 644 37:38.850 --> 37:41.310 So now, one more thing. 645 37:47.950 --> 37:49.450 Can I use like this? 646 37:51.550 --> 37:52.570 Yes, yes. 647 37:53.410 --> 37:54.280 Why? 648 37:55.570 --> 37:56.920 Because we have defined a name. 649 37:56.920 --> 37:57.910 Temporary name. 650 37:58.030 --> 37:58.660 Total. 651 37:58.660 --> 38:00.160 So you know. 652 38:01.150 --> 38:03.310 Okay, now, one more question. 653 38:09.590 --> 38:11.210 Can I use like this? 654 38:16.070 --> 38:19.130 No, I think it will not accept because it's not a column. 655 38:19.880 --> 38:25.490 So here you are telling that it is a column and here you are telling order by. 656 38:25.490 --> 38:26.330 It will accept. 657 38:27.260 --> 38:28.100 I think so. 658 38:28.940 --> 38:30.710 Yeah, right. 659 38:30.950 --> 38:33.020 So let me try this one. 660 38:36.240 --> 38:38.280 Invalid column name total. 661 38:39.480 --> 38:43.830 But let me try over here. 662 38:51.120 --> 38:54.150 It is working, right. 663 38:54.750 --> 38:55.500 But why? 664 38:55.500 --> 38:56.400 You know why. 665 38:56.570 --> 38:58.340 Because of this one. 666 38:59.270 --> 39:00.530 Because of this one. 667 39:00.620 --> 39:01.940 I have shown you. 668 39:04.760 --> 39:07.190 What is the execution order over here. 669 39:07.430 --> 39:10.370 Order by clause will be lastly executed. 670 39:10.370 --> 39:14.120 Whereas order by clause will be executed. 671 39:16.190 --> 39:17.630 After select. 672 39:18.530 --> 39:19.370 And over here. 673 39:19.370 --> 39:20.600 What is the problem? 674 39:22.470 --> 39:28.230 Select will be used after where clause. 675 39:30.000 --> 39:33.660 So whatever the column name is there in the select. 676 39:36.030 --> 39:43.170 Is alias we cannot use in the Where clause where clause, but we can use in the order by clause because 677 39:43.290 --> 39:45.730 order by will be executed after select. 678 39:45.730 --> 39:52.120 So if we give alias over here, that will be accessible in the order by clause. 679 39:54.970 --> 39:55.690 Right, right. 680 39:56.170 --> 40:03.850 But over here it is not accessible because this one will be already executed before the select statement. 681 40:04.360 --> 40:06.790 So how come this alias will be? 682 40:06.790 --> 40:08.500 We can refer in the Where clause. 683 40:08.500 --> 40:10.040 We cannot refer it. 684 40:12.200 --> 40:15.950 So that is the power of execution order. 685 40:16.880 --> 40:19.820 Now this is the same thing I have written over here. 686 40:20.960 --> 40:28.280 Now this one also someone told write total descending display the detail of the student in order of 687 40:28.280 --> 40:31.130 their total marks in descending order. 688 40:31.130 --> 40:35.560 In case of in Thai, Then sort by match in descending. 689 40:35.980 --> 40:38.560 In in case of further each tie. 690 40:40.390 --> 40:42.400 Then sort by physics descending. 691 40:42.400 --> 40:47.440 So over here we have done the multiple sorting right again. 692 40:49.780 --> 40:51.100 Order by total. 693 40:53.740 --> 40:55.060 Order by total descending. 694 40:55.060 --> 40:58.580 But if it is tie then sort by math descending still. 695 40:58.580 --> 41:01.520 If it is tight and sort by physics maybe. 696 41:01.520 --> 41:09.290 Currently we do not have data like that, but I just show you this example. 697 41:13.070 --> 41:15.320 Now let's see one more example. 698 41:16.550 --> 41:21.260 Display the average marks of each student in front of him. 699 41:23.190 --> 41:26.340 and also show the result as per their average marks. 700 41:29.040 --> 41:31.920 So average mark how we can get the average mark. 701 41:32.280 --> 41:34.800 We need to do total divided by three, right? 702 41:34.800 --> 41:36.510 How many students are there? 703 41:36.870 --> 41:38.580 We have three students are there. 704 41:38.580 --> 41:39.150 Right? 705 41:41.730 --> 41:42.450 Sorry. 706 41:42.450 --> 41:44.100 We have three subjects are there right. 707 41:44.100 --> 41:45.030 We have three subjects. 708 41:45.030 --> 41:45.650 Are there? 709 41:45.680 --> 41:46.970 Yes, yes. 710 41:46.970 --> 41:52.970 So total of all the three subjects and then divided by three. 711 41:56.360 --> 41:59.810 And we need to mention that mark in front of each of the student. 712 41:59.900 --> 42:01.970 So this is the average mark. 713 42:03.020 --> 42:10.560 And now we are sorting based upon what we are sorting based upon order by average descending, because 714 42:10.560 --> 42:12.660 we have given Avg's alias over here. 715 42:12.660 --> 42:13.170 Right? 716 42:13.410 --> 42:18.420 And we can use alias in the order by clause, as we have already discussed. 717 42:18.690 --> 42:25.860 So eating it is sorting based upon the average, not not any of the subject Mark. 718 42:25.860 --> 42:26.490 Right? 719 42:32.700 --> 42:40.990 Now this is also a very simple display employee working as clerk or manager and arrange the list. 720 42:40.990 --> 42:47.860 Arrange the list salary wise descending order so all the clerks and manager will come in. 721 42:47.860 --> 42:50.710 Salary will be in descending order. 722 42:50.710 --> 42:51.850 So let's see. 723 42:58.490 --> 43:00.470 Ordered by salary descending order. 724 43:00.470 --> 43:04.340 So now everything is ordered in descending order, right? 725 43:04.640 --> 43:09.410 But now what if I want to show Clark in first and manager will be second? 726 43:09.710 --> 43:12.020 Then what change I can do over here. 727 43:17.210 --> 43:17.540 Order! 728 43:17.540 --> 43:19.220 By now we have to put order by. 729 43:19.220 --> 43:19.670 Yeah. 730 43:19.670 --> 43:20.570 Order by job. 731 43:22.690 --> 43:23.110 Ascending? 732 43:25.360 --> 43:30.940 Yeah, ascending will be by default, so no need to mention right now. 733 43:33.430 --> 43:37.900 All the class will come first based upon salary descending in order. 734 43:37.900 --> 43:41.290 The manager will come later based upon salary descending. 735 43:44.410 --> 43:50.690 Now distinct operator is very important in everything is important. 736 43:51.110 --> 43:56.660 So distinct will be used to eliminate the duplicates from the select statement output. 737 43:57.830 --> 44:00.020 So suppose. 738 44:03.170 --> 44:08.690 Let me I think this table is not created with me. 739 44:16.890 --> 44:17.250 Okay. 740 44:17.250 --> 44:17.670 Over here. 741 44:17.670 --> 44:19.140 Any name is repeating. 742 44:19.290 --> 44:19.800 Okay. 743 44:19.800 --> 44:21.270 We can use job, right? 744 44:21.450 --> 44:28.530 So now if we want to consider what are the distinct jobs? 745 44:28.530 --> 44:29.130 Are there. 746 44:29.130 --> 44:32.250 What are the distinct jobs are there in the employee table. 747 44:32.520 --> 44:37.330 Then we can say 4 or 5 distinct jobs are there, right? 748 44:37.390 --> 44:43.600 So what we can do distinct job from EMP table. 749 44:46.240 --> 44:50.560 So these five distinct jobs are available in the EMP table right. 750 44:55.360 --> 44:58.440 These five distinct jobs are there in the EMP table. 751 44:58.470 --> 45:03.960 Also, we can do do distinct on the multiple columns as well. 752 45:03.960 --> 45:05.370 So suppose. 753 45:15.270 --> 45:17.190 Let's see. 754 45:23.200 --> 45:25.240 Any salary is repeating. 755 45:26.200 --> 45:26.980 Okay. 756 45:26.980 --> 45:32.170 We can consider this one higher rate in Clark. 757 45:32.170 --> 45:39.520 So if we are mentioned over here job comma higher date then. 758 45:39.670 --> 45:42.730 And let me take this EMP table as well. 759 45:42.970 --> 45:45.730 Select star from EMP table. 760 45:49.880 --> 45:51.620 Now this record. 761 45:59.660 --> 46:00.050 Yes. 762 46:00.050 --> 46:01.040 Duplicates is there. 763 46:01.460 --> 46:03.890 Okay I have forget to mention distinct. 764 46:03.890 --> 46:04.850 Distinct. 765 46:07.820 --> 46:09.080 Now let's see. 766 46:14.640 --> 46:16.170 Correct output is correct. 767 46:17.760 --> 46:18.210 Correct. 768 46:18.210 --> 46:18.810 Right. 769 46:19.140 --> 46:19.710 Over. 770 46:19.710 --> 46:20.370 Where is that? 771 46:20.370 --> 46:22.140 Clark is coming two times. 772 46:24.090 --> 46:29.940 This is one record, and I think another record is over the top itself. 773 46:29.940 --> 46:30.450 You can see. 774 46:30.450 --> 46:30.660 Okay. 775 46:30.660 --> 46:31.170 Yes, yes. 776 46:31.170 --> 46:32.310 Top one itself. 777 46:32.310 --> 46:35.030 It is duplicated 112. 778 46:35.030 --> 46:37.640 And over here 112 is coming only once, right? 779 46:38.540 --> 46:42.590 112 is coming only once, but above one it is coming two times. 780 46:42.590 --> 46:47.000 So we can use multiple columns as well as well as what we can do. 781 46:48.020 --> 46:48.710 Now. 782 46:48.710 --> 46:50.120 Can anyone tell me? 783 46:50.240 --> 46:51.980 Either this will work or not. 784 46:56.060 --> 46:56.270 Uh. 785 46:56.270 --> 46:56.930 This statement. 786 46:57.780 --> 46:58.260 Yeah. 787 46:58.260 --> 46:59.970 This is highlighted statement. 788 47:03.570 --> 47:05.760 Select distinct country from experience. 789 47:05.760 --> 47:07.050 I think it will work. 790 47:07.080 --> 47:07.770 No, no. 791 47:07.770 --> 47:08.700 Not country. 792 47:08.700 --> 47:08.820 Uh. 793 47:08.820 --> 47:09.600 This one. 794 47:12.390 --> 47:13.410 This one. 795 47:14.910 --> 47:15.840 It will not work. 796 47:15.840 --> 47:16.440 Why? 797 47:20.850 --> 47:21.570 Try I should. 798 47:21.570 --> 47:21.580 it. 799 47:24.370 --> 47:25.570 It will work. 800 47:28.540 --> 47:31.960 It will do distinct, based upon all the columns. 801 47:31.960 --> 47:34.750 It will do distinct based upon all the columns. 802 47:42.910 --> 47:44.110 So. 803 47:50.600 --> 47:55.070 Then this one will give top close. 804 47:55.070 --> 47:56.480 Top close is there. 805 47:56.510 --> 48:03.260 So suppose if I want only top five rows from the EMP table, right? 806 48:03.500 --> 48:06.500 Only top five rows from the EMP table. 807 48:06.890 --> 48:11.270 Then what we can do we can do select top five star from EMP. 808 48:12.410 --> 48:14.870 So it will give me only top five rows. 809 48:18.200 --> 48:21.800 Now top 10%. 810 48:21.800 --> 48:22.730 Also we can use. 811 48:22.730 --> 48:28.640 So suppose top 10%. 812 48:31.370 --> 48:35.660 Now let me try whether star star it will take or not. 813 48:37.400 --> 48:38.150 Okay. 814 48:38.150 --> 48:40.100 It is showing top 10%. 815 48:40.100 --> 48:42.170 So around it will be. 816 48:42.200 --> 48:44.540 We have 20 rows or around 18 rows. 817 48:44.540 --> 48:46.490 Are there right in the empty table. 818 48:49.310 --> 48:52.610 In in the empty table we have 18 rows are there. 819 48:52.700 --> 48:54.860 So randomly it will take. 820 48:56.900 --> 49:01.740 Randomly it will consider top 10%. 821 49:01.740 --> 49:02.610 Right? 822 49:05.250 --> 49:11.910 Because from 18 to top ten 1.8 row, it cannot return right 1.8 row it cannot return. 823 49:12.510 --> 49:13.350 So it will. 824 49:13.350 --> 49:17.490 It has randomly selected two rows and it is returning that two rows. 825 49:18.990 --> 49:21.720 Now how do you interpret the second statement? 826 49:21.750 --> 49:23.300 The interpreter of the answers. 827 49:23.300 --> 49:26.630 So, you know, let me do top 20%. 828 49:26.630 --> 49:29.570 So what is the 20% of 18. 829 49:33.740 --> 49:36.350 20% of 18 two. 830 49:38.630 --> 49:42.050 3.63.6. 831 49:42.050 --> 49:42.500 Right. 832 49:45.050 --> 49:45.500 Okay. 833 49:45.500 --> 49:45.980 Right. 834 49:45.990 --> 49:49.740 So either it will return three rows or four rows. 835 49:52.950 --> 49:55.590 Because half row it cannot return right. 836 49:55.590 --> 49:57.960 Either it will return three rows or four rows. 837 49:58.020 --> 49:59.520 Let's see the result. 838 50:00.720 --> 50:03.930 It is returning four rows randomly. 839 50:03.930 --> 50:05.130 It will do randomly. 840 50:05.130 --> 50:08.280 It will do 20% of the entire table. 841 50:08.280 --> 50:11.360 And then it will return retain all the data's. 842 50:13.070 --> 50:16.250 Means all the data means 20% data only. 843 50:18.020 --> 50:22.610 So now if I do the 50% right, then how much row it will return. 844 50:24.710 --> 50:26.180 The 99th row. 845 50:26.180 --> 50:26.660 Right. 846 50:29.180 --> 50:29.960 Perfect. 847 50:29.960 --> 50:30.470 It is. 848 50:31.340 --> 50:31.790 Yeah. 849 50:31.790 --> 50:32.210 Perfect. 850 50:32.210 --> 50:34.010 It is returning only nine nine rows. 851 50:34.940 --> 50:35.390 Okay. 852 50:35.390 --> 50:37.370 Because it is 18 rows, which we. 853 50:37.430 --> 50:37.940 Yeah, right. 854 50:37.940 --> 50:39.410 1,850% is okay. 855 50:39.410 --> 50:39.650 Yeah. 856 50:39.650 --> 50:40.880 50% of 18. 857 50:40.880 --> 50:41.750 Nine half. 858 50:52.340 --> 50:53.810 Top with ties. 859 50:53.810 --> 50:54.830 Are there one operation? 860 50:54.860 --> 50:56.810 Operation is top with ties. 861 50:56.960 --> 50:59.560 Let's try to use this one. 862 51:01.270 --> 51:02.650 Top two with ties. 863 51:02.650 --> 51:03.790 So. 864 51:07.450 --> 51:08.260 Okay. 865 51:08.260 --> 51:09.610 Now over here. 866 51:09.610 --> 51:11.110 What is the problem? 867 51:12.160 --> 51:13.960 Now remove this one. 868 51:16.360 --> 51:20.110 Now can anyone tell me what this will return? 869 51:21.520 --> 51:22.060 This. 870 51:22.060 --> 51:29.570 Both the queries means how many rows it will return in the first query, and how many rows it will return 871 51:29.570 --> 51:31.580 in the second query. 872 51:35.450 --> 51:37.370 In first it will return two records. 873 51:37.370 --> 51:45.860 In case of second, it will return the uh, the records which are also uh name is same like okay name 874 51:45.860 --> 51:48.060 multiple records in the subsecond query. 875 51:49.290 --> 51:53.640 I think it will return to only in both both query. 876 51:55.530 --> 51:57.180 I think this no. 877 51:57.180 --> 51:58.800 The second query can return more. 878 51:59.250 --> 51:59.520 Okay. 879 51:59.520 --> 52:00.270 Let's see. 880 52:13.480 --> 52:19.420 Okay, so now what we are doing over here is my second query is returning three record. 881 52:19.420 --> 52:24.430 Because in this salary it is going to be tie right. 882 52:24.430 --> 52:25.510 In the salary. 883 52:25.510 --> 52:26.650 It is going to be tie. 884 52:26.650 --> 52:27.370 Why tie. 885 52:27.370 --> 52:33.250 Because 5000 salaries are given for the multiple employees. 886 52:33.250 --> 52:37.380 So over here it is written order by salary descending. 887 52:37.380 --> 52:44.850 So whenever in the salary it will be tie it will return more than more than two two rows as well. 888 52:44.850 --> 52:51.690 So if suppose if the three employees will have the same salary 5000, then again it will return four 889 52:51.690 --> 52:52.950 rows in this case. 890 52:52.950 --> 52:54.030 Second case. 891 52:54.870 --> 53:00.670 But in the first case it is where we're not doing any ties kind of thing. 892 53:00.670 --> 53:00.910 Right. 893 53:00.910 --> 53:04.780 So it will by default it is showing only two rows only. 894 53:09.790 --> 53:11.920 So here salary. 895 53:11.920 --> 53:18.070 But we mentioned that descending it takes the highest value of the salary and takes a tie. 896 53:18.100 --> 53:18.520 Right. 897 53:19.060 --> 53:19.900 Yeah. 898 53:19.900 --> 53:23.090 It will take the highest value first. 899 53:23.120 --> 53:24.740 Now in the highest value. 900 53:24.740 --> 53:30.200 If in the highest value tie is there, then it will take the ties as well. 901 53:30.230 --> 53:33.950 But if if it is ties is not there then. 902 53:34.820 --> 53:39.350 Then it will then above query and below query both will be give same result. 903 53:39.350 --> 53:40.010 Right. 904 53:41.450 --> 53:49.440 If a 5000 salary will be allocated to only one employee, Then it will give two rows only. 905 53:53.970 --> 53:57.450 That is very much gettable, right? 906 54:01.200 --> 54:02.370 But over here. 907 54:03.270 --> 54:04.170 But over here. 908 54:04.170 --> 54:07.920 Tie is there in the highest salary or second highest salary. 909 54:08.100 --> 54:12.200 That is why it is giving me more than two rows. 910 54:20.300 --> 54:22.490 I think this will take some time. 911 54:22.490 --> 54:25.070 So this way we will see you tomorrow. 912 54:25.070 --> 54:25.550 Offset. 913 54:25.550 --> 54:26.180 Fetch. 914 54:29.570 --> 54:30.440 Yes. 915 54:30.440 --> 54:31.730 Too many inputs today. 916 54:33.590 --> 54:34.880 Yes, yes.