1 00:00.360 --> 00:02.880 Let's jump into our first question. 2 00:03.960 --> 00:07.240 Percentage of students scoring over 90. 3 00:08.680 --> 00:10.200 So this is our problem. 4 00:10.800 --> 00:14.000 Now the below is the problem statement has been given. 5 00:14.400 --> 00:23.160 Calculate the percentage of students who have scored more than 90 marks in at least one subject. 6 00:24.720 --> 00:24.920 Right. 7 00:24.960 --> 00:26.480 This is our problem statement. 8 00:26.840 --> 00:30.680 Now they have given us the table student scores table. 9 00:31.080 --> 00:32.920 And these are the data. 10 00:33.200 --> 00:35.080 So now let's see what I am doing. 11 00:35.720 --> 00:43.320 I am copying this table structure and the data and asking to the ChatGPT. 12 00:43.840 --> 00:44.200 Right. 13 00:44.240 --> 00:44.640 That. 14 00:45.760 --> 00:46.440 Let's see. 15 00:48.480 --> 00:50.360 I have a table. 16 00:52.560 --> 00:54.800 And data with me. 17 00:55.880 --> 00:59.870 And it is as per below. 18 01:00.950 --> 01:01.950 Now let's see. 19 01:04.190 --> 01:08.270 So it will ask you please share your table structure and data. 20 01:08.430 --> 01:08.990 Right. 21 01:09.550 --> 01:13.710 So now we are I am just pasting over here my table structure and data. 22 01:14.150 --> 01:16.870 Let's see what it happens. 23 01:20.350 --> 01:24.430 So I have pasted my table structure right now. 24 01:25.270 --> 01:31.470 ChatGPT will tell us that you have shared the table and the data for the students course table. 25 01:31.470 --> 01:31.950 Right. 26 01:32.390 --> 01:38.510 And before we move ahead, please tell me what you would like to find or analyze from this data. 27 01:39.070 --> 01:39.630 Right. 28 01:39.630 --> 01:46.030 So now what we we will do, we will ask our question or we will ask our problem statement. 29 01:46.270 --> 01:47.710 What is our problem statement? 30 01:47.750 --> 01:55.750 Our problem statement is calculate the percentage of unique students who have scored more than 90 marks 31 01:55.750 --> 01:57.270 in at least one subject. 32 01:57.610 --> 02:03.970 So now just let me copy this problem statement and paste it to ChatGPT. 33 02:04.130 --> 02:06.610 Now let's see what happened. 34 02:10.130 --> 02:10.530 Okay. 35 02:10.570 --> 02:11.730 Excellent question. 36 02:12.010 --> 02:15.930 This is a classic SQL analytical problem right now. 37 02:18.770 --> 02:21.050 Now let's restate what you need. 38 02:21.570 --> 02:24.970 Now it will restart our problem right. 39 02:25.330 --> 02:31.690 Calculate the percentage of unique student who scored more than 90 marks in at least one subject. 40 02:32.010 --> 02:39.130 So over here they have provided us the test step by step logic that you need to use count total unique 41 02:39.130 --> 02:39.730 student. 42 02:40.010 --> 02:42.690 So how can you count the total unique students? 43 02:43.410 --> 02:50.090 Obviously right count of distinct student ID now count of students who scored greater than 90 in any 44 02:50.090 --> 02:50.770 subject. 45 02:51.050 --> 02:55.960 So how we can get that those students who have scored greater than 90. 46 02:56.200 --> 02:59.400 Again count of distinct student. 47 02:59.400 --> 02:59.840 Right. 48 03:00.120 --> 03:06.920 So obviously we need to use the filter condition over here where Mark should be greater than 90. 49 03:08.040 --> 03:15.280 And to calculate the percentage what we have to do to calculate the percentage, it is showing that 50 03:15.680 --> 03:21.080 students scoring above 90 divided by total student and multiply by 100. 51 03:21.760 --> 03:22.160 Right. 52 03:22.240 --> 03:24.520 So suppose from this data set. 53 03:24.760 --> 03:29.000 From this data set let me show you the table over here. 54 03:30.080 --> 03:31.680 Select star from. 55 03:38.600 --> 03:40.320 From this data set. 56 03:43.920 --> 03:49.800 There are few students who has scored more than 90 in at least one subject that we have to identify. 57 03:50.240 --> 03:56.020 And total number of unique students that also we need to identify right now. 58 03:56.020 --> 03:59.780 See, they have provided us the solution, right? 59 04:00.020 --> 04:08.060 That below is the SQL query for the same works in SQL server, PostgreSQL, my SQL and BigQuery. 60 04:08.100 --> 04:14.580 They've told us that this query will work in all these four databases. 61 04:14.740 --> 04:19.580 So what I'm doing over here, I'm just copying code over here and pasting over here. 62 04:20.460 --> 04:27.740 Right above one is my solution and this one is the ChatGPT solution right now see. 63 04:29.980 --> 04:31.820 What they are doing over here. 64 04:32.100 --> 04:35.180 So they have given the explanation as well over here. 65 04:35.180 --> 04:35.700 Right. 66 04:35.940 --> 04:44.100 So case when so Mars greater than 90 then what we have to do, we have to do student ID and else we 67 04:44.100 --> 04:45.580 have to do nothing else. 68 04:45.580 --> 04:46.740 We have to do nothing. 69 04:47.180 --> 04:56.610 So we have to calculate those students whose mark is greater than 90, so that I have used the count 70 04:56.610 --> 04:57.810 function as well, right? 71 04:58.050 --> 05:05.410 ChatGPT has used the count function as well and count of distinct because we want unique students who 72 05:05.410 --> 05:08.090 has scored more than 90 in at least one subject. 73 05:08.090 --> 05:08.530 Right? 74 05:08.930 --> 05:11.610 And then it has done the multiply by 100. 75 05:12.130 --> 05:12.530 Right? 76 05:12.530 --> 05:14.650 Then it has done the multiply by 100. 77 05:15.010 --> 05:20.130 Now in the division part, what it is doing in the division part. 78 05:23.850 --> 05:30.210 C d division and multiplication by 100 ensure decimal precision. 79 05:32.650 --> 05:38.250 So this case when will include what include only students who scored above 90 right. 80 05:38.570 --> 05:46.050 Include this one will include only students who score more than 90 and below one ensures we count each 81 05:46.050 --> 05:51.350 student only once, even if they score greater than 90 in multiple subjects. 82 05:51.830 --> 05:57.950 So if the student has scored greater than 90 in multiple subjects, still we have to count it only once, 83 05:57.950 --> 05:58.590 right? 84 05:58.630 --> 06:03.350 That is why it has used the distinct over here and in the division part. 85 06:03.630 --> 06:06.590 We want total number of distinct student right. 86 06:07.430 --> 06:10.510 Not greater than 90 but total number of distinct student. 87 06:10.750 --> 06:13.110 And then we have done the multiply by 100. 88 06:13.750 --> 06:17.110 And what is done over here. 89 06:17.150 --> 06:19.630 See it is done over here rounding only. 90 06:19.630 --> 06:24.070 So it will give us the rounding of two decimal points. 91 06:24.070 --> 06:24.510 Right. 92 06:25.750 --> 06:27.990 So let me execute this query. 93 06:28.030 --> 06:30.190 See it is 40 right. 94 06:30.190 --> 06:31.910 40 is our answer over here. 95 06:32.230 --> 06:38.550 Now see above query is almost the same one but it is written by us. 96 06:38.750 --> 06:41.230 So let's see what it is giving. 97 06:41.830 --> 06:43.390 It is also giving the 40. 98 06:43.430 --> 06:47.780 It means the ChatGPT has provided the solution Allusion. 99 06:48.180 --> 06:50.100 That is exactly correct. 100 06:51.180 --> 06:51.660 Right. 101 06:51.660 --> 06:53.060 That is exactly correct. 102 06:53.060 --> 07:01.700 It is not necessary that ChatGPT will give always the correct solution, but we need to identify or 103 07:01.700 --> 07:06.580 we need to check whether their solution is as per our requirement or not. 104 07:06.620 --> 07:07.140 Right. 105 07:09.540 --> 07:12.380 You can see over here, uh, total unique students. 106 07:12.380 --> 07:18.420 They have mentioned 5Y5C, Alice, Bob, Charlie, David and you. 107 07:18.700 --> 07:23.580 These five students are there then students scoring greater than 90 in any subject. 108 07:24.020 --> 07:28.420 So of course Alice and Charlie. 109 07:28.420 --> 07:28.700 Right. 110 07:28.740 --> 07:29.820 Alice and Charlie. 111 07:30.100 --> 07:32.300 So see, Alice 95 inmates. 112 07:32.500 --> 07:38.540 So see Alice, 95 inmates and Charlie 991 inmates and 92 in science. 113 07:39.580 --> 07:42.140 See that thing it has provided, right? 114 07:43.060 --> 07:46.100 So it means total two students in divided by. 115 07:48.480 --> 07:50.680 Divided by total distinct student. 116 07:50.680 --> 07:51.200 Right. 117 07:52.040 --> 07:53.480 So two divided by five. 118 07:53.480 --> 07:54.560 Multiply by 100. 119 07:54.600 --> 07:55.560 It means 40. 120 07:55.960 --> 07:58.000 So it is perfectly curated. 121 07:58.000 --> 08:00.920 The answer it is perfectly written. 122 08:00.920 --> 08:02.160 The query for the US. 123 08:02.840 --> 08:07.640 So this is how we can use the ChatGPT in our day to day life. 124 08:08.080 --> 08:15.200 If it is required in real time environment, you cannot give your company data to ChatGPT, right? 125 08:15.240 --> 08:22.080 In that particular case, what you can do is you can generate some sample data by yourself, and then 126 08:22.680 --> 08:31.960 you can put that data into ChatGPT and then, uh, ask ChatGPT to provide the solution for the same 127 08:32.560 --> 08:34.160 or for the problem statement. 128 08:34.160 --> 08:34.680 Right. 129 08:34.960 --> 08:37.200 So this is how you can use ChatGPT. 130 08:37.880 --> 08:42.160 We will see the next query in the next lecture. 131 08:42.160 --> 08:42.880 Thank you.