1 00:01.070 --> 00:06.560 So today we are going to cover this set operators in SQL. 2 00:06.980 --> 00:12.350 So there are mainly these four set operators are available. 3 00:12.350 --> 00:16.610 Union Union all intersect and accept. 4 00:17.240 --> 00:25.250 And the alternative of accept is minus which is available in Oracle SQL. 5 00:26.360 --> 00:28.430 Now we have these two data. 6 00:28.430 --> 00:30.350 We can say we have these two data. 7 00:30.380 --> 00:35.180 One is in we can say table A and another is in table B. 8 00:36.140 --> 00:39.770 So I have copied the data into the Excel file. 9 00:41.000 --> 00:43.370 Table A is covering this data. 10 00:43.370 --> 00:46.520 And table B is covering this data. 11 00:47.120 --> 00:50.060 Now if we do the union. 12 00:54.380 --> 01:03.370 If we do the union then then it will be all the elements of A and B without duplicates, so it will. 13 01:03.910 --> 01:10.840 All the elements means all the rows from the A and B will be together and without duplicates. 14 01:10.840 --> 01:14.110 So what we have to do, we have to just copy this one. 15 01:16.990 --> 01:19.660 And we have to copy this one. 16 01:23.620 --> 01:26.890 And then in the data tab we have to remove duplicates. 17 01:26.890 --> 01:27.190 Right. 18 01:27.190 --> 01:31.240 Remove duplicates in column H. 19 01:31.240 --> 01:32.770 So let me remove it. 20 01:33.760 --> 01:36.010 So 123456. 21 01:36.010 --> 01:39.880 So this will be the result of union. 22 01:39.880 --> 01:42.130 So let me show you in the query. 23 01:46.270 --> 01:49.000 I think I don't have that table created. 24 01:49.000 --> 01:50.980 Let me create it first. 25 01:52.270 --> 01:53.350 So. 26 01:57.400 --> 01:58.870 Create table A. 27 02:02.200 --> 02:04.630 Integer in. 28 02:04.630 --> 02:06.190 Create table B. 29 02:08.470 --> 02:09.550 Integer. 30 02:12.250 --> 02:18.430 Now let me create both the table and insert the data into the same. 31 02:19.120 --> 02:20.170 Insert. 32 02:22.240 --> 02:26.110 Into a values one. 33 02:27.430 --> 02:29.440 Comma two. 34 02:31.540 --> 02:33.070 Comma three. 35 02:33.580 --> 02:34.930 Comma four. 36 02:36.040 --> 02:38.380 So ideally it will insert four records. 37 02:38.410 --> 02:39.790 Let me try. 38 02:41.290 --> 02:41.770 Okay. 39 02:41.800 --> 02:43.390 Four record is inserted. 40 02:43.420 --> 02:47.200 And then let me insert data into table B. 41 02:47.230 --> 02:53.230 So 1 to 5 and six. 42 02:56.070 --> 02:58.020 Now let me select. 43 02:58.050 --> 03:08.400 Select star from A and select star from B, and let me execute both of the query or something is wrong. 44 03:16.440 --> 03:17.760 Truncate table a. 45 03:23.910 --> 03:27.030 Now let me insert the data into both table. 46 03:32.880 --> 03:38.790 So this is the data 12341234 and 1256. 47 03:38.790 --> 03:40.980 Now if we do the union. 48 03:41.580 --> 03:43.740 So union between these two. 49 03:45.900 --> 03:47.700 These two table. 50 03:50.010 --> 03:52.260 A and B. 51 03:58.290 --> 04:00.960 Then it will be this much of record. 52 04:00.960 --> 04:02.700 Six record will be there, right? 53 04:03.360 --> 04:06.720 So let me show one, two, three, four, five, six. 54 04:08.490 --> 04:11.370 So union is basically union. 55 04:15.030 --> 04:16.110 Is doing. 56 04:20.730 --> 04:21.660 Append. 57 04:21.690 --> 04:22.620 Append. 58 04:26.670 --> 04:28.380 Which is available. 59 04:31.260 --> 04:32.130 Power BI. 60 04:32.160 --> 04:36.660 So union is doing append basically. 61 04:39.180 --> 04:43.080 And now let's see for union. 62 04:43.110 --> 04:44.160 Union all. 63 04:44.190 --> 04:47.190 So in the union all it will be simple thing. 64 04:47.340 --> 04:53.330 All the data from table A as well as all the data from table B will be appended. 65 04:56.300 --> 05:02.510 All the data from table A will be appended with all the data from table B, and it will not delete the 66 05:02.540 --> 05:03.590 duplicates data. 67 05:03.620 --> 05:06.830 Union all will not delete the duplicates data. 68 05:08.270 --> 05:09.530 So. 69 05:16.580 --> 05:19.070 So if I do union all over here. 70 05:22.280 --> 05:24.140 See how many records are coming. 71 05:24.170 --> 05:25.130 Eight records. 72 05:25.520 --> 05:28.490 So one is coming two times as well as. 73 05:30.830 --> 05:31.250 Two. 74 05:31.280 --> 05:31.460 Three. 75 05:31.490 --> 05:31.670 Four. 76 05:31.700 --> 05:33.170 Two is coming also two times. 77 05:33.170 --> 05:33.830 Right. 78 05:36.290 --> 05:37.610 So it will be eight records. 79 05:37.610 --> 05:41.480 So over here it also you can see it will be eight records are there right. 80 05:43.280 --> 05:49.490 Now if you do the intersect between these two then it will be one and two only. 81 05:49.520 --> 05:50.120 Right. 82 05:53.750 --> 06:00.800 Intersect means common data from all the data sets. 83 06:01.970 --> 06:03.290 This is one data set. 84 06:03.290 --> 06:04.490 This is the second data set. 85 06:04.520 --> 06:05.000 Right. 86 06:05.000 --> 06:08.060 So what are the common one is common in both of them. 87 06:08.090 --> 06:11.720 Two is also common in both of them. 88 06:14.000 --> 06:16.490 So let me copy. 89 06:19.640 --> 06:21.500 And write the intersect. 90 06:23.930 --> 06:27.350 So it will be one and two only right one and two only. 91 06:27.740 --> 06:30.470 Now there is one more thing is there accept. 92 06:30.710 --> 06:35.480 So accept means a minus b. 93 06:35.510 --> 06:39.050 Or we can say that b minus a. 94 06:39.890 --> 06:47.600 So when we do a minus b then it will be all the records which is available in table A but not available 95 06:47.600 --> 06:48.710 in table B. 96 06:49.340 --> 06:50.090 so. 97 06:52.310 --> 06:54.140 Only three and four, we can say. 98 06:54.650 --> 06:57.980 And in case of B minus A it will be five and six. 99 06:57.980 --> 06:58.640 Right. 100 06:58.820 --> 07:03.680 So let me execute the query. 101 07:05.990 --> 07:08.540 When we do a minus b. 102 07:11.000 --> 07:12.350 Then three and four. 103 07:14.930 --> 07:16.430 And if we do. 104 07:25.490 --> 07:28.220 If we do b minus a then it will be. 105 07:30.410 --> 07:31.580 Five and six. 106 07:31.580 --> 07:31.970 Right. 107 07:32.000 --> 07:33.320 Five and six. 108 07:39.500 --> 07:40.820 So. 109 07:46.880 --> 07:47.240 Okay. 110 07:47.260 --> 07:47.530 Yes. 111 07:47.530 --> 07:47.710 Yes. 112 07:47.710 --> 07:48.430 Right. 113 07:49.540 --> 07:51.790 So now this is the basic thing. 114 07:51.790 --> 07:59.410 Now let's see further what what are the rules and regulations now Union all all elements of table A 115 07:59.410 --> 08:01.750 and B including duplicates. 116 08:01.750 --> 08:02.020 Right. 117 08:02.050 --> 08:10.270 Where while we are doing union, all intersects common elements between A and B, except elements of 118 08:10.300 --> 08:19.660 A are not present in B and B, except a means elements of B not present in A right now. 119 08:19.660 --> 08:26.560 In SQL server, the set operations are performed between sets of rows returned by two select statements 120 08:27.610 --> 08:30.790 written by written by two select statements. 121 08:30.790 --> 08:36.280 Select statement one, then Union Union all intersect or accept. 122 08:36.400 --> 08:43.060 So any of the this right any of these and then select statement two. 123 08:43.090 --> 08:45.840 And now there are some certain rules are there? 124 08:48.570 --> 08:50.250 Certain rules are there. 125 08:50.250 --> 08:53.340 Both the queries should return same number of columns. 126 08:54.630 --> 08:58.350 Both the queries should return same number of columns. 127 08:58.620 --> 09:06.360 So if you do, we will see later on and corresponding column data types must be same. 128 09:06.360 --> 09:09.510 Corresponding column data types must be same. 129 09:10.710 --> 09:18.990 Now suppose let's say we have this table where department number ten jobs are present right. 130 09:18.990 --> 09:19.410 So. 131 09:19.440 --> 09:24.810 So in department number ten manager president and clerk jobs are available. 132 09:25.560 --> 09:32.280 And in department number 30 salesman manager and clerk jobs are available. 133 09:32.310 --> 09:34.830 So over here president is not there right. 134 09:34.860 --> 09:39.900 In this department number 30 president is not there. 135 09:39.990 --> 09:45.750 So if we do the union between these two, then what it will do. 136 09:45.750 --> 09:49.260 So it combines the rows written by two select statements. 137 09:49.530 --> 09:51.780 So this is one select statement and it is. 138 09:51.780 --> 09:56.310 This is the second select statement or multiple select statement also. 139 09:56.310 --> 09:57.330 That is also fine. 140 09:57.330 --> 10:01.530 Then it when we do union it will remove the duplicates. 141 10:01.530 --> 10:05.850 It will remove the duplicates and it will sort the result. 142 10:05.850 --> 10:06.540 It will sort. 143 10:06.570 --> 10:11.010 The result means let's do union between these two. 144 10:14.700 --> 10:18.930 So let me just comment this one. 145 10:28.230 --> 10:31.380 So what are the jobs are available in. 146 10:36.000 --> 10:37.410 Department number ten. 147 10:37.410 --> 10:39.330 And in department number 30. 148 10:40.260 --> 10:42.230 So here union will remove the duplicate. 149 10:42.230 --> 10:42.470 Right. 150 10:42.470 --> 10:47.330 So it will return this this this this three then salesman. 151 10:50.930 --> 10:53.480 And and these four rows should be written. 152 10:53.480 --> 10:58.880 So let me execute this query while doing the union. 153 11:00.890 --> 11:01.640 Right. 154 11:01.670 --> 11:05.000 Clerk manager president and salesman. 155 11:05.750 --> 11:07.520 These four jobs will be written. 156 11:08.000 --> 11:14.750 Now suppose we have seen over here both the queries should return same number of columns. 157 11:15.350 --> 11:21.110 So if I am trying to do the union over here, if you see there are two columns are there. 158 11:21.260 --> 11:25.100 And over here there is only one column is there. 159 11:25.400 --> 11:31.550 So if we do the union then it will be all the queries. 160 11:34.370 --> 11:36.140 It will throw this error. 161 11:36.500 --> 11:42.560 All the queries combined using union intersect accept Except operator must have equal number of expressions 162 11:42.560 --> 11:45.560 in their target list. 163 11:48.170 --> 11:48.620 Right. 164 11:48.620 --> 11:50.540 So this should be the result. 165 11:53.810 --> 11:55.760 This is the error when we are. 166 11:55.790 --> 11:58.550 Columns are not same in both the queries. 167 11:59.240 --> 12:01.490 So in that particular then. 168 12:01.490 --> 12:04.310 Now see the second scenario here. 169 12:04.310 --> 12:05.750 Columns are same. 170 12:06.500 --> 12:10.100 Here columns are same but data type will not match right. 171 12:10.130 --> 12:12.020 Data type will not match because. 172 12:16.460 --> 12:18.230 Integer and job. 173 12:21.590 --> 12:22.880 Is where cur. 174 12:26.030 --> 12:29.630 Salary data type is integer and job data type is varchar. 175 12:29.750 --> 12:33.620 So it will be select cell comma job from EMP. 176 12:34.280 --> 12:35.810 So it will throw some error. 177 12:35.810 --> 12:38.600 It will try to convert this salary into job. 178 12:40.090 --> 12:44.980 And if you see over here one more thing I forget to tell you all. 179 12:46.510 --> 12:46.960 Sorry. 180 12:46.960 --> 12:47.800 Over here. 181 12:50.320 --> 12:54.520 If you see, the data will be in short sorted. 182 12:54.520 --> 12:54.760 Right? 183 12:54.790 --> 12:55.900 Sorted order. 184 12:55.930 --> 12:58.690 C will come first and M will come second. 185 12:58.720 --> 13:03.220 Then P will come third and S will come at the last. 186 13:03.220 --> 13:07.870 So over here in union, data will be in the sorted order. 187 13:11.320 --> 13:19.390 Now for this solution we need two salaries integer and job will be in worker. 188 13:19.390 --> 13:21.340 So it will definitely give error right. 189 13:21.340 --> 13:27.850 So over here we need to do column should be in same order in both the queries. 190 13:27.850 --> 13:29.620 Right column should be in same order. 191 13:29.620 --> 13:30.820 In both the queries. 192 13:31.990 --> 13:34.330 Then only result will be given. 193 13:36.340 --> 13:39.040 Otherwise it will throw some error, right? 194 13:46.570 --> 13:48.970 Otherwise it will throw some error. 195 13:49.420 --> 13:55.030 Now if we do the union, then union will combine the rows. 196 13:55.990 --> 13:56.860 Union. 197 13:56.860 --> 13:59.470 What is the difference between union and join. 198 13:59.500 --> 14:01.870 So union will combine the rows over here. 199 14:01.870 --> 14:05.530 One table row is combining with second table row right. 200 14:05.980 --> 14:11.590 Whereas join will be combining columns and perform between similar structure. 201 14:11.620 --> 14:15.970 So over here structure should be similar of both the queries. 202 14:16.240 --> 14:20.770 Whereas while doing some similar structure is not mandatory right. 203 14:21.340 --> 14:23.380 Similar structure is not mandatory. 204 14:23.410 --> 14:30.130 We can do multiple multiple queries Union also 2030 and 20. 205 14:30.160 --> 14:31.840 Also we can do union. 206 14:33.190 --> 14:36.270 So over here more than two queries. 207 14:36.270 --> 14:37.890 We have done the union right. 208 14:44.460 --> 14:46.230 Then union all. 209 14:48.690 --> 14:55.380 So combine the rows written by two select statements and duplicates are not removed and result is not 210 14:55.380 --> 14:55.920 sorted. 211 14:55.920 --> 14:58.620 So sometimes they will ask you the question. 212 15:00.630 --> 15:01.980 What is the. 213 15:10.800 --> 15:15.180 What is the difference between union and union all at that time? 214 15:15.180 --> 15:22.320 You can say that union will not remove the duplicate records, whereas union sorry, union will remove 215 15:22.320 --> 15:24.870 the duplicate records, whereas union all will not remove. 216 15:24.870 --> 15:26.850 The duplicates are not removed. 217 15:26.850 --> 15:27.450 Right? 218 15:27.750 --> 15:33.270 Then result is sorted in this union, whereas in union all result is not sorted. 219 15:33.270 --> 15:33.300 Noted. 220 15:33.780 --> 15:35.250 So only two three. 221 15:35.280 --> 15:37.650 This difference you can give. 222 15:38.640 --> 15:45.780 So over here see cleric is at the last then salesman also multiple records are coming right. 223 15:46.710 --> 15:49.170 Duplicates are not removed over here. 224 15:49.200 --> 15:51.630 And data is not sorted as well. 225 15:52.980 --> 15:55.110 Data is not sorted as well. 226 15:58.080 --> 16:02.100 So now let me do the union all between two columns. 227 16:09.210 --> 16:12.810 So these are the result set of two when multiple columns. 228 16:12.810 --> 16:17.280 Also we can do using while doing using set operators. 229 16:21.360 --> 16:28.440 And I told this union like append operation in power BI then intersect. 230 16:28.440 --> 16:32.510 So returns common values from the output of two select statements. 231 16:35.750 --> 16:41.210 Return common values from the output of two Select statements, so display the jobs which are common 232 16:41.210 --> 16:44.150 in department 20 and 30. 233 16:45.620 --> 16:50.450 Display the jobs which are common in department number 30, 20 and 30. 234 16:50.570 --> 16:53.990 So what are the jobs are common, we can say. 235 16:55.340 --> 17:00.530 Clerk, manager clerk and manager I think only common, right. 236 17:01.100 --> 17:04.490 So let me run this query. 237 17:07.460 --> 17:10.130 So clerk and manager are only common jobs. 238 17:10.130 --> 17:12.620 So intersect will give common only. 239 17:12.620 --> 17:15.170 And let's see the accept one. 240 17:18.560 --> 17:26.750 So returns values from the first query output which are not which are not present in the second query 241 17:26.750 --> 17:27.560 output. 242 17:28.340 --> 17:33.470 So display the jobs in department 30, which are not available in Department 20. 243 17:35.090 --> 17:39.410 So let's see that 120 and 30. 244 17:43.730 --> 17:49.340 So we can say salesman manager salesman only right. 245 17:49.370 --> 17:55.880 Salesman which is not available in below result set so that. 246 17:59.090 --> 18:00.380 It should return. 247 18:00.410 --> 18:01.460 Salesman. 248 18:01.460 --> 18:02.150 Right. 249 18:04.880 --> 18:05.930 Perfect. 250 18:08.030 --> 18:12.590 Now display the jobs in Department 30 which are not common to Department 20. 251 18:12.620 --> 18:16.610 Okay, these two are different ways, but both are same. 252 18:16.610 --> 18:16.850 Only. 253 18:16.850 --> 18:17.150 Right? 254 18:17.180 --> 18:18.020 Different. 255 18:21.530 --> 18:25.520 Now display the job which is only available in department 30. 256 18:25.550 --> 18:28.010 So we have total three departments are there. 257 18:28.010 --> 18:28.190 Right. 258 18:28.210 --> 18:30.220 Department ten, 20 and 30. 259 18:30.340 --> 18:35.350 And we want to display all the jobs which are only available in department number 30. 260 18:35.740 --> 18:42.550 So in the second query we need to mention like ten and 20 and above query we can mention department 261 18:42.580 --> 18:42.940 30. 262 18:42.940 --> 18:49.300 So only salesman is there which is not available in department. 263 18:52.660 --> 18:53.650 Ten and 30. 264 18:53.650 --> 18:55.090 So let me. 265 18:57.490 --> 18:59.740 Show you over here. 266 18:59.770 --> 19:01.000 Salesman is not there. 267 19:01.000 --> 19:05.560 And in the below query also salesman is not there right. 268 19:14.380 --> 19:20.470 So now I will unmute you for few minutes if you have. 269 19:25.840 --> 19:28.150 Any question you can ask. 270 19:39.850 --> 19:40.900 So let's see that. 271 19:40.930 --> 19:42.400 Now the derived table concept. 272 19:42.400 --> 19:45.520 So derived table we can say subquery inside from clause. 273 19:45.880 --> 19:47.440 And the same thing. 274 19:49.780 --> 19:58.120 In Oracle SQL we can say that it is inline view. 275 19:59.170 --> 20:02.290 Inline view subquery inside the From clause. 276 20:04.750 --> 20:11.650 So so we can say that select select then this will be the ideal syntax for the derived table. 277 20:11.680 --> 20:13.720 Select all the column names. 278 20:16.330 --> 20:18.760 Select column names then. 279 20:22.660 --> 20:29.040 Select column from then inside the From clause we can say select statement as alias. 280 20:29.040 --> 20:36.030 So over here alias we have to mandatory give alias then where condition we need to give where condition. 281 20:36.030 --> 20:41.670 Also it is not mandatory but it is optional one. 282 20:42.060 --> 20:43.830 But this thing is mandatory. 283 20:43.860 --> 20:48.690 Select column name from then inside form from clause. 284 20:48.690 --> 20:53.100 We need to start bracket, select statement and then end bracket as alias. 285 20:54.090 --> 20:59.100 So subquery subquery output x like a table for outer query. 286 21:00.120 --> 21:02.790 So this will give some output right. 287 21:02.790 --> 21:06.300 This inner query inside the From clause will give some output. 288 21:06.510 --> 21:10.650 So that will be act as one alias. 289 21:10.650 --> 21:13.470 So this alias is particularly. 290 21:16.290 --> 21:18.870 Treated as one table. 291 21:19.770 --> 21:21.690 Treated as one table. 292 21:22.650 --> 21:30.240 So subquery output acts like a table for outer query in derived tables help us in achieving the requirement, 293 21:30.240 --> 21:34.440 which are difficult to achieve because of execution order. 294 21:36.330 --> 21:38.490 Because of execution order. 295 21:38.520 --> 21:40.230 So now over. 296 21:50.610 --> 21:59.790 So we have seen previously we have used the ranking functions like rank dense rank then entail all the 297 21:59.790 --> 22:01.140 ranking functions. 298 22:01.170 --> 22:06.810 So while using the ranking functions if we want the. 299 22:06.930 --> 22:09.300 So let me execute this query first. 300 22:09.300 --> 22:10.860 Then I will let you know. 301 22:12.150 --> 22:22.070 So now from this particular table, if I want the six highest salary or a fifth highest salary. 302 22:22.640 --> 22:27.230 Then what is my sixth highest salary? 303 22:27.260 --> 22:29.660 We can say or fifth highest salary. 304 22:30.500 --> 22:32.930 This is my fifth highest salary, right? 305 22:35.210 --> 22:37.490 This is my fifth highest salary. 306 22:39.140 --> 22:50.570 Now, if I want this fifth highest salary, then I cannot use directly where r and k equal to five. 307 22:50.600 --> 22:52.430 I cannot use like this. 308 22:55.970 --> 23:02.720 Because invalid column name it will throw some error which is known as invalid column name, because 309 23:02.720 --> 23:09.560 actually this r and k is not a column r and k is not particular any column. 310 23:11.960 --> 23:17.480 Which is physically exist in this EMP table, which is physically exist in this EMP table. 311 23:17.510 --> 23:18.140 Right. 312 23:18.920 --> 23:21.170 So in that particular case. 313 23:23.270 --> 23:28.400 We need to either write the subquery or derived table. 314 23:28.400 --> 23:30.020 Or we can write the city. 315 23:30.500 --> 23:34.460 But now we are learning the derived table so how we can write it. 316 23:34.460 --> 23:36.710 So let me show you that one. 317 23:37.010 --> 23:38.150 What we need to do. 318 23:38.180 --> 23:42.170 We just write like this select star from. 319 23:43.820 --> 23:46.580 Then we can remove this one. 320 23:47.330 --> 23:50.900 And we can mention like this s t. 321 23:53.570 --> 23:56.480 Now this will become one table right? 322 23:58.370 --> 24:02.750 Now this inner query will become one table for me. 323 24:04.370 --> 24:07.640 This entire inner query will become one table for me. 324 24:07.820 --> 24:10.550 And now this column is available. 325 24:10.910 --> 24:20.410 Additionally, in this particular table, not physically but logically, it is available now, but logically 326 24:20.410 --> 24:21.880 it is available now. 327 24:22.420 --> 24:26.290 So now I can use this column right? 328 24:26.410 --> 24:28.510 I can use this column R and k. 329 24:28.540 --> 24:30.100 So where. 330 24:34.630 --> 24:38.200 T dot r and k equal to five. 331 24:42.070 --> 24:45.040 Now let me try to run this query. 332 24:45.880 --> 24:46.330 See. 333 24:46.360 --> 24:52.390 Now now it is giving me fifth highest salary across all the data. 334 24:52.420 --> 24:52.690 Right. 335 24:52.720 --> 24:53.500 Right. 336 24:54.580 --> 24:56.440 And what is this known as. 337 24:57.730 --> 24:59.260 This is known as. 338 25:03.340 --> 25:04.270 Derived table. 339 25:04.270 --> 25:04.870 Why? 340 25:05.500 --> 25:18.370 Because select statement inside the From clause of another Select Statement, right? 341 25:26.680 --> 25:32.140 So now display only the employee having rank less than or equal to five. 342 25:33.010 --> 25:37.780 So all the employee I want to display which is less than or equal to five. 343 25:37.810 --> 25:41.320 So again the same thing same problem is over here. 344 25:41.350 --> 25:42.940 Rank is not a column. 345 25:43.090 --> 25:48.550 So what I have done I have mentioned this select query inside the. 346 25:49.960 --> 25:51.940 This will inside the from clause. 347 25:51.940 --> 25:53.710 This one will become my. 348 25:56.110 --> 25:57.340 One table. 349 26:00.010 --> 26:02.080 This one will become my one table. 350 26:02.080 --> 26:03.100 And. 351 26:08.320 --> 26:13.630 And it will give me all the salary which is top 1 to 5 only. 352 26:13.630 --> 26:13.900 Right. 353 26:13.930 --> 26:15.210 Top 1 to 5. 354 26:15.570 --> 26:17.430 So one two is coming. 355 26:17.460 --> 26:19.680 Two times 3000 is second highest salary. 356 26:19.680 --> 26:24.480 So it is coming two times now 12345. 357 26:24.480 --> 26:25.230 All the. 358 26:25.830 --> 26:29.130 So this is again another use of derived table right. 359 26:32.070 --> 26:34.740 Now this is one more example. 360 26:35.070 --> 26:43.530 Display the fifth display fifth record to 10th record of EMP table display fifth record to 10th record. 361 26:44.580 --> 26:46.140 Record of EMP table. 362 26:46.380 --> 26:51.660 So select star from select again what this will give me. 363 26:51.660 --> 26:52.710 Let me. 364 27:00.270 --> 27:03.030 So this one will be again become my one. 365 27:03.030 --> 27:03.720 My table right. 366 27:03.750 --> 27:08.190 Row number I have given row number to all the all the rows. 367 27:08.190 --> 27:12.630 Right now I want 5 to 10 only right 5 to 10. 368 27:13.530 --> 27:17.220 So where between now I can use this column. 369 27:17.220 --> 27:17.520 Right. 370 27:17.520 --> 27:24.570 Where r n where r n between 5 to 10, five and ten. 371 27:24.690 --> 27:28.200 So it will give me five, six, seven, eight, nine, ten. 372 27:28.230 --> 27:29.610 It will give me six record. 373 27:29.610 --> 27:30.180 Right. 374 27:36.420 --> 27:38.010 Then let's see. 375 27:47.220 --> 27:52.560 Now display only the first five first five records from the EMP table as per row number. 376 27:52.710 --> 27:56.340 So again this one will become the same thing right. 377 28:02.010 --> 28:06.450 This will give me all the rank 123456789 ten. 378 28:06.810 --> 28:07.890 And then. 379 28:09.900 --> 28:12.960 I want less than five, right? 380 28:12.990 --> 28:15.780 All less than five. 381 28:15.810 --> 28:16.680 Row number. 382 28:16.680 --> 28:17.190 So less than. 383 28:17.190 --> 28:18.210 Equal to five. 384 28:19.590 --> 28:21.450 So it will give me all the data. 385 28:21.450 --> 28:23.040 12345. 386 28:28.350 --> 28:29.280 Right. 387 28:36.510 --> 28:37.740 I will do the cut. 388 28:41.580 --> 28:44.370 So this is we can say. 389 28:56.880 --> 29:00.030 18.0. 390 29:08.640 --> 29:10.170 Now let me.