1 00:00.600 --> 00:01.400 Hello all. 2 00:02.040 --> 00:03.240 Good morning everyone. 3 00:04.440 --> 00:07.640 So today we will see the interview question. 4 00:10.520 --> 00:14.640 Write a query to find the nth. 5 00:17.600 --> 00:21.640 Highest salary department wise. 6 00:22.360 --> 00:26.760 So we will find the third highest salary or fourth highest salary like that. 7 00:27.160 --> 00:30.080 But that is also department wise we need to find. 8 00:30.600 --> 00:33.520 So we will create a data set also in front of you. 9 00:34.000 --> 00:36.120 So you can understand better. 10 00:36.720 --> 00:38.640 So let me create table. 11 00:39.280 --> 00:40.720 Create table E. 12 00:44.000 --> 00:44.920 Employees. 13 00:50.160 --> 00:55.360 Then id integer then name where k of ten. 14 00:58.320 --> 01:00.000 Salary into R. 15 01:00.000 --> 01:08.660 Uh celery integer and then department id also in uh we will take it as an integer. 16 01:08.660 --> 01:09.060 Right. 17 01:09.660 --> 01:15.300 So let me create this first table and then we will create another table D underscore. 18 01:17.700 --> 01:29.940 Departments in that we will take uh id integer and uh dept name where care of ten. 19 01:31.500 --> 01:33.500 So we will create uh these two table. 20 01:33.500 --> 01:37.780 And based on these two table we will uh generate our query. 21 01:37.780 --> 01:38.220 Right. 22 01:39.580 --> 01:41.540 So let me create the first table. 23 01:45.180 --> 01:46.380 This is the first table. 24 01:46.380 --> 01:49.660 And then let me create the second table. 25 01:49.660 --> 01:51.100 This is the second table. 26 01:51.420 --> 01:53.940 Now we will insert the data accordingly. 27 01:53.940 --> 01:54.340 Right. 28 01:54.820 --> 01:57.540 Because we want third highest salary department wise. 29 01:58.060 --> 02:05.080 So we need to do multiple entries, write for department ID, so let me first. 30 02:09.040 --> 02:10.800 Enter the value for the employees. 31 02:10.800 --> 02:16.720 Table one the name we can take Janmi then. 32 02:20.240 --> 02:22.160 One lakh and department id one. 33 02:22.520 --> 02:26.160 So this is the first one then second third fourth. 34 02:26.600 --> 02:30.400 So this is our second employee. 35 02:30.440 --> 02:33.160 Third employee, fourth employee name. 36 02:33.160 --> 02:36.080 We can take anything na. 37 02:39.840 --> 02:40.400 Pooja. 38 02:40.400 --> 02:41.120 Ramesh. 39 02:43.320 --> 02:44.040 Salary. 40 02:44.040 --> 02:48.720 We can take 80,000 80,000, 60,000. 41 02:51.480 --> 02:54.280 Is one minute. 42 02:54.680 --> 02:59.240 Then we will take one more employee because we want third is salary, right? 43 03:03.260 --> 03:04.540 Let's take anything. 44 03:09.020 --> 03:09.540 So. 45 03:14.220 --> 03:14.740 Okay. 46 03:15.020 --> 03:18.820 So now these are the our entry for the department ID one right. 47 03:19.140 --> 03:26.500 Now same entry we have to do for department ID two three, four or any thing we have to take. 48 03:26.500 --> 03:27.020 Right. 49 03:27.060 --> 03:31.380 So let me now do it for department ID two. 50 03:32.260 --> 03:35.220 So let me change this 1 to 5. 51 03:35.740 --> 03:40.700 Then 6789 ten. 52 03:41.780 --> 03:43.900 We need to take any names. 53 03:48.980 --> 03:49.660 Hema. 54 03:50.260 --> 03:51.060 Diksha. 55 03:54.780 --> 03:55.140 Singh. 56 03:58.640 --> 03:59.440 Rakesh. 57 04:03.120 --> 04:03.560 Anything. 58 04:03.560 --> 04:04.680 We can take values. 59 04:04.880 --> 04:08.200 Then we can take values in the salary column. 60 04:15.000 --> 04:16.160 Any values we can take. 61 04:16.200 --> 04:18.320 And then department ID will be this time. 62 04:19.320 --> 04:20.960 Second department ID right. 63 04:23.880 --> 04:25.360 So this will be the. 64 04:26.440 --> 04:28.400 Now let's take another one. 65 04:30.320 --> 04:37.960 So this time we will change the employee ID 1112 1314 and 15. 66 04:40.600 --> 04:43.360 And then these all are the department ID three values. 67 04:43.360 --> 04:43.640 Right. 68 04:43.680 --> 04:44.080 Three. 69 04:46.440 --> 04:47.720 Three, three. 70 04:49.760 --> 04:52.080 Then we need to change name also. 71 04:55.920 --> 04:57.400 We can change any name. 72 04:57.400 --> 04:57.680 Right? 73 04:57.720 --> 04:58.440 Deepika. 74 05:03.730 --> 05:07.690 Then you can take a. 75 05:11.050 --> 05:11.690 Cue to. 76 05:14.610 --> 05:15.450 Nikhil. 77 05:17.730 --> 05:18.490 Sagar. 78 05:26.170 --> 05:29.290 In any value we can give it in the salary column. 79 05:29.290 --> 05:32.530 We just want to right now. 80 05:32.530 --> 05:33.930 Let's take another. 81 05:36.050 --> 05:37.610 Four department ID four. 82 05:37.650 --> 05:40.010 Also we can take values. 83 05:40.930 --> 05:42.050 Let me take it. 84 05:44.170 --> 05:45.490 16. 85 05:45.530 --> 05:46.530 17. 86 05:46.690 --> 05:47.530 18. 87 05:47.570 --> 05:48.610 19. 88 05:49.730 --> 05:50.490 20. 89 05:53.010 --> 05:55.170 Then any values in the salary column? 90 06:00.390 --> 06:00.790 Um. 91 06:06.230 --> 06:08.470 And over here, Department ID four we are taking. 92 06:08.470 --> 06:08.790 Right. 93 06:08.790 --> 06:12.070 So 4444. 94 06:14.630 --> 06:18.310 We can change these names again. 95 06:19.430 --> 06:19.950 So. 96 06:23.190 --> 06:23.750 Jenny. 97 06:27.030 --> 06:27.590 Diya. 98 06:30.150 --> 06:31.030 Madhuri. 99 06:36.430 --> 06:37.110 Raj. 100 06:38.710 --> 06:39.430 Ritesh. 101 06:40.150 --> 06:40.430 So. 102 06:40.470 --> 06:43.750 And now let's take the final one for the department ID five. 103 06:44.830 --> 06:56.950 So let me change the employee ID 21, 22, 23, 24, 25. 104 06:57.550 --> 07:01.050 And then let's take the department ID five this time. 105 07:05.130 --> 07:07.770 And let's take the name Ambika. 106 07:13.730 --> 07:14.970 Any name we can take. 107 07:21.050 --> 07:21.850 Harshit. 108 07:21.850 --> 07:22.570 Hardik. 109 07:28.050 --> 07:29.010 Hrithik. 110 07:30.250 --> 07:30.930 Rahul. 111 07:31.850 --> 07:34.810 And let's change the salary also of all these employees. 112 07:51.890 --> 07:54.850 Now let's insert the data into the employees table. 113 07:55.330 --> 07:57.830 So let me insert from the beginning. 114 07:58.990 --> 08:00.830 So it will insert five rows, right? 115 08:01.470 --> 08:01.830 Okay. 116 08:01.870 --> 08:03.190 Five rows are inserted. 117 08:03.430 --> 08:07.350 Then let's take insert another entries. 118 08:07.350 --> 08:09.430 Then let's insert another bunch. 119 08:13.150 --> 08:15.110 Then let's insert another bunch. 120 08:19.350 --> 08:21.350 And then let's insert another bunch. 121 08:21.750 --> 08:25.630 So total 25 rows will be there in this employees table right. 122 08:26.110 --> 08:29.030 So let me select the data from this table. 123 08:34.590 --> 08:38.110 So I have selected right all these 25 rows. 124 08:38.430 --> 08:41.390 So let me take this data into the Excel file. 125 08:48.110 --> 08:50.990 So this will be our data right. 126 08:54.310 --> 08:59.050 Now we need to identify the department wise salary. 127 08:59.050 --> 08:59.490 Right? 128 08:59.530 --> 09:05.410 So let me insert the data into the departments table as well. 129 09:05.450 --> 09:08.370 So how many entries will be there in the departments table? 130 09:09.610 --> 09:11.290 It will be five entries right. 131 09:16.970 --> 09:20.610 One IT department then. 132 09:21.930 --> 09:22.290 Two. 133 09:22.330 --> 09:22.690 Three. 134 09:22.690 --> 09:23.090 Four. 135 09:23.130 --> 09:23.610 Five. 136 09:27.050 --> 09:27.370 Two. 137 09:27.410 --> 09:27.850 Three. 138 09:27.890 --> 09:28.370 Four. 139 09:28.530 --> 09:29.050 Five. 140 09:30.010 --> 09:32.250 This we can say that HR department. 141 09:34.490 --> 09:36.210 Mechanical department. 142 09:37.210 --> 09:40.010 Then we can say that education. 143 09:43.610 --> 09:46.530 In income tax department. 144 09:46.810 --> 09:49.130 So any random values we can take. 145 09:49.650 --> 09:52.930 So let me insert into this table. 146 09:53.970 --> 09:58.550 Now let me select the data from this table as well. 147 10:03.390 --> 10:04.470 So let me. 148 10:07.190 --> 10:09.830 Copy this data into the Excel file as well. 149 10:10.190 --> 10:15.750 Now what we require we require department wise fourth highest salary. 150 10:16.030 --> 10:20.870 So we need to display the department name this time not the department ID right. 151 10:21.630 --> 10:24.430 So third highest salary. 152 10:24.470 --> 10:26.950 Let's take the third highest salary department wise. 153 10:27.590 --> 10:30.670 So what will be the third highest salary for this department. 154 10:30.670 --> 10:32.470 So it will be 60,000 right. 155 10:33.030 --> 10:35.350 And what is the name of this department. 156 10:35.590 --> 10:39.510 So it will be it department for employee ID. 157 10:39.790 --> 10:46.950 So what we want to display we want to display employee ID then name salary and then department name, 158 10:46.990 --> 10:50.510 not the department ID right now. 159 10:50.550 --> 10:52.910 What what will be the department ID two. 160 10:54.270 --> 10:55.270 Third is salary. 161 10:55.270 --> 11:02.130 So this is the first highest salary, then this is second and this will be third highest salary. 162 11:02.130 --> 11:02.490 Right. 163 11:03.410 --> 11:12.170 So we want to display this one with two department ID means this one is the department ID of two. 164 11:12.210 --> 11:12.610 Right. 165 11:12.650 --> 11:15.090 So the this one we want to display. 166 11:17.130 --> 11:22.250 Now what will be the third highest salary for department ID three. 167 11:22.450 --> 11:27.570 So you can say that first one is this. 168 11:28.890 --> 11:30.370 Second one is this. 169 11:32.450 --> 11:36.610 And third one will be this one right. 170 11:38.610 --> 11:40.970 This one will be the third one. 171 11:41.930 --> 11:44.290 So let me select again this data. 172 11:48.570 --> 11:49.890 And let me select. 173 11:53.770 --> 11:54.930 This department. 174 11:55.610 --> 11:59.830 So likewise this we need to display in our final output. 175 12:00.350 --> 12:01.870 So how can we display. 176 12:02.270 --> 12:07.030 So basically we want to partition data based upon the department ID wise right. 177 12:07.910 --> 12:11.550 This will become our first partition. 178 12:13.470 --> 12:15.070 Then this will become our. 179 12:17.270 --> 12:18.590 Second partition. 180 12:19.510 --> 12:22.710 And this will become our third partition. 181 12:23.390 --> 12:24.150 Likewise. 182 12:24.870 --> 12:27.510 So how can we display. 183 12:27.910 --> 12:32.430 So for that purpose we can write the rank and denserank function. 184 12:32.910 --> 12:44.550 Select star from e underscore employees e inner join d underscore departments d on e dot department 185 12:44.550 --> 12:48.190 id equal to d d.id right? 186 12:48.230 --> 12:50.590 We need to write a simple join condition. 187 12:50.990 --> 12:54.430 Then what is our requirement e.id. 188 12:56.490 --> 13:00.130 Then e dot salary, then e dot. 189 13:01.810 --> 13:02.370 Of course. 190 13:02.370 --> 13:02.850 Name. 191 13:04.290 --> 13:06.210 Then we need to give a rank. 192 13:06.210 --> 13:06.730 Right. 193 13:07.090 --> 13:08.890 So how can we give rank? 194 13:09.570 --> 13:10.770 Rank over. 195 13:11.490 --> 13:13.490 Then we need to do partition by right. 196 13:13.890 --> 13:16.450 So over here we have done the partition by. 197 13:17.210 --> 13:24.050 So in our query also we need to do partition by this department ID because department ID wise we require 198 13:24.090 --> 13:24.890 data right. 199 13:26.450 --> 13:27.970 Now what we can do. 200 13:30.170 --> 13:31.090 Rank over. 201 13:34.130 --> 13:35.170 Partition by. 202 13:38.770 --> 13:49.130 We can say that dept.id order by salary descending as rank. 203 13:52.050 --> 13:55.010 Now it will generate a rank. 204 13:55.390 --> 13:58.110 and now we can display also department name as well. 205 13:58.150 --> 13:58.550 Right. 206 13:58.950 --> 14:02.470 So D dot department name. 207 14:07.390 --> 14:09.950 Now let me select the data from this query. 208 14:12.270 --> 14:14.350 So it is giving a rank right. 209 14:14.350 --> 14:17.590 So whenever two values are same it will give the same rank. 210 14:18.110 --> 14:18.590 But. 211 14:21.470 --> 14:23.550 Actually there is one problem over here. 212 14:24.150 --> 14:27.470 If you see over here it is not gender. 213 14:27.510 --> 14:31.150 Whenever two values are same it is not. 214 14:31.390 --> 14:33.310 It is not giving the proper rank. 215 14:33.750 --> 14:36.710 Actually we require the third highest salary, right? 216 14:37.310 --> 14:39.630 But it is not giving the third highest salary. 217 14:40.550 --> 14:47.150 It is, uh, directly jump to the fourth rank over here because these two values are same. 218 14:47.950 --> 14:56.480 So in that case we require we need to use in place of this one we need to use Denserank function whenever 219 14:56.480 --> 14:57.560 two values are same. 220 14:57.600 --> 14:59.320 It will not work properly. 221 14:59.800 --> 15:05.240 So let me use this Denserank function. 222 15:05.240 --> 15:06.920 And now let me see the data. 223 15:09.400 --> 15:12.120 So that will be the difference between rank and denserank. 224 15:13.080 --> 15:16.760 Now you can see this Ramesh is the third highest salary right. 225 15:16.800 --> 15:20.360 So that we have seen over here in the Excel file. 226 15:20.880 --> 15:22.880 Then for Department two. 227 15:23.640 --> 15:25.120 Department two. 228 15:25.560 --> 15:28.320 Let me take okay. 229 15:28.360 --> 15:29.560 This is HR right. 230 15:30.280 --> 15:30.720 So. 231 15:32.920 --> 15:33.600 Diksha. 232 15:33.920 --> 15:38.720 Diksha will be the the third highest salary for department ID two. 233 15:39.200 --> 15:47.240 Then again for the department mechanical third highest salary will be Deepika. 234 15:47.240 --> 15:48.760 So that is showing over here. 235 15:48.760 --> 15:50.400 Right Deepika. 236 15:51.240 --> 15:53.720 So this is how we can give the answer. 237 15:54.500 --> 15:58.940 But now we require where arc equal to three. 238 15:58.980 --> 15:59.300 Right. 239 15:59.300 --> 16:05.220 So can we give over here as where r k or RNK1. 240 16:05.220 --> 16:09.500 You can see different alias r and k one equal to three. 241 16:09.540 --> 16:10.900 Can you write like this. 242 16:10.900 --> 16:11.980 You cannot write. 243 16:13.780 --> 16:15.460 You cannot write like this. 244 16:15.460 --> 16:22.780 So for that reason you can either write the city which city is. 245 16:25.980 --> 16:31.140 And then close the bracket and then select star from CTE. 246 16:31.420 --> 16:31.980 Where. 247 16:34.460 --> 16:37.820 Or in place of star we require only ID right. 248 16:37.860 --> 16:45.900 So city.id then salary, then name and then dept underscore name. 249 16:48.300 --> 16:50.620 Likewise this you can give the column name. 250 16:51.060 --> 16:56.880 Now if you run this query it will give all the five departments third highest salary. 251 16:57.960 --> 17:00.720 So it's mechanical education and income tax. 252 17:00.720 --> 17:07.840 We will see only one one records are there with employee ID as well as salary. 253 17:09.520 --> 17:12.240 And now one more thing. 254 17:12.600 --> 17:13.200 What is the. 255 17:13.200 --> 17:18.920 Another way to write this query means apart from the city, what we can use over here. 256 17:21.440 --> 17:27.480 Uh, so let me tell you we can use this sub query or you can say that derived table. 257 17:28.440 --> 17:30.600 So select star from. 258 17:31.480 --> 17:34.440 Then this one will become our entire table right. 259 17:34.920 --> 17:37.040 This one will become our entire table. 260 17:37.440 --> 17:38.560 We can give alias. 261 17:38.560 --> 17:40.840 And there where t dot. 262 17:43.840 --> 17:45.520 R and k one equal to three. 263 17:46.720 --> 17:58.340 And now over here you can give this values like id then In celery, then name and then department name 264 17:58.660 --> 17:59.420 like this. 265 17:59.700 --> 18:02.580 So both this query will give the same result. 266 18:03.540 --> 18:05.380 This one we can say that. 267 18:07.940 --> 18:09.020 Derived table. 268 18:12.820 --> 18:17.300 And this one is you can say that common table expressions. 269 18:22.580 --> 18:24.860 So let me execute both of this query. 270 18:26.220 --> 18:28.060 It will give the same result right. 271 18:28.740 --> 18:29.500 Ramesh. 272 18:29.980 --> 18:31.780 Diksha Deepika Rajan Rahul. 273 18:34.660 --> 18:39.980 So this is how you can identify the difference between rank and. 274 18:45.100 --> 18:46.140 Dense rank right. 275 18:51.580 --> 18:53.960 This is the most asked interview questions. 276 18:56.040 --> 19:00.360 Are asked in many, many interviews. 277 19:01.800 --> 19:03.960 And you can uh. 278 19:05.480 --> 19:14.680 These are also the most, uh, commonly write the SQL concepts in the day to day life that is derived 279 19:14.680 --> 19:16.560 tables and common table expressions. 280 19:18.160 --> 19:24.720 And over here partitioned by we have to do right because we are generating the report based upon partition 281 19:24.720 --> 19:25.680 by right. 282 19:26.520 --> 19:29.120 So this is how you can write the query. 283 19:31.040 --> 19:31.920 Thank you so much. 284 19:31.960 --> 19:34.640 We will meet again in the next video. 285 19:34.640 --> 19:37.760 And uh, please do like, comment, share and subscribe. 286 19:38.600 --> 19:45.560 And please mention my YouTube channel link in the your LinkedIn feed and tag me if possible. 287 19:45.560 --> 19:47.520 That will be a great help to me. 288 19:49.760 --> 19:50.920 Thank you everyone. 289 19:51.440 --> 19:52.320 Have a nice day.