1 00:00.880 --> 00:01.880 Hello everyone. 2 00:03.000 --> 00:09.840 So let's have today we will, uh, see the S project in SQL. 3 00:10.080 --> 00:12.720 And this is our second project in SQL. 4 00:14.000 --> 00:17.120 So actually we have given few tables. 5 00:17.120 --> 00:21.600 I will directly show you the tables in some time. 6 00:22.280 --> 00:30.000 And from that tables we need to analyze this, uh, all these ten queries and we need to answer these 7 00:30.000 --> 00:32.520 ten queries solutions. 8 00:34.160 --> 00:38.400 So over here, uh, this is our first table. 9 00:39.200 --> 00:42.760 Let me show you directly the table data. 10 00:46.400 --> 00:48.120 Pisa Runner is our schema name. 11 00:48.120 --> 00:50.360 And then customer orders is the table name. 12 00:50.720 --> 00:52.240 So let me show you the data. 13 00:52.240 --> 00:58.640 So if you see over here the data are in inconsistent type right. 14 00:59.040 --> 01:01.450 Customer ID is uh perfect. 15 01:01.450 --> 01:02.010 No problem. 16 01:02.010 --> 01:05.210 Order ID is fine, pizza ID is fine. 17 01:05.530 --> 01:07.170 But this exclusions. 18 01:07.650 --> 01:11.970 If you see over here in exclusions, then some values are empty. 19 01:12.290 --> 01:23.290 Then some value is new in small capital which is also in uh wrong type and same as for this uh, extras 20 01:23.330 --> 01:29.210 as well in that uh there is one value which is Nan which is wrong one. 21 01:29.210 --> 01:31.050 And this empty values are wrong. 22 01:31.410 --> 01:36.690 And then in small letter a new double L is also wrong. 23 01:36.690 --> 01:37.130 Right. 24 01:37.730 --> 01:39.250 So first of all we need to. 25 01:41.850 --> 01:48.370 We need to do the uh data correction in this particular table. 26 01:48.770 --> 01:54.490 So for that purpose we will insert this data into some kind of, uh, temporary table. 27 01:54.850 --> 02:01.740 And at the time of, uh, insertion only, we will do the data manipulation to correct this data. 28 02:02.780 --> 02:07.420 So that is also one kind of part of the data cleaning process. 29 02:07.940 --> 02:09.780 We can say this one as a. 30 02:12.980 --> 02:14.700 Data cleaning process. 31 02:15.740 --> 02:19.540 And this happens many times in data migration projects. 32 02:19.780 --> 02:25.460 So if you see over here the customer order table has inconsistent data types. 33 02:26.460 --> 02:31.140 This table we must first clean the data before answering any questions. 34 02:31.140 --> 02:37.300 So all these questions or some of these questions are based on this particular table. 35 02:37.980 --> 02:45.580 Now the exclusions and extra columns contains values that are either null that I have shown shown you 36 02:45.580 --> 02:46.380 already right. 37 02:46.780 --> 02:51.100 That are either null or a new data type, or empty right. 38 02:51.140 --> 02:53.140 These are the empty values over here. 39 02:54.540 --> 03:01.830 And we will create a temporary table where all forms of null will be transformed to a new Doubleline 40 03:01.830 --> 03:03.590 capital little data type. 41 03:04.270 --> 03:09.110 So for that purpose, we need to create one temporary table. 42 03:10.750 --> 03:15.630 Now how we can create this temporary table. 43 03:15.950 --> 03:17.830 So this is the original table. 44 03:17.830 --> 03:18.710 This is the same. 45 03:19.270 --> 03:21.790 What you can see it present in your screen. 46 03:22.790 --> 03:25.830 So I have taken the copy over here. 47 03:25.870 --> 03:33.550 Now we need to create the same table structure but with data correction. 48 03:33.790 --> 03:38.550 So for that purpose what I have done select Select star into. 49 03:38.550 --> 03:41.510 This is our temporary table. 50 03:41.510 --> 03:43.790 And we will further use this table only. 51 03:48.110 --> 03:48.750 Right. 52 03:48.950 --> 03:50.670 We will further use this table only. 53 03:51.190 --> 03:51.670 So. 54 03:56.550 --> 03:57.430 So we will. 55 03:59.950 --> 04:03.120 Do order id customer ID and ID. 56 04:03.720 --> 04:09.200 These three columns, if you see over here are has no issue right now. 57 04:09.880 --> 04:14.240 Exclusions is one of the column in which we have to do the data correction. 58 04:14.560 --> 04:16.680 So for that purpose what I have done. 59 04:17.760 --> 04:26.760 Wherever we find empty or new double L we need to convert it into null right. 60 04:28.760 --> 04:29.640 Or none. 61 04:29.680 --> 04:31.080 Nine is not over here. 62 04:31.080 --> 04:31.920 Exclusions. 63 04:31.960 --> 04:33.000 None is not over here. 64 04:33.000 --> 04:33.400 Right? 65 04:33.760 --> 04:37.640 But still I have written so case when. 66 04:38.600 --> 04:43.760 Case when in this particular column exclusions column whenever it is empty. 67 04:43.800 --> 04:52.760 This one is known as empty or exclusions equal to null means this one or exclusion is equal to none. 68 04:53.760 --> 05:02.140 So whenever either of these three values are there in the exclusion column, for all these three value, 69 05:02.140 --> 05:04.020 we are converting into the null. 70 05:04.260 --> 05:05.180 So then null. 71 05:05.420 --> 05:05.860 Else. 72 05:06.420 --> 05:10.900 Else whatever is there in the exclusion column which will be present. 73 05:11.700 --> 05:12.260 Right. 74 05:13.180 --> 05:14.860 So that is a simple one. 75 05:15.500 --> 05:18.180 Now what we have done for the extras column. 76 05:18.380 --> 05:23.380 So for the extras column also we have done the same thing because over here also. 77 05:23.420 --> 05:27.100 Empty non-null values are there right. 78 05:29.420 --> 05:32.020 So for that also we have done the same thing. 79 05:32.700 --> 05:38.820 Case when x is equal to null or extras like null or extras is equal to null, then null. 80 05:38.860 --> 05:42.100 Else extras and then order type. 81 05:42.500 --> 05:43.700 Everything is perfect. 82 05:43.740 --> 05:45.620 No need to do any conversion. 83 05:46.380 --> 05:53.540 So I have retrieved entire data from this particular table and inserted into select star into. 84 05:53.580 --> 05:57.780 Means it will create and insert the data with the same data type. 85 05:58.140 --> 06:01.740 Whatever are there in the From clause of the Select statement. 86 06:01.740 --> 06:01.830 Mint. 87 06:03.230 --> 06:04.670 So now this is our. 88 06:06.910 --> 06:07.950 Corrected table. 89 06:07.950 --> 06:11.070 And further we will use this table only. 90 06:11.510 --> 06:11.950 Right. 91 06:12.670 --> 06:14.790 Now let's see the another table. 92 06:16.630 --> 06:20.070 So this is the clean customer orders okay. 93 06:20.470 --> 06:22.830 This is the above this table only. 94 06:23.310 --> 06:26.070 Now we have one more table is there. 95 06:26.750 --> 06:29.750 So after cleaning, this table will look like this. 96 06:31.390 --> 06:34.590 Let me show you the data of this clean customer table. 97 06:35.310 --> 06:35.950 Table. 98 06:37.350 --> 06:39.270 So this is the perfect table, right? 99 06:40.390 --> 06:43.190 So I have taken this data also into the screen. 100 06:43.750 --> 06:45.150 And you can see over here. 101 06:46.750 --> 06:49.230 Now again clean data. 102 06:49.230 --> 06:54.590 So the runner order table is now another table is the runner order table is there. 103 06:55.310 --> 06:59.950 So in that table also data is inconsistent format. 104 07:00.590 --> 07:05.440 So for that also we need to clean the data with inconsistent data types. 105 07:05.440 --> 07:10.120 We need to clean that, and we must first clean the data before answering any questions. 106 07:10.600 --> 07:13.520 The distance and duration columns have tags and numbers. 107 07:14.120 --> 07:17.560 We will remove the tags values and convert to numeric values. 108 07:18.560 --> 07:20.560 I will show you in some time. 109 07:20.600 --> 07:22.320 Let me show you over here. 110 07:25.040 --> 07:31.600 So you see over here distance and duration has numbers and tags as well. 111 07:32.080 --> 07:37.800 But we will remove the tags values and convert to numeric values for both of these columns. 112 07:37.800 --> 07:44.880 It means 20km will become 2013, 4.5km will become 13.4. 113 07:45.440 --> 07:51.880 Like that in 25 minutes will become 25 and 15 minutes will become 15. 114 07:52.240 --> 07:56.240 And this null value will be converted to a new double L. 115 07:56.400 --> 07:56.880 Right. 116 07:59.040 --> 08:03.050 Now again this table we need to do the data cleaning. 117 08:03.490 --> 08:11.050 So for that purpose again, I have inserted the data into some temporary table and selecting data from 118 08:11.250 --> 08:14.130 that particular table. 119 08:14.130 --> 08:21.730 So now here if you see order ID and run ID we not do not need to do anything right now. 120 08:22.090 --> 08:25.010 Pickup time, what we have done in the pickup time. 121 08:25.010 --> 08:25.770 So pickup time. 122 08:25.770 --> 08:29.610 If you see over here there are two null values are there. 123 08:29.650 --> 08:30.090 Right. 124 08:31.170 --> 08:32.930 There are two null values are there. 125 08:33.530 --> 08:40.130 So whenever pickup time like null then new pickup time. 126 08:43.850 --> 08:52.890 Because all other data is correct right and end as date time two because pickup time is if you see hours 127 08:52.890 --> 08:54.650 minutes and seconds are also there. 128 08:55.010 --> 09:05.460 So we need to cast it in, cast it into the date time two data type right Now let's see what we have 129 09:05.460 --> 09:06.700 done for the distance. 130 09:07.860 --> 09:15.100 So for the distance, let me take this data like this and remove this one. 131 09:17.260 --> 09:19.580 And take data like this. 132 09:23.620 --> 09:24.100 Okay. 133 09:24.500 --> 09:25.940 Now for the distance. 134 09:25.940 --> 09:34.940 What we have done case when distance is equal to null, then we need to convert it into a new double 135 09:34.980 --> 09:35.380 right. 136 09:35.740 --> 09:37.500 In else distance. 137 09:39.500 --> 09:44.940 Now we have to replace with null value came with empty value. 138 09:45.300 --> 09:48.260 So for that purpose replace distance. 139 09:48.260 --> 09:54.140 Whenever you find came in the distance it will replace it to the empty value right. 140 09:55.020 --> 09:59.740 And end as decimal comma five comma comma one. 141 09:59.740 --> 10:05.750 Because we need after point one decimal digit, right? 142 10:05.990 --> 10:10.550 So for that purpose here, five comma, 1 or 2 comma 1 or 3 comma one. 143 10:10.550 --> 10:11.990 Anything you can write. 144 10:14.230 --> 10:15.310 As per the data. 145 10:15.310 --> 10:18.190 And then what we have done again again we have. 146 10:26.550 --> 10:29.270 Again we have entire thing we have replaced. 147 10:29.750 --> 10:41.750 And in that whenever we find .0.0 with the empty space, because of this decimal value, we are getting 148 10:42.150 --> 10:49.430 after, uh, any integer digit after we are getting point 23.0. 149 10:49.430 --> 10:51.310 So point zero we do not require. 150 10:51.310 --> 10:51.710 Right. 151 10:51.990 --> 10:56.350 So for that purpose from this entire string, again I have done the replace over here. 152 10:56.710 --> 11:01.390 And that will this replace will work for this entire string. 153 11:02.190 --> 11:02.710 Right. 154 11:03.440 --> 11:06.120 For this entire one. 155 11:07.960 --> 11:10.480 So that will column will become the distance column. 156 11:10.840 --> 11:12.120 Now for the duration. 157 11:12.120 --> 11:18.800 Also wherever null is there I have replaced it with the null and then replace three times I have used 158 11:18.800 --> 11:25.040 replace because in the over here minutes are there means are there and minutes are there. 159 11:25.360 --> 11:30.360 So all these three values I have to replace it with the empty string right. 160 11:30.960 --> 11:34.720 Because I don't want tax value in this duration column. 161 11:35.280 --> 11:41.240 So three times I have used the replace function and convert it into the empty string. 162 11:41.240 --> 11:46.520 So now in the duration column it will be integer value only. 163 11:46.520 --> 11:48.320 Only numeric values will be there. 164 11:50.880 --> 11:51.400 Right. 165 11:53.280 --> 11:59.200 Here no need to do any kind of cast casting or decimal point because everything is there in the integer. 166 11:59.840 --> 12:01.320 There is no after point. 167 12:01.320 --> 12:02.160 There is no value. 168 12:02.160 --> 12:06.050 Is there in any of the rows, write in the duration column. 169 12:06.810 --> 12:11.250 Now what we have done in the in the cancellation column. 170 12:15.970 --> 12:23.930 So in the cancellation column, when cancellation like null or none or like empty. 171 12:23.970 --> 12:25.090 So it is empty. 172 12:25.850 --> 12:26.650 This is null. 173 12:26.890 --> 12:29.490 So we need to convert it into a new Dublin. 174 12:29.490 --> 12:32.010 Rest will be same as it is. 175 12:32.090 --> 12:38.730 So for that else cancellation end as cancellation and all these three value will be converted to a new 176 12:38.730 --> 12:45.930 double L right to be a consistent data according to the data type in the table. 177 12:48.890 --> 12:53.930 So this will become our clean table clean runner orders. 178 12:56.890 --> 12:57.850 Whenever. 179 12:57.850 --> 13:00.050 Let me show you the table data. 180 13:03.870 --> 13:06.310 So perfect right now. 181 13:06.630 --> 13:09.790 In this distance column, only numeric value will be there. 182 13:10.110 --> 13:10.910 Duration column. 183 13:10.910 --> 13:12.630 Also numeric value will be there. 184 13:13.230 --> 13:23.110 There is no n in small letter or empty string or Nan values are nowhere right. 185 13:23.150 --> 13:24.470 So this is a clean table. 186 13:24.510 --> 13:29.150 Now onwards in any of the query we will be using this table only. 187 13:30.430 --> 13:33.110 Now let me show you the data. 188 13:35.550 --> 13:36.670 I have shown you right. 189 13:37.230 --> 13:38.990 So this is the clean table. 190 13:39.790 --> 13:42.750 Now this is our second project. 191 13:42.750 --> 13:47.270 And you can see the second case study and it is based on the Pisa. 192 13:49.230 --> 13:51.150 So everyone likes Pisa right. 193 13:53.190 --> 13:55.110 Now let's see the question. 194 13:55.510 --> 14:02.670 So all these ten questions which is above here that I have written over here one by one with solution. 195 14:03.880 --> 14:06.240 And we need to use clean table over here. 196 14:06.600 --> 14:09.240 So now what is the first question. 197 14:10.200 --> 14:12.040 How many pizzas were ordered. 198 14:12.640 --> 14:17.160 So now let me just take this three four tables. 199 14:23.840 --> 14:25.560 These are all the tables used. 200 14:26.560 --> 14:28.560 But we will use the clean table only. 201 14:32.720 --> 14:35.960 Let me copy over here. 202 14:41.480 --> 14:43.480 So how many pizzas were ordered. 203 14:43.520 --> 14:45.400 So let me see. 204 14:46.720 --> 14:47.960 It is a 14 pizza. 205 14:48.000 --> 14:48.160 Right. 206 14:48.160 --> 14:52.360 Because in the order ID this is the customer orders table. 207 14:52.360 --> 14:53.520 And in the customer order. 208 14:53.520 --> 14:57.000 Order ID total 14 order IDs are present. 209 14:57.000 --> 14:57.400 Right? 210 14:57.440 --> 15:05.850 14 actually distinct IDs are less, but This pizza is ordered two times, right? 211 15:05.890 --> 15:06.570 Order ID? 212 15:09.690 --> 15:10.530 Two times. 213 15:10.770 --> 15:16.490 Uh, for customer ID 104 has ordered two times pizza. 214 15:16.490 --> 15:16.850 Right? 215 15:18.770 --> 15:21.570 So for that purpose, order ID also it is same. 216 15:24.530 --> 15:26.010 So how many pizzas were ordered? 217 15:26.010 --> 15:30.490 So it is a simple query count of star from customer orders. 218 15:32.530 --> 15:34.010 So it is a 14 right. 219 15:34.810 --> 15:36.970 Now let's see the another question. 220 15:37.370 --> 15:39.730 How many unique customer orders were made. 221 15:40.850 --> 15:46.290 So here from this table we only need to do one change. 222 15:47.330 --> 15:49.210 Count of distinct order ID. 223 15:50.090 --> 15:51.290 So let me again. 224 15:55.010 --> 15:57.450 So this 1233 is coming twice. 225 15:57.450 --> 16:00.050 So we will consider it as a one time 226 16:03.500 --> 16:06.540 Seven, eight, nine and ten. 227 16:07.140 --> 16:08.980 So ten pizzas. 228 16:11.820 --> 16:13.940 How many unique customer orders were made? 229 16:13.940 --> 16:15.220 So it will be ten, right? 230 16:15.780 --> 16:23.140 Count of distinct order ID right now let's see another question. 231 16:24.780 --> 16:29.340 How many successful orders were delivered by each runner? 232 16:30.100 --> 16:35.260 How many successful orders were delivered by each runner? 233 16:35.500 --> 16:40.300 So over here, if you see the clean runner order table. 234 16:46.580 --> 16:48.140 Clean runner order table. 235 16:48.780 --> 16:49.660 And if you. 236 16:56.060 --> 16:57.500 Apart from these two. 237 16:57.900 --> 16:59.220 So let me. 238 17:01.220 --> 17:07.710 How many what is how many successful order were delivered by each runner. 239 17:12.830 --> 17:13.350 Each runner. 240 17:13.390 --> 17:16.630 Okay, so one has delivered three orders, right? 241 17:17.430 --> 17:20.350 One has delivered three orders. 242 17:20.830 --> 17:22.590 Then two has delivered one orders. 243 17:22.590 --> 17:25.670 Three delivered two orders like that. 244 17:25.710 --> 17:27.550 Let me show you. 245 17:29.270 --> 17:29.750 One has. 246 17:29.790 --> 17:29.990 Okay. 247 17:29.990 --> 17:31.430 One has delivered four orders. 248 17:31.430 --> 17:31.990 Actually. 249 17:32.470 --> 17:37.710 Then two has delivered also four orders to deliver. 250 17:37.710 --> 17:38.190 One. 251 17:38.550 --> 17:38.950 Two. 252 17:38.990 --> 17:39.430 Okay. 253 17:39.790 --> 17:46.630 This one we cannot consider because customer cancellation status we need to check. 254 17:46.630 --> 17:47.070 Right. 255 17:47.630 --> 17:51.510 So for so we cannot consider this is a cancellation. 256 17:51.910 --> 17:54.110 So this is not a delivered order right. 257 17:54.390 --> 17:57.590 This order has been canceled by the customer after ordering. 258 17:58.510 --> 17:59.750 So simple. 259 17:59.750 --> 18:05.840 One clean runner order table where cancellation Is null. 260 18:15.080 --> 18:16.080 One minute. 261 18:16.080 --> 18:17.800 Let me execute this query. 262 18:18.680 --> 18:20.000 One for. 263 18:22.640 --> 18:25.560 Perfect, right. 264 18:25.600 --> 18:26.520 We need to check. 265 18:26.560 --> 18:28.040 Cancellation is null, right? 266 18:28.720 --> 18:31.560 So one is delivered for successful orders. 267 18:31.560 --> 18:32.520 Then two is delivered. 268 18:32.520 --> 18:34.160 Three and three is delivered. 269 18:34.160 --> 18:35.800 One successful order. 270 18:37.400 --> 18:38.200 Now how many? 271 18:38.240 --> 18:40.360 Each type of pizzas was delivered. 272 18:41.040 --> 18:41.560 How many? 273 18:41.600 --> 18:43.840 Each type of pizzas were delivered. 274 18:47.280 --> 18:50.160 So here we need to use three different tables. 275 18:50.160 --> 18:53.680 One is let me show you. 276 18:58.200 --> 19:00.360 This table then. 277 19:02.520 --> 19:03.120 Pizza dot. 278 19:03.120 --> 19:03.840 Pizza names. 279 19:03.840 --> 19:04.320 Table. 280 19:13.770 --> 19:15.130 And then run a stable. 281 19:25.690 --> 19:28.410 We need to use all these three tables over here. 282 19:29.170 --> 19:33.330 Now there are two different pizza names, are there? 283 19:33.330 --> 19:33.770 Right. 284 19:34.010 --> 19:35.050 You can say that. 285 19:35.610 --> 19:39.410 I think it's a pizza ID and pizza names. 286 19:40.090 --> 19:40.610 Right. 287 19:43.290 --> 19:47.370 And this pizza ID is present over here in this table. 288 19:47.610 --> 19:49.090 Clean customer order table. 289 19:49.330 --> 19:52.090 So first of all, clean customer order table use. 290 19:53.290 --> 19:57.490 And we have then join it with the pizza names table with pizza ID column. 291 19:57.490 --> 19:59.970 This pizza ID and pizza ID is common over here. 292 20:00.330 --> 20:05.130 And then we have used the clean runners order table. 293 20:05.180 --> 20:08.620 And from here we have joined up with. 294 20:11.460 --> 20:21.300 We have joined with order ID column, right order ID column and cancellation is null. 295 20:21.300 --> 20:22.420 We have to check right. 296 20:23.500 --> 20:27.460 And then we need to check how many each type of pizza was delivered. 297 20:27.820 --> 20:33.780 So pizza pizza name delivered whenever we can say delivered whenever cancellation is null. 298 20:34.500 --> 20:41.020 In this cancellation column, there should be a null value right in this particular column. 299 20:41.820 --> 20:44.980 If it is a value, then that order is cancelled. 300 20:45.220 --> 20:49.180 So we cannot consider this one. 301 20:49.180 --> 20:49.620 Right. 302 20:50.340 --> 20:52.300 So cancellation is null. 303 20:52.700 --> 20:56.780 And then we need to do group by over here because how many. 304 20:57.060 --> 21:00.580 So it means that we need to count the pizza ID over here. 305 21:01.460 --> 21:03.780 Count of pizza ID and then pizza name. 306 21:04.180 --> 21:10.030 Then then at the end we need to do we need to do group by right. 307 21:12.350 --> 21:21.670 We need to do group by at the end to consider an highest number of order will be come first. 308 21:21.670 --> 21:23.470 So let me show you. 309 21:24.310 --> 21:29.110 Meat lovers delivery count will be nine and vegetarian delivery count will be three. 310 21:33.350 --> 21:37.110 Now let me jump to the fifth question. 311 21:37.670 --> 21:42.390 How many vegetarian and meat lovers were ordered by each customer? 312 21:44.510 --> 21:46.550 How many vegetarian? 313 21:48.190 --> 21:52.430 So if you see over here, let me show you this table again. 314 22:03.630 --> 22:05.630 So this is a pizza ID one. 315 22:05.630 --> 22:05.690 id1. 316 22:09.130 --> 22:10.330 And id2. 317 22:10.370 --> 22:17.090 So two is the order by one, two, three, four times. 318 22:25.930 --> 22:29.690 How many vegetarian and meat lover were ordered by each customer? 319 22:30.090 --> 22:34.690 So customer ID 101101 has ordered. 320 22:39.970 --> 22:41.570 101 okay. 321 22:42.370 --> 22:44.330 Two times over here in one. 322 22:44.330 --> 22:45.010 Let me. 323 22:47.170 --> 22:49.770 Order this data order by. 324 22:53.010 --> 22:56.330 Customer ID and then a pizza ID. 325 22:58.530 --> 23:00.490 Now we can easily say this right. 326 23:02.530 --> 23:07.500 So now see 101 has ordered two this and one this. 327 23:08.100 --> 23:08.460 Right. 328 23:08.460 --> 23:12.260 So that will be coming in our answer to this. 329 23:12.300 --> 23:14.740 And one vegetarian in two meat lovers. 330 23:16.500 --> 23:20.380 Then 102102 is done. 331 23:22.900 --> 23:25.060 Also same to this and one this. 332 23:25.460 --> 23:26.780 Right then 103. 333 23:26.820 --> 23:36.500 Let me show you three and 1103 is three and one and 10410411 coming three times. 334 23:36.940 --> 23:39.300 So three zero and 105. 335 23:42.140 --> 23:44.180 10501. 336 23:44.180 --> 23:44.580 Right. 337 23:46.500 --> 23:47.220 So here. 338 23:50.300 --> 23:55.540 Here we need to do how many vegetarian and meat lovers were ordered by each customer. 339 23:55.580 --> 24:00.820 So first column will be customer ID then meat lovers column. 340 24:00.820 --> 24:06.750 But for that what we need to do pizza type pizza ID equal to one means meat lovers. 341 24:07.070 --> 24:17.590 So when we have done some over here case, when p is equal to one, then one else zero and end and we 342 24:17.590 --> 24:20.030 need, we have done the sum of all this. 343 24:20.590 --> 24:23.110 One plus one means two right. 344 24:24.390 --> 24:26.310 Per each customer we have to do. 345 24:26.350 --> 24:30.790 So for that purpose at the end we have done group by customer ID also. 346 24:30.790 --> 24:31.230 Right. 347 24:39.310 --> 24:42.830 So is this uh fine. 348 24:42.870 --> 24:43.230 Right. 349 24:43.550 --> 24:48.830 And now for again for this vegetarian also which is pizza ID equal to two. 350 24:52.350 --> 24:53.430 Then one else zero. 351 24:53.430 --> 24:57.590 And we have done the sum of that one also over here. 352 24:58.150 --> 24:58.670 Right. 353 25:01.070 --> 25:03.350 So that will be our answer. 354 25:03.350 --> 25:05.150 Let me execute the query. 355 25:12.880 --> 25:18.480 So 101 has order, two meat lovers and one vegetarian pizza. 356 25:19.040 --> 25:19.520 Right? 357 25:27.400 --> 25:30.680 I hope uh, so this is the second project. 358 25:30.680 --> 25:31.920 First part. 359 25:32.560 --> 25:40.400 We have solved the five queries over here, and if you want the create table script, then I will give 360 25:40.400 --> 25:47.560 you in the description box of the video, and we will solve the rest of the queries in the next, uh 361 25:49.480 --> 25:50.560 uh, next video. 362 25:51.480 --> 26:01.480 So if you like the way of my explanation, please do like, comment and share as much as possible and 363 26:01.480 --> 26:03.840 please do subscribe to my channel. 364 26:03.840 --> 26:04.960 Thank you everyone.