1 00:00.200 --> 00:02.200 Let's see another SQL problem. 2 00:03.600 --> 00:05.920 Find the nth highest salary. 3 00:06.800 --> 00:13.920 So given an employee table with salary information, write a SQL query to find the nth highest salary. 4 00:14.240 --> 00:17.880 For this example, we will find the second highest salary, right? 5 00:18.480 --> 00:25.440 So they have given us the employee salary table and in which employee ID, employee name and salary 6 00:26.760 --> 00:27.800 column is there. 7 00:27.800 --> 00:28.240 Right. 8 00:28.680 --> 00:33.000 Now let me ask the ChatGPT about the same. 9 00:33.640 --> 00:36.080 So let me ask. 10 00:36.120 --> 00:46.440 I am having table structure and data like below. 11 00:47.760 --> 00:48.040 Okay. 12 00:48.080 --> 00:53.040 I am asking this to ChatGPT and let's see what happens. 13 00:55.080 --> 00:55.520 Okay. 14 00:55.720 --> 01:02.870 It is asking that please share your table structure and sample data Like create table in insert into 15 01:03.070 --> 01:03.750 right? 16 01:03.750 --> 01:06.550 So I am sharing the same with ChatGPT. 17 01:09.230 --> 01:12.670 Okay, now it will show something like perfect. 18 01:12.670 --> 01:14.150 Thanks for sharing your data. 19 01:14.790 --> 01:16.550 Here's your setup. 20 01:17.350 --> 01:18.830 Here's your setup clearly. 21 01:19.310 --> 01:27.790 Okay, so they've provided us our table structure and data into the tabular format which is very easy 22 01:27.790 --> 01:29.550 to analyze right. 23 01:29.590 --> 01:31.430 Which is very easy to analyze. 24 01:32.710 --> 01:33.470 Perfect. 25 01:33.670 --> 01:38.630 Now what they are showing over here, they are showing over here based on our data set. 26 01:39.270 --> 01:40.990 They are telling that. 27 01:41.030 --> 01:46.230 Here are a few common examples like what you want to identify from this data set. 28 01:46.550 --> 01:50.110 So they have already provided some of the examples. 29 01:50.110 --> 01:53.190 But now we can ask our requirement to them right. 30 01:53.670 --> 01:54.910 So let's see. 31 01:55.710 --> 02:00.220 So given an example given an employee table with salary The information. 32 02:00.780 --> 02:01.740 Write a query. 33 02:01.780 --> 02:03.860 SQL query to find the second. 34 02:03.900 --> 02:07.980 We can say second highest salary, right? 35 02:08.220 --> 02:13.620 So let me just copy this thing only and let me ask. 36 02:15.620 --> 02:16.540 Perfect. 37 02:16.740 --> 02:17.740 Now let's see. 38 02:21.340 --> 02:22.140 Okay. 39 02:22.580 --> 02:27.980 Now let's see what is the solution provided by the ChatGPT. 40 02:29.780 --> 02:32.220 So this time it is provided. 41 02:32.860 --> 02:34.340 Provided the multiple. 42 02:36.700 --> 02:37.380 Solutions. 43 02:37.380 --> 02:37.900 Right. 44 02:41.220 --> 02:43.260 It is provided the multiple solution. 45 02:43.260 --> 02:47.980 Let me copy any one of them and write it. 46 02:51.380 --> 02:52.220 Over here. 47 02:53.060 --> 02:54.100 Let me copy. 48 02:56.380 --> 02:58.140 The second solution as well. 49 03:03.200 --> 03:03.520 Okay. 50 03:03.560 --> 03:04.880 Were there any third solution? 51 03:04.880 --> 03:05.720 Is there or not? 52 03:05.720 --> 03:06.320 Let's see. 53 03:08.200 --> 03:08.680 Okay. 54 03:08.720 --> 03:10.440 Third solution is also there. 55 03:11.200 --> 03:11.920 Fine. 56 03:14.640 --> 03:15.440 Perfect. 57 03:16.680 --> 03:17.760 Any other solution? 58 03:17.760 --> 03:18.120 No. 59 03:19.840 --> 03:23.440 So this time ChatGPT has provided. 60 03:27.320 --> 03:27.880 Three. 61 03:27.920 --> 03:29.120 Three different solutions. 62 03:29.120 --> 03:29.400 Right? 63 03:29.440 --> 03:30.760 Three different solutions. 64 03:31.560 --> 03:39.400 So based on the information available with ChatGPT, they've provided three different solutions. 65 03:40.240 --> 03:42.600 So and what is our solution. 66 03:42.600 --> 03:44.760 Our solution is about two right. 67 03:44.840 --> 03:46.640 So about two is our solution. 68 03:47.160 --> 03:51.400 And this is we can say solution provided by. 69 03:53.480 --> 03:54.280 ChatGPT. 70 03:54.520 --> 03:57.960 So all the below solutions has been provided by ChatGPT. 71 03:58.480 --> 04:00.550 So what it is doing over here? 72 04:01.510 --> 04:04.470 Select distinct salary from this particular table. 73 04:04.470 --> 04:05.390 Order by salary. 74 04:05.390 --> 04:09.230 Descending offset one row. 75 04:09.390 --> 04:11.470 Fetch next one row only. 76 04:11.870 --> 04:19.070 So it will not pick up the first row, but it will do offset of first row and it will pick up the next 77 04:19.070 --> 04:22.950 row only next one row only right. 78 04:22.950 --> 04:27.710 So it will provide the second highest salary by this way. 79 04:27.710 --> 04:32.070 So this is one solution we can say which is 90,000. 80 04:32.510 --> 04:39.030 Now the same this second solution has been written by us as well. 81 04:39.070 --> 04:39.630 Right. 82 04:39.670 --> 04:42.150 Second solution has been written by us as well. 83 04:42.550 --> 04:47.030 So what it is doing over here, it is using the density window function. 84 04:47.630 --> 04:50.950 Over it is over ordered by salary descending. 85 04:51.350 --> 04:54.590 So it will give the highest salary rank one. 86 04:54.630 --> 05:00.260 See, let me execute this query only And you will get an idea. 87 05:00.860 --> 05:04.380 It will for the highest salary. 88 05:04.380 --> 05:05.580 It will give rank one. 89 05:05.860 --> 05:07.300 For the second highest salary. 90 05:07.300 --> 05:08.540 It will give rank two. 91 05:09.300 --> 05:11.380 Rank two for the third highest salary. 92 05:11.420 --> 05:12.740 It will give rank three. 93 05:13.740 --> 05:14.460 Like that. 94 05:14.460 --> 05:17.540 Right now it has used the derived table. 95 05:17.540 --> 05:21.380 And the same thing we have also used over here as well. 96 05:21.420 --> 05:22.140 Derived table. 97 05:22.140 --> 05:22.580 Right. 98 05:23.260 --> 05:25.940 So this is called the derived table. 99 05:25.940 --> 05:26.660 Why derived table. 100 05:26.700 --> 05:34.180 Because select query inside the from clause of the another select statement that is known as the derived 101 05:34.220 --> 05:34.700 table. 102 05:35.140 --> 05:42.500 And over here what it is doing where k equal to two means from this all this data wherever the k equal 103 05:42.500 --> 05:47.540 to two, that is our requirement because we want second highest salary only right. 104 05:49.620 --> 05:57.900 And over here what it is doing it is selecting the maximum salary where salary less than in inside the 105 05:57.900 --> 05:58.650 inner query. 106 05:58.650 --> 06:03.010 Also, it is, uh, checking the maximum salary. 107 06:03.250 --> 06:12.050 So wherever the maximum salary is, there it is provided that that first one and then what it is doing 108 06:12.810 --> 06:14.930 the second maximum salary should be less. 109 06:14.930 --> 06:16.970 Less than the maximum salary. 110 06:17.490 --> 06:18.010 Right. 111 06:22.010 --> 06:29.730 So this all these three query in fact we can say all these five query will provide the same solution. 112 06:29.730 --> 06:32.650 All these five query will provide the same solution. 113 06:33.130 --> 06:39.170 See 90,000 90,000 and over here as well 90,000 90,000. 114 06:39.810 --> 06:42.050 But the problem over here is. 115 06:45.170 --> 06:45.730 Okay. 116 06:45.770 --> 06:49.450 The let me remove this distinct and let let me check. 117 06:53.050 --> 06:53.490 Okay. 118 06:53.530 --> 06:57.610 Still it is giving only 190 90,000. 119 06:58.080 --> 07:03.880 So can you tell me what is the perfect answer for this query? 120 07:05.160 --> 07:07.080 What is the perfect answer for this query? 121 07:07.120 --> 07:11.440 Actually, perfect answer should be we can say middle one. 122 07:12.960 --> 07:19.080 So below one is the perfect solution. 123 07:19.080 --> 07:19.440 Why? 124 07:23.000 --> 07:25.840 Because it is giving 125 07:27.520 --> 07:31.720 90,002 times, right? 126 07:31.720 --> 07:34.520 Because 90,000 is our second highest salary. 127 07:34.760 --> 07:37.640 So it should be coming two times, right? 128 07:37.680 --> 07:38.760 Not the one time. 129 07:40.360 --> 07:40.600 Right. 130 07:40.600 --> 07:43.520 So this is our perfect answer we can say. 131 07:43.760 --> 07:49.520 But because of in the first query and in the third query, 90,000 is coming one one time. 132 07:49.520 --> 07:52.920 So that is not the optimum answer right now. 133 07:52.920 --> 07:55.920 Let's see what the GPT is further explained. 134 07:56.720 --> 08:03.110 So inner query finds the highest salary and outer query finds the maximum salary less than that. 135 08:03.190 --> 08:03.550 Right? 136 08:03.590 --> 08:05.150 Which is second highest salary. 137 08:05.190 --> 08:05.710 Right. 138 08:09.150 --> 08:15.830 So would you like me to show how to get the both the employee name and salaries for the second highest 139 08:15.830 --> 08:16.390 salary? 140 08:16.990 --> 08:25.830 So here they've given us the some question over here in the answer what it is giving it is giving only 141 08:27.030 --> 08:28.230 salary information. 142 08:28.230 --> 08:35.590 But if I want the employee name as well as salary information, then what I can do so I can write. 143 08:35.590 --> 08:36.150 Yes. 144 08:37.390 --> 08:37.670 Right. 145 08:37.670 --> 08:38.630 I can write yes. 146 08:38.630 --> 08:39.350 Over here. 147 08:39.590 --> 08:44.070 So this is how you can further explore the ChatGPT as well, right. 148 08:46.950 --> 08:48.750 Now see what will happen. 149 08:51.670 --> 08:51.990 Okay. 150 08:51.990 --> 08:58.090 Now let me copy this result set or let me copy this result Set only. 151 08:58.090 --> 08:59.010 And let's see. 152 09:01.530 --> 09:04.130 So this time it will give the employee name as well. 153 09:04.130 --> 09:04.610 Right. 154 09:05.330 --> 09:08.130 This time it will give the employee name as well. 155 09:09.010 --> 09:11.370 Wherever the second highest salary is there. 156 09:11.370 --> 09:11.810 Right. 157 09:12.610 --> 09:13.570 So perfect. 158 09:14.530 --> 09:18.090 Now let's see whether it is asking any further thing. 159 09:18.290 --> 09:24.650 Would you like me to also show how to get the highest salary dynamically using the same logic? 160 09:25.250 --> 09:33.010 So over here it will ask us whether to find the third highest salary or fourth highest salary, or the 161 09:33.010 --> 09:35.010 sixth highest salary dynamically. 162 09:35.370 --> 09:36.970 So let me write. 163 09:36.970 --> 09:38.370 Yes as well. 164 09:42.290 --> 09:43.890 So now what it is doing. 165 09:43.890 --> 09:45.850 Let's make your query dynamic. 166 09:46.210 --> 09:48.930 So you can get the nth highest salary right. 167 09:49.610 --> 09:51.130 So let's see. 168 09:54.490 --> 10:01.560 Okay let's us see whether this query will work in our SQL server or not. 169 10:01.600 --> 10:02.320 Let's see. 170 10:06.560 --> 10:08.440 I think it should work right. 171 10:08.760 --> 10:10.000 I think it should work. 172 10:10.400 --> 10:11.760 What it is doing over here. 173 10:11.760 --> 10:19.840 It has declared the variable n right integer and it is giving some static value to that variable. 174 10:20.320 --> 10:23.440 And now in the filter condition it is using that variable. 175 10:23.760 --> 10:31.000 So whenever you want to retrieve any other salary you need to change it over here only. 176 10:31.000 --> 10:31.480 Right. 177 10:32.360 --> 10:35.160 You need to change it over here so it will give this time. 178 10:35.520 --> 10:37.320 Third highest salary right. 179 10:43.240 --> 10:44.640 It will give this time. 180 10:44.680 --> 10:45.720 Third highest salary. 181 10:46.000 --> 10:46.800 Perfect. 182 10:47.880 --> 10:49.640 So this is what we want, right? 183 10:50.360 --> 10:55.520 So let's see the next question into the next lecture. 184 10:55.520 --> 10:56.160 Thank you.