1 00:01.670 --> 00:08.390 So today we are going to cover three total three things we will try to cover. 2 00:08.480 --> 00:10.670 One is a CTE. 3 00:11.330 --> 00:15.290 Then second one is we can say that multiple cities. 4 00:16.340 --> 00:17.630 And third one is. 5 00:20.810 --> 00:22.160 Recursive cities. 6 00:22.160 --> 00:25.580 So these three things we will try to cover in today's session. 7 00:25.670 --> 00:28.820 So let's start with the CTE first. 8 00:29.390 --> 00:31.460 So what is a city. 9 00:31.490 --> 00:35.660 So we can say that city a common table expression in SQL. 10 00:36.350 --> 00:43.250 Common table expression in SQL is a temporary result set that can be referenced within a select within 11 00:43.250 --> 00:46.250 a select, insert, update or delete statement. 12 00:48.680 --> 00:55.850 And cities are defined using the with keyword and allow you to create a name reusable subquery within 13 00:55.850 --> 00:57.590 your SQL statement. 14 00:58.130 --> 01:04.760 They provide a way to simplify complex queries and make sure and makes them more readable. 15 01:04.760 --> 01:09.140 So city is providing more readable code. 16 01:11.990 --> 01:18.410 We can say that it is easy to debug. 17 01:18.410 --> 01:18.800 Why? 18 01:18.830 --> 01:25.010 Because it is more readable format, right? 19 01:25.040 --> 01:26.480 More readable format. 20 01:27.110 --> 01:30.620 Now I think we have already seen but yet. 21 01:31.130 --> 01:32.690 Yet one more. 22 01:32.720 --> 01:35.870 Let's see one this example. 23 01:35.900 --> 01:38.270 So we have this employee table, right. 24 01:38.300 --> 01:40.130 We have this employee table. 25 01:40.790 --> 01:43.670 And we want to identify. 26 01:47.030 --> 01:49.610 We want to identify. 27 01:51.710 --> 01:55.160 Top five top five employees salary. 28 01:55.790 --> 02:01.540 So let me do order by order, by salary descending. 29 02:02.590 --> 02:06.160 And let me execute this query again. 30 02:15.670 --> 02:21.580 So now over here our top five salary will be we can say that 12345. 31 02:21.610 --> 02:22.180 Right. 32 02:23.770 --> 02:27.040 These five are comes in the top five salary. 33 02:28.930 --> 02:33.730 So over here I have already told that we have to use Denserank function. 34 02:33.730 --> 02:34.000 Right. 35 02:34.300 --> 02:35.920 Denserank function. 36 02:35.920 --> 02:42.580 From the EMP table, select only those records whose rank is less than or equal to five, and ranking 37 02:42.580 --> 02:47.140 has to be done on basis of salary column in. 38 02:47.140 --> 02:50.170 The person having maximum salary should have rank one. 39 02:50.770 --> 02:54.640 So we we have seen the derived table concept right. 40 02:54.850 --> 02:57.070 We have seen the derived table concept. 41 02:57.100 --> 02:59.380 So if we use this one. 42 02:59.470 --> 03:02.530 So it will give me the top five salary right. 43 03:02.770 --> 03:04.840 It will give me the top five salary. 44 03:04.840 --> 03:15.430 If you see I just copying this column and see over here, this will become my top five salary, right. 45 03:15.820 --> 03:19.630 And because of this 3000 is coming two times. 46 03:19.780 --> 03:23.170 So it is having the same rank two two. 47 03:23.200 --> 03:27.130 And now it is not skipped because of y. 48 03:27.160 --> 03:30.340 Because of we have used the dense rank function. 49 03:34.270 --> 03:38.020 So if we use rank then it will not give us the proper result. 50 03:38.050 --> 03:38.620 Right. 51 03:38.620 --> 03:41.770 So that is why we have to use dense rank function. 52 03:42.370 --> 03:50.530 Now we cannot directly write the Where clause over here that also I have explained you uh. 53 03:52.720 --> 03:55.140 Explain you in the previous sessions, right? 54 03:55.140 --> 04:00.420 We cannot directly use the rank because rank is not a specific column. 55 04:00.570 --> 04:02.880 And why we cannot use because of. 56 04:06.540 --> 04:10.590 Execution order of the query. 57 04:13.200 --> 04:17.040 Now the solution of this one is one solution is. 58 04:19.830 --> 04:20.790 Derived table. 59 04:20.790 --> 04:23.520 So what is a derived table that also we have seen? 60 04:23.550 --> 04:29.700 Derived table is a Select query inside the From clause of another Select statement. 61 04:29.700 --> 04:35.910 Right now I can use this rank column outside of this particular block. 62 04:36.210 --> 04:37.920 This will become my. 63 04:38.760 --> 04:40.440 This will become my. 64 04:41.100 --> 04:44.490 One table and table name will be E right. 65 04:44.490 --> 04:46.980 So this one will become my one table. 66 04:48.060 --> 04:51.990 Now I can use this rank column over here in the filter clause. 67 04:52.050 --> 04:58.440 So if I run this one it will give me this five records only, which is showing over here. 68 04:59.730 --> 05:00.390 King. 69 05:00.390 --> 05:03.420 King, Ford, Scott Jones and Black and Clark. 70 05:03.420 --> 05:04.080 Right. 71 05:04.500 --> 05:08.970 Now the same thing we can do with the use of city also. 72 05:10.530 --> 05:13.890 So cities will be more easy than derived table. 73 05:14.670 --> 05:26.310 So over here what I have given this e you can mention as city also or you can mention as any name also 74 05:26.340 --> 05:26.700 e. 75 05:27.360 --> 05:29.340 So this will become my. 76 05:33.660 --> 05:35.400 City name right. 77 05:35.430 --> 05:39.210 This has become my city name with s e s. 78 05:40.890 --> 05:45.600 Then this entire statement you have to write in the first block. 79 05:46.440 --> 05:49.680 In the first block you have to write this entire statement. 80 05:49.700 --> 05:55.550 so it will give me all the salary and all the employee detail with the respective rank. 81 05:55.550 --> 06:02.960 Right now I want to filter, so I have to close this bracket and outside this particular bracket select 82 06:02.960 --> 06:03.890 star from E. 83 06:04.520 --> 06:06.200 So what what is this E? 84 06:07.010 --> 06:11.330 This E is a city name where rank less than equal to five. 85 06:11.330 --> 06:15.200 So if I run both of this query it will give me the same result. 86 06:15.740 --> 06:19.280 See over here both of this query will give me the same result. 87 06:21.890 --> 06:23.300 Top five salary. 88 06:23.480 --> 06:26.870 If I mention over here city then also. 89 06:27.380 --> 06:30.410 Then I have to mention over here also city. 90 06:32.690 --> 06:33.230 Right. 91 06:33.230 --> 06:35.540 I have to mention over here also city. 92 06:37.460 --> 06:44.450 So whatever is we are mentioning over here, that should be we can mention outside the first block of 93 06:44.450 --> 06:54.230 the city after block of the city we can say you cannot mention like this over here, city two because 94 06:54.230 --> 06:56.990 there is no city two is defined over here, right? 95 06:57.110 --> 06:58.730 What is city two? 96 06:59.120 --> 07:00.230 Invalid object name. 97 07:00.230 --> 07:01.280 City two. 98 07:05.090 --> 07:06.050 Right. 99 07:07.070 --> 07:09.860 Now let's see one more example. 100 07:09.950 --> 07:11.000 We have this. 101 07:11.000 --> 07:14.150 Duplicates data is available in one of the table. 102 07:16.940 --> 07:19.190 We have this duplicate data. 103 07:19.760 --> 07:21.650 I am not sure. 104 07:22.910 --> 07:23.690 Okay. 105 07:23.690 --> 07:27.110 So if you see over here we have this duplicate data. 106 07:27.110 --> 07:28.520 Raj India. 107 07:28.550 --> 07:30.170 Raj India then Rohit UK. 108 07:30.200 --> 07:30.770 Rohit UK. 109 07:30.800 --> 07:31.640 Rohit UK. 110 07:32.000 --> 07:34.880 All these are coming multiple times, right? 111 07:34.970 --> 07:38.090 Now I want to delete this duplicate data. 112 07:38.120 --> 07:40.700 I want to delete this duplicate data. 113 07:40.820 --> 07:44.330 So first of all I need to give some kind of ranking right. 114 07:44.330 --> 07:48.320 Some kind of ranking like one, ten, two. 115 07:49.130 --> 07:52.760 Then again, 1Y1 because it is only one record. 116 07:52.940 --> 07:54.410 One USA only one record. 117 07:54.440 --> 07:55.580 That is y one. 118 07:55.880 --> 07:59.390 Now Rohit UK one, two and three. 119 07:59.420 --> 08:02.870 Because this Rohit UK is coming three times. 120 08:02.870 --> 08:06.020 That is Y and one India. 121 08:06.020 --> 08:07.550 So again it is one. 122 08:08.780 --> 08:11.840 Now what can I do now. 123 08:11.870 --> 08:21.680 Now I have to create one another table which will be defining which will be removing this second record 124 08:21.680 --> 08:25.010 which will be removing this second record from the table. 125 08:25.040 --> 08:27.890 So it will have only this data. 126 08:34.550 --> 08:36.920 It will have only this data. 127 08:36.950 --> 08:39.260 So what is the common thing over here. 128 08:39.260 --> 08:43.040 We have to keep only rank equal to one record. 129 08:43.190 --> 08:46.150 Not We do not require ranking equal to two. 130 08:46.180 --> 08:47.560 Ranking equal to three. 131 08:47.590 --> 08:49.690 We do not require that data. 132 08:50.380 --> 08:51.310 Right. 133 08:53.830 --> 08:58.000 So I have created this table and inserted also the data. 134 08:58.120 --> 09:02.260 So now over here we have to use row number function. 135 09:02.560 --> 09:07.150 We cannot use dense rank or rank function or Intel function. 136 09:07.150 --> 09:12.520 Better to use row number whenever we are trying to delete duplicate data. 137 09:12.550 --> 09:16.450 I have already told you when we have covered the ranking functions right? 138 09:16.870 --> 09:19.420 Now we have to do over partition by. 139 09:19.450 --> 09:22.240 So partition by name and country. 140 09:22.240 --> 09:23.740 Both of the column we are checking. 141 09:23.770 --> 09:26.140 Checking right name and country. 142 09:26.170 --> 09:28.390 Both of the column we are checking. 143 09:28.390 --> 09:34.840 So that is why we need to do partition by name and country right? 144 09:37.480 --> 09:39.490 Partition by name and country. 145 09:39.550 --> 09:42.610 Now order by name. 146 09:42.610 --> 09:43.330 Order by name. 147 09:43.330 --> 09:47.350 Also you can do right as from duplicates. 148 09:47.350 --> 09:49.960 So what this query will do. 149 09:52.810 --> 09:58.540 This query will do the same thing which I have explained you already in the Excel file, right? 150 10:00.130 --> 10:02.980 Which I have already explained you in the Excel file. 151 10:03.010 --> 10:06.010 See over here above result and below result. 152 10:06.010 --> 10:07.660 Both will be same right? 153 10:11.890 --> 10:15.760 Now over here our requirement is we want only. 154 10:18.370 --> 10:19.600 Equal to one data. 155 10:19.600 --> 10:27.370 So if I mention over here equal to one then it will be having only unique data right. 156 10:27.400 --> 10:30.490 It will be having only unique data. 157 10:30.490 --> 10:37.930 So all the greater than equal to one I need to delete all the greater than equal to one. 158 10:37.930 --> 10:38.830 I need to delete. 159 10:38.830 --> 10:39.160 Right. 160 10:39.160 --> 10:40.350 All this data. 161 10:41.220 --> 10:46.140 If I delete this three data, one data from this one, two data from this one. 162 10:46.140 --> 10:51.180 So two plus one three data I have to delete three rows I have to delete. 163 10:51.360 --> 10:53.010 Now only simple thing. 164 10:53.010 --> 10:54.720 We need to remove this select. 165 10:54.720 --> 10:58.890 And in place of that we need to mention delete. 166 10:59.520 --> 11:04.770 So it will three rows affected three. 167 11:05.460 --> 11:06.870 It is written over here. 168 11:13.380 --> 11:14.730 Three rows affected. 169 11:14.760 --> 11:16.020 And I have to. 170 11:18.210 --> 11:22.860 Now see the okay some problem is okay. 171 11:22.890 --> 11:30.390 Now if I check data from this duplicates table I have to check this duplicate table. 172 11:30.420 --> 11:31.050 Right. 173 11:31.830 --> 11:32.790 See over here. 174 11:32.820 --> 11:33.990 Now everything is unique. 175 11:33.990 --> 11:38.310 Only now everything is unique only right? 176 11:41.850 --> 11:44.910 So this is another use of CTE. 177 11:46.110 --> 11:47.850 This is the another use of CTE. 178 11:47.880 --> 11:48.750 We can say. 179 12:01.770 --> 12:06.780 Now let me show you one more thing. 180 12:09.930 --> 12:11.910 So name country and MN. 181 12:13.050 --> 12:13.500 Yes. 182 12:31.170 --> 12:31.470 Okay. 183 12:31.500 --> 12:32.400 One minute. 184 12:55.160 --> 12:57.890 Now let's understand one more thing. 185 12:57.980 --> 13:04.760 If you want to declare the column names over here, if you want to declare column names over here, 186 13:05.300 --> 13:07.670 then then what will happen? 187 13:07.670 --> 13:11.120 See over here, this is my duplicates table. 188 13:11.150 --> 13:16.760 Right above one is my duplicates table and below one is my city. 189 13:17.360 --> 13:18.410 Now. 190 13:19.730 --> 13:20.210 Okay. 191 13:20.240 --> 13:21.140 One minute. 192 13:31.190 --> 13:36.350 Let me mention go over here and let me try to execute. 193 13:37.580 --> 13:38.420 So. 194 13:40.820 --> 13:42.350 So see over here. 195 13:42.380 --> 13:45.320 Now carefully see over here. 196 13:46.340 --> 13:54.110 It has a common table expression has in inner aliases as well as outer aliases. 197 13:54.140 --> 13:56.570 So what is known as inner aliases. 198 13:56.570 --> 13:57.500 Whatever. 199 13:57.590 --> 14:01.040 If you see over here what are the column names in the duplicates table. 200 14:01.040 --> 14:02.180 It is name and country. 201 14:02.210 --> 14:02.840 Right. 202 14:02.930 --> 14:04.730 Name and country. 203 14:06.530 --> 14:13.550 But now if you see over here in the below one, there is a name, one is extra and country in place 204 14:13.550 --> 14:18.110 of country count is there in in place of RK rank is there. 205 14:18.110 --> 14:21.200 So I can change the inner alias name. 206 14:21.200 --> 14:28.760 Also by default it will pointing the inner alias if you do not specify any outer alias. 207 14:28.760 --> 14:41.590 So whatever I have mentioned over here that is known as Iter alias to the seat city column names. 208 14:43.300 --> 14:48.520 So if you mention outer alias over here, then it will be having the first preference. 209 14:50.410 --> 15:00.940 If it it is there, then it has having the first preference. 210 15:00.940 --> 15:04.360 But if it is not there then there is no problem. 211 15:04.360 --> 15:07.000 If it is not there, then there is no problem. 212 15:07.000 --> 15:13.270 Whatever the column name is mentioned over here, it will refer that particular name only. 213 15:13.540 --> 15:15.700 But you have to give name over here. 214 15:15.700 --> 15:24.370 If you do not name then let me try to execute this one then also no problem over here. 215 15:24.370 --> 15:27.220 See it is taking rank over here rank. 216 15:27.370 --> 15:30.580 But now if I remove this particular thing. 217 15:32.230 --> 15:35.770 If I remove this particular thing over here. 218 15:37.030 --> 15:39.700 And now if I try to execute this one. 219 15:40.360 --> 15:46.630 See over here, no column name was specified for the column three of E. 220 15:46.990 --> 15:48.730 What is the column three? 221 15:48.790 --> 15:50.950 One is name, another is country. 222 15:50.950 --> 15:52.660 And this is my column three. 223 15:53.260 --> 15:55.630 There is no column name specified. 224 15:55.720 --> 15:59.950 Now if you mention over here RK, then your problem will be solved. 225 16:00.370 --> 16:03.820 If you specify RK then what problem will be solved? 226 16:04.480 --> 16:07.600 But you have to specify RK over here as well. 227 16:13.000 --> 16:15.430 You have to specify RK over here as well. 228 16:15.460 --> 16:16.060 Right. 229 16:17.260 --> 16:25.180 So now RK will it will refer because we have not specified any outer references, outer aliases. 230 16:25.180 --> 16:25.750 Right. 231 16:25.780 --> 16:37.330 But now if I Specify any outer aliases, and even though the inner inner alias has name or it has no 232 16:37.330 --> 16:41.920 name, then it will not give me any error. 233 16:44.440 --> 16:46.600 Then it will not give me any error. 234 16:46.630 --> 16:47.320 See? 235 16:50.650 --> 16:51.220 Right. 236 16:51.220 --> 16:53.080 So this should be. 237 16:53.260 --> 16:58.750 Maybe you are not aware about this thing, but you should be aware now, right? 238 17:03.400 --> 17:06.670 Now let's see one of the example of multiple cities. 239 17:06.670 --> 17:09.340 This is what city is all about net. 240 17:11.350 --> 17:15.790 Now let us see one more example of one example of multiple city. 241 17:17.020 --> 17:21.130 So with some practical example. 242 17:22.330 --> 17:28.230 So now assume you are given the tables with information. 243 17:28.230 --> 17:36.510 Assume you are given the table with information on the Snapchat user, including their ages and time 244 17:36.540 --> 17:38.790 spent sending and opening snaps. 245 17:39.240 --> 17:46.560 Write a query to obtain a breakdown of time spent sending versus opening snaps as a percentage of total 246 17:46.560 --> 17:54.570 time spent on this activities group by age group and round the percentage to two decimal places in the 247 17:54.570 --> 17:55.410 output. 248 17:55.740 --> 17:58.350 So calculate the following percentages. 249 17:58.350 --> 18:02.580 So we have total three different uh. 250 18:04.920 --> 18:06.690 Total two different. 251 18:09.210 --> 18:12.540 Way uh values of activity type has been given. 252 18:12.540 --> 18:15.990 That is one is we can say that. 253 18:18.630 --> 18:21.300 Time spent sending. 254 18:21.720 --> 18:30.750 Then another one is time spent opening and what is the total time spent? 255 18:31.260 --> 18:34.140 It is time spent sending. 256 18:34.440 --> 18:36.960 We can say that time spent sending. 257 18:40.560 --> 18:43.110 Plus time spent opening. 258 18:47.100 --> 18:50.100 Plus time spent opening. 259 18:50.100 --> 18:50.760 Right? 260 18:51.690 --> 18:55.710 Now, how can we find the percentage? 261 18:55.740 --> 18:58.500 So percentage we can find like this one. 262 18:58.530 --> 19:04.620 We have to calculate this time spent sending divided by divided by what? 263 19:04.920 --> 19:08.490 Divided by this particular total time spent. 264 19:12.960 --> 19:15.810 And we can say multiply by 100. 265 19:17.940 --> 19:22.020 And how we can specify time spent opening. 266 19:22.110 --> 19:32.480 So again we can specify time, space and opening by this particular thing divided by again total multiply 267 19:32.510 --> 19:33.500 by 100. 268 19:37.820 --> 19:41.030 So we have this activities table is given. 269 19:41.480 --> 19:44.870 Activities table and age breakdown table is given. 270 19:44.960 --> 19:51.170 We need to specify this time span sending an opening with age breakdown. 271 19:51.170 --> 19:53.150 So let me show you the table. 272 19:53.180 --> 19:55.010 Both the tables first. 273 19:58.400 --> 20:04.970 So this will become my both the tables activities table. 274 20:11.780 --> 20:13.070 This is the date. 275 20:15.830 --> 20:16.100 Okay. 276 20:16.130 --> 20:17.660 This is my activities table. 277 20:17.660 --> 20:21.650 And this is my age breakdown table. 278 20:25.740 --> 20:32.400 So now if you see over here in our activities table, we have one, two three, 456 and 789. 279 20:32.490 --> 20:33.810 User ID are there. 280 20:33.810 --> 20:34.110 Right. 281 20:34.110 --> 20:39.840 So this user ID is common with this user ID right. 282 20:39.840 --> 20:42.750 This user ID and this user id are same. 283 20:42.990 --> 20:45.810 And now this is my. 284 20:48.840 --> 20:54.150 We can say this is my age breakdown table. 285 20:54.450 --> 20:56.280 And above one is my. 286 21:02.190 --> 21:03.930 Activities table right. 287 21:06.000 --> 21:08.190 This is my activities table. 288 21:08.370 --> 21:12.630 Now our requirement is we have to first find out the. 289 21:14.670 --> 21:16.110 We have to first find. 290 21:16.200 --> 21:21.430 And over here if you see over here in the activity type Open is also there. 291 21:23.380 --> 21:25.630 Sand is also there. 292 21:27.790 --> 21:31.630 And open sand and chat is also there. 293 21:31.960 --> 21:34.600 But you do not need to consider chat. 294 21:34.630 --> 21:37.330 You do not need to consider all the chat. 295 21:39.940 --> 21:43.780 Whatever chats are there, you do not need to consider that one. 296 21:43.810 --> 21:51.280 Now let me sort the data sort. 297 21:55.900 --> 22:00.520 With user id A, b and C right? 298 22:02.710 --> 22:03.730 I am not able to sort. 299 22:03.760 --> 22:04.030 Okay. 300 22:04.030 --> 22:04.990 No problem. 301 22:05.410 --> 22:09.070 Now if you see over here, open in the one two, three. 302 22:09.100 --> 22:10.870 How much of open are there. 303 22:10.870 --> 22:18.010 So you have to do what you have to do 4.5 then you have to do 123 open. 304 22:18.010 --> 22:18.010 Reopen. 305 22:18.040 --> 22:18.610 Yeah. 306 22:19.360 --> 22:21.460 You have to do this one also. 307 22:21.490 --> 22:22.510 So what is the. 308 22:22.540 --> 22:23.500 This one and this one. 309 22:23.500 --> 22:25.870 It will be 5.75, right. 310 22:28.120 --> 22:29.980 5.75. 311 22:30.100 --> 22:32.320 Now what is the sending in. 312 22:32.320 --> 22:32.890 123. 313 22:32.920 --> 22:36.040 It is 3.5 right. 314 22:36.790 --> 22:38.800 3.5. 315 22:38.920 --> 22:41.740 Now you need to do some of both of these. 316 22:41.770 --> 22:44.020 You need to do some of both of these. 317 22:44.710 --> 22:54.370 Now if you want to calculate this percentage this divided by this multiply by 100. 318 22:55.930 --> 22:57.490 It is 62 right. 319 22:57.520 --> 22:59.350 Now let me try. 320 22:59.710 --> 23:04.210 So over here why I have used the multiple. 321 23:04.900 --> 23:06.970 This is called as multiplicity. 322 23:07.060 --> 23:09.430 Why what I have to do. 323 23:10.000 --> 23:11.980 I have to first find the. 324 23:20.820 --> 23:21.630 I have to do. 325 23:21.660 --> 23:25.320 First find the sand, then find the open. 326 23:25.320 --> 23:26.250 Right? 327 23:27.270 --> 23:27.990 Then find. 328 23:27.990 --> 23:31.080 Find the open and then find the sum. 329 23:31.950 --> 23:33.300 Sum of sand and open. 330 23:33.300 --> 23:35.700 How can I find the sum of sand and open? 331 23:35.790 --> 23:37.320 I need both of them. 332 23:37.320 --> 23:37.920 Right? 333 23:38.130 --> 23:39.810 I do not require. 334 23:42.540 --> 23:45.900 So if you see over here, this will become my sand. 335 23:46.170 --> 23:48.090 This will become my sand. 336 23:50.940 --> 23:52.680 Sand, right? 337 23:52.680 --> 23:53.850 This will become my sand. 338 23:53.880 --> 23:56.520 Let me copy the data 3.5. 339 23:57.690 --> 23:59.760 Now what will be my open? 340 24:01.200 --> 24:04.140 My open will be user ID device. 341 24:04.140 --> 24:05.700 I need to do some of open. 342 24:05.700 --> 24:06.360 Right. 343 24:06.900 --> 24:10.020 So that I have done over here in the second step. 344 24:10.980 --> 24:15.630 So 5.75 I have explained you over here, right? 345 24:15.660 --> 24:24.150 This is 5.75 and this is also 5.75, right. 346 24:24.180 --> 24:26.310 Now I have to do total as well. 347 24:26.310 --> 24:28.050 So how can I do the total. 348 24:28.770 --> 24:30.570 This will become my total. 349 24:32.550 --> 24:34.890 This will become my total right. 350 24:35.940 --> 24:38.580 See 5.75. 351 24:38.580 --> 24:41.640 And you need to do some of this 3.5. 352 24:41.640 --> 24:44.700 Also 9.25 will be my total, right? 353 24:46.380 --> 24:51.360 9.25 will be my total now. 354 24:53.340 --> 24:55.800 So this will become my one city. 355 24:56.520 --> 25:00.180 This will become my second city, and this will become my third city. 356 25:00.210 --> 25:04.290 So what we have to do with city. 357 25:04.440 --> 25:05.670 City as. 358 25:09.120 --> 25:10.590 Then this will become. 359 25:10.620 --> 25:12.090 We have to mention comma. 360 25:12.630 --> 25:15.360 Density to s. 361 25:19.470 --> 25:22.170 Then again, city three s. 362 25:26.790 --> 25:28.140 Now what? 363 25:30.240 --> 25:32.580 I have three total cities are ready. 364 25:32.610 --> 25:33.570 Now I have three. 365 25:33.570 --> 25:35.310 Total cities are ready. 366 25:35.670 --> 25:42.330 But now I have to mention age bucket wise, I have to mention age bucket wise. 367 25:42.600 --> 25:55.320 So now I can join all these three three cities with inner join city two on city dot user ID because 368 25:55.320 --> 26:00.540 user ID is present in all the cities, right in city two dot user ID. 369 26:03.240 --> 26:05.130 Now one more city. 370 26:06.240 --> 26:07.230 One more city. 371 26:07.230 --> 26:07.920 I have to join. 372 26:07.920 --> 26:08.430 Right? 373 26:08.460 --> 26:09.930 City three. 374 26:10.730 --> 26:14.870 So city is now. 375 26:14.870 --> 26:17.780 If I run this entire query, then let's see. 376 26:20.240 --> 26:21.950 The data is showing me, right? 377 26:21.950 --> 26:23.810 The data is showing me the result. 378 26:27.650 --> 26:31.250 Now I want age breakdown wise. 379 26:31.280 --> 26:32.330 Age breakdown wise. 380 26:32.330 --> 26:32.690 Detailed. 381 26:32.690 --> 26:32.990 Right? 382 26:32.990 --> 26:35.330 I want age breakdown. 383 26:35.330 --> 26:35.990 Wise detail. 384 26:35.990 --> 26:42.350 So over here in the age breakdown table also my user ID is present right in this particular table. 385 26:42.350 --> 26:44.330 Also my user ID is present. 386 26:44.330 --> 26:50.120 So what I have to do I have to do that age breakdown table join also. 387 26:50.120 --> 26:50.690 Right. 388 26:53.750 --> 26:56.660 This age breakdown table join also. 389 26:56.690 --> 27:03.620 And what age dot user id equal to city dot user id. 390 27:03.620 --> 27:07.340 So this is my age breakdown table is also now joined. 391 27:09.770 --> 27:11.420 Right now. 392 27:11.420 --> 27:13.040 What is my requirement now? 393 27:13.070 --> 27:17.870 My requirement is I just need to mention. 394 27:20.420 --> 27:21.650 This thing. 395 27:22.640 --> 27:25.070 I just need to mention this thing. 396 27:28.040 --> 27:29.180 See? 397 27:29.210 --> 27:30.230 Sending. 398 27:30.230 --> 27:35.930 I have now sending sending details with me of all the user ID, right? 399 27:35.930 --> 27:44.210 I have my I have with my sending details with me with all the user id I have opening detail with me 400 27:44.240 --> 27:52.430 with actually this one is open and this one is total. 401 27:52.970 --> 28:01.100 We can say sending, then opening, this is opening and this is total. 402 28:04.040 --> 28:08.800 Now if I run this Entire query. 403 28:09.310 --> 28:11.170 Then I will get my. 404 28:13.210 --> 28:15.100 Multipart only. 405 28:21.130 --> 28:22.720 I will get my data right. 406 28:22.720 --> 28:24.940 I will get my data. 407 28:24.970 --> 28:27.010 Let me try to run this. 408 28:28.360 --> 28:29.560 See over here. 409 28:29.980 --> 28:33.880 Sending percentage and opening percentage. 410 28:33.910 --> 28:34.660 See over here. 411 28:34.690 --> 28:35.950 Carefully this one. 412 28:36.970 --> 28:38.470 If I copy this result. 413 28:38.500 --> 28:42.190 Now we have seen this over already. 414 28:42.220 --> 28:45.460 See 62 point. 415 28:48.160 --> 28:51.700 One six is my result. 416 28:56.740 --> 28:59.770 62.16 is my result set right? 417 29:00.460 --> 29:01.690 I have already seen you. 418 29:01.720 --> 29:02.500 Shown you over here. 419 29:02.530 --> 29:03.430 Calculation. 420 29:03.460 --> 29:05.590 I have done what I have to do. 421 29:06.160 --> 29:08.500 Then let me try to do this one. 422 29:08.500 --> 29:10.000 Let me show you this one also. 423 29:10.000 --> 29:13.420 This one, this divided by this. 424 29:13.420 --> 29:15.460 Multiply by 100. 425 29:15.670 --> 29:22.060 See over here 37.84 is my open. 426 29:22.960 --> 29:25.570 37.84 is my open. 427 29:27.430 --> 29:29.350 Now let me unmute you. 428 29:29.350 --> 29:33.070 If you have any question you can ask quickly. 429 29:36.100 --> 29:39.610 This is the perfect example of multiple city. 430 29:41.410 --> 29:44.260 This is the perfect example of multiple city. 431 29:52.960 --> 30:01.480 This is very easy to understand, easy to debug, and easy code is easy to read right? 432 30:03.300 --> 30:05.700 Let me know if you have any question. 433 30:06.960 --> 30:08.790 Otherwise we will move forward. 434 30:12.960 --> 30:15.090 To why we are using lower keyboard. 435 30:15.780 --> 30:17.880 Yeah, we are using the lower key. 436 30:17.910 --> 30:22.680 Actually let me try it. 437 30:22.710 --> 30:27.870 Actually in Postgres it is effective but over here it will not affect. 438 30:27.900 --> 30:31.620 Now over here it will not affect. 439 30:31.620 --> 30:41.040 But in Postgres or Postgres if you mention like this uh send then it will not retrieve any data. 440 30:41.040 --> 30:43.290 It will not retrieve any data over here. 441 30:43.290 --> 30:44.730 It is not affecting. 442 30:44.760 --> 30:54.360 Now if you mention like this entire thing is capital letter s e n d, then in Postgres, if it is in 443 30:54.360 --> 31:02.670 your table in small letter, then it will not give you any result if you mention like this, but over 444 31:02.670 --> 31:04.140 here it is not affecting. 445 31:04.140 --> 31:07.710 I have executed this query in the PostgreSQL. 446 31:08.400 --> 31:15.390 Now if you mention over here lower and it should be all the letters should be over in the small letter. 447 31:15.420 --> 31:22.170 Now this will work in Postgres as well and as well as well as in any of the database. 448 31:23.700 --> 31:29.190 So this query is considering two Postgres database. 449 31:29.190 --> 31:33.270 That is why I have mentioned lower keyword over here. 450 31:33.270 --> 31:35.670 It is not effect anything much. 451 31:38.880 --> 31:39.660 Yeah okay. 452 31:41.130 --> 31:44.310 Because Postgres I think case sensitive. 453 31:44.790 --> 31:49.680 So in that if you mention like this it will not give you proper result. 454 31:51.030 --> 31:53.100 In fact it will not give you any data. 455 31:53.100 --> 31:59.310 If in your activity table all the send will be in the capital letter. 456 31:59.330 --> 32:00.260 like this. 457 32:07.370 --> 32:08.090 Oh, let me know. 458 32:08.090 --> 32:09.050 Any question. 459 32:09.140 --> 32:10.280 Any one. 460 32:16.880 --> 32:18.680 What is a recursive city? 461 32:18.710 --> 32:20.810 What is a recursive city? 462 32:22.340 --> 32:23.870 So recursive city. 463 32:23.900 --> 32:24.860 When city. 464 32:24.860 --> 32:25.730 When city. 465 32:25.730 --> 32:26.900 References. 466 32:27.080 --> 32:32.450 When city references itself it is known as recursive city. 467 32:32.570 --> 32:35.060 So this is the ideal syntax. 468 32:35.180 --> 32:36.470 Let me. 469 32:39.410 --> 32:40.160 Sit. 470 32:40.190 --> 32:42.710 Recursive city has two different members. 471 32:42.710 --> 32:43.340 One. 472 32:43.400 --> 32:44.750 One member is. 473 32:45.050 --> 32:46.310 I have written over here. 474 32:46.340 --> 32:53.000 Anchor member that is a first member we can say and one is recursive. 475 32:53.000 --> 32:53.750 Member. 476 32:55.220 --> 32:56.540 One is recursive. 477 32:56.540 --> 32:57.260 Member. 478 33:01.430 --> 33:02.450 That is a second. 479 33:02.480 --> 33:03.620 Second member. 480 33:03.800 --> 33:04.430 Now. 481 33:04.460 --> 33:06.530 Applications of recursive CTE. 482 33:07.790 --> 33:13.220 Example I want to generate a list of dates or I want to express access hierarchy. 483 33:13.250 --> 33:13.850 Okay. 484 33:13.880 --> 33:16.430 So I will give you that example very soon. 485 33:16.820 --> 33:18.350 Anchor query will. 486 33:18.860 --> 33:19.940 So over here. 487 33:19.970 --> 33:20.990 Anchor query. 488 33:21.020 --> 33:24.950 Anchor member will be invoked only once. 489 33:24.980 --> 33:27.740 Anchor member will be invoked only once. 490 33:28.550 --> 33:32.570 And recursive query is a query that has a reference to the. 491 33:34.790 --> 33:35.810 To the. 492 33:36.590 --> 33:37.160 What is the. 493 33:39.590 --> 33:44.570 Outer name and will be invoked again and again till it returns a blank result. 494 33:44.720 --> 33:47.960 So outer query this recursive query will be. 495 33:50.390 --> 33:54.740 Invoked again and again until it will give the blank result right. 496 33:54.770 --> 33:57.440 First time recursive member is invoked. 497 33:57.440 --> 34:04.850 First time in the previous result set of the represents the whatever the anchor member has returned. 498 34:05.120 --> 34:12.050 I will explain you practically in some time in each subsequent invocation of the recursive member, 499 34:12.050 --> 34:20.900 the ref reference to the city name represents the result set returned by the previous invocation recursive 500 34:20.930 --> 34:21.650 member. 501 34:25.430 --> 34:31.220 And over here you need to understand both of the anchor member and recursive member will have the same 502 34:31.220 --> 34:35.930 number of columns and data types, same number of columns and data types. 503 34:35.930 --> 34:42.800 Because of you are using Union all over here, you cannot use union over here you cannot use. 504 34:50.120 --> 34:52.670 Or intersect also you cannot use. 505 34:52.670 --> 34:53.240 Right? 506 34:55.120 --> 34:57.820 You have to use Union all over here. 507 34:57.850 --> 34:59.050 Mandatory. 508 34:59.170 --> 35:01.600 Now the reference of city is outer. 509 35:01.600 --> 35:04.570 Query means unified result sets of. 510 35:05.470 --> 35:08.200 For now, forget this theory. 511 35:08.230 --> 35:10.030 You can read it later on. 512 35:10.420 --> 35:13.330 Now let me show you one example. 513 35:15.430 --> 35:18.250 Now I have this table with me. 514 35:18.280 --> 35:18.580 Right? 515 35:18.580 --> 35:20.560 I have this table with me. 516 35:25.180 --> 35:27.100 I have this table with me now. 517 35:27.130 --> 35:31.000 Now I have manager ID data with me. 518 35:31.030 --> 35:31.270 Right? 519 35:31.300 --> 35:33.160 Manager ID data with me. 520 35:33.250 --> 35:35.140 Now what I want. 521 35:35.170 --> 35:37.840 I want some kind of level information. 522 35:38.500 --> 35:43.000 That which employee is coming on which particular level. 523 35:43.150 --> 35:46.120 So let me copy this data again. 524 35:49.000 --> 35:52.090 And again over here. 525 35:52.090 --> 35:55.840 So now this will become my level one. 526 35:55.840 --> 35:56.650 Why? 527 35:57.370 --> 35:59.950 Because King is a super manager, right? 528 35:59.980 --> 36:01.960 King has no manager. 529 36:02.770 --> 36:04.300 King has no manager. 530 36:04.300 --> 36:06.820 So it will become my super manager. 531 36:06.850 --> 36:09.760 So now all those employees. 532 36:11.620 --> 36:15.940 Now let me directly show you the query. 533 36:21.100 --> 36:22.990 So now this is the. 534 36:23.020 --> 36:24.850 This is the recursive city. 535 36:24.880 --> 36:27.670 See over here in the first query. 536 36:27.670 --> 36:32.650 So first query I have to mention the anchor member right. 537 36:36.190 --> 36:38.920 First query I have to mention anchor member. 538 36:38.920 --> 36:44.860 So how can I mention the anchor member. 539 36:45.640 --> 36:47.950 I want this is r and k. 540 36:47.980 --> 36:49.630 R and k means level right. 541 36:49.660 --> 36:51.340 R and k Rank means level one. 542 36:51.880 --> 36:54.040 And this is my super. 543 36:56.200 --> 36:57.010 Manager. 544 36:57.010 --> 37:00.400 So those employee who do not have any manager. 545 37:01.480 --> 37:03.280 King has no manager, right? 546 37:03.310 --> 37:07.120 King has no manager over here. 547 37:07.120 --> 37:09.850 If you see over here, King has no manager. 548 37:10.240 --> 37:12.730 That is why it is a super manager, right? 549 37:12.730 --> 37:14.830 That is why it is a super manager. 550 37:15.280 --> 37:16.960 So this will become my. 551 37:21.880 --> 37:25.600 This will become my anchor member, right? 552 37:25.600 --> 37:27.760 This will become my anchor member. 553 37:29.320 --> 37:34.000 Now what I have to do, I have to write Union all. 554 37:34.390 --> 37:35.470 Union all. 555 37:35.500 --> 37:39.910 Then see over here this one will become my E. 556 37:39.940 --> 37:42.460 This entire query will become my e! 557 37:44.980 --> 37:53.640 And now I have joined inner join, Which same table because over here also I have used the EMP table 558 37:53.640 --> 37:56.340 and over here also I have used the EMP table. 559 37:56.430 --> 38:04.800 Right now this e I have to compare. 560 38:07.140 --> 38:14.550 This emp e e dot manager with this employee e dot emp number. 561 38:14.550 --> 38:18.630 Right e dot manager with this employee EMP number. 562 38:19.110 --> 38:23.430 Now let's understand this result set carefully. 563 38:33.660 --> 38:36.840 Type doesn't match okay okay okay okay. 564 38:44.280 --> 38:46.550 Now understand this one carefully. 565 38:46.550 --> 38:48.680 Let me explain you this one. 566 39:01.610 --> 39:03.110 Now let's see. 567 39:04.160 --> 39:06.530 Let's take one gap over here. 568 39:07.250 --> 39:07.940 Okay. 569 39:08.090 --> 39:10.010 Now what? 570 39:10.040 --> 39:11.390 How it will execute. 571 39:11.390 --> 39:12.830 How it will execute. 572 39:12.920 --> 39:15.080 This query will execute first. 573 39:15.110 --> 39:15.680 Right. 574 39:15.860 --> 39:18.110 Anchor member will be execute first. 575 39:18.110 --> 39:18.950 Always. 576 39:19.490 --> 39:20.810 Anchor member will be. 577 39:20.810 --> 39:21.770 Execute first. 578 39:21.770 --> 39:22.670 Always. 579 39:22.880 --> 39:28.220 Now in the second step what will happen? 580 39:28.670 --> 39:36.200 MP so this will become my table E we can consider. 581 39:36.200 --> 39:43.100 This will become my table E and and this will become my table MP. 582 39:50.450 --> 39:52.400 This will become my table EMP. 583 39:52.430 --> 39:52.760 Right. 584 39:52.760 --> 39:54.470 And this will become my table E. 585 39:55.070 --> 40:00.080 Now what it will do EMP dot h. 586 40:00.110 --> 40:03.170 We have given the alias to both of the table right already. 587 40:03.200 --> 40:05.090 So h dot manager. 588 40:05.810 --> 40:07.250 H dot manager. 589 40:17.420 --> 40:20.060 So at the first what will happen. 590 40:25.400 --> 40:31.580 It will, it will try to see where this particular manager ID column is coming. 591 40:34.040 --> 40:35.150 First time. 592 40:35.150 --> 40:35.780 First time. 593 40:35.780 --> 40:36.440 What it will do. 594 40:36.470 --> 40:39.440 First time it will give me the this result set right. 595 40:39.470 --> 40:41.930 First time it will give me this result set. 596 40:43.840 --> 40:44.230 Right. 597 40:44.230 --> 40:46.540 First time it will give me this result set. 598 40:46.600 --> 40:52.630 Now it will compare this result set with this result set. 599 40:57.730 --> 41:01.270 This result set with this result set and it will check. 600 41:05.110 --> 41:08.950 78397839. 601 41:09.640 --> 41:09.880 Which. 602 41:14.380 --> 41:15.550 Is coming. 603 41:16.090 --> 41:17.410 Coming where. 604 41:18.610 --> 41:19.240 Coming. 605 41:19.240 --> 41:21.070 So it got MP number. 606 41:21.100 --> 41:23.500 What is my e.mp number. 607 41:24.460 --> 41:25.510 e.mp number. 608 41:25.540 --> 41:28.330 Now e.mp number is 7839. 609 41:28.330 --> 41:28.900 Right. 610 41:29.140 --> 41:31.120 And this is my table H. 611 41:31.930 --> 41:33.280 This is my table H. 612 41:33.280 --> 41:34.030 Right. 613 41:34.720 --> 41:36.010 This is my table H. 614 41:36.010 --> 41:40.600 So where this 78397839 is coming. 615 41:40.930 --> 41:43.060 where this seven, eight, three, nine is coming. 616 41:43.060 --> 41:45.610 So it will pick all that particular record. 617 41:45.790 --> 41:49.900 So seven, eight, three nine is coming over here, over here and over here. 618 41:50.110 --> 41:53.050 So it will pick up all these three records. 619 41:55.150 --> 41:56.350 See over here. 620 41:56.380 --> 41:57.970 Jones Black and Clark. 621 41:58.630 --> 42:07.930 And all of this will be all of this will be h dot e name h dot e name. 622 42:07.930 --> 42:11.800 So what are the h dot e name h dot e name. 623 42:11.800 --> 42:12.520 Yeah. 624 42:17.470 --> 42:18.940 Just a minute. 625 42:19.600 --> 42:21.610 Let me execute this query. 626 42:25.300 --> 42:25.960 Okay. 627 42:25.960 --> 42:29.320 So this will become now my h dot name right. 628 42:29.500 --> 42:30.910 This one John. 629 42:31.030 --> 42:34.870 Uh, Jones, Black and Clark. 630 42:35.170 --> 42:37.000 But now what will be my. 631 42:39.640 --> 42:42.280 What will be my manager name? 632 42:42.280 --> 42:44.800 How we can identify my manager name. 633 42:45.310 --> 42:47.890 So manager name will be coming from the. 634 42:47.920 --> 42:48.910 From which table? 635 42:48.910 --> 42:51.280 Manager name will be coming from E table. 636 42:51.310 --> 42:51.880 Right. 637 42:52.210 --> 42:55.300 Manager name will be coming from E table. 638 42:57.370 --> 42:59.950 So this king will become my manager. 639 42:59.950 --> 43:04.510 Manager of it will become my manager. 640 43:04.510 --> 43:05.080 Right. 641 43:05.170 --> 43:07.090 King will become my manager. 642 43:07.450 --> 43:10.180 Because in this particular table. 643 43:12.280 --> 43:13.480 Employee name. 644 43:13.480 --> 43:15.250 Is this this record only. 645 43:15.250 --> 43:15.730 Right. 646 43:15.760 --> 43:22.900 Where 78397839 is representing where 7839 is representing. 647 43:22.900 --> 43:28.480 But in this table I have to pick I have to pick this. 648 43:29.770 --> 43:31.450 This is my table E right. 649 43:31.450 --> 43:32.680 This is my table E. 650 43:35.710 --> 43:40.620 So I have to pick this manager name from E dot e dot e name. 651 43:40.620 --> 43:41.040 Right? 652 43:41.070 --> 43:42.210 E dot e name. 653 43:42.990 --> 43:43.920 Now next time. 654 43:43.920 --> 43:45.750 What will happen next time? 655 43:45.750 --> 43:46.770 What will happen? 656 43:46.920 --> 43:49.140 Let's understand this one. 657 43:52.320 --> 43:54.210 So seven eight. 658 43:54.240 --> 43:56.910 So it will pick up this record right. 659 43:56.940 --> 43:58.770 It will pick up this record. 660 44:01.800 --> 44:07.110 Now it will check where this 787788 is coming. 661 44:07.500 --> 44:11.850 Where this 778, eight is coming in the. 662 44:11.880 --> 44:12.780 In which column. 663 44:12.780 --> 44:14.190 In manager column. 664 44:14.280 --> 44:18.630 So seven it is coming over here. 665 44:18.630 --> 44:19.260 Right. 666 44:19.380 --> 44:21.360 It is coming over here. 667 44:21.390 --> 44:23.160 So what it will do. 668 44:28.260 --> 44:30.450 It will take this Adams data. 669 44:30.480 --> 44:32.190 Adams data over here. 670 44:37.920 --> 44:39.360 Let me. 671 44:39.900 --> 44:43.290 We have already seen this this much of record, right? 672 44:43.320 --> 44:46.380 We have already seen this much of record. 673 44:46.410 --> 44:50.040 Now, let's let me show you for seven, nine, three, four. 674 44:50.070 --> 44:51.990 So this is 7934. 675 44:52.020 --> 44:52.530 Right. 676 44:52.530 --> 44:54.750 7934 is coming. 677 44:54.750 --> 44:56.490 Where in this particular. 678 45:01.260 --> 45:01.710 Seven. 679 45:01.710 --> 45:02.160 Nine. 680 45:02.190 --> 45:02.490 Three. 681 45:02.520 --> 45:03.240 Four. 682 45:03.270 --> 45:03.780 Miller. 683 45:06.000 --> 45:09.330 In this manager ID column 7934. 684 45:14.550 --> 45:15.180 Seven. 685 45:15.210 --> 45:15.990 Nine three. 686 45:16.020 --> 45:16.740 Four. 687 45:21.030 --> 45:21.210 Okay. 688 45:21.210 --> 45:21.660 One minute. 689 45:21.690 --> 45:23.940 We we need to identify the manager. 690 45:23.970 --> 45:30.480 Uh, actually, we need to take the employee number also over here, which we haven't taken. 691 45:32.070 --> 45:32.580 Okay. 692 45:32.610 --> 45:32.820 No. 693 45:32.820 --> 45:33.470 No problem. 694 45:33.500 --> 45:35.090 We need to identify this. 695 45:35.090 --> 45:36.530 7934 is coming. 696 45:36.530 --> 45:37.160 Where? 697 45:37.370 --> 45:37.940 Where? 698 45:37.970 --> 45:40.730 From this employee number column. 699 45:45.860 --> 45:47.540 7934. 700 45:58.010 --> 45:59.750 Let me copy this one. 701 46:03.080 --> 46:05.330 Miller, it is coming over here. 702 46:05.360 --> 46:05.960 Miller. 703 46:05.990 --> 46:07.070 And now what is? 704 46:07.070 --> 46:07.490 Actually. 705 46:07.490 --> 46:10.220 We need to copy the employee ID over here. 706 46:10.250 --> 46:14.360 Then it is better to understand. 707 46:29.840 --> 46:31.760 Now you have to check this. 708 46:31.910 --> 46:32.990 Uh, this 709 46:33.590 --> 46:37.670 76987698. 710 46:37.700 --> 46:38.870 One minute. 711 46:44.900 --> 46:46.670 Something is wrong. 712 46:50.450 --> 46:52.730 So now this is what we have. 713 46:53.000 --> 46:56.510 This is will become my first super manager, right? 714 46:56.540 --> 46:58.790 This will become my first super manager. 715 46:58.820 --> 47:01.910 Now we have two different tables. 716 47:01.910 --> 47:02.870 One is. 717 47:05.450 --> 47:09.020 One is this particular E table and another is EMP table. 718 47:09.050 --> 47:09.680 Right. 719 47:09.770 --> 47:13.100 Which is actually we can say that h table. 720 47:13.100 --> 47:15.320 This is actually a h table. 721 47:15.710 --> 47:17.450 This is actually h table. 722 47:17.450 --> 47:20.570 We have given the alias right now. 723 47:20.960 --> 47:23.030 Now we have to identify. 724 47:23.930 --> 47:25.760 We have to see our query. 725 47:25.760 --> 47:30.310 Let me show you what is written over here? 726 47:30.340 --> 47:31.960 H h dot manager. 727 47:32.080 --> 47:33.730 So h dot manager. 728 47:33.730 --> 47:35.950 So we need to identify from. 729 47:35.950 --> 47:38.350 This will become my first e table. 730 47:38.380 --> 47:40.420 So e dot emp number. 731 47:40.420 --> 47:44.530 We need to find this e dot emp number equal to h dot manager. 732 47:44.710 --> 47:51.280 So where where this h dot manager is coming 7839 is coming three different times right. 733 47:51.550 --> 47:55.420 This is coming three different times 7839. 734 47:55.450 --> 47:58.870 If you see over here 7839 is coming over here. 735 48:01.420 --> 48:03.550 Over here and over here as well. 736 48:03.670 --> 48:07.090 So this king will become the manager. 737 48:07.090 --> 48:08.350 Manager of this. 738 48:08.350 --> 48:09.970 All these three employee. 739 48:10.150 --> 48:11.740 All these three employee. 740 48:11.770 --> 48:14.080 Jones, Blake and Clark. 741 48:14.200 --> 48:16.840 So that is why King is written over here. 742 48:16.870 --> 48:19.630 See, King is written over here. 743 48:19.630 --> 48:30.400 And this is we can say this is a level level one because this particular King has Superman, so it will 744 48:30.430 --> 48:32.470 become in the level one, right? 745 48:32.800 --> 48:34.780 It will become in the level one. 746 48:34.780 --> 48:45.010 And now I have mentioned at h dot, I have mentioned over here e dot e name right e dot e name. 747 48:45.130 --> 48:49.000 So if I mention e dot e name it means. 748 48:52.510 --> 48:56.200 I have to manager take the manager name from this particular table. 749 48:56.230 --> 48:56.860 Right. 750 48:56.890 --> 49:05.260 I cannot take the manager name from this, this table itself because I am checking King against this 751 49:05.260 --> 49:06.880 particular all three employees. 752 49:06.880 --> 49:07.510 Right. 753 49:07.750 --> 49:09.520 King against this particular. 754 49:09.550 --> 49:10.900 All three employees. 755 49:11.170 --> 49:17.530 Now let's see for this particular 7934. 756 49:17.560 --> 49:22.810 So what is the 79347934 is coming. 757 49:23.740 --> 49:26.200 We have to check it over here. 758 49:26.230 --> 49:26.740 Seven. 759 49:26.770 --> 49:27.070 Nine. 760 49:27.100 --> 49:27.340 Three. 761 49:27.370 --> 49:28.000 Four. 762 49:29.680 --> 49:30.400 Okay. 763 49:30.430 --> 49:33.730 And its manager will be 7782. 764 49:33.880 --> 49:36.340 Its manager will be 7782. 765 49:36.370 --> 49:39.640 So you have to reference this 7782. 766 49:39.670 --> 49:41.500 So where this 7782. 767 49:41.530 --> 49:46.570 Is coming over here again you have to check that 17782. 768 49:47.470 --> 49:50.290 7782 is coming. 769 49:50.290 --> 49:50.800 Where. 770 49:54.940 --> 49:57.130 7782. 771 49:57.160 --> 49:58.030 One minute. 772 50:03.700 --> 50:05.800 So it is coming over here right? 773 50:05.800 --> 50:07.630 It is coming over here. 774 50:12.460 --> 50:16.930 So clerk will become clerk will become the manager of this particular employee. 775 50:16.960 --> 50:17.710 Miller. 776 50:19.780 --> 50:23.320 Clerk will become the employee. 777 50:23.400 --> 50:24.960 Manager of Miller. 778 50:24.990 --> 50:29.160 Now, if we say 747499. 779 50:29.190 --> 50:32.730 So you have to check this 7499 is coming where? 780 50:33.330 --> 50:36.690 74997499. 781 50:44.280 --> 50:45.870 7499. 782 50:45.960 --> 50:47.670 So 7698. 783 50:47.670 --> 50:51.420 You need to reference this particular manager ID. 784 50:51.450 --> 50:52.770 Manager ID right. 785 50:57.540 --> 50:58.920 Let's let me ask you. 786 50:58.950 --> 51:00.630 Are you getting or not? 787 51:06.720 --> 51:08.940 Are you getting this thing or not? 788 51:08.940 --> 51:10.200 You can speak now. 789 51:12.300 --> 51:14.400 It is a little bit complex now. 790 51:14.400 --> 51:15.150 Like, uh. 791 51:16.050 --> 51:19.050 Yeah, it is little bit complex thing. 792 51:20.520 --> 51:21.090 So. 793 51:22.830 --> 51:23.190 Yeah. 794 51:23.220 --> 51:23.460 Okay. 795 51:23.460 --> 51:23.940 No problem. 796 51:23.970 --> 51:24.330 Let. 797 51:24.750 --> 51:27.840 Are you having five, ten minutes more time or. 798 51:27.870 --> 51:29.700 Then I will explain again. 799 51:32.070 --> 51:32.640 Why not? 800 51:32.640 --> 51:34.470 We take it upon tomorrow then. 801 51:35.430 --> 51:38.880 Actually tomorrow I want to take a new topic. 802 51:39.960 --> 51:45.510 Okay, so now what we have to do if we see over here. 803 51:46.500 --> 51:47.730 Now, this, uh. 804 51:47.760 --> 51:51.870 First, in the first step, this will become our super manager. 805 51:51.870 --> 51:53.070 Right in the first step. 806 51:53.070 --> 51:53.430 Step. 807 51:53.430 --> 51:55.410 This will become our super manager. 808 51:55.650 --> 51:59.490 Now, if you see over here what will be in the second step. 809 51:59.670 --> 52:06.510 So in the second step it will check this particular employee number is this particular employee number 810 52:06.510 --> 52:11.880 is coming where in the in this particular table in this particular table. 811 52:11.880 --> 52:14.250 So it will be coming over here three times. 812 52:14.250 --> 52:14.730 Right. 813 52:14.730 --> 52:17.910 So it will pick up all these three rows over here. 814 52:17.910 --> 52:23.720 It will pick up all these three rows over here and it will take the manager name. 815 52:29.180 --> 52:29.840 It will. 816 52:30.170 --> 52:34.160 It will take actually salaries not needed, I think. 817 52:34.820 --> 52:36.500 So let me delete this one. 818 52:36.530 --> 52:38.000 It will take this. 819 52:38.300 --> 52:39.230 Now what. 820 52:39.230 --> 52:41.810 It will become in the one side. 821 52:43.820 --> 52:48.500 Let me explain you in the completely new file. 822 52:54.140 --> 52:54.830 Okay. 823 52:54.860 --> 52:57.050 This will become my first table. 824 52:57.050 --> 52:59.390 And you can say that. 825 53:07.850 --> 53:11.060 This is my first table and this is my second table. 826 53:11.060 --> 53:15.230 So at the first step, it will identify the king, right? 827 53:15.230 --> 53:16.970 It will identify the king. 828 53:17.300 --> 53:21.720 It will identify the king and it will pick up the record right over here. 829 53:21.750 --> 53:23.040 Simply. 830 53:23.070 --> 53:29.430 Now, in the second step, what it will do, it will check this e dot what we are doing. 831 53:29.460 --> 53:31.140 E dot EMP number. 832 53:31.140 --> 53:34.320 So let me do again merge. 833 53:37.320 --> 53:38.250 It will check. 834 53:38.280 --> 53:39.810 E dot emp number. 835 53:39.810 --> 53:42.030 And it will do what. 836 53:42.060 --> 53:43.230 H dot manager. 837 53:45.570 --> 53:52.380 So where this particular emp emp number is coming in this particular manager number column. 838 53:52.380 --> 53:54.690 So it will take all these three records. 839 53:54.690 --> 53:55.650 All these three records. 840 53:55.650 --> 53:55.920 Right. 841 53:55.920 --> 53:57.180 All these three records. 842 54:01.350 --> 54:02.100 This one. 843 54:02.100 --> 54:03.600 This one and this one. 844 54:04.320 --> 54:05.280 Right. 845 54:05.310 --> 54:06.510 Now. 846 54:09.990 --> 54:11.130 Again this many. 847 54:11.160 --> 54:14.700 This will become my super manager. 848 54:14.700 --> 54:16.020 And this will become king. 849 54:16.020 --> 54:16.470 Will. 850 54:16.540 --> 54:16.870 Seven. 851 54:16.900 --> 54:17.020 Eight. 852 54:17.050 --> 54:17.200 Three. 853 54:17.230 --> 54:17.860 Nine. 854 54:17.980 --> 54:22.600 It will take 7839 ename from this particular E table. 855 54:23.170 --> 54:26.050 So it will take this name over here. 856 54:26.080 --> 54:26.770 King. 857 54:26.800 --> 54:27.550 Then King. 858 54:27.580 --> 54:28.180 Then King. 859 54:28.210 --> 54:29.470 Right now. 860 54:29.470 --> 54:30.160 In the second. 861 54:30.820 --> 54:31.930 For step. 862 54:31.960 --> 54:32.320 Seven. 863 54:32.350 --> 54:32.590 Nine. 864 54:32.620 --> 54:32.830 Three. 865 54:32.860 --> 54:33.460 Four. 866 54:33.490 --> 54:35.170 So it will compare this. 867 54:35.170 --> 54:39.040 7934 from this this table. 868 54:39.070 --> 54:39.400 Seven. 869 54:39.430 --> 54:40.060 934. 870 54:40.090 --> 54:40.690 It is coming. 871 54:40.690 --> 54:41.200 Where? 872 54:42.700 --> 54:43.510 Okay. 873 54:43.540 --> 54:46.870 So now this 7934. 874 54:46.900 --> 54:50.530 It will compare with this 7782. 875 54:50.560 --> 54:53.500 So we have to pick up this 7782. 876 54:57.940 --> 55:04.660 And you have to take this 7782 from this particular table. 877 55:04.690 --> 55:08.560 77827782 is coming. 878 55:08.560 --> 55:08.740 Where. 879 55:08.770 --> 55:09.370 Over here. 880 55:09.370 --> 55:12.310 7782 okay. 881 55:12.340 --> 55:12.700 Clerk. 882 55:12.700 --> 55:13.000 Right. 883 55:13.030 --> 55:13.720 Clerk. 884 55:17.110 --> 55:23.620 So what you have to do this three thing we have to mention, like as it is, this will become our third 885 55:23.620 --> 55:24.280 level. 886 55:24.310 --> 55:28.450 But now 7782 you have to identify. 887 55:31.210 --> 55:34.690 In this particular table only 7782. 888 55:34.960 --> 55:38.290 Let me execute this one. 889 55:40.450 --> 55:40.990 Right. 890 55:40.990 --> 55:44.500 So Clark will become the manager of this particular employee. 891 55:51.160 --> 55:59.860 Now if you see for this one, if you see for this one, uh, let's see for seven, four, nine nine. 892 56:00.910 --> 56:06.100 So Alan is there, let me copy the data now. 893 56:06.130 --> 56:15.390 777499 Alan, you have to identify this particular 6987698. 894 56:19.230 --> 56:21.060 And where it is coming? 895 56:23.340 --> 56:24.240 Seven. 896 56:24.270 --> 56:24.540 Six. 897 56:24.570 --> 56:25.440 Nine eight. 898 56:25.470 --> 56:26.370 Where it is coming. 899 56:26.370 --> 56:27.780 It is coming over here. 900 56:27.780 --> 56:28.140 Black. 901 56:28.140 --> 56:28.740 Right. 902 56:28.980 --> 56:29.760 Black. 903 56:29.760 --> 56:33.300 So black will be the manager of this particular employee. 904 56:36.090 --> 56:40.890 And now you have to also identify this 769, eight is coming. 905 56:40.920 --> 56:42.690 How many times over here. 906 56:42.690 --> 56:47.790 So 76987698 is coming over here three times. 907 56:51.570 --> 56:54.900 So black will be the manager of all of this employee right. 908 56:54.930 --> 56:59.250 So this is how Recursive City is working right. 909 56:59.250 --> 57:01.140 This is how Recursive City is working. 910 57:01.140 --> 57:01.740 Right. 911 57:03.480 --> 57:09.360 We will take couple of more examples tomorrow and then we will move to the next topic. 912 57:09.390 --> 57:11.160 Thank you so much everyone.