1 00:03.720 --> 00:04.760 Hi everyone. 2 00:07.720 --> 00:09.920 So this is our second class. 3 00:11.000 --> 00:14.160 In the last class we have seen the five queries. 4 00:19.080 --> 00:21.680 In the last class we have seen the five queries. 5 00:23.440 --> 00:26.880 So let's move ahead. 6 00:29.200 --> 00:33.200 So this is the first query for today one minute. 7 00:37.000 --> 00:39.320 Photoshop revenue analysis. 8 00:39.960 --> 00:41.880 My voice is clear and loud. 9 00:41.880 --> 00:42.320 Right? 10 00:49.720 --> 00:50.680 Good, good. 11 00:50.680 --> 00:51.120 Audible. 12 00:51.120 --> 00:51.520 Right. 13 00:54.160 --> 00:54.680 Okay. 14 00:55.560 --> 01:03.870 So let's see today's first query Photoshop revenue analysis in this, uh, query has been asked in Adobe 15 01:03.910 --> 01:05.870 SQL interview question. 16 01:07.510 --> 01:09.230 Adobe SQL interview question. 17 01:09.830 --> 01:12.910 So for every customer that bought Photoshop. 18 01:13.710 --> 01:18.190 So here there is one product which is uh known as Photoshop product. 19 01:18.670 --> 01:26.710 So for every customer that bought Photoshop, written a list of the customers in the total spent on 20 01:26.710 --> 01:29.950 all the products except for Photoshop products. 21 01:34.310 --> 01:37.830 Sort your answer by customer IDs in ascending order. 22 01:43.190 --> 01:43.550 Okay. 23 01:43.590 --> 01:51.110 For every customer that bought Photoshop, return a list of customers and total spent on all the products 24 01:51.110 --> 01:53.790 except for Photoshop products. 25 01:55.270 --> 01:58.290 So I have created this table already. 26 01:59.370 --> 02:01.970 This is a adopt transaction table. 27 02:08.490 --> 02:15.570 This is a adopt transaction table and in which customer ID product and revenue is are given. 28 02:17.330 --> 02:22.290 So if you see oh let me select the data from this table. 29 02:27.930 --> 02:28.450 Okay. 30 02:28.770 --> 02:32.490 So I have already taken that this data into the Excel file. 31 02:33.010 --> 02:37.970 So let me show you this in Excel file okay. 32 02:38.370 --> 02:43.090 So over here what is our query is for every customer that bought Photoshop. 33 02:44.090 --> 02:49.810 So if you see over here customer ID is given right customer ID product and revenue. 34 02:50.850 --> 02:58.280 So if you see every customer has some of the customer has buy a product which is known as Photoshop 35 02:58.280 --> 02:58.920 product. 36 02:59.440 --> 03:00.400 For 1 to 3. 37 03:00.400 --> 03:02.000 You can see from 913. 38 03:02.000 --> 03:04.000 Also you can see photos of product. 39 03:04.760 --> 03:09.360 Now for every customer that bought Photoshop, return a list of customers. 40 03:10.200 --> 03:17.880 We require the list of customer ID or customers and total spent on all the products. 41 03:18.440 --> 03:25.760 Total spent on all products except for except for Photoshop products. 42 03:26.520 --> 03:34.320 So now can you can anyone tell me what will be the answer over here? 43 03:45.120 --> 03:53.680 Okay, so for example, uh, can you tell me for this, uh, what will be the answer over here? 44 04:04.100 --> 04:04.780 Okay. 45 04:07.100 --> 04:07.500 Okay. 46 04:07.540 --> 04:08.860 Revenue is 150. 47 04:08.900 --> 04:09.380 Okay. 48 04:18.580 --> 04:19.020 Yeah. 49 04:19.060 --> 04:23.300 Require for every or every customer that bought Photoshop. 50 04:23.300 --> 04:23.740 Right? 51 04:24.220 --> 04:33.060 For every customer that bought Photoshop, return a list of customers and total spend on all products 52 04:33.060 --> 04:35.260 except for Photoshop products. 53 04:42.500 --> 04:43.180 No, no. 54 04:43.420 --> 04:45.620 Read this two line carefully. 55 04:54.090 --> 04:54.450 For. 56 04:54.450 --> 05:02.770 For every customer that bought Photoshop, written a list of customers and total spent on all the products 57 05:02.770 --> 05:04.890 except for Photoshop products. 58 05:17.890 --> 05:19.050 I yes yes yes. 59 05:20.050 --> 05:27.330 But but again, again, this line is important for every customer that bought Photoshop. 60 05:29.610 --> 05:29.930 Yes. 61 05:29.930 --> 05:31.370 Now see the data. 62 05:32.770 --> 05:34.170 And again let me. 63 05:37.130 --> 05:37.570 Okay. 64 05:37.610 --> 05:38.690 Nine nine okay. 65 05:38.730 --> 05:40.610 913 300. 66 05:40.650 --> 05:41.610 Yes okay. 67 05:42.970 --> 05:43.610 Perfect. 68 05:43.610 --> 05:48.290 This is the answer because 234 doesn't have bought the Photoshop products. 69 05:48.290 --> 05:56.150 So it will not include in our result set again 562 has not bought Photoshop, so again this one will 70 05:56.150 --> 05:57.830 not include in our result set. 71 05:57.830 --> 05:59.630 So this should be our answer. 72 06:00.150 --> 06:04.150 Now we have to write this same thing into SQL right? 73 06:09.830 --> 06:10.710 One minute. 74 06:13.630 --> 06:16.590 We have to write the same thing into SQL. 75 06:17.030 --> 06:27.510 So first of all what we have to do we have to consider those customer who has bought the Photoshop product. 76 06:27.510 --> 06:27.750 Right. 77 06:27.790 --> 06:29.590 So 123 and 913. 78 06:30.070 --> 06:30.510 Right. 79 06:30.910 --> 06:39.150 Now what we have to do select star from the same table. 80 06:40.030 --> 06:45.470 Same table where customer ID in. 81 06:50.060 --> 06:50.580 Cross. 82 06:50.620 --> 06:59.820 Now we have taken those customer IDs who has bought photos of products, but now we do not need to. 83 07:04.620 --> 07:05.660 No no no no no. 84 07:05.660 --> 07:06.260 One query. 85 07:06.260 --> 07:07.180 Not possible. 86 07:16.100 --> 07:16.380 Ah. 87 07:18.540 --> 07:21.860 Let me write the query. 88 07:21.900 --> 07:23.460 Now, now what I have to do. 89 07:41.260 --> 07:45.340 Where product is equal to. 90 07:47.720 --> 07:48.160 Okay. 91 07:48.160 --> 07:48.480 Now. 92 08:13.720 --> 08:16.760 Now, now, in this query, what I have to do ahead. 93 08:40.280 --> 08:40.680 Then. 94 08:47.510 --> 08:47.910 Okay. 95 08:47.910 --> 08:48.270 Now. 96 08:53.110 --> 08:53.990 Okay, now. 97 09:03.910 --> 09:05.750 No no no no no no no no no. 98 09:09.750 --> 09:10.430 Grab. 99 09:13.990 --> 09:14.430 A. 100 09:17.430 --> 09:19.030 Customer ID then. 101 09:34.270 --> 09:34.710 Okay. 102 09:34.710 --> 09:35.070 Now. 103 09:37.350 --> 09:38.110 Now what? 104 09:51.090 --> 09:52.250 Actually, the. 105 09:57.130 --> 10:03.210 Actually are actually one person who has written this resultset. 106 10:04.050 --> 10:09.970 That is only the correct person that has understood the question very clearly. 107 10:10.610 --> 10:14.890 Apart from that, I think you haven't understood the question clearly. 108 10:16.650 --> 10:18.330 That is the problem. 109 10:19.970 --> 10:22.290 Now these two line is the question. 110 10:23.450 --> 10:28.810 These two line is the question for every customer that bought Photoshop. 111 10:29.250 --> 10:36.170 Return a list of customers and total spent on all products except for Photoshop products. 112 10:40.930 --> 10:42.410 No no no no no line doesn't. 113 10:42.450 --> 10:43.400 No no no no no. 114 10:43.560 --> 10:43.960 Line. 115 10:44.000 --> 10:45.080 Line first. 116 10:45.120 --> 10:46.200 Makes sense. 117 10:46.200 --> 10:46.880 Makes sense. 118 10:47.560 --> 10:48.560 No no no. 119 10:59.560 --> 11:00.160 No. 120 11:03.760 --> 11:04.360 But. 121 11:10.960 --> 11:11.800 No no no. 122 11:11.800 --> 11:15.920 But who has compulsorily purchased Photoshop product? 123 11:18.640 --> 11:23.920 If that person doesn't have purchase Photoshop, then we have to exclude those customers. 124 11:23.960 --> 11:24.840 See these three? 125 11:25.720 --> 11:26.120 These three? 126 11:26.160 --> 11:29.200 We don't require these three. 127 11:29.200 --> 11:30.360 We don't require. 128 11:34.000 --> 11:34.800 That is the meaning. 129 11:34.800 --> 11:36.680 That is the sense of first line. 130 11:36.680 --> 11:38.360 That is the sense of first line. 131 11:45.140 --> 11:45.900 Now. 132 11:51.180 --> 11:53.940 Actually, actually, the problem is. 133 11:57.420 --> 11:58.980 No, no, no problem is not you. 134 11:58.980 --> 12:01.020 You are not understanding thing. 135 12:01.060 --> 12:02.580 Actually, that is the problem. 136 12:03.340 --> 12:06.460 A very simple thing. 137 12:08.580 --> 12:12.860 If you see the data, this is the data I have taken in the Excel file. 138 12:13.300 --> 12:16.140 Now I have highlighted the green, yellow and red. 139 12:16.940 --> 12:19.220 So there is a meaning of green. 140 12:19.620 --> 12:20.660 Meaning of yellow. 141 12:20.700 --> 12:21.780 Meaning of red. 142 12:22.620 --> 12:23.460 Why green? 143 12:23.660 --> 12:27.420 Because I want those customer who has purchased Photoshop. 144 12:27.780 --> 12:28.660 But now. 145 12:28.700 --> 12:29.940 Now the second thing. 146 12:30.380 --> 12:32.540 I don't want the Photoshop revenue. 147 12:32.540 --> 12:42.250 I want the revenue of those customer who has purchased Photoshop but revenue of other products from 148 12:42.250 --> 12:43.490 that customer only. 149 12:44.010 --> 12:47.170 So that is why I have highlighted 150. 150 12:47.370 --> 12:49.770 So 150 means 150. 151 12:50.770 --> 12:56.330 And now again see this green 913 has purchased Photoshop. 152 12:56.650 --> 12:58.690 So I do not want Photoshop revenue. 153 12:58.690 --> 13:02.290 I want revenue of those products apart from Photoshop. 154 13:02.850 --> 13:06.330 But that customer must have purchased Photoshop product. 155 13:06.850 --> 13:09.930 So 9 to 102 hundred. 156 13:10.410 --> 13:12.130 So 300 is the answer. 157 13:12.130 --> 13:13.530 And why this is red. 158 13:14.010 --> 13:18.210 Because this three two customer has not purchased Photoshop product. 159 13:19.050 --> 13:22.610 So that is not in my requirement. 160 13:25.330 --> 13:30.090 So now this that is the reason I have written sub query. 161 13:30.090 --> 13:30.970 This is wrong. 162 13:31.850 --> 13:32.810 This is wrong. 163 13:33.370 --> 13:37.930 We cannot write like this one minute. 164 13:40.550 --> 13:45.390 Actually, the problem is I have to cover five queries in one or 1.5 hour. 165 13:46.190 --> 13:49.790 So if like this, we cannot cover five queries. 166 13:53.470 --> 13:53.830 Okay. 167 13:53.870 --> 13:54.270 Okay. 168 13:54.550 --> 13:57.790 So this is so this is simple thing right. 169 13:58.150 --> 14:01.070 First thing I have taken a Photoshop product. 170 14:01.710 --> 14:07.390 I am always dividing the question into multiple parts to understand better. 171 14:08.070 --> 14:12.950 So now from subquery is the better option over here. 172 14:13.510 --> 14:16.790 Now where customer ID in. 173 14:17.790 --> 14:22.390 Now I got the all the customer who has purchased Photoshop product right. 174 14:23.230 --> 14:27.430 But now the thing is let me write this select star. 175 14:29.990 --> 14:31.590 Now what is my requirement. 176 14:34.230 --> 14:38.660 Where Product not equal to Photoshop. 177 14:40.820 --> 14:43.340 Because I don't want Photoshop revenue. 178 14:43.340 --> 14:43.740 Right? 179 14:46.900 --> 14:47.620 And now. 180 14:50.220 --> 14:52.300 Here I will take customer ID. 181 14:54.380 --> 14:55.020 Comma. 182 14:55.060 --> 14:57.380 Sum of revenue. 183 14:57.380 --> 14:57.780 Right. 184 14:59.860 --> 15:01.100 Sum of revenue. 185 15:01.100 --> 15:05.420 And because of this here I have to write end. 186 15:07.140 --> 15:11.740 And now because of I have written the aggregate function I have to use group by right. 187 15:12.580 --> 15:14.620 Now let me execute this query. 188 15:16.340 --> 15:19.340 This is what the same thing I have written in Excel right. 189 15:19.940 --> 15:23.540 1231 50 913 300. 190 15:39.280 --> 15:40.080 Same thing. 191 15:40.080 --> 15:45.760 What I have explained in Excel file and these two lines. 192 15:45.760 --> 15:46.280 Right. 193 15:46.320 --> 15:47.320 These two lines. 194 15:55.440 --> 15:58.800 So now let's move ahead with the next question. 195 16:01.720 --> 16:02.480 One minute. 196 16:28.240 --> 16:28.680 Okay. 197 16:28.680 --> 16:31.000 Let's move ahead with the next question. 198 16:42.510 --> 16:43.630 Which city? 199 17:01.590 --> 17:06.150 Okay, let me write this query, whether it is correct or not. 200 17:06.190 --> 17:07.750 Actually, this is also wrong. 201 17:10.190 --> 17:11.350 No, brother. 202 17:16.830 --> 17:17.430 Okay, okay. 203 17:17.470 --> 17:18.030 City. 204 17:30.630 --> 17:30.990 Mm. 205 17:37.970 --> 17:39.570 But why it is giving error. 206 17:43.450 --> 17:46.130 Where clause should be above group by right. 207 17:48.850 --> 17:50.170 Again, this is wrong. 208 18:03.970 --> 18:05.490 Invalid column name. 209 18:12.090 --> 18:12.810 Okay. 210 18:17.010 --> 18:17.570 Nothing. 211 18:17.610 --> 18:18.850 Nothing is given. 212 18:53.080 --> 18:53.480 Okay. 213 18:53.480 --> 18:54.240 This is wrong. 214 18:54.280 --> 18:55.400 Actually, this is wrong. 215 18:57.000 --> 18:58.600 Are you. 216 18:58.640 --> 19:01.080 Are selecting data from city only, right? 217 19:01.320 --> 19:04.320 You are selecting data from city only and product. 218 19:04.800 --> 19:09.960 Here it is taken Photoshop product and here it is taken product not equal to Photoshop. 219 19:09.960 --> 19:10.920 That is never. 220 19:11.560 --> 19:14.520 This condition will never satisfy. 221 19:18.400 --> 19:18.880 Yeah. 222 19:18.920 --> 19:19.640 This query. 223 19:19.680 --> 19:21.680 We can join with this one. 224 19:22.240 --> 19:23.040 Like this. 225 19:26.680 --> 19:36.460 This own city dot, we can say that customer ID is equal to, uh, this one dot. 226 19:38.500 --> 19:43.460 Customer ID and now here we can take that this this one dot. 227 19:43.540 --> 19:43.900 Uh. 228 19:47.940 --> 19:49.980 Now I think it will give result. 229 20:12.380 --> 20:12.900 Revenue. 230 20:13.380 --> 20:14.540 This revenue. 231 20:24.340 --> 20:24.820 Okay. 232 20:24.820 --> 20:26.540 Now it is giving perfect result. 233 20:28.970 --> 20:30.410 We have to do joint, right? 234 20:30.770 --> 20:33.890 Without this joint, it is not possible. 235 20:36.730 --> 20:39.530 Okay, let's move ahead with the next query. 236 20:41.970 --> 20:48.770 Uh, CVS health is analyzing its pharmacy sales data and how well different products are selling in 237 20:48.770 --> 20:49.650 the market. 238 20:51.170 --> 20:55.730 Each drug is exclusively manufactured by a single manufacturer. 239 20:56.890 --> 21:04.850 Write a query to identify the manufacturer associated with the drugs that resulted in the losses for 240 21:05.370 --> 21:10.250 CVS health, and calculate the total amount of losses incurred. 241 21:11.770 --> 21:13.370 Output the manufacturer's name. 242 21:13.370 --> 21:14.450 The number of drugs. 243 21:14.490 --> 21:17.930 Drugs associated with the losses in total. 244 21:17.930 --> 21:19.970 Losses in absolute value. 245 21:21.330 --> 21:25.050 So here we have to use ABS function right abs function. 246 21:26.350 --> 21:33.030 then display the results sorted in descending order, with the highest losses displayed at the top. 247 21:34.310 --> 21:36.430 So here are this. 248 21:37.270 --> 21:41.670 All the manufacturers are associated with the drugs, right? 249 21:42.070 --> 21:48.790 So here we have to identify the manufacturer name the number of drugs associated with the losses. 250 21:49.710 --> 21:56.750 So how we can identify the losses that we will see in the total losses in absolute value. 251 21:57.470 --> 22:04.670 So here I have pharmacy sales table in which product ID ten units sold total sales. 252 22:04.670 --> 22:10.670 And Cogs means cost of goods sales manufacturer drug. 253 22:10.990 --> 22:13.110 And these three are actually not required. 254 22:13.110 --> 22:14.150 These three columns. 255 22:14.790 --> 22:15.550 As of now. 256 22:17.190 --> 22:22.350 Now I have already created this table and inserted the data into the same. 257 22:24.780 --> 22:28.180 Now let me select the data from this table. 258 22:32.780 --> 22:37.380 Select star from pharmacy sales okay. 259 22:37.860 --> 22:41.140 Now how can we identify the losses. 260 22:41.820 --> 22:46.820 So total sales minus Cogs means where. 261 22:47.940 --> 22:50.340 Let me select the data over here. 262 22:55.980 --> 23:03.020 Drug then manufacturer then total sales then. 263 23:05.380 --> 23:17.820 So here the definition of losses is equal to total underscore sales minus Cogs means cost of goods sales. 264 23:17.820 --> 23:23.880 And if this amount if this result comes in the minus sign. 265 23:25.920 --> 23:28.200 Then we will consider it as a. 266 23:28.240 --> 23:31.800 We will consider it as a loss. 267 23:35.240 --> 23:36.880 Right now. 268 23:36.920 --> 23:40.120 Over here we have to write the where clause. 269 23:40.160 --> 23:40.640 Where. 270 23:42.400 --> 23:44.760 Total sales minus. 271 23:50.360 --> 23:52.680 Cogs less than equal to zero. 272 24:03.800 --> 24:05.640 Or we can say minus. 273 24:05.680 --> 24:06.800 We have to consider only. 274 24:06.800 --> 24:07.160 Right. 275 24:07.880 --> 24:09.760 Like over here I have written. 276 24:15.480 --> 24:23.470 Total sales minus Cogs and total sales minus cost less than zero means when it is in minus, then we 277 24:23.470 --> 24:25.070 will consider it as a loss. 278 24:27.990 --> 24:30.230 Please be on mute everyone. 279 24:58.230 --> 24:59.590 So total sales. 280 25:00.070 --> 25:01.030 Total sales. 281 25:15.030 --> 25:16.830 You are able to hear me, right? 282 25:20.610 --> 25:22.290 Total sales minus Cokes. 283 25:22.330 --> 25:27.050 Less than zero means that will consider as a losses. 284 25:27.090 --> 25:30.650 Now what if what is our requirement? 285 25:32.130 --> 25:32.570 Output? 286 25:32.570 --> 25:34.170 The manufacturer's name. 287 25:34.170 --> 25:36.970 The number of drugs associated with the losses. 288 25:37.810 --> 25:38.290 So. 289 25:40.610 --> 25:43.530 Manufacturer name then. 290 25:43.530 --> 25:45.050 Count of Dreux. 291 25:47.930 --> 25:48.730 Count of. 292 25:57.010 --> 25:57.650 Drugs. 293 25:58.010 --> 25:58.690 And then. 294 26:05.050 --> 26:05.650 Output. 295 26:05.650 --> 26:06.450 The manufacturer. 296 26:06.450 --> 26:06.850 The name. 297 26:06.850 --> 26:12.130 The number of drugs associated with the losses and the total losses in absolute value. 298 26:12.490 --> 26:16.370 So here we need to do total losses. 299 26:16.370 --> 26:17.560 Means this one. 300 26:22.640 --> 26:24.320 Into absolute value. 301 26:24.680 --> 26:26.080 So let me. 302 26:29.280 --> 26:33.680 If I consider this one, then I have to do group by write. 303 26:33.720 --> 26:34.440 Group by. 304 26:38.520 --> 26:39.680 Manufacturer. 305 26:40.080 --> 26:41.960 Now let me execute this query. 306 26:45.080 --> 26:49.080 So if you see over here results are coming in the minus right. 307 26:49.080 --> 26:50.480 But what is our requirement. 308 26:50.480 --> 26:52.720 We want it into the absolute value. 309 26:57.760 --> 27:01.240 As total losses absolute. 310 27:01.760 --> 27:03.160 So this will be our. 311 27:06.240 --> 27:07.040 Result set. 312 27:07.040 --> 27:10.080 Let me compare both the result set. 313 27:17.620 --> 27:25.020 And one more thing it has been said is display the results sorted in descending order, with the highest 314 27:25.020 --> 27:26.780 losses displayed at the top. 315 27:30.220 --> 27:36.060 So over here we need to display the highest losses displayed at the top right. 316 27:36.060 --> 27:41.820 So over here we need to change the order by order by this and descending. 317 27:44.620 --> 27:46.420 Let me execute this query now. 318 27:48.060 --> 27:51.460 So result set of both of these one should be the same. 319 27:58.140 --> 28:00.580 Let me unmute if. 320 28:14.810 --> 28:15.130 Hello. 321 28:15.130 --> 28:16.130 Hi, Chinmoy. 322 28:19.730 --> 28:23.850 Yeah, actually, if you are not speaking, then please be on mute. 323 28:24.330 --> 28:24.770 Okay. 324 28:29.450 --> 28:31.770 When you are, anyone has any question. 325 28:31.770 --> 28:35.690 Then only you can unmute yourself now. 326 28:36.490 --> 28:37.690 Let me know this. 327 28:37.810 --> 28:40.930 Uh, this is clear or not. 328 28:46.370 --> 28:47.170 Okay. 329 28:52.770 --> 28:56.450 Now let me over here. 330 28:56.650 --> 28:59.010 We have given the table pharmacy sales table. 331 28:59.010 --> 28:59.450 Right. 332 28:59.890 --> 29:01.610 Pharmacy sales table. 333 29:01.650 --> 29:05.890 Select star from pharmacy sales. 334 29:07.890 --> 29:16.030 Now you write a query to identify the manufacturer associated with the drugs that resulted in the losses 335 29:16.030 --> 29:19.750 for serious health, losses for serious health. 336 29:20.470 --> 29:24.230 So now total how can we identify the losses. 337 29:24.750 --> 29:32.630 So losses equal to total underscore sales minus Cogs. 338 29:32.630 --> 29:43.990 And if this amount if this amount comes in minus sign then we will consider it as a loss. 339 29:46.750 --> 29:48.470 Right now. 340 29:50.950 --> 29:55.390 Now we have to identify output the manufacturer name. 341 29:55.630 --> 30:01.470 So manufacturer name we have to give then total number of drugs associated with the losses. 342 30:01.910 --> 30:03.710 So now let me do. 343 30:03.950 --> 30:04.470 Where. 344 30:05.790 --> 30:06.470 Total. 345 30:06.510 --> 30:08.710 Underscore one minute. 346 30:14.700 --> 30:18.740 Total underscore sales minus cogs. 347 30:19.220 --> 30:20.340 Less than zero. 348 30:20.980 --> 30:24.460 So let me execute this one. 349 30:24.820 --> 30:27.140 So total 18 record are coming right. 350 30:28.340 --> 30:30.820 Let me taken this data into the Excel file. 351 30:34.780 --> 30:36.620 Total 18 records are coming. 352 30:36.620 --> 30:38.340 So these 18 records are. 353 30:40.500 --> 30:45.900 Now this 18 manufacturers are occur in the losses. 354 30:47.660 --> 30:50.860 Because if we do if we do this minus this. 355 30:53.220 --> 31:02.060 Insert equal to this minus this then it is coming in the negative side right. 356 31:02.220 --> 31:04.820 So all this coming are are in the negative sign. 357 31:04.820 --> 31:07.700 So all these are our losses. 358 31:08.460 --> 31:11.520 Now what is our requirement? 359 31:11.520 --> 31:14.000 We have to display the manufacturer right. 360 31:14.520 --> 31:20.320 So I will menu actually why automatic. 361 31:20.520 --> 31:21.800 It is not coming. 362 31:26.520 --> 31:27.480 Manufacturer. 363 31:27.480 --> 31:29.440 Let me take this column. 364 31:36.240 --> 31:38.120 Then what is our second requirement. 365 31:38.120 --> 31:41.240 We require the number of drugs associated with the losses. 366 31:42.160 --> 31:46.040 Manufacturer name number of drugs associated with the losses. 367 31:46.400 --> 31:56.040 So if you see over here this manufacturer Johnson and Johnson is coming 12345 and six six. 368 31:56.040 --> 31:57.320 Time it is coming right. 369 32:00.080 --> 32:02.440 This manufacturer is coming six times. 370 32:02.840 --> 32:06.640 So over here we need to do count of drug. 371 32:09.270 --> 32:12.510 As number of drugs. 372 32:13.390 --> 32:13.950 Right. 373 32:13.950 --> 32:19.110 And then we need to display total sales minus Cogs. 374 32:21.230 --> 32:23.630 And we need to do some of this one right. 375 32:24.150 --> 32:29.510 Some of this one total loss is absolute. 376 32:30.110 --> 32:33.470 But now if we do over here. 377 32:51.510 --> 32:58.030 Actually output the manufacturer name the number of drugs associated with the losses. 378 32:59.710 --> 33:00.270 Right. 379 33:00.470 --> 33:06.370 And if we are doing the manufacturer name and if we are doing the count right, count em them some. 380 33:06.850 --> 33:10.210 Then obviously we have to do group by manufacturer, right? 381 33:18.490 --> 33:22.090 The column should be available in the group by condition. 382 33:23.930 --> 33:29.130 So now if we do over here like this let me write group by. 383 33:36.170 --> 33:36.690 Yeah. 384 33:37.250 --> 33:39.570 Group by manufacturer one. 385 33:46.370 --> 33:47.050 Okay. 386 33:47.090 --> 33:50.090 Now over everywhere here. 387 33:50.090 --> 33:51.210 Minus sign is coming. 388 33:51.210 --> 33:51.610 Right. 389 33:51.650 --> 33:55.170 So for that we need to convert it into absolute value. 390 33:55.170 --> 33:55.570 Right. 391 33:58.810 --> 34:02.090 So minus sign will be gone now. 392 34:03.130 --> 34:04.880 Now again one more thing. 393 34:05.560 --> 34:10.320 Display the results sorted in descending order, with the highest losses displayed at the top. 394 34:10.840 --> 34:12.880 So this is the highest losses, right? 395 34:13.200 --> 34:15.800 So for that we have to do order by. 396 34:18.360 --> 34:19.560 Order by this column. 397 34:19.560 --> 34:20.760 And then descending. 398 34:23.760 --> 34:25.560 Now this will be our result set. 399 34:33.360 --> 34:33.960 Now. 400 34:34.080 --> 34:34.520 Got it. 401 34:34.520 --> 34:34.960 Right. 402 34:49.720 --> 34:50.360 Okay. 403 34:50.640 --> 34:53.400 Now let me jump to the another query. 404 35:07.900 --> 35:08.660 Ah, yes. 405 35:08.660 --> 35:08.900 Yes. 406 35:08.900 --> 35:09.340 Right. 407 35:19.540 --> 35:20.380 Ah, yes. 408 35:22.980 --> 35:23.460 Ah, yes. 409 35:23.460 --> 35:23.860 Yes. 410 35:24.140 --> 35:29.500 Because if you see over here, uh, this Johnson and Johnson coming six times, right. 411 35:30.100 --> 35:33.620 So all the six value we have to do addition, right. 412 35:34.980 --> 35:40.060 So for that purpose, we have done some of total sales minus books. 413 35:46.540 --> 35:46.900 Okay. 414 35:46.900 --> 35:49.100 Now let's jump to the next query. 415 35:50.700 --> 35:54.740 So health is trying again this is related to CVS. 416 35:54.740 --> 35:59.020 Health is trying to better understand is pharmacy sales. 417 36:04.650 --> 36:07.570 Pharmacists how well different products are selling. 418 36:07.970 --> 36:11.170 Each drug can only be produced by one manufacturer. 419 36:12.050 --> 36:18.970 Write a query to find the top three most profitable drugs sold and how much profit they made. 420 36:22.130 --> 36:24.810 Assume that there are no ties in the profits. 421 36:25.290 --> 36:28.730 Display the result from the highest to the lowest total profit. 422 36:32.810 --> 36:37.250 So over here we need to identify the top three most profitable drugs. 423 36:38.370 --> 36:38.890 Right. 424 36:39.250 --> 36:46.050 So if you see over here Coke stands for cost of goods sold, which is the direct cost associated with 425 36:46.050 --> 36:47.410 producing the drug. 426 36:48.130 --> 36:49.330 And now total profit. 427 36:49.330 --> 36:54.570 We have identified our total sales minus cost of goods sold. 428 36:55.370 --> 37:00.630 Now over here let me select the data from the table. 429 37:12.910 --> 37:13.670 Where. 430 37:14.550 --> 37:15.270 Total. 431 37:15.590 --> 37:19.230 Total sales minus. 432 37:21.470 --> 37:22.870 Cost of goods sold. 433 37:26.630 --> 37:29.470 Let me write it in the select statement. 434 37:36.990 --> 37:38.310 As total. 435 37:41.710 --> 37:42.470 Profit. 436 37:43.070 --> 37:44.830 And now I will do order by. 437 37:50.630 --> 37:51.990 Now what is our requirement. 438 37:51.990 --> 37:53.630 What are the columns we require. 439 37:53.870 --> 37:59.100 Top three most profitable drugs sold and how much profit they made. 440 37:59.700 --> 38:02.300 So here we require drug. 441 38:04.580 --> 38:07.020 Let me take manufacturer for now. 442 38:10.740 --> 38:16.780 Now, if you see over here this this three row row only are our requirement, right? 443 38:29.180 --> 38:33.620 Actually, if you see over here, we have already taken. 444 38:36.220 --> 38:39.380 Order the result set into the highest profit already. 445 38:39.380 --> 38:39.780 Right. 446 38:40.500 --> 38:43.340 So this top three only our requirement. 447 38:43.740 --> 38:47.220 No other rows are our requirement. 448 38:47.380 --> 38:47.900 Right. 449 38:48.980 --> 38:51.620 So if we do over here top three. 450 38:54.620 --> 38:55.820 Can it be possible. 451 38:55.820 --> 38:55.920 Well. 452 38:58.080 --> 38:59.520 Yeah, it is possible. 453 39:00.000 --> 39:02.560 And now, another way we can write it. 454 39:02.600 --> 39:09.840 Can anyone tell me now what is the another query we can write for the same. 455 39:22.960 --> 39:23.560 Yeah. 456 39:23.560 --> 39:31.160 I think, uh, limit is working in SQL or not in SQL server. 457 39:31.160 --> 39:34.280 I think it is not working. 458 39:36.360 --> 39:37.200 Limit. 459 39:44.800 --> 39:46.720 Yeah it is, I think not working. 460 39:49.680 --> 39:50.760 Let me do. 461 39:50.760 --> 39:51.320 Over here. 462 39:52.040 --> 39:52.560 Drug. 463 39:55.790 --> 39:56.390 Now. 464 39:56.750 --> 40:02.750 Anything else coming into your mind when we are trying to find the top three kind of thing? 465 40:12.110 --> 40:12.870 Uh, sorry. 466 40:12.910 --> 40:13.310 What? 467 40:21.550 --> 40:22.030 Yeah. 468 40:22.070 --> 40:24.070 Top three working in SQL server. 469 40:24.830 --> 40:27.590 Actually, we have already solved the problem, right? 470 40:27.590 --> 40:29.510 We have already solved the problem. 471 40:30.030 --> 40:32.630 But in SQL, you know speciality, right? 472 40:32.830 --> 40:36.750 One problem we can solve with multi multiple ways. 473 40:40.150 --> 40:40.630 Yeah. 474 40:40.670 --> 40:41.510 Perfect. 475 40:42.190 --> 40:45.390 Now let me do the same thing. 476 40:45.430 --> 40:49.710 Rank over order. 477 40:49.710 --> 40:50.510 By order. 478 40:50.510 --> 40:53.530 By what order by this same thing, right? 479 40:53.570 --> 40:55.090 What we have written over here. 480 40:56.210 --> 40:58.130 Total sales minus cogs. 481 41:01.530 --> 41:05.090 And then descending as arc. 482 41:05.810 --> 41:08.130 So now if you see over here. 483 41:10.690 --> 41:11.370 One minute. 484 41:11.370 --> 41:14.010 Let me take this same thing over here. 485 41:24.690 --> 41:28.690 So now if you see over here 123 these these three. 486 41:29.050 --> 41:30.250 Now what we can do. 487 41:33.970 --> 41:35.450 We require 123 only. 488 41:35.450 --> 41:35.850 Right. 489 41:36.250 --> 41:37.170 Not others. 490 41:37.210 --> 41:38.330 Now what we can do. 491 41:41.850 --> 41:42.970 So like this. 492 41:43.010 --> 41:45.690 Where arc less than equal to three. 493 41:45.690 --> 41:46.370 Like this. 494 41:46.410 --> 41:47.250 You are telling. 495 41:51.000 --> 41:52.080 Like this. 496 41:54.680 --> 41:57.280 Actually, we cannot refer arc over here. 497 42:04.320 --> 42:05.680 Okay, we have we can. 498 42:05.720 --> 42:10.800 We have to do with the city and any other option also. 499 42:16.720 --> 42:17.320 No, no. 500 42:18.840 --> 42:20.880 In this one only I am telling. 501 42:29.720 --> 42:30.160 Okay. 502 42:30.200 --> 42:30.720 Fetch. 503 42:30.760 --> 42:31.280 Where? 504 42:38.040 --> 42:40.560 Where I have to write it over here. 505 43:02.860 --> 43:03.740 Okay. 506 43:06.620 --> 43:10.580 Invalid usage of the option first in the fetch statement. 507 43:19.420 --> 43:19.660 And. 508 43:22.180 --> 43:22.940 Fetch. 509 43:27.860 --> 43:28.460 Okay. 510 43:29.380 --> 43:29.740 Okay. 511 43:29.780 --> 43:39.780 I think this I'm also not sure, but here what I am telling in place of city what any other thing also 512 43:39.780 --> 43:47.820 we can use which also work as a city only means not, uh, not a city. 513 43:47.860 --> 43:50.530 But that is the alternative of city. 514 43:50.850 --> 43:52.050 Everybody knows. 515 43:54.130 --> 43:54.490 Yeah. 516 43:54.530 --> 44:03.770 Perfect sub query select star from now what I have to give bracket right now I have to close the bracket 517 44:03.770 --> 44:07.210 and then where are okay. 518 44:07.690 --> 44:11.730 Here I can give alias in arc less than equal to three. 519 44:13.690 --> 44:14.970 Now let me try. 520 44:16.130 --> 44:17.530 Why it is giving error. 521 44:25.730 --> 44:29.290 Select star from from bracket. 522 44:33.610 --> 44:34.370 Is. 523 44:39.970 --> 44:41.410 Okay, now it will work. 524 44:45.970 --> 44:46.530 Right. 525 44:48.950 --> 44:53.390 Now it is working and our requirement is top three column only. 526 44:53.870 --> 44:55.710 Parquet is not our requirement. 527 44:55.710 --> 44:57.550 So that is also very simple. 528 44:58.870 --> 45:05.030 We can write then manufacturer we can write and then P we can write. 529 45:05.030 --> 45:07.270 We can ignore r r k. 530 45:11.830 --> 45:14.190 Now this both will give same result. 531 45:16.710 --> 45:17.990 And same also city. 532 45:17.990 --> 45:19.310 Also we can write. 533 45:19.670 --> 45:22.030 But I am showing you this another way. 534 45:28.950 --> 45:37.670 Okay this one actually what I have taken I have uh firstly this is simple right. 535 45:38.550 --> 45:39.630 Top three rank. 536 45:39.710 --> 45:47.940 It is giving based upon the total sales minus cogs and that two Also, highest profit will come at the 537 45:47.980 --> 45:52.940 first and second highest will come at the second and third highest will come at the third. 538 45:52.940 --> 45:53.580 Like this. 539 45:54.020 --> 45:57.660 Now our requirement is 1 to 3 only we require right. 540 45:58.260 --> 46:01.500 So what we can do this will consider this. 541 46:01.540 --> 46:04.180 We want to consider as a table. 542 46:04.180 --> 46:06.580 So how we can consider this as a table. 543 46:07.100 --> 46:13.860 We have to write something like a select star from select star from. 544 46:14.220 --> 46:18.060 And if we in from close there is always table right. 545 46:18.620 --> 46:27.140 So this entire query we have consider as a table and how this is we have given as a alias T. 546 46:27.900 --> 46:32.780 So now this T is actually this T is considered as a table. 547 46:33.340 --> 46:37.980 And this entire all the result set from this query. 548 46:38.620 --> 46:42.660 This all the four rows are now considered as a table t. 549 46:43.560 --> 46:44.920 But now how? 550 46:45.720 --> 46:48.240 But now this where clause. 551 46:48.240 --> 46:52.800 This column we can use outside of this bracket. 552 46:52.800 --> 46:55.920 Right outside of this bracket to limit. 553 46:56.360 --> 47:00.160 So for that I have written where arc less than equal to three. 554 47:00.920 --> 47:06.440 Right now our requirement is simple. 555 47:06.440 --> 47:11.320 We require drug manufacturer and then P. 556 47:14.960 --> 47:25.160 So all the columns which is coming into this one T we can refer it over here all the columns. 557 47:26.280 --> 47:33.000 So the can now can anyone tell me what is this known as this concept. 558 47:33.000 --> 47:34.880 What is this concept known as. 559 47:38.360 --> 47:39.720 In SQL server. 560 47:44.350 --> 47:45.510 And in Oracle. 561 47:55.270 --> 47:56.070 Perfect. 562 47:59.230 --> 48:01.710 In SQL server it is known as Derived Table. 563 48:01.750 --> 48:02.590 And in Oracle. 564 48:04.950 --> 48:06.390 Does anyone has idea? 565 48:10.270 --> 48:12.430 Perfect inline view. 566 48:15.790 --> 48:20.110 It means select query in the From clause of the Select statement right. 567 48:20.590 --> 48:22.510 That is known as inline view. 568 48:26.590 --> 48:28.750 Now let's see another question. 569 48:31.430 --> 48:33.750 We have already seen this one right now. 570 48:33.790 --> 48:36.790 Eight absolute. 571 48:46.850 --> 48:47.290 Okay. 572 48:47.290 --> 48:49.410 Now let me jump to the ninth query. 573 48:52.410 --> 48:53.370 That is clear, right? 574 48:53.370 --> 48:54.490 This one is clear, right? 575 48:54.530 --> 48:55.090 Eight one. 576 48:55.930 --> 48:56.450 This is. 577 48:59.330 --> 49:00.290 Very simple one. 578 49:00.290 --> 49:02.450 Top three most profitable drugs. 579 49:03.930 --> 49:08.250 Now let's jump to the ninth query health. 580 49:08.290 --> 49:15.930 CVS health wants to gain a clear understanding of its pharmacy sales and the performance of various 581 49:15.930 --> 49:16.690 products. 582 49:17.570 --> 49:22.530 Now write a query to calculate the total drug sales for each manufacturer. 583 49:23.690 --> 49:26.450 Total drug sales for each manufacturer. 584 49:26.930 --> 49:33.490 Round the answer to the nearest million and report your results in descending order of total sales. 585 49:33.650 --> 49:39.080 In case of any duplicates, sort them alphabetically by manufacturer name. 586 49:41.040 --> 49:47.600 Since this data will be displayed on a dashboard viewed by business stakeholders, please format your 587 49:47.600 --> 49:50.080 results as a $36 million. 588 49:50.600 --> 49:51.520 Like this. 589 49:54.040 --> 49:55.800 $36 million. 590 49:57.040 --> 49:58.880 So now understand one thing. 591 49:59.400 --> 50:03.800 Whenever we find each manufacturer kind of thing. 592 50:07.080 --> 50:07.640 You know. 593 50:12.000 --> 50:19.120 Whenever we find kind of each product, each product category, each product subcategory, each manufacturer 594 50:19.640 --> 50:22.560 at that time, either we have to use group by clause. 595 50:25.280 --> 50:26.800 Or we have to use. 596 50:29.680 --> 50:31.880 Ranking functions. 597 50:34.040 --> 50:35.560 This is a simple thing right? 598 50:35.800 --> 50:37.260 Each Manufacturer. 599 50:37.300 --> 50:42.420 Each means definitely we have to use group by or ranking functions. 600 50:43.820 --> 50:48.580 Right now I have created the table and inserted the data into the same. 601 50:50.940 --> 50:58.380 Now we have to identify total sales for each manufacturer, right. 602 51:02.820 --> 51:06.700 But now how we can. 603 51:09.660 --> 51:12.420 We have to display the result like this one. 604 51:12.460 --> 51:12.700 One. 605 51:12.740 --> 51:19.380 $3,000,000.10. 606 51:19.540 --> 51:23.500 $20 million. 607 51:23.980 --> 51:24.820 Like this. 608 51:25.380 --> 51:28.340 We can't display a result like this, right? 609 51:28.940 --> 51:30.420 Because we have to display. 610 51:32.420 --> 51:32.860 Yes. 611 51:37.090 --> 51:37.530 Okay. 612 51:37.570 --> 51:43.490 We can divide the same thing by, uh, ten lakhs. 613 51:43.490 --> 51:48.450 Okay, now let me try to do. 614 51:48.690 --> 51:49.250 Okay. 615 51:49.970 --> 51:50.250 Now. 616 51:50.250 --> 51:52.570 113 3233. 617 51:53.930 --> 51:55.370 It is giving, right? 618 51:55.370 --> 51:57.170 But now what is our requirement? 619 51:58.810 --> 52:01.410 We want only one one $3 million, right? 620 52:03.690 --> 52:04.410 No no no. 621 52:04.450 --> 52:04.970 Over here. 622 52:05.010 --> 52:06.410 Actually, it is not required. 623 52:07.730 --> 52:09.810 When when absolute is required. 624 52:10.130 --> 52:12.370 When data is coming in the minus. 625 52:12.370 --> 52:12.810 Right. 626 52:13.450 --> 52:16.370 But here everything is coming in the positive sign. 627 52:17.210 --> 52:18.890 So absolute is not required. 628 52:18.930 --> 52:21.930 Now we want 113 only 32. 629 52:21.930 --> 52:22.930 Only 33. 630 52:22.930 --> 52:24.570 Only 69 only. 631 52:25.170 --> 52:26.410 So what we can do? 632 52:30.170 --> 52:30.770 Sorry. 633 52:33.490 --> 52:34.750 I am I'm not getting what? 634 52:38.950 --> 52:39.550 String. 635 52:42.230 --> 52:44.350 Can you write in the message box? 636 52:49.870 --> 52:50.630 Okay. 637 52:50.670 --> 52:53.910 One minute in string function. 638 52:55.390 --> 52:57.150 I think it is in Oracle only. 639 52:57.190 --> 52:59.030 Here, here it is not there. 640 53:03.230 --> 53:03.590 Okay. 641 53:03.630 --> 53:04.830 If we do round. 642 53:04.830 --> 53:05.310 Right. 643 53:05.310 --> 53:10.870 If we do round, then 113 become 114I think write 114. 644 53:18.150 --> 53:21.230 But we want one one $3 million like that. 645 53:28.910 --> 53:29.270 Any. 646 53:29.270 --> 53:29.950 Any idea? 647 53:29.990 --> 53:30.910 Any other idea. 648 53:34.140 --> 53:38.820 We want to eliminate all the digits after point, right? 649 53:48.460 --> 53:49.020 Anyone? 650 53:49.020 --> 53:49.300 Any. 651 53:49.300 --> 53:50.020 Any idea? 652 53:58.180 --> 53:59.060 Round. 653 53:59.100 --> 54:04.260 But if we do round, then one, one, three will will become 114. 654 54:04.300 --> 54:05.340 Let me do it. 655 54:05.940 --> 54:07.340 Round of this. 656 54:09.380 --> 54:10.060 Comma. 657 54:14.260 --> 54:15.260 Comma zero. 658 54:15.500 --> 54:16.500 So 114. 659 54:18.300 --> 54:19.860 Still it is giving wrong. 660 54:21.340 --> 54:26.380 We don't like one like .00000 that we don't require. 661 54:26.420 --> 54:28.260 And we require 113 only. 662 54:28.980 --> 54:29.500 Now. 663 54:32.320 --> 54:32.840 Okay. 664 54:32.840 --> 54:41.160 Now let me do we have if you have familiar with SQL server then cast and convert function are there 665 54:41.160 --> 54:43.440 right cast and convert. 666 54:43.440 --> 54:46.720 So here we need to do cast s. 667 54:49.080 --> 54:55.320 S decimal decimal five comma zero. 668 54:56.800 --> 54:59.480 Now let me do execute. 669 55:02.240 --> 55:02.720 Okay. 670 55:02.760 --> 55:05.520 Why it is giving rounding of. 671 55:14.920 --> 55:19.920 It is rounding to I don't want to do rounding. 672 55:23.120 --> 55:25.880 Some of this this this okay. 673 55:26.840 --> 55:28.520 Let me try it over here. 674 55:28.670 --> 55:29.830 Cost of this? 675 55:36.510 --> 55:38.350 Okay, I think over here is. 676 55:43.910 --> 55:44.510 Over here. 677 55:44.510 --> 55:45.630 It is not required. 678 55:54.470 --> 55:55.110 One minute. 679 55:55.110 --> 55:56.390 What I have done. 680 56:16.710 --> 56:18.510 Still it is giving 114. 681 56:18.550 --> 56:21.390 If we require 113, then what we can do. 682 56:34.890 --> 56:35.570 Sorry. 683 56:45.690 --> 56:47.010 Round of zero. 684 57:01.690 --> 57:03.530 113.77. 685 57:04.010 --> 57:05.490 So let me do. 686 57:07.730 --> 57:08.530 Round. 687 57:12.610 --> 57:15.010 Zero is working or not? 688 57:19.010 --> 57:23.450 It is rounding to 114. 689 57:31.680 --> 57:32.040 Okay. 690 57:32.080 --> 57:35.440 One thing we can do is, uh. 691 57:47.240 --> 57:47.920 Truncate. 692 57:50.920 --> 57:52.880 I think truncate. 693 57:56.560 --> 57:57.880 Then what I have to do. 694 58:03.040 --> 58:05.520 But I think this is function is not. 695 58:08.400 --> 58:09.600 Is there anything wrong? 696 58:09.640 --> 58:10.520 Incorrect syntax. 697 58:10.520 --> 58:11.520 Near truncate. 698 58:13.840 --> 58:16.360 Truncate is not there in SQL server, I think. 699 58:23.080 --> 58:23.680 For. 700 58:23.680 --> 58:23.740 Floor. 701 58:30.020 --> 58:30.700 Okay. 702 58:32.620 --> 58:33.340 Okay. 703 58:33.700 --> 58:34.620 Perfect. 704 58:42.300 --> 58:42.700 Yeah. 705 58:42.780 --> 58:43.220 Floor. 706 58:44.300 --> 58:44.740 Ceiling. 707 58:44.780 --> 58:45.260 Ceiling. 708 58:48.540 --> 58:51.100 It is not working in this SQL server. 709 59:12.420 --> 59:13.020 Okay. 710 59:13.140 --> 59:13.940 Select. 711 59:15.380 --> 59:16.020 Truncate. 712 59:31.610 --> 59:31.970 And. 713 59:38.570 --> 59:42.290 I think it is there in Oracle or any other database. 714 59:46.490 --> 59:46.890 I think. 715 59:46.930 --> 59:47.610 Flow flow. 716 59:48.490 --> 59:52.570 Flow flow is the correct one for sealing, right? 717 59:53.090 --> 59:54.610 Two functions are coming. 718 59:56.970 --> 59:59.490 If we use sealing then it will become 114. 719 59:59.530 --> 59:59.890 Right. 720 01:00:03,650 --> 01:00:04,810 What is the spelling? 721 01:00:05,210 --> 01:00:06,850 It will become now 114. 722 01:00:09,090 --> 01:00:09,850 Perfect. 723 01:00:10,210 --> 01:00:12,330 So flow here we can use flow. 724 01:00:14,690 --> 01:00:15,930 Now what we can do. 725 01:00:18,250 --> 01:00:19,090 Now, now. 726 01:00:19,090 --> 01:00:19,650 Simple thing. 727 01:00:19,650 --> 01:00:20,050 Right. 728 01:00:20,710 --> 01:00:24,870 We have to do concrete dollar. 729 01:00:27,150 --> 01:00:29,070 And then again. 730 01:00:31,910 --> 01:00:32,470 Million. 731 01:00:32,510 --> 01:00:33,030 Million. 732 01:00:33,030 --> 01:00:37,550 We can write or m we can write whether it is working or not. 733 01:00:40,110 --> 01:00:40,430 Why? 734 01:00:40,430 --> 01:00:41,590 It is giving error. 735 01:00:58,030 --> 01:00:58,590 Okay. 736 01:00:58,630 --> 01:00:59,430 Concrete. 737 01:01:04,390 --> 01:01:06,790 Dollar comma. 738 01:01:07,750 --> 01:01:09,430 Then we have to close this bracket. 739 01:01:10,590 --> 01:01:11,590 Now let me try. 740 01:01:13,870 --> 01:01:14,390 Okay. 741 01:01:14,750 --> 01:01:16,510 Now again we have to use. 742 01:01:19,780 --> 01:01:20,340 And then. 743 01:01:26,460 --> 01:01:27,900 This is our requirement right. 744 01:01:28,460 --> 01:01:30,260 And we can give as. 745 01:01:35,660 --> 01:01:37,540 Total sales in million. 746 01:01:37,540 --> 01:01:38,140 We can give. 747 01:01:44,940 --> 01:01:46,500 One one $3 million. 748 01:01:46,500 --> 01:01:46,900 Right. 749 01:01:47,620 --> 01:01:48,860 This is our requirement. 750 01:01:57,420 --> 01:01:59,740 Now final query for today. 751 01:02:00,180 --> 01:02:04,780 So we will take five queries per session. 752 01:02:06,980 --> 01:02:12,220 CBS health is trying to better understand its pharmacy sales and how well different drugs are selling. 753 01:02:12,940 --> 01:02:19,920 Write a query to find the top three drugs sold in terms of unique If units manufacture sold units sold 754 01:02:20,480 --> 01:02:22,080 for each manufacturer. 755 01:02:25,480 --> 01:02:31,800 So now again for each manufacturer and top two drugs sold in terms of units sold. 756 01:02:33,120 --> 01:02:35,920 So here we need to use ranking function. 757 01:02:35,920 --> 01:02:36,320 Right. 758 01:02:37,560 --> 01:02:42,080 Or in any row number or ranking function anything we can use. 759 01:02:42,680 --> 01:02:47,320 So if you see over here let me execute this query. 760 01:02:50,080 --> 01:02:53,800 Now over here if I take this data into the Excel file. 761 01:03:04,680 --> 01:03:07,520 So for each manufacturer we have mentioned right. 762 01:03:08,000 --> 01:03:16,950 So I already told whenever for each manufacturer it is written we have to either use group by or ranking 763 01:03:16,950 --> 01:03:19,030 functions any of the ranking function. 764 01:03:19,590 --> 01:03:21,950 So in place of in terms of units sold. 765 01:03:22,310 --> 01:03:27,830 So I have already unit sold descending I have already taken. 766 01:03:27,830 --> 01:03:28,270 Right. 767 01:03:29,470 --> 01:03:31,990 So for this this is our requirement. 768 01:03:35,110 --> 01:03:38,710 Now a top two for each manufacturer. 769 01:03:39,230 --> 01:03:42,030 So here again manufacturer will change right. 770 01:03:42,430 --> 01:03:45,790 So again these three will become our requirement. 771 01:03:48,110 --> 01:03:51,550 Now again manufacturer will change. 772 01:03:54,790 --> 01:03:57,070 So these three will become our requirement. 773 01:03:57,070 --> 01:03:57,470 Why. 774 01:03:57,990 --> 01:04:00,430 Because these are the top two right. 775 01:04:00,470 --> 01:04:05,190 For this manufacturer these are the top two for this manufacturer. 776 01:04:06,030 --> 01:04:08,510 And these are the top two for this manufacturer. 777 01:04:08,550 --> 01:04:12,750 Likewise we require top two for each manufacturer. 778 01:04:12,790 --> 01:04:13,190 Right. 779 01:04:13,990 --> 01:04:22,290 So for that simple thing, partition by wave to do manufacturer and owner by units sold descending, 780 01:04:22,290 --> 01:04:23,330 we have to do right. 781 01:04:23,730 --> 01:04:26,650 And here we can do use any of the function. 782 01:04:26,650 --> 01:04:29,970 We can use rank. 783 01:04:33,170 --> 01:04:34,250 Or we can use. 784 01:04:39,090 --> 01:04:42,530 Dense rank anything we can use. 785 01:04:42,530 --> 01:04:50,890 And here also I have used the same query derived table where r k less than equal to two. 786 01:04:51,370 --> 01:04:53,770 So it will display the data. 787 01:04:55,130 --> 01:05:01,010 If you see for this abbreviated version 222331 is given. 788 01:05:01,490 --> 01:05:05,490 Then again 189925 is given 189925 is given. 789 01:05:06,330 --> 01:05:12,610 Then this one is changing to Astra's AstraZeneca. 790 01:05:12,760 --> 01:05:24,760 So again, 289 1 to 1 is given in 217652 is given, so you got it right for each manufacturer, top 791 01:05:24,800 --> 01:05:34,360 products sold based upon units sold which is highest units sold right now. 792 01:05:34,400 --> 01:05:35,640 Can anyone tell me. 793 01:05:38,400 --> 01:05:43,200 Top two lowest unit manufacturer buys. 794 01:05:43,200 --> 01:05:44,120 Units sold. 795 01:05:45,440 --> 01:05:48,600 So for that, what change I have to do in this query. 796 01:06:00,280 --> 01:06:04,120 The top two lowest units sold for each manufacturer. 797 01:06:05,240 --> 01:06:11,340 So for that result, set what changes I have to do in the query. 798 01:06:20,180 --> 01:06:21,220 Perfect. 799 01:06:21,260 --> 01:06:27,740 Here I have to remove this only because by default it is ascending. 800 01:06:28,140 --> 01:06:28,900 Ascending. 801 01:06:29,100 --> 01:06:30,820 By default it is ascending, right? 802 01:06:32,260 --> 01:06:42,460 So if I do this one then for abbreviation, if you see over here uh in the Excel file then these are 803 01:06:42,460 --> 01:06:43,500 the result now. 804 01:06:48,500 --> 01:06:54,900 (975) 209-7520 and 6969720. 805 01:06:54,900 --> 01:06:55,260 Right. 806 01:06:57,380 --> 01:07:00,620 And this same thing we can write into the city also. 807 01:07:03,500 --> 01:07:05,220 That I have written in the below. 808 01:07:09,770 --> 01:07:15,610 Whenever you are using derived table at the same thing you can right city also. 809 01:07:19,410 --> 01:07:20,810 Now it is clear, right? 810 01:07:26,290 --> 01:07:29,530 Okay, so from this one we have. 811 01:07:31,650 --> 01:07:33,250 To write the ten queries. 812 01:07:34,370 --> 01:07:38,050 Last Saturday, last last Sunday we have written five queries. 813 01:07:38,930 --> 01:07:41,450 Today also we have written five queries. 814 01:07:45,050 --> 01:07:47,810 We will write around 100 queries guys. 815 01:07:51,970 --> 01:07:54,570 And let me know if you have any question. 816 01:08:05,370 --> 01:08:06,850 Yeah, definitely. 817 01:08:06,850 --> 01:08:07,950 You will get it. 818 01:08:07,990 --> 01:08:11,910 I will put it into the Google Drive. 819 01:08:12,270 --> 01:08:13,110 One minute. 820 01:08:21,390 --> 01:08:22,430 One minute. 821 01:08:38,750 --> 01:08:39,030 Yeah. 822 01:08:39,030 --> 01:08:41,430 Actually, from now onwards, I will put. 823 01:08:42,150 --> 01:08:45,670 Actually, I have already given this link in the last lecture. 824 01:08:45,990 --> 01:08:47,230 Let me know if you are. 825 01:08:49,070 --> 01:08:52,990 But I will not put the solution over here. 826 01:08:54,830 --> 01:08:58,550 I will put the DDL in insert statement only. 827 01:09:13,540 --> 01:09:14,300 But answer. 828 01:09:14,300 --> 01:09:15,620 You can answer. 829 01:09:15,620 --> 01:09:17,180 You can watch the video, right? 830 01:09:22,220 --> 01:09:25,140 But that only I will get benefit, right? 831 01:09:25,500 --> 01:09:30,140 Uh, if you if you watch video, then only I will get benefit. 832 01:09:30,140 --> 01:09:30,660 Right? 833 01:09:33,820 --> 01:09:34,900 Because everything. 834 01:09:35,220 --> 01:09:38,580 Because all this thing I am doing for my YouTube only. 835 01:09:38,580 --> 01:09:39,020 Right. 836 01:09:40,100 --> 01:09:42,580 All this thing I am doing for my YouTube only. 837 01:09:42,580 --> 01:09:44,780 I am investing time. 838 01:09:44,820 --> 01:09:46,500 I am taking time. 839 01:09:49,660 --> 01:09:50,700 No, no. 840 01:09:51,020 --> 01:09:53,740 Yeah, I will give you query script. 841 01:09:54,340 --> 01:09:54,620 No. 842 01:09:54,620 --> 01:09:55,180 No answer. 843 01:09:55,180 --> 01:09:57,300 I can't give you for answer. 844 01:09:57,300 --> 01:10:00,540 You can watch video but you can fast forward also, right? 845 01:10:00,580 --> 01:10:01,780 That is no problem. 846 01:10:02,260 --> 01:10:05,080 You can fast forward the video right Direct. 847 01:10:05,120 --> 01:10:06,640 You can go to the solution. 848 01:10:10,800 --> 01:10:13,840 No no no no no no. 849 01:10:13,880 --> 01:10:17,280 If I give you solution then you will never watch the video, right? 850 01:10:18,440 --> 01:10:19,440 No, no you will. 851 01:10:19,680 --> 01:10:22,200 I think I this is my perception. 852 01:10:24,120 --> 01:10:24,600 You. 853 01:10:24,920 --> 01:10:30,040 Because why you go to the YouTube and waste your time. 854 01:10:31,360 --> 01:10:35,440 You directly go to the solution and copy paste the solution. 855 01:10:35,440 --> 01:10:35,840 Right? 856 01:10:38,760 --> 01:10:39,840 Now let me know. 857 01:10:39,880 --> 01:10:42,640 Are you able to access this drive or not? 858 01:10:49,000 --> 01:10:52,520 Are you able to download those script script? 859 01:10:54,240 --> 01:10:54,960 If you. 860 01:10:54,960 --> 01:10:57,760 Because then only you will.