1 00:00.320 --> 00:02.400 Let's solve the another SQL problem. 2 00:04.000 --> 00:06.040 Count medal types per country. 3 00:09.040 --> 00:10.920 So below is the problem statement. 4 00:11.280 --> 00:18.160 Given a table of Olympic results, write a query to create a summary table that shows the total count 5 00:18.200 --> 00:22.560 of gold, silver and bronze medals for each country. 6 00:24.320 --> 00:27.240 Okay, now they have given us this table. 7 00:27.400 --> 00:29.440 So let's do the prompt. 8 00:30.360 --> 00:43.040 I am just copying this data and let's ask the ChatGPT I am having below table structure and data. 9 00:44.840 --> 00:45.800 Okay, fine. 10 00:45.840 --> 00:47.400 Let's see what's happening. 11 00:49.280 --> 00:51.600 Please share your table structure and sample data. 12 00:51.640 --> 00:52.200 Right. 13 00:52.600 --> 00:54.200 So let me share it. 14 00:58.040 --> 01:00.040 Okay I have shared my data. 15 01:00.920 --> 01:04.840 Now it has given the solution. 16 01:04.960 --> 01:08.910 It is has given the table structure in tabular format, right? 17 01:10.310 --> 01:10.670 Okay. 18 01:10.670 --> 01:13.190 Now ask me the problem statement. 19 01:14.390 --> 01:20.310 So let me copy my problem statement and let me ask the ChatGPT now. 20 01:23.230 --> 01:25.790 Right to query to create a summary table okay. 21 01:25.830 --> 01:28.430 That shows the total count of gold. 22 01:28.510 --> 01:29.150 Silver. 23 01:29.350 --> 01:29.790 Okay. 24 01:32.310 --> 01:32.990 Excellent. 25 01:33.030 --> 01:37.670 This is a classic SQL pivot style aggregation problem, right? 26 01:39.150 --> 01:44.150 We will create a summary of total gold, silver and bronze medals for each country. 27 01:44.870 --> 01:45.390 Okay. 28 01:45.430 --> 01:46.150 Perfect. 29 01:46.150 --> 01:50.790 So suppose C USA has won two gold medals and one silver medal. 30 01:50.790 --> 01:51.190 Right. 31 01:51.590 --> 01:52.950 And zero bronze medal. 32 01:53.190 --> 01:59.550 And China has won two bronze medals and one gold medal. 33 01:59.550 --> 01:59.750 Right. 34 01:59.790 --> 02:00.870 No silver medal. 35 02:01.310 --> 02:04.950 So this is the solution one it has provided. 36 02:04.950 --> 02:09.350 And I think it is matching with our solution C over here. 37 02:10.060 --> 02:11.580 Only one difference is there. 38 02:12.220 --> 02:15.060 We have done the count and they have done the sum. 39 02:16.420 --> 02:16.980 So. 40 02:20.100 --> 02:21.620 Solution provided by the. 41 02:25.660 --> 02:28.700 So see both of these thing will give the same result. 42 02:28.700 --> 02:32.380 So let me execute both the queries together. 43 02:34.740 --> 02:35.140 Okay. 44 02:35.220 --> 02:36.660 China 102. 45 02:36.700 --> 02:37.420 Perfect. 46 02:38.340 --> 02:39.940 And USA 210. 47 02:39.980 --> 02:40.740 Perfect. 48 02:41.260 --> 02:42.780 So see what it is doing. 49 02:43.020 --> 02:49.660 Case when medal type equal to gold, then one right, then one else zero. 50 02:49.700 --> 02:49.980 Else. 51 02:49.980 --> 02:54.180 We are not counting anything over here in in the above query. 52 02:54.220 --> 02:56.660 We haven't done else part right. 53 02:56.980 --> 02:58.300 So that will be fine. 54 03:00.740 --> 03:07.860 Whenever the medal type is silver, then again we are counting each counting with silver and for bones. 55 03:08.140 --> 03:11.340 We have also written the another case statement. 56 03:11.340 --> 03:11.780 Right. 57 03:12.180 --> 03:13.380 Okay, that is fine. 58 03:13.900 --> 03:14.930 Now see? 59 03:14.970 --> 03:16.250 Is there any other solution. 60 03:16.250 --> 03:16.850 Is there. 61 03:18.050 --> 03:18.530 Okay. 62 03:18.570 --> 03:22.330 Using pivot also it has done okay. 63 03:22.370 --> 03:24.330 And it is SQL server specific. 64 03:24.770 --> 03:25.730 It is written. 65 03:25.890 --> 03:28.290 This one will work in SQL server only. 66 03:29.010 --> 03:34.290 It might not work in PostgreSQL as well as Oracle SQL. 67 03:34.330 --> 03:34.770 Right. 68 03:36.930 --> 03:42.530 So let me copy the solution and let me see okay. 69 03:42.530 --> 03:45.850 Let me run both of this solution together. 70 03:47.250 --> 03:47.490 Okay. 71 03:47.530 --> 03:49.490 It is perfectly working. 72 03:49.490 --> 03:49.970 Right. 73 03:50.850 --> 03:55.810 So in the second uh solution it has done the pivot right. 74 03:56.170 --> 03:58.210 So over here it has done the pivot. 75 03:58.450 --> 04:03.250 So it is also it is also showing the solution using. 76 04:03.290 --> 04:03.890 Pivot. 77 04:05.810 --> 04:09.490 So subquery provides country and middle type. 78 04:10.330 --> 04:13.330 Pivot covers unique values in middle type. 79 04:13.810 --> 04:14.930 Into columns. 80 04:17.610 --> 04:19.840 So what what will pivot do over here? 81 04:20.560 --> 04:24.920 Whichever the metal type, there are distinct metal types will be there, right? 82 04:25.280 --> 04:27.640 Like gold, silver and bronze. 83 04:27.880 --> 04:33.920 So this metal type value, it will it is converting it to uh column column base. 84 04:33.920 --> 04:34.240 Right. 85 04:34.240 --> 04:42.640 Because count of metal type for metal type in whichever the distinct metal type we are giving over here, 86 04:42.640 --> 04:49.120 it will generate those particular columns from gold, silver and bronze right over here. 87 04:49.880 --> 04:53.280 Now, now what it will do. 88 04:53.320 --> 04:54.520 Count of metal type counts. 89 04:54.520 --> 04:58.920 Metal types of each type in each null replaces null with zero. 90 04:59.840 --> 05:01.520 Both queries give same result. 91 05:01.520 --> 05:01.960 Right? 92 05:03.400 --> 05:10.040 So pivot will will be SQL server specific and above one will be working in all the databases. 93 05:10.040 --> 05:12.880 It is shown that thing as well right. 94 05:13.240 --> 05:18.920 Now would you like me to extend this query to also show the total medals per country? 95 05:19.960 --> 05:22.960 So let me try. 96 05:23.310 --> 05:24.590 Uh, let me see. 97 05:24.630 --> 05:25.150 Yes. 98 05:27.590 --> 05:31.710 Now it will give additional one column with the total. 99 05:31.750 --> 05:34.110 Total medals per country as well. 100 05:34.150 --> 05:34.710 Right. 101 05:34.710 --> 05:37.470 So see, it has given additional one column. 102 05:38.790 --> 05:43.150 USA has won two gold and one silver in total will be three. 103 05:43.750 --> 05:48.990 China has won one gold and two bronze in total is three. 104 05:48.990 --> 05:53.190 So what additional thing it is it has done over here? 105 05:53.910 --> 05:55.950 Just count of star only right? 106 05:56.150 --> 06:05.230 So let me copy this code and let me write it over here and let me check it is giving the same result 107 06:05.230 --> 06:07.510 or not perfect. 108 06:07.510 --> 06:09.190 It is giving the same result, right? 109 06:09.830 --> 06:11.790 It is simply doing the count of star. 110 06:13.910 --> 06:14.430 Over here. 111 06:14.430 --> 06:14.910 Right. 112 06:15.870 --> 06:22.110 So this is how we can use the ChatGPT in our problem statement, right? 113 06:22.750 --> 06:23.430 Thank you so much. 114 06:23.430 --> 06:26.230 We will see the next question into the next lecture. 115 06:26.590 --> 06:27.310 Thank you.