1 00:01.100 --> 00:04.430 Okay, so today we will see the Self-join. 2 00:04.460 --> 00:06.710 Self-join in SQL. 3 00:08.030 --> 00:12.770 So joining a table with itself is known as Self-join. 4 00:12.860 --> 00:18.950 And record in one table is joined with other record on same table. 5 00:19.670 --> 00:20.180 Right. 6 00:20.180 --> 00:24.710 So over here in the self-join we are joining the same table with itself. 7 00:25.070 --> 00:27.170 And this table contains. 8 00:27.380 --> 00:30.440 So let me directly take one example. 9 00:33.710 --> 00:35.930 Let me copy this data. 10 00:39.950 --> 00:41.690 Into the Excel file. 11 00:45.020 --> 00:54.560 In I am copying this data two times because I want to join same table with multiple times right. 12 00:56.990 --> 01:02.630 So this table contains manager ID but we need to display manager name for the same purpose. 13 01:02.630 --> 01:05.900 We can use Self-join to perform the Self-join. 14 01:05.900 --> 01:11.630 The same table must be declared two times with different aliases in the from clause. 15 01:11.840 --> 01:18.410 So over here what we are doing is uh, see here properly. 16 01:18.410 --> 01:21.440 So over here we have EMP table. 17 01:21.470 --> 01:21.710 Right. 18 01:21.710 --> 01:32.690 I have copied EMP table two times and we have to we, I have given EMP as ex and EMP as y. 19 01:33.020 --> 01:34.460 So. 20 01:37.400 --> 01:42.170 If you see this is my we can consider this one as ex table. 21 01:44.360 --> 01:46.460 And this one is y table. 22 01:46.610 --> 01:49.670 So actually the table is same only. 23 01:49.670 --> 01:55.160 But what we have given this x and y is alias right. 24 01:55.190 --> 01:55.670 Alias. 25 01:55.670 --> 01:58.980 Then what we are doing x dot manager. 26 02:00.000 --> 02:02.370 We are joining ex-manager. 27 02:02.400 --> 02:07.410 X dot manager means this table manager. 28 02:08.580 --> 02:16.650 This table manager column with a y table EMP number column means this table EMP number column. 29 02:16.800 --> 02:21.810 Right now let's start with the first first record. 30 02:21.990 --> 02:25.980 What is the manager ID of this table? 31 02:26.010 --> 02:28.080 7902. 32 02:28.110 --> 02:28.350 Right. 33 02:28.380 --> 02:29.730 7902. 34 02:29.760 --> 02:32.340 It will be joining with EMP. 35 02:32.340 --> 02:33.480 Number of second table. 36 02:33.510 --> 02:34.110 Right. 37 02:36.030 --> 02:40.050 So 7902 with EMP table of this one. 38 02:40.080 --> 02:46.080 So ideally we can say this Smith Smith employee. 39 02:46.530 --> 02:49.110 What is the employee manager ID of Smith. 40 02:49.140 --> 02:50.970 This is 7902. 41 02:51.090 --> 02:53.250 So where there 7902. 42 02:53.280 --> 02:55.050 In this particular Y table. 43 02:55.050 --> 02:56.860 So it will be over here right? 44 02:56.890 --> 02:59.440 So manager of Smith is Ford. 45 03:00.310 --> 03:01.900 We can say manager. 46 03:01.930 --> 03:03.610 This is employee name. 47 03:09.490 --> 03:13.600 And this is manager name. 48 03:13.600 --> 03:16.420 So manager of Smith is Ford. 49 03:16.450 --> 03:18.550 Now what will be the manager of Allen. 50 03:18.550 --> 03:21.100 So let me take all the data over here. 51 03:23.860 --> 03:25.930 These are my all the employees. 52 03:25.930 --> 03:28.630 What is the main manager of Allen. 53 03:28.630 --> 03:31.750 So for that firstly we need to look into manager ID. 54 03:33.130 --> 03:37.030 Manager ID in the X table 6798. 55 03:37.060 --> 03:37.600 Right. 56 03:37.660 --> 03:39.550 7698. 57 03:39.550 --> 03:42.310 So where is 7698 is coming over here. 58 03:42.310 --> 03:44.410 So it is coming over here right. 59 03:44.410 --> 03:45.100 Black. 60 03:45.520 --> 03:49.960 So black will be the manager of this particular Allen. 61 03:54.400 --> 03:58.710 Then you can see over here also it will be black only, right? 62 03:59.070 --> 04:04.140 Over here also over here also it will be black only. 63 04:04.140 --> 04:05.910 So let me copy again. 64 04:06.480 --> 04:08.460 Then let's check for this one. 65 04:08.490 --> 04:09.240 Jones. 66 04:09.360 --> 04:13.110 So 7839 where 7839 is coming over here. 67 04:13.140 --> 04:14.640 You need to look into it. 68 04:15.330 --> 04:16.560 So King. 69 04:17.730 --> 04:19.740 King will be coming over here. 70 04:19.770 --> 04:24.540 Now let's say for others as well Martin. 71 04:24.540 --> 04:28.260 So for Martin again 7698 right. 72 04:29.130 --> 04:31.770 So for Martin also it will be black. 73 04:33.330 --> 04:40.080 Then for black, black will be seven, 839 again seven, 839. 74 04:40.080 --> 04:42.780 So King will be repeating over here. 75 04:44.850 --> 04:46.110 Then for Scott. 76 04:47.190 --> 04:50.130 So Scott 7566. 77 04:50.250 --> 04:53.370 So where 7566 is coming over here. 78 04:55.060 --> 04:56.350 said Jones. 79 04:56.350 --> 04:56.620 Right. 80 04:56.650 --> 04:57.220 Seven five. 81 04:57.250 --> 04:58.090 Six six. 82 04:58.570 --> 05:02.020 So Jones will be the manager of Scott, right? 83 05:02.650 --> 05:05.470 Then King, let's say carefully over here. 84 05:05.500 --> 05:06.310 King. 85 05:06.460 --> 05:08.230 There is no manager. 86 05:08.440 --> 05:10.900 Manager ID is null over here, right? 87 05:10.930 --> 05:15.580 Manager ID so null cannot be joined over here in the employee column. 88 05:16.240 --> 05:19.570 So actually this record will not come. 89 05:21.790 --> 05:26.200 When we specify the inner join, this record will not come. 90 05:26.620 --> 05:28.690 Then let's see for Turner. 91 05:29.290 --> 05:31.960 So Turner 7698. 92 05:32.650 --> 05:35.320 So I think again it will be black only. 93 05:36.400 --> 05:42.790 So for Turner again it will be black then for Adam 7788. 94 05:42.820 --> 05:43.600 Where seven. 95 05:43.630 --> 05:44.350 Scott. 96 05:44.380 --> 05:45.190 Right. 97 05:48.580 --> 05:49.780 Then James. 98 05:49.780 --> 05:53.290 James 17698. 99 05:53.320 --> 05:55.540 So I think again it will be black only. 100 05:57.280 --> 05:57.820 Ten. 101 05:57.820 --> 05:58.390 Four four. 102 05:58.420 --> 05:58.810 Seven. 103 05:58.840 --> 05:59.080 Five. 104 05:59.110 --> 05:59.350 Six. 105 05:59.380 --> 05:59.620 Six. 106 05:59.650 --> 05:59.860 Where? 107 05:59.890 --> 06:00.130 Seven. 108 06:00.160 --> 06:00.340 Five. 109 06:00.370 --> 06:00.550 Six. 110 06:00.580 --> 06:01.540 Six is coming. 111 06:01.810 --> 06:02.710 Seven. 112 06:02.740 --> 06:03.400 Five. 113 06:03.430 --> 06:03.940 Six. 114 06:03.970 --> 06:04.840 Six. 115 06:07.210 --> 06:07.870 Jones. 116 06:07.870 --> 06:08.200 Right. 117 06:08.230 --> 06:09.040 Jones. 118 06:10.960 --> 06:11.650 So. 119 06:11.680 --> 06:12.040 Jones. 120 06:12.040 --> 06:13.240 It will become ten. 121 06:13.240 --> 06:13.660 Miller. 122 06:13.690 --> 06:14.380 Miller. 123 06:14.410 --> 06:14.950 Seven. 124 06:14.980 --> 06:16.060 782. 125 06:16.360 --> 06:17.170 Seven. 126 06:17.200 --> 06:17.560 Seven. 127 06:17.590 --> 06:17.830 Eight. 128 06:17.860 --> 06:18.310 Two. 129 06:19.330 --> 06:20.110 Clark. 130 06:23.230 --> 06:24.310 And for Jerry. 131 06:24.340 --> 06:25.450 Last one is there. 132 06:25.450 --> 06:25.750 Seven. 133 06:25.780 --> 06:25.990 Six. 134 06:26.020 --> 06:26.260 Five. 135 06:26.290 --> 06:26.860 Four. 136 06:26.950 --> 06:27.790 Seven. 137 06:27.820 --> 06:28.330 Martin. 138 06:28.330 --> 06:28.960 Right. 139 06:34.000 --> 06:36.280 Now, this is our solution. 140 06:36.460 --> 06:39.190 Now over here. 141 06:39.190 --> 06:40.510 One thing we want. 142 06:40.540 --> 06:43.390 Employee name means from this name column. 143 06:43.900 --> 06:46.720 From this x table and this name. 144 06:46.720 --> 06:48.550 Column from this y table. 145 06:49.090 --> 06:49.690 Y table. 146 06:49.690 --> 06:54.370 So what I have done x dot name and y dot name. 147 06:55.870 --> 06:58.060 Now let me execute this query. 148 07:09.100 --> 07:09.910 Mm. 149 07:10.180 --> 07:10.420 Mm. 150 07:11.680 --> 07:13.210 So one record is coming. 151 07:13.240 --> 07:14.350 Coming less right. 152 07:14.350 --> 07:16.180 So if you see Jerry Martin. 153 07:16.210 --> 07:23.920 Jerry Martin Miller Clark Miller Clark for for Jones for Jones, James Blake, James Blake. 154 07:23.920 --> 07:25.120 Everything is correct. 155 07:25.240 --> 07:26.950 This one record is not coming. 156 07:26.950 --> 07:27.580 Right. 157 07:29.230 --> 07:30.100 Because of. 158 07:30.130 --> 07:33.100 We have written the inner join while doing the self join. 159 07:33.490 --> 07:37.420 But in Oracle we can have a self join as well. 160 07:40.150 --> 07:44.080 From my self join as well. 161 07:44.080 --> 07:46.750 But in SQL server there is no self join. 162 07:46.750 --> 07:50.210 We need to write either Inner join or left join. 163 07:50.240 --> 07:54.080 Right inner join or left join. 164 07:54.080 --> 07:59.180 We have to write right then if I want. 165 07:59.210 --> 08:00.440 If I want. 166 08:05.270 --> 08:08.600 This I if I want this King record also. 167 08:10.460 --> 08:18.710 If I want this King record also then what I need to do, I need to do left join right I need to do left 168 08:18.710 --> 08:23.780 join because I want all the record from this left table X table. 169 08:27.080 --> 08:30.290 And matching record from the right table. 170 08:30.650 --> 08:40.700 So I want King also so so to want King I want to mention left join right then. 171 08:44.180 --> 08:48.230 But now the problem over here is in place of King. 172 08:48.230 --> 08:51.110 It is coming over here as a null. 173 08:51.110 --> 08:53.900 So I don't want null over here. 174 08:53.900 --> 09:01.280 I want either CEO or a super manager or that kind of thing. 175 09:01.280 --> 09:06.440 So for that purpose I need to mention like this is null. 176 09:09.800 --> 09:15.590 Is null and then comma super manager. 177 09:17.690 --> 09:21.020 So when there is a null it will be a super manager right. 178 09:27.680 --> 09:30.170 When there is a null it will be a super manager. 179 09:32.810 --> 09:35.510 So see over here in place of King it is coming. 180 09:35.510 --> 09:39.230 Super manager uh right now. 181 09:52.430 --> 09:57.800 since the same thing I have display the employee name reporting to blank. 182 09:58.460 --> 10:01.580 Display the employee name reporting to blank. 183 10:02.900 --> 10:10.940 So y dot e name over here we want e name from the manager column right E name from the manager column. 184 10:10.940 --> 10:16.940 So employee name reporting to blank means y dot e name equal to blank right. 185 10:19.010 --> 10:21.620 So how many employees are reporting to blank. 186 10:24.680 --> 10:26.270 So I think one two. 187 10:28.760 --> 10:29.930 Then three. 188 10:33.530 --> 10:34.520 Then four. 189 10:37.940 --> 10:38.900 Then five. 190 10:38.930 --> 10:41.930 So around five employees are reporting to blank right. 191 10:41.960 --> 10:43.910 Let me execute this query. 192 10:44.250 --> 10:45.150 Perfect. 193 10:45.150 --> 10:46.500 All these five employees. 194 10:46.530 --> 10:53.700 Alan Ward, Martin Turner and James, all these five employees are reporting to Blake. 195 10:54.330 --> 10:55.710 But now I want. 196 10:55.740 --> 10:57.870 Okay, this is the same query only. 197 11:02.370 --> 11:05.490 But now I want Blake's manager name. 198 11:05.490 --> 11:08.910 So in this place, the query will be the same. 199 11:09.540 --> 11:13.200 But Blake will become in the x note dot name. 200 11:13.200 --> 11:13.410 Right. 201 11:13.440 --> 11:15.450 Because I want Blake's manager name. 202 11:15.540 --> 11:18.240 So who is the manager of Blake? 203 11:18.240 --> 11:19.380 So let me. 204 11:19.410 --> 11:20.760 It is a king, right? 205 11:21.750 --> 11:22.110 Blake. 206 11:22.110 --> 11:23.880 Manager is king over here. 207 11:25.350 --> 11:27.480 So let me execute this query. 208 11:30.150 --> 11:32.340 It will be king only, right? 209 11:36.840 --> 11:39.750 So now let me unmute you for two minutes. 210 11:39.750 --> 11:42.600 This is what Self-join is. 211 11:51.060 --> 11:51.960 It is clear. 212 11:51.960 --> 11:52.890 Self-join. 213 11:57.120 --> 11:58.830 Probably the last one only. 214 11:59.040 --> 12:01.800 I need a little more clarity on it. 215 12:02.580 --> 12:03.150 The last one. 216 12:03.150 --> 12:03.330 What? 217 12:03.330 --> 12:03.900 You thought? 218 12:04.620 --> 12:05.070 Yeah. 219 12:05.070 --> 12:06.120 So over here. 220 12:06.150 --> 12:07.350 What is my requirement? 221 12:07.350 --> 12:09.510 Display manager name? 222 12:09.840 --> 12:10.590 So? 223 12:10.590 --> 12:18.780 So in this particular case, I want black manager name means I want employee name from this table. 224 12:18.810 --> 12:19.290 Right. 225 12:19.320 --> 12:20.850 In the filtration. 226 12:21.030 --> 12:25.890 In the filter I want employee name means who is the manager of black. 227 12:25.890 --> 12:28.650 So black manager is 1739. 228 12:28.920 --> 12:30.480 So 7839. 229 12:30.630 --> 12:33.630 Where 7839 is coming over here. 230 12:34.350 --> 12:36.390 7839. 231 12:36.420 --> 12:36.810 King. 232 12:36.810 --> 12:37.440 Right. 233 12:37.830 --> 12:40.290 7839 is coming over here. 234 12:41.130 --> 12:43.470 So King will be the manager of black. 235 12:46.500 --> 12:51.600 You need to understand this filter only from where this filter will come. 236 12:53.040 --> 12:53.550 Why? 237 12:53.550 --> 12:54.450 It is inner join. 238 12:54.450 --> 12:54.810 Why not? 239 12:54.840 --> 12:56.010 It is left join then. 240 12:56.070 --> 12:58.140 Then also there will be the same. 241 12:58.140 --> 12:59.490 No problem. 242 13:01.080 --> 13:02.550 Result will be the same. 243 13:06.840 --> 13:09.540 Is that healthy to do it in inner join? 244 13:09.570 --> 13:12.450 Reason being is both the columns are of same numbers. 245 13:13.800 --> 13:14.160 No, no. 246 13:14.190 --> 13:15.840 Actually left join when? 247 13:16.080 --> 13:19.920 When left join is required in this particular scenario only. 248 13:20.520 --> 13:21.810 Because. 249 13:22.920 --> 13:27.450 Because this record has null value in the manager column. 250 13:28.410 --> 13:29.850 If I join null. 251 13:30.210 --> 13:34.530 But in this over here we have all the employee number. 252 13:34.530 --> 13:34.830 Right? 253 13:34.860 --> 13:36.360 We have all the employee number. 254 13:36.360 --> 13:40.180 We cannot join null with any manager ID from this? 255 13:40.210 --> 13:42.940 Any employee ID from this column. 256 13:43.450 --> 13:48.190 So in this particular case I want all the records from X table. 257 13:49.150 --> 13:49.630 Right. 258 13:49.630 --> 13:52.060 And matching record from the Y table. 259 13:52.900 --> 13:54.940 So all the records from the X table. 260 13:54.970 --> 13:59.620 So that is why I must have to do left join only I cannot do inner join. 261 14:02.500 --> 14:03.250 Okay. 262 14:06.130 --> 14:11.590 So it is based upon the scenario only what you want to prefer. 263 14:14.560 --> 14:20.290 If I want all the record from the left table, then left join must is compulsory. 264 14:20.290 --> 14:21.040 But if I. 265 14:21.070 --> 14:29.050 If my task will be achieved by a common record, then inner join is more better to use. 266 14:35.950 --> 14:36.580 Now. 267 14:36.580 --> 14:36.910 No. 268 14:36.910 --> 14:38.680 Anyone has any question. 269 14:45.850 --> 14:47.320 Then we move forward. 270 14:57.010 --> 14:59.470 So now let's see the non-Equity join. 271 14:59.470 --> 15:01.000 non-Equity join. 272 15:01.510 --> 15:05.860 So basically non-equity join in non-equity join. 273 15:05.860 --> 15:09.820 The join condition is not based on equality operator. 274 15:09.850 --> 15:12.580 This is known as equality operator. 275 15:12.970 --> 15:13.990 This is. 276 15:19.960 --> 15:28.060 So when we are not specifying this particular equality operator or rather than non-equity join uses 277 15:28.060 --> 15:32.860 greater than, greater than equal to, less than less than equal to between operators. 278 15:33.970 --> 15:41.710 So whenever this operators are used to perform the join condition at the time we are. 279 15:41.740 --> 15:44.320 We can say it is a non equi join. 280 15:45.460 --> 15:49.060 Now let's understand with one example. 281 15:53.920 --> 15:55.180 One minute. 282 15:57.640 --> 16:01.660 So now let's understand with one example we have this two table. 283 16:02.530 --> 16:04.600 Select star from this. 284 16:06.400 --> 16:08.050 Select star from. 285 16:14.530 --> 16:15.700 This I have. 286 16:19.570 --> 16:19.930 Okay. 287 16:19.960 --> 16:21.100 This is the table. 288 16:45.860 --> 16:48.950 Okay, I have this two table is with me. 289 16:52.700 --> 16:53.420 Now. 290 16:53.450 --> 16:54.560 Table one name is. 291 16:54.590 --> 16:58.880 We can say table one name is m p n, right? 292 16:58.910 --> 16:59.930 M p n. 293 17:09.470 --> 17:11.270 And table two name is. 294 17:14.750 --> 17:16.190 Salary grade. 295 17:19.610 --> 17:20.690 Salary grade. 296 17:20.720 --> 17:21.530 Table right. 297 17:21.560 --> 17:25.940 Table one name is m p n and table two name is. 298 17:28.340 --> 17:29.120 Salary grade. 299 17:29.150 --> 17:29.810 Table. 300 17:29.870 --> 17:33.860 But now in the result set, I want one additional column. 301 17:34.490 --> 17:36.320 I want the grade column. 302 17:36.350 --> 17:38.390 Grade column from this one. 303 17:40.100 --> 17:46.460 Like suppose if salary is between 7000 to 1000, then there will be. 304 17:50.870 --> 17:52.790 I want, uh like. 305 17:56.120 --> 17:58.100 This one is my output. 306 18:05.780 --> 18:08.000 I want this one in my output. 307 18:08.030 --> 18:10.640 Let's take this. 308 18:10.640 --> 18:13.640 All five rows now 5000. 309 18:14.930 --> 18:16.280 5000 is. 310 18:25.400 --> 18:27.770 Now 5000 is coming under. 311 18:28.160 --> 18:29.240 Uh, between. 312 18:29.330 --> 18:31.970 This is the lowest salary and this is the highest salary. 313 18:31.970 --> 18:38.540 salaries of 5000 is coming between which range you have to check in all the five row in it is coming 314 18:38.540 --> 18:39.650 in which range. 315 18:40.010 --> 18:42.350 So it is coming in this range, right. 316 18:42.350 --> 18:44.090 And what is the grade of this one? 317 18:44.090 --> 18:44.660 It is five. 318 18:44.690 --> 18:45.230 Right. 319 18:45.230 --> 18:48.020 So you have to mention five over here. 320 18:50.120 --> 18:52.790 Now 2500 is coming in which range. 321 18:53.930 --> 19:05.360 So you have to mention three over here then 1001 over here 3003 over here. 322 19:09.530 --> 19:11.930 And again 1500. 323 19:11.960 --> 19:14.030 You have to mention two over here. 324 19:15.200 --> 19:17.720 So this should be my output here. 325 19:17.870 --> 19:20.090 We are not comparing with equality. 326 19:20.090 --> 19:20.420 Operator. 327 19:20.420 --> 19:21.020 Right. 328 19:21.290 --> 19:24.050 We are comparing with between operator. 329 19:24.230 --> 19:31.980 So let's see this one, this uh, what I have written over here Select star from EMP. 330 19:34.470 --> 19:37.050 And then great column from this particular table. 331 19:37.050 --> 19:38.340 And E dot salary. 332 19:39.150 --> 19:47.970 So this salary between lowest salary and highest salary right between s dot low salary and s dot high 333 19:48.000 --> 19:48.810 salary. 334 19:49.290 --> 19:49.860 Right. 335 19:49.860 --> 19:53.670 So that let me execute this query. 336 19:57.660 --> 20:01.860 See now let's compare. 337 20:06.960 --> 20:09.570 This result so three. 338 20:09.570 --> 20:12.720 So employee number three it is coming in one range right. 339 20:12.720 --> 20:14.220 So grade one is there. 340 20:14.250 --> 20:19.530 Then employee five is coming in second range. 341 20:19.530 --> 20:20.700 So this is correct. 342 20:20.700 --> 20:21.060 Right. 343 20:21.060 --> 20:27.330 So if if we want to use no non-equality operator like uh. 344 20:29.460 --> 20:30.960 Between operator. 345 20:31.050 --> 20:33.840 We can also achieve same thing with. 346 20:33.870 --> 20:35.460 Let me try that one. 347 20:38.850 --> 20:46.500 E dot salary greater than s dot salary and e dot. 348 20:49.770 --> 20:51.510 E salary less than. 349 20:53.340 --> 20:57.660 Now let me try 3000. 350 20:59.370 --> 21:02.220 Let me mention over here equal to. 351 21:02.250 --> 21:03.000 Less than. 352 21:03.030 --> 21:03.600 Greater than. 353 21:03.600 --> 21:04.020 Equal to. 354 21:04.050 --> 21:04.470 Less than. 355 21:04.470 --> 21:05.220 Equal to. 356 21:05.580 --> 21:08.160 And let me run both of these query. 357 21:09.810 --> 21:18.210 Now see now both of the result set is giving same result right 1312335. 358 21:21.420 --> 21:26.820 So this is the alternative of between operator you can use like this also. 359 21:32.400 --> 21:33.870 Now let me know. 360 21:33.900 --> 21:35.790 Till now it is clear. 361 21:41.250 --> 21:43.860 Let me know if you clear. 362 21:43.860 --> 21:44.430 Right. 363 21:46.140 --> 21:46.770 Oh, right. 364 21:46.800 --> 21:47.220 Yeah. 365 21:48.030 --> 21:49.200 Yes it is. 366 21:49.590 --> 21:50.250 Okay, okay. 367 21:50.280 --> 21:51.990 Let's move forward. 368 21:55.020 --> 22:02.430 Now my requirement is assign and assign a new column with row number to each row in the EMP and temp 369 22:02.460 --> 22:03.180 table. 370 22:03.330 --> 22:06.960 So now what is my requirement in this particular table. 371 22:08.550 --> 22:16.890 EMP and table I want to assign the row number column like 12345 right. 372 22:19.650 --> 22:22.080 One new column which is row number 373 22:25.000 --> 22:26.770 Four five. 374 22:29.830 --> 22:31.120 So. 375 22:37.060 --> 22:40.030 So we have already seen this row number function. 376 22:40.060 --> 22:40.210 Right. 377 22:40.240 --> 22:43.750 Row number over order by MP number. 378 22:46.060 --> 22:54.940 So it will directly give me giving me this particular particular rank to each of the row. 379 22:54.940 --> 23:03.460 But now I want to again generate this row number column without using the row without using the row 380 23:03.460 --> 23:04.570 number function. 381 23:05.440 --> 23:12.250 I want to generate this 12345 without without using this particular function. 382 23:12.250 --> 23:14.230 So how can I do that. 383 23:21.160 --> 23:24.730 So for that purpose we need to mention like this. 384 23:26.410 --> 23:27.700 Okay, not this one. 385 23:27.700 --> 23:29.170 I think this one. 386 23:29.200 --> 23:32.350 Okay, we need to mention like this. 387 23:32.350 --> 23:37.090 I will explain you in some time. 388 23:40.990 --> 23:49.570 So see over here you need to over here I have done MPN number MPN table. 389 23:49.570 --> 23:51.040 I have written two times right. 390 23:51.070 --> 23:55.210 This MPN table I have written two times. 391 23:55.210 --> 23:56.800 So let me. 392 24:05.710 --> 24:08.800 Copy this same table with two times. 393 24:09.820 --> 24:19.360 Now see over here every time we are check we have given MPN table as E alias and second table as P alias. 394 24:19.360 --> 24:28.060 So what you have to do, you have to give one alias over here, which is E, and one alias over here, 395 24:28.090 --> 24:30.670 which is P, right? 396 24:30.700 --> 24:34.390 Now this is our E table and this is our p table. 397 24:35.620 --> 24:36.340 Right. 398 24:36.370 --> 24:44.830 Now, what I have done, we have to check every time e dot emp number greater than equal to p dot emp 399 24:44.830 --> 24:45.610 n number. 400 24:48.340 --> 24:51.820 And we have three column emp number name and salary. 401 24:52.180 --> 24:57.880 So now for the first time when we are checking E dot EMP number. 402 24:57.880 --> 25:09.400 So this particular column is greater than equal to how many times of this e dot EMP number. 403 25:10.090 --> 25:10.960 How many times. 404 25:10.960 --> 25:13.750 So it is greater than equal to only one time right. 405 25:14.020 --> 25:22.640 For the first row it is only one time because it is comparing one greater than equal to one. 406 25:25.220 --> 25:29.330 For the first time, it is comparing only one greater than or equal to one. 407 25:29.930 --> 25:32.180 So this is the count. 408 25:33.950 --> 25:36.140 This is my count of star. 409 25:36.440 --> 25:45.020 Now, when it is coming to the second row, this two is coming from how many times greater than or equal 410 25:45.020 --> 25:45.590 to. 411 25:46.940 --> 25:50.330 So it is coming for this is one. 412 25:50.330 --> 25:55.220 For also it is greater than or equal to and for two also it is greater than equal to right. 413 25:55.640 --> 25:58.280 So this time it will be for two records. 414 25:58.310 --> 25:59.660 Two records. 415 26:00.770 --> 26:07.700 Now when we come to the three third number it will be greater than or equal to three times. 416 26:07.700 --> 26:08.060 Right. 417 26:08.060 --> 26:13.070 One also it is greater than or equal to three is greater than or equal to two also. 418 26:13.070 --> 26:15.290 And three is greater than or equal to three also. 419 26:15.890 --> 26:17.780 So now it will be three over here. 420 26:17.780 --> 26:25.040 How many times One, two, three, three rows, then one again four, four. 421 26:26.300 --> 26:28.670 When we go to the fourth record. 422 26:32.030 --> 26:38.210 So four is greater than or equal to one also two also three also and four also. 423 26:38.420 --> 26:41.150 So four times and four five times so over. 424 26:41.150 --> 26:46.520 We have generated this row number column without using the row number function. 425 26:47.240 --> 26:50.210 Row number column without using the row number function. 426 26:50.360 --> 26:56.420 And because of we have done the grouping based on all these three columns, it will be uniquely generated 427 26:56.420 --> 26:59.510 only right 12345. 428 26:59.900 --> 27:02.840 There is no duplicate data in this count column, right? 429 27:03.260 --> 27:08.720 So now let me execute this query for temporary. 430 27:08.720 --> 27:12.380 Forget this one one minute. 431 27:20.180 --> 27:22.640 And now execute me this query. 432 27:24.740 --> 27:30.020 So see over here we have generated this DN column without using the row number function. 433 27:30.590 --> 27:36.950 So maybe they will ask this in your interview that how can you generate the row number without using 434 27:36.950 --> 27:37.940 the node number. 435 27:38.780 --> 27:44.150 How you how can you generate the sequential value without using the row number function? 436 27:45.440 --> 27:47.000 It is a sequential value, right? 437 27:47.030 --> 27:51.020 12345 without using the row number function. 438 27:51.020 --> 27:56.330 Then you have to write most probably this kind of query only. 439 28:12.320 --> 28:14.150 So this is clear right? 440 28:17.940 --> 28:18.570 So how is it? 441 28:18.600 --> 28:20.940 What if there will be duplicate? 442 28:21.300 --> 28:22.740 Then it will be groupwise. 443 28:23.880 --> 28:24.300 No, no. 444 28:24.330 --> 28:26.370 Yeah, but ideally it will. 445 28:26.400 --> 28:27.330 Ideally or. 446 28:29.340 --> 28:32.520 Ideally all the columns value will not be duplicate, right? 447 28:32.550 --> 28:36.420 Ideally, all the columns value will not be duplicate. 448 28:36.450 --> 28:37.860 Otherwise it will. 449 28:37.890 --> 28:38.190 Okay. 450 28:38.190 --> 28:38.430 Yeah. 451 28:38.460 --> 28:38.790 Yeah. 452 28:38.820 --> 28:39.480 Got it, got it. 453 28:39.510 --> 28:40.710 Yeah it will. 454 28:40.920 --> 28:42.690 Because by all of the columns. 455 28:42.720 --> 28:43.020 Yeah. 456 28:43.050 --> 28:44.850 Group by all the columns. 457 28:46.200 --> 28:46.590 Okay. 458 28:47.310 --> 28:48.420 One minute. 459 28:50.910 --> 28:51.330 Right. 460 28:51.690 --> 28:52.050 Yes. 461 28:52.050 --> 28:52.200 Yeah. 462 28:52.230 --> 28:53.370 Can you please repeat? 463 28:54.420 --> 28:55.110 Yeah. 464 28:58.590 --> 28:59.280 Hello? 465 29:02.730 --> 29:05.040 One minute, one minute to talk. 466 29:07.080 --> 29:07.380 Yeah. 467 29:07.620 --> 29:09.420 Can you speak now? 468 29:12.270 --> 29:12.810 Okay. 469 29:12.840 --> 29:14.130 Uh, here. 470 29:14.130 --> 29:14.340 Why? 471 29:14.340 --> 29:15.840 We are mentioning Count Star. 472 29:17.190 --> 29:17.730 Yeah. 473 29:18.180 --> 29:19.770 Because of, uh. 474 29:19.770 --> 29:22.530 Because of we because of. 475 29:22.560 --> 29:25.320 We want to do, uh. 476 29:26.100 --> 29:28.530 Then only it will generate this column. 477 29:28.530 --> 29:29.010 Right. 478 29:29.040 --> 29:31.740 Then only it will generate this column. 479 29:35.370 --> 29:39.570 Actually count star if it getting the like, uh, all the records count. 480 29:39.600 --> 29:40.890 No no no no no. 481 29:41.670 --> 29:45.000 Uh, but yes, all the record of count but depend on the group. 482 29:45.000 --> 29:45.630 Right? 483 29:45.960 --> 29:47.580 Depend on the group. 484 29:49.020 --> 29:52.290 It will not, uh, count all the record of the table. 485 29:52.290 --> 29:52.710 It is. 486 29:52.740 --> 29:53.400 It will count. 487 29:53.400 --> 29:55.650 All the record depend on the group. 488 29:55.980 --> 29:59.880 Over here we are creating group with three different columns. 489 29:59.880 --> 30:00.480 Right? 490 30:01.110 --> 30:05.910 Here I have used the group by clause over here EMP number, EMP name and E salary. 491 30:08.280 --> 30:11.880 And then we have done the group by of all these three columns. 492 30:11.910 --> 30:14.550 So first time what it will do first. 493 30:14.580 --> 30:14.790 Time. 494 30:14.790 --> 30:15.900 It will compare this one. 495 30:15.900 --> 30:16.410 Greater than. 496 30:16.410 --> 30:18.060 Equal to how? 497 30:18.600 --> 30:20.190 How many MP. 498 30:20.220 --> 30:21.390 Number from this table? 499 30:21.390 --> 30:21.960 Only one. 500 30:21.960 --> 30:22.560 Right. 501 30:23.460 --> 30:24.000 Yeah. 502 30:24.180 --> 30:26.310 So it will give count of star of. 503 30:26.340 --> 30:29.550 That particular one row only. 504 30:30.240 --> 30:33.330 Count of star of this particular one row only. 505 30:34.020 --> 30:35.580 So it will give me first time. 506 30:35.580 --> 30:38.820 Only one only then second time it will come. 507 30:38.850 --> 30:43.140 Compare two greater than or equal to how many records from this MP. 508 30:43.170 --> 30:47.250 Number from this table one also and two also. 509 30:47.250 --> 30:49.110 So two two greater than. 510 30:49.290 --> 30:50.220 Two records. 511 30:50.220 --> 30:50.640 Right. 512 30:50.640 --> 30:53.100 So it will give me two count of star. 513 30:56.490 --> 30:56.760 Yeah. 514 30:57.000 --> 30:58.890 It will not check all the record. 515 30:58.890 --> 31:02.550 All the five every time five record. 516 31:02.580 --> 31:02.760 Right. 517 31:02.790 --> 31:04.860 So it will not give like five every time. 518 31:04.860 --> 31:05.430 Right. 519 31:06.450 --> 31:08.820 If employee numbers will be change. 520 31:09.360 --> 31:14.920 Like if employee numbers All first row one second row is also one. 521 31:14.950 --> 31:16.450 Third row is also. 522 31:16.480 --> 31:17.650 Third row is two. 523 31:17.680 --> 31:19.630 Then it may be a change. 524 31:19.660 --> 31:20.140 Now. 525 31:24.430 --> 31:26.170 Here therefore that. 526 31:26.200 --> 31:29.560 Let me try to update, uh, one thing. 527 31:34.750 --> 31:40.840 But ideally you have this kind of, uh, question only because. 528 31:55.660 --> 31:57.700 So let me try to do this. 529 31:59.680 --> 32:01.870 And now let me execute this query. 530 32:10.750 --> 32:12.670 22345. 531 32:17.920 --> 32:18.550 Two, two. 532 32:18.580 --> 32:19.990 Now this time it is giving. 533 32:20.020 --> 32:20.230 Two. 534 32:20.260 --> 32:21.160 2345. 535 32:21.190 --> 32:21.730 Why? 536 32:23.830 --> 32:24.790 What is my. 537 32:24.820 --> 32:28.630 We have to compare this entire table again. 538 32:28.660 --> 32:28.990 In. 539 32:29.140 --> 32:29.890 Again. 540 32:33.460 --> 32:35.080 This is my one table. 541 32:38.140 --> 32:39.790 This is my second table. 542 32:39.790 --> 32:41.650 So now one one is greater than. 543 32:41.650 --> 32:42.010 Equal to. 544 32:42.040 --> 32:43.090 How many times? 545 32:43.720 --> 32:44.350 Two times. 546 32:44.350 --> 32:44.980 Right. 547 32:45.430 --> 32:46.090 Two times. 548 32:46.120 --> 32:46.720 Two times. 549 32:46.720 --> 32:47.080 So that. 550 32:47.080 --> 32:48.220 That is why it is generating. 551 32:48.250 --> 32:48.700 Over here. 552 32:48.700 --> 32:49.360 Two. 553 32:49.720 --> 32:50.890 Then again this one. 554 32:50.890 --> 32:51.340 Greater than. 555 32:51.340 --> 32:52.750 Equal to how many times. 556 32:53.890 --> 32:55.870 Two times then three. 557 32:55.900 --> 32:57.700 Is greater than or equal to how many times? 558 32:57.700 --> 32:58.720 Three times. 559 32:59.560 --> 33:02.410 Why this one, this one and this three. 560 33:02.710 --> 33:03.970 Then four is greater than. 561 33:04.210 --> 33:05.290 Equal to how many times? 562 33:05.290 --> 33:06.310 Four times. 563 33:08.440 --> 33:10.810 So it is giving 2 to 3 for five. 564 33:11.770 --> 33:12.220 Right. 565 33:12.250 --> 33:14.290 Let me execute this query again. 566 33:17.290 --> 33:18.010 Perfect. 567 33:18.040 --> 33:18.280 Two. 568 33:18.310 --> 33:18.460 Two. 569 33:18.490 --> 33:18.670 Three. 570 33:18.700 --> 33:18.940 Four. 571 33:18.970 --> 33:19.450 Five. 572 33:25.120 --> 33:28.600 Now I need to update it again m. 573 33:31.300 --> 33:35.470 And e name equal to b. 574 33:38.740 --> 33:40.870 I need to mention this. 575 33:40.870 --> 33:41.320 Right. 576 33:45.070 --> 33:48.070 So now my data is old as it is, right. 577 33:48.580 --> 33:52.570 Now again we have to do one thing now. 578 33:57.460 --> 34:00.670 Now let me tell you one. 579 34:00.700 --> 34:02.080 Ask you one question. 580 34:02.740 --> 34:08.600 If I want the salary also or our running total. 581 34:08.600 --> 34:12.410 If I want running total also, then how can I do. 582 34:19.340 --> 34:22.790 If I want running total also, then how can I do? 583 34:23.150 --> 34:27.290 I just want to mention over here like this. 584 34:41.630 --> 34:44.240 What is the problem over here? 585 34:51.290 --> 34:51.710 Okay. 586 35:02.390 --> 35:04.250 Now see over here carefully. 587 35:04.250 --> 35:10.610 This 5000 Howson will be adding to this 2500, it will become 70 500. 588 35:10.820 --> 35:13.070 Then this will add it to this 1000. 589 35:13.100 --> 35:15.170 It will become 80 500. 590 35:15.380 --> 35:17.120 This will add it to this one. 591 35:17.120 --> 35:19.340 It will become 11500. 592 35:19.370 --> 35:21.290 And this will add it to this one. 593 35:21.290 --> 35:23.090 So it will 13,000. 594 35:25.520 --> 35:29.570 Only I have added this one but the process will be the same. 595 35:29.570 --> 35:34.640 Only right it will comparing each row one by one. 596 35:36.890 --> 35:39.830 Are you getting the process this time? 597 35:39.830 --> 35:40.730 Let me. 598 35:40.820 --> 35:41.570 Yes. 599 35:41.570 --> 35:44.450 Uh, one question on this sheet. 600 35:45.680 --> 35:46.250 Yes. 601 35:46.700 --> 35:54.410 Um, probably, you know, let's have the same scenario, but can I get the total of, uh, the final 602 35:54.410 --> 35:59.540 value, which is for the accumulated value of I see it is a 13,000 there. 603 35:59.540 --> 36:08.150 So can I, can I able to display this 13,000 separately in the sense, uh, it has to show the value 604 36:08.180 --> 36:09.620 at the last line. 605 36:09.650 --> 36:10.580 Will that be possible? 606 36:10.610 --> 36:12.380 Let me try this one. 607 36:19.940 --> 36:22.340 Group by roll up. 608 36:36.560 --> 36:38.930 I am not sure whether this will run or not. 609 36:38.960 --> 36:40.520 No, it will not run. 610 36:41.540 --> 36:43.190 Now let me. 611 36:49.940 --> 36:50.330 See? 612 36:50.330 --> 36:50.810 Over here. 613 36:51.110 --> 36:51.440 Now. 614 36:51.470 --> 36:52.400 Total, total. 615 36:52.400 --> 36:54.170 This total is coming over here. 616 36:57.500 --> 36:58.460 But it will. 617 36:58.460 --> 37:01.100 It will be the grand total of all this salary. 618 37:01.130 --> 37:03.360 Grand total of all this salary. 619 37:06.360 --> 37:08.940 Not only 30,000, 13,000 is. 620 37:10.110 --> 37:12.450 But I think this one is also wrong. 621 37:12.450 --> 37:19.080 Only because if we do the grand total from this one, then it will be 13,000. 622 37:19.110 --> 37:22.860 Only C 13,000 only. 623 37:26.250 --> 37:29.070 But actually this is a wrong scenario. 624 37:34.350 --> 37:35.970 Let me try. 625 37:39.810 --> 37:42.570 Now can you again repeat your question? 626 37:43.200 --> 37:44.910 13,000 separately. 627 37:44.910 --> 37:46.920 You cannot get over here. 628 37:49.800 --> 37:54.000 Say like if you want to get a count of it, we we get the count. 629 37:54.420 --> 37:58.920 Uh, you know, select a count asterisk and mention the alias. 630 37:58.920 --> 38:02.550 And then, uh, from statement, we'll put it and we'll get the count right. 631 38:02.580 --> 38:03.780 Same way. 632 38:04.350 --> 38:07.530 Can we get this, you know, cumulated value? 633 38:07.890 --> 38:08.430 Um. 634 38:11.880 --> 38:14.070 Just by applying one line. 635 38:14.250 --> 38:15.240 Is it possible? 636 38:18.240 --> 38:18.750 Yeah. 637 38:18.750 --> 38:25.740 So for that thing we have, uh, used over here, some other function, right? 638 38:25.830 --> 38:27.630 Sum of salary. 639 38:31.740 --> 38:33.930 And let me try to use this one. 640 38:35.610 --> 38:36.450 See over here. 641 38:36.480 --> 38:38.220 This is one line query only. 642 38:42.570 --> 38:43.860 This is one line query only. 643 38:43.860 --> 38:47.670 But this is again one more interview question that. 644 38:47.670 --> 38:49.710 Let's do the cumulative sum. 645 38:49.890 --> 38:53.880 Cumulative sum without using the sum over clause. 646 38:55.260 --> 39:03.210 Let's do cumulative running total without using the sum over close, and let's do the serial number 647 39:03.240 --> 39:06.750 or sequential number without using the row number function. 648 39:07.020 --> 39:10.200 So at that time you have to write like this only. 649 39:12.240 --> 39:13.680 This is also same thing right. 650 39:13.680 --> 39:15.360 And this is also same thing. 651 39:16.410 --> 39:19.050 We have done this one liner query. 652 39:19.530 --> 39:23.460 And this is a that is why windows function is very powerful. 653 39:23.790 --> 39:27.750 See over here this both thing will be give me the same result. 654 39:29.010 --> 39:30.600 This row you need to check. 655 39:30.930 --> 39:32.850 But this is one liner query. 656 39:32.850 --> 39:39.090 And this is very difficult query right to understand sometimes. 657 39:41.040 --> 39:44.100 That is why windows function are very powerful thing. 658 39:44.100 --> 39:45.720 This is windows function. 659 39:57.210 --> 40:02.980 But sometimes in the interview they will check your logic and to check your logic they may ask you this 660 40:02.980 --> 40:05.440 kind of questions. 661 40:12.670 --> 40:15.820 Okay, we have seen this one. 662 40:15.850 --> 40:17.650 This is the same thing, right? 663 40:22.060 --> 40:24.250 Now let me know your. 664 40:24.970 --> 40:27.010 I think this is regarding join thing. 665 40:27.010 --> 40:29.920 I will give you this one in the assignment. 666 40:31.450 --> 40:37.330 I will upload this file into the that Udemy course only. 667 40:37.330 --> 40:40.390 And I will try to raise some questions from this one. 668 40:41.260 --> 40:46.030 Then you can try to solve these questions from that file. 669 40:46.150 --> 40:47.890 I will not write the solution. 670 40:47.890 --> 40:51.580 I will just mention this create table and insert table script. 671 40:52.030 --> 40:53.800 Then you can practice.