1 00:01.080 --> 00:02.160 Hello everyone. 2 00:03.680 --> 00:08.160 So let's have the continuation of our project two. 3 00:09.160 --> 00:14.320 We have already seen the five queries in the this project. 4 00:14.360 --> 00:16.600 Now let's see the another five queries. 5 00:17.840 --> 00:22.280 So I have already explained the data set in the previous video. 6 00:22.800 --> 00:25.840 You can check that one. 7 00:26.160 --> 00:28.600 Now let's move to the sixth query. 8 00:29.280 --> 00:34.280 What was the maximum number of pizzas delivered in a single order? 9 00:35.080 --> 00:38.520 So if you see over here we have two different tables right. 10 00:39.400 --> 00:45.320 We have already cleaned the table select star from this one. 11 00:46.120 --> 00:47.960 Clean customer orders and then. 12 00:51.120 --> 00:54.360 Select star from clean runners order. 13 00:54.680 --> 01:00.990 Now we need to check what was the maximum number of pizzas delivered in a single order. 14 01:01.710 --> 01:09.790 So if you run both this query, then order ID column will be the common column between the both the 15 01:09.790 --> 01:10.190 tables. 16 01:10.190 --> 01:10.670 Right. 17 01:10.950 --> 01:14.990 So that we will do in a join between these two table. 18 01:15.350 --> 01:22.510 Now we do not need to consider where order is cancelled means over here cancellation should be null. 19 01:22.510 --> 01:22.830 Right. 20 01:22.870 --> 01:24.390 That we need to take care of. 21 01:24.990 --> 01:25.550 Now. 22 01:27.870 --> 01:31.790 We need to do order ID and then ID is there, right? 23 01:31.950 --> 01:35.230 Pizza ID is available in this column. 24 01:36.310 --> 01:39.550 So in a single order what was the maximum order. 25 01:39.670 --> 01:45.270 So if you see over here order ID four has three pizzas are there. 26 01:45.270 --> 01:45.710 Right. 27 01:45.750 --> 01:47.790 Three pizza ID are there four. 28 01:47.790 --> 01:48.750 Order ID four. 29 01:49.190 --> 01:50.430 So let's see now. 30 01:50.790 --> 02:01.660 Now we have taken this, uh select order ID then count of pizza ID so per per order, we need to mention 31 02:01.660 --> 02:03.300 the count of Pisa ID, right? 32 02:03.580 --> 02:10.420 And then we have done the join of these two table clean customer orders and clean runners order based 33 02:10.420 --> 02:14.260 on order ID column which is common column between both the tables. 34 02:14.780 --> 02:19.380 And then we need to consider cancellation is null right. 35 02:20.220 --> 02:25.420 Because if cancellation is there then that will be the canceled order. 36 02:25.580 --> 02:28.180 So we are not considering that one. 37 02:29.300 --> 02:33.460 So we can take this entire block into the first city. 38 02:33.700 --> 02:36.420 So now let me run this query individually. 39 02:40.420 --> 02:41.820 Let me run it okay. 40 02:42.380 --> 02:48.260 So if you see for the fourth order ID three pizzas are delivered right over here. 41 02:49.260 --> 02:51.620 So that will be the maximum number of orders. 42 02:51.660 --> 02:57.100 Now we need to from this entire column we need to take the maximum one. 43 02:57.690 --> 02:58.490 Right. 44 02:58.490 --> 03:04.570 So what I have done, this entire block will be taken into one city and then outside of city. 45 03:04.610 --> 03:11.570 What I have done select maximum of n of order N of order from this column. 46 03:11.570 --> 03:14.090 Minute maximum value. 47 03:14.090 --> 03:14.450 Right. 48 03:14.450 --> 03:15.970 So which will be three. 49 03:16.490 --> 03:16.930 So. 50 03:20.210 --> 03:28.530 Select maximum of node order x maximum delivered pizza from this city right order count city. 51 03:28.850 --> 03:30.930 So now let me execute this query. 52 03:37.250 --> 03:42.450 So which will be three right four order uh order ID four. 53 03:42.490 --> 03:47.210 We have delivered three, uh times three pizzas. 54 03:48.170 --> 03:48.610 Right. 55 03:48.650 --> 03:50.330 So that will be our answer. 56 03:51.490 --> 03:53.770 I have already written the answer over here. 57 03:53.770 --> 03:55.970 Three is the our answer right now. 58 03:55.970 --> 03:58.000 Let's jump to the next query. 59 03:59.600 --> 04:06.760 For each customer, how many delivered pizza had at least one change in how many head. 60 04:07.000 --> 04:08.120 No changes. 61 04:10.400 --> 04:14.240 So now again let me take this both query. 62 04:17.520 --> 04:20.560 Here we need to consider for each customer right. 63 04:21.600 --> 04:22.280 So now. 64 04:25.760 --> 04:28.320 Here also this same cancellation is null. 65 04:28.320 --> 04:31.160 We have to take into consideration. 66 04:31.160 --> 04:31.640 Right. 67 04:31.880 --> 04:37.440 How many delivered pizzas had at least one change and how many had no changes. 68 04:37.800 --> 04:40.240 So let me run this query again. 69 04:44.720 --> 04:47.360 So if you see over here exclusion. 70 04:50.040 --> 04:52.320 So for exclusion. 71 04:56.710 --> 05:03.750 If you see, we need to consider over here for each customer ID, right? 72 05:03.910 --> 05:10.830 So if you see customer ID 103 has three exclusions. 73 05:10.830 --> 05:11.350 Are there. 74 05:11.350 --> 05:11.790 Right. 75 05:13.350 --> 05:14.870 Three exclusions are there. 76 05:14.910 --> 05:20.590 Or if you see 4103 extras or. 77 05:23.790 --> 05:26.910 103 extras are nowhere right. 78 05:27.430 --> 05:30.950 And then 41041 extras are there. 79 05:31.550 --> 05:37.630 So what I have done over here, we need to do some of case when for exclusion. 80 05:37.670 --> 05:40.310 Actually for we with no changes. 81 05:40.310 --> 05:40.790 Right. 82 05:41.830 --> 05:43.830 So sorry with changes. 83 05:43.830 --> 05:48.270 So we need to consider exclusion and extras both right over here. 84 05:48.990 --> 05:54.150 So for one one there will be answer will be three. 85 05:54.150 --> 05:54.590 Right. 86 05:55.810 --> 05:56.130 Sorry. 87 05:56.170 --> 05:56.890 103. 88 05:56.930 --> 05:57.730 Answer will be three. 89 05:57.730 --> 05:59.930 So let me show you. 90 05:59.970 --> 06:05.810 Actually, let me run entire query with above two query as well. 91 06:08.210 --> 06:14.650 So now let's see what I have told you for 103. 92 06:15.370 --> 06:21.370 If you see over here 1031 exclusion is there. 93 06:21.890 --> 06:23.050 Second exclusion is there. 94 06:23.050 --> 06:24.770 And three exclusion is there. 95 06:25.210 --> 06:30.770 Now for 101 there will be nothing 102 there will be nothing. 96 06:31.770 --> 06:32.210 Right. 97 06:32.610 --> 06:39.450 So with changes there will be nothing I am talking about with changes then 1033 then 104. 98 06:40.290 --> 06:51.970 Let's see for 1041041 is coming over here and then one is coming over here. 99 06:52.010 --> 06:53.960 So so it will be two right? 100 06:53.960 --> 06:55.160 For 104. 101 06:55.800 --> 06:56.720 Yeah, exactly. 102 06:56.720 --> 06:57.400 Perfect. 103 06:58.120 --> 06:59.040 It will be true. 104 06:59.080 --> 07:04.000 So we need to consider exclusion and extras value in either of these two. 105 07:04.000 --> 07:06.920 Value has some value for each customer. 106 07:06.920 --> 07:11.600 And then we need to do summation of that exclusion and extras. 107 07:12.240 --> 07:12.720 Right. 108 07:14.600 --> 07:16.000 And with no changes. 109 07:16.400 --> 07:19.760 So how many had no changes. 110 07:20.040 --> 07:22.920 So if you see over here how many had no changes. 111 07:23.360 --> 07:35.960 So for 101 let me see over here 101 for no changes we need to consider exclusion is null as well as 112 07:36.000 --> 07:37.160 extras is null. 113 07:37.480 --> 07:38.200 Then one. 114 07:38.560 --> 07:42.960 So 101 has one. 115 07:43.320 --> 07:44.840 One value is over here. 116 07:46.280 --> 07:49.480 Then 101. 117 07:52.830 --> 07:54.670 As one value is over here as well. 118 07:54.670 --> 07:56.510 One value is over here as well. 119 07:57.110 --> 08:01.350 So let me show you 101. 120 08:01.350 --> 08:03.710 No changes is there in two times. 121 08:05.070 --> 08:08.150 Okay 101101. 122 08:10.390 --> 08:10.950 Then. 123 08:16.950 --> 08:17.630 Is null. 124 08:17.630 --> 08:20.430 Is null okay. 125 08:20.470 --> 08:23.110 We need to consider this one also. 126 08:23.110 --> 08:23.590 Right. 127 08:23.630 --> 08:32.630 So let me go to this order ID and then order ID six six has this cancellation is there. 128 08:32.630 --> 08:33.070 Right. 129 08:33.270 --> 08:36.110 So that we it will be two only not three. 130 08:37.830 --> 08:40.430 So 1012 has no changes right. 131 08:42.470 --> 08:49.150 I hope you are getting this because we need to consider cancellation is null values only right. 132 08:56.260 --> 08:59.940 Or let me show you in the Excel file. 133 09:00.700 --> 09:03.740 Let me take this all the data into the Excel file. 134 09:05.740 --> 09:06.740 One minute. 135 09:11.380 --> 09:14.380 Let me take it. 136 09:17.460 --> 09:17.980 Okay. 137 09:25.540 --> 09:27.620 Then let me take this data also. 138 09:33.500 --> 09:35.220 And then let me take this. 139 09:35.220 --> 09:37.260 Our answer sheet. 140 09:37.820 --> 09:39.020 Answer also. 141 09:43.980 --> 09:44.620 Now. 142 09:49.820 --> 09:52.930 Now how Now we are considering the no changes. 143 09:52.930 --> 09:54.770 So if you see for 101. 144 09:57.130 --> 09:59.530 These two both the value should be null. 145 10:00.770 --> 10:08.970 Then for a again this 101 both the values should be null and then again for 101 both the values should 146 10:08.970 --> 10:09.530 be null. 147 10:09.970 --> 10:15.770 But now let's see this order id order id of 101. 148 10:15.810 --> 10:16.690 Which is this one. 149 10:16.690 --> 10:18.210 Right one, two and six. 150 10:19.370 --> 10:24.130 Now check for this one, two and six into this table. 151 10:24.730 --> 10:27.570 So for one and two. 152 10:31.410 --> 10:34.370 If you see over here cancellation is null right. 153 10:34.370 --> 10:36.730 But for six cancellation is there. 154 10:37.130 --> 10:42.170 So we need to uh eliminate this record. 155 10:42.170 --> 10:42.610 Right. 156 10:42.930 --> 10:44.650 So now no changes. 157 10:45.010 --> 10:52.120 Uh, whenever we are considering no changes when exclusion has null value as well as extras is also 158 10:52.120 --> 10:52.720 null value. 159 10:52.760 --> 11:02.080 It means that end condition between exclusion and extras, so that over here for 1012 only two known 160 11:02.120 --> 11:03.200 no changes are there. 161 11:03.200 --> 11:03.600 Right? 162 11:08.160 --> 11:09.560 So which will be correct? 163 11:09.560 --> 11:13.000 One because we are not considering. 164 11:15.120 --> 11:16.600 Cancellation values. 165 11:19.720 --> 11:20.280 Okay. 166 11:20.600 --> 11:23.080 So now let's jump to the next question. 167 11:24.960 --> 11:30.080 How many pizzas will deliver that had both exclusions and extras. 168 11:31.280 --> 11:34.040 So how many pizzas were delivered. 169 11:34.040 --> 11:37.200 That is exclusion and extras. 170 11:37.240 --> 11:39.520 So now. 171 11:42.000 --> 11:48.710 In this case we need to check both value exclusion as well as Uh x trans. 172 11:48.710 --> 11:50.430 Both values should be there, right? 173 11:51.350 --> 11:53.870 So let me show you. 174 11:59.950 --> 12:00.430 So. 175 12:02.870 --> 12:05.470 Exclusion is not null as well as extras is not null. 176 12:05.470 --> 12:05.790 Right. 177 12:06.150 --> 12:07.750 So this is the one value. 178 12:08.350 --> 12:11.030 And this will be the second value. 179 12:12.070 --> 12:12.550 Right. 180 12:13.310 --> 12:15.070 So now is there anywhere. 181 12:18.190 --> 12:19.470 That both has value. 182 12:19.510 --> 12:21.510 So two and now nine. 183 12:22.990 --> 12:25.710 So I think the answer will be one only right. 184 12:26.750 --> 12:36.110 Because nine we are not considering because for nine uh cancellation is there in the status. 185 12:36.110 --> 12:36.510 Right. 186 12:36.870 --> 12:39.390 So we can consider only ten. 187 12:39.390 --> 12:43.110 Only because it has the cancellation value is null. 188 12:43.590 --> 12:44.110 Right. 189 12:44.750 --> 12:46.350 So 10th will be one. 190 12:46.550 --> 12:47.540 So how many? 191 12:48.140 --> 12:55.820 So what I have done over here case when t dot exclusion is not null and t one dot x is not null, then 192 12:55.860 --> 12:56.820 one else null. 193 12:57.100 --> 12:59.500 And then we need to do summation of that one. 194 12:59.500 --> 12:59.940 Right. 195 13:00.500 --> 13:02.420 We need to do summation of that one. 196 13:02.900 --> 13:06.980 So number of pizzas and then cancellation is null. 197 13:06.980 --> 13:09.060 So that will be our common condition. 198 13:09.340 --> 13:14.940 So if you execute this query then answer will be one only right. 199 13:18.780 --> 13:20.900 Now let's see the ninth query. 200 13:23.780 --> 13:27.740 What was the total volume of pizzas order for. 201 13:30.740 --> 13:32.140 Each hour of the day. 202 13:32.660 --> 13:33.380 So now. 203 13:38.140 --> 13:43.140 If you run this clean customer orders table now for each hour of the day. 204 13:43.140 --> 13:43.580 Right. 205 13:43.780 --> 13:47.330 So from this, uh, order uh, time column. 206 13:47.730 --> 13:51.930 We need to consider the order for. 207 13:54.690 --> 13:55.010 Sorry. 208 13:55.010 --> 13:59.050 We need to consider the hour for each of the ordered time column. 209 13:59.050 --> 13:59.490 Right. 210 13:59.810 --> 14:07.170 And then for each of the hour means from some of the pizzas, well, deliver 18 to 5. 211 14:07.210 --> 14:08.770 Right then. 212 14:08.810 --> 14:09.330 11. 213 14:09.330 --> 14:11.450 So see this one again. 214 14:11.490 --> 14:12.450 18 to 34. 215 14:12.490 --> 14:12.690 Again. 216 14:12.730 --> 14:13.890 18 to 34. 217 14:14.490 --> 14:21.490 So for 6:00, uh, format, we can say that, uh, three pizzas were delivered, right? 218 14:22.330 --> 14:23.930 Let me execute this one. 219 14:25.890 --> 14:28.530 See, over here for 6:00 format. 220 14:28.530 --> 14:30.130 Three pizzas were delivered. 221 14:30.330 --> 14:32.450 Let me again execute this. 222 14:32.450 --> 14:33.410 Both the queries. 223 14:36.050 --> 14:36.570 Okay. 224 14:36.850 --> 14:39.210 So for because of one is this 18. 225 14:40.210 --> 14:46.800 Then again 18 coming two times right now check for 11:00. 226 14:47.240 --> 14:49.640 So let me show you. 227 14:54.600 --> 14:56.080 411. 228 14:56.200 --> 14:56.720 Okay. 229 14:57.080 --> 14:58.000 11:00. 230 14:58.040 --> 14:58.840 There is only one. 231 14:58.840 --> 14:59.560 Pizzas are there? 232 14:59.560 --> 14:59.760 Right? 233 14:59.760 --> 15:00.720 Four 1:00. 234 15:01.080 --> 15:02.120 We can say 13. 235 15:02.160 --> 15:03.880 We need to check 13 value. 236 15:04.160 --> 15:05.960 So 13 is coming over here. 237 15:05.960 --> 15:07.040 13 is coming over here. 238 15:07.040 --> 15:08.360 13 is coming over here. 239 15:08.880 --> 15:11.560 So for 113 means 11:00. 240 15:11.560 --> 15:11.960 Right? 241 15:12.240 --> 15:12.760 13. 242 15:12.800 --> 15:15.880 Sorry 13 means 1 p.m. so it will be three. 243 15:15.880 --> 15:23.280 So what I have done over here from the entire order time column, I have firstly generated the date 244 15:23.280 --> 15:24.600 part and then hour. 245 15:24.880 --> 15:30.080 So from this date, uh, from this order time column, I need hour only. 246 15:30.080 --> 15:33.360 So for that date part function is used. 247 15:33.360 --> 15:37.840 And for which, uh, we need to give hour as a parameter. 248 15:37.840 --> 15:38.280 Right. 249 15:39.200 --> 15:42.080 So it will give hours of 24 four. 250 15:42.080 --> 15:47.870 Format means 13, 15, 16 like that up to 24. 251 15:48.110 --> 15:49.990 But now what if I want? 252 15:52.430 --> 15:55.310 Overs in this format as well. 253 15:55.310 --> 15:59.310 Like 11, one, two, three, four and up to 12. 254 15:59.310 --> 16:01.350 Only not in 24 format. 255 16:01.630 --> 16:06.350 So for that purpose, what I have done over here, I have used the format function. 256 16:06.350 --> 16:18.550 And then I have used this order time column and then in which I have given as a parameter to the order 257 16:18.590 --> 16:25.510 time column to extract over and to t from order time column. 258 16:25.510 --> 16:28.150 And for that purpose I have used the format function. 259 16:29.830 --> 16:31.070 And now how many? 260 16:31.590 --> 16:35.110 What was the total volume of pizzas order for each hour of the day. 261 16:35.430 --> 16:41.430 So for that count of star I have given and then because of count of star I have given, I need to do 262 16:41.470 --> 16:44.610 aggregate for This one and this one also. 263 16:44.610 --> 16:45.010 Right? 264 16:45.050 --> 16:49.330 So for that purpose I have used the group by function and then order by. 265 16:52.370 --> 16:53.610 So let me. 266 16:57.450 --> 16:59.370 So that this will be our answer. 267 16:59.370 --> 16:59.810 Right. 268 17:01.130 --> 17:05.090 For each of the our how many pizzas were be delivered. 269 17:08.410 --> 17:09.610 Now let me. 270 17:12.090 --> 17:14.090 Show you the final question. 271 17:14.770 --> 17:21.530 What was the what was the volume of orders for each day of the week? 272 17:22.530 --> 17:23.050 So. 273 17:25.250 --> 17:27.530 Let me run this query. 274 17:34.370 --> 17:35.530 Each day of the week. 275 17:35.570 --> 17:41.290 So for how how we can find the each day of the week, we can find, find it by this way. 276 17:41.290 --> 17:41.730 Right. 277 17:45.320 --> 17:47.040 We have to use date name function. 278 17:47.040 --> 17:54.200 And in that date name function we need to give a parameter of week day because we want Saturday. 279 17:54.240 --> 17:58.440 It is Saturday or there is there or Sunday is there or Monday is there. 280 17:58.480 --> 17:58.880 Right. 281 17:59.560 --> 18:06.400 So for that weekday function function we have used and then cast of order time as n where k right. 282 18:06.960 --> 18:09.280 So let me execute this query. 283 18:12.880 --> 18:16.520 So here it is given as Friday. 284 18:16.520 --> 18:21.680 So Friday Saturday Monday Friday again Saturday Monday. 285 18:21.680 --> 18:25.040 But now we need to do count of uh. 286 18:27.920 --> 18:28.920 Count of one. 287 18:28.920 --> 18:31.520 And then uh group by. 288 18:36.960 --> 18:38.320 Uh, this entire column. 289 18:38.320 --> 18:38.720 Right. 290 18:39.160 --> 18:41.190 So let me execute this one. 291 18:42.830 --> 18:46.270 So if you see over here for Saturday we have delivered five pizza. 292 18:46.830 --> 18:49.310 Then Monday also we have delivered five pizza. 293 18:52.830 --> 18:54.750 Let me execute this query. 294 18:59.070 --> 19:01.510 Write for and then for Saturday. 295 19:01.550 --> 19:06.510 Ah we have delivered three pizza and Sunday we have delivered one pizza right. 296 19:07.550 --> 19:08.830 So this is actually. 297 19:16.670 --> 19:19.350 The date name function is there in SQL server. 298 19:19.350 --> 19:23.590 Or maybe it is available in other relational database as well. 299 19:24.110 --> 19:27.310 And what will be the parameter of the date name function. 300 19:27.950 --> 19:29.630 So that you can see over here. 301 19:29.670 --> 19:29.950 Right. 302 19:29.990 --> 19:31.110 It is a function. 303 19:32.230 --> 19:34.190 Let me show you the parameter. 304 19:47.180 --> 19:54.220 So what we, uh, can give our our over here, we our requirement is to give weekday. 305 19:54.220 --> 19:54.660 Right. 306 19:55.020 --> 19:57.900 So we have given the weekday over here then comma. 307 19:58.740 --> 20:02.740 So for which date we need to give the week name. 308 20:02.740 --> 20:11.420 If we give get date right then it will give Sunday over here because today is Sunday right. 309 20:11.860 --> 20:16.140 For each of the 14 records then. 310 20:18.540 --> 20:23.860 But our requirement is to give weekday of the day order time column. 311 20:24.380 --> 20:28.500 So for that purpose over here we have casting the. 312 20:32.500 --> 20:34.140 Order time into n varchar. 313 20:34.140 --> 20:34.540 Right. 314 20:35.180 --> 20:37.980 But now we want Friday. 315 20:37.980 --> 20:41.130 How many pizzas will be delivered. 316 20:41.130 --> 20:45.130 So Friday 12345 pizzas were delivered. 317 20:45.370 --> 20:49.170 Saturday again 123 pizzas were delivered. 318 20:50.130 --> 20:59.170 Then we can say Sunday Monday 12345 pizzas were delivered like that. 319 20:59.210 --> 21:01.210 If I execute both of these query. 320 21:04.890 --> 21:07.210 Then it will give the proper output right? 321 21:11.770 --> 21:15.650 So thank you so much everyone. 322 21:17.690 --> 21:22.010 This is our second project we have completed today in SQL. 323 21:23.650 --> 21:27.250 I will come again with the next project as soon as possible. 324 21:27.770 --> 21:34.690 Please kindly support my channel and do watch and like, comment, share and subscribe. 325 21:35.970 --> 21:36.730 Thank you.