1 00:00.360 --> 00:02.720 Let's solve the SQL problem. 2 00:03.480 --> 00:07.440 Find manager with exactly five direct reports. 3 00:07.800 --> 00:14.120 So below is the problem statement using the employees table which contains employee ID and manager ID. 4 00:14.600 --> 00:19.480 Find the name of all managers who have exactly five direct reports. 5 00:20.240 --> 00:24.760 So this is my table structure and data. 6 00:24.760 --> 00:28.200 So let me share the same with the ChatGPT. 7 00:31.480 --> 00:39.200 So I am having the below table structure and data. 8 00:40.080 --> 00:46.280 So it will ask that please share your table structure and data with us. 9 00:46.280 --> 00:48.840 So I am sharing my table structure and data. 10 00:49.360 --> 00:50.480 Now let's see. 11 00:51.200 --> 00:53.760 Now I am sharing my problem statement. 12 00:54.200 --> 00:56.880 So this is my problem statement. 13 00:57.400 --> 01:00.400 So let me share the same with ChatGPT. 14 01:03.540 --> 01:04.340 Okay, fine. 15 01:07.100 --> 01:10.660 Now it will provide us the multiple. 16 01:10.700 --> 01:15.860 If if they have multiple solution, then it will provide us the multiple solution. 17 01:15.860 --> 01:19.580 Otherwise it will provide one specific solution. 18 01:19.580 --> 01:20.060 Right. 19 01:20.820 --> 01:27.140 Now let's see what it has provided. 20 01:27.740 --> 01:34.580 So see um it is showing the joining the same table multiple times. 21 01:38.380 --> 01:40.780 It is joining the same table multiple times. 22 01:41.660 --> 01:50.300 And with uh employee EMP table ID column is e dot manager ID column. 23 01:51.020 --> 01:54.900 So let me, uh, explain you how this works. 24 01:55.540 --> 01:56.380 So let me. 25 01:59.020 --> 02:01.940 Copy this table data into the Excel file. 26 02:02.660 --> 02:04.370 And then I will explain you. 27 02:07.170 --> 02:09.770 Okay, this is my first table. 28 02:09.770 --> 02:11.850 Or we can say first Elias. 29 02:11.850 --> 02:12.330 Right. 30 02:13.050 --> 02:23.730 So suppose if you see suppose this one is employee E table and this one is employee. 31 02:27.610 --> 02:28.450 EMP table. 32 02:28.850 --> 02:31.970 Now what is the condition over here? 33 02:37.010 --> 02:47.090 The condition over here is E dot manager ID is equal to m.id right. 34 02:47.130 --> 02:48.850 So now how it works. 35 02:48.890 --> 02:52.410 See this one will be our first record. 36 02:53.050 --> 02:54.570 And it has no manager. 37 02:54.570 --> 02:54.930 Right. 38 02:55.010 --> 02:59.730 So from this table it will be having no no data. 39 02:59.730 --> 03:03.730 We can say right because its manager ID is null. 40 03:04.230 --> 03:05.910 So over here it will be. 41 03:05.950 --> 03:07.190 Every record will be null. 42 03:07.230 --> 03:07.630 Right. 43 03:08.190 --> 03:09.870 And let me copy this. 44 03:13.830 --> 03:14.790 Thing as well. 45 03:16.190 --> 03:18.630 And this is my employee table. 46 03:19.550 --> 03:22.510 And this is my employee EMP table. 47 03:22.550 --> 03:23.030 Right. 48 03:25.710 --> 03:28.310 Now let's check for the 102 record. 49 03:28.630 --> 03:32.110 So 102 manager will be 101. 50 03:32.150 --> 03:32.670 Right. 51 03:34.790 --> 03:40.550 So we have to check that 101 in ID column in the employee table. 52 03:40.550 --> 03:40.950 Right. 53 03:41.270 --> 03:42.630 So it is over here. 54 03:43.070 --> 03:44.910 So we need to copy this one. 55 03:47.990 --> 03:56.150 Now again for all this record wherever the manager ID is 1001I am copying that one. 56 03:57.230 --> 04:00.470 And I am copying this one to all these four records. 57 04:02.110 --> 04:03.030 Perfect. 58 04:04.510 --> 04:13.890 Now again for check for this 1107 it is null manager ID, so obviously it will be null null null everywhere 59 04:13.890 --> 04:14.690 over here. 60 04:14.690 --> 04:15.770 And for this one. 61 04:19.450 --> 04:25.090 It is having 101107 as manager ID. 62 04:25.090 --> 04:29.610 So let me find the 107 in the ID column in the employee table. 63 04:30.250 --> 04:32.170 So it is over here right. 64 04:35.770 --> 04:38.010 Now let's see now. 65 04:40.730 --> 04:41.930 What we have to do. 66 04:42.210 --> 04:51.970 We have to check the ID column from the employee ID comma count of. 67 04:52.010 --> 04:54.610 We can say e.id right. 68 04:54.650 --> 04:56.610 We can write it like this one. 69 04:56.930 --> 04:59.850 And then group by m.id. 70 05:00.370 --> 05:09.320 And we want to find those employee which is having exactly five and five employees reporting to them. 71 05:10.360 --> 05:12.320 We have to find that particular manager. 72 05:12.320 --> 05:17.000 So what we have to do over here, we can do the filter over here, right. 73 05:17.760 --> 05:20.920 And we want to filter on the grouping. 74 05:21.320 --> 05:22.720 So how we can do it. 75 05:22.760 --> 05:28.360 Having count of e.id should be exactly five. 76 05:29.240 --> 05:29.760 Right. 77 05:29.760 --> 05:32.760 So this is the same thing it has done over here. 78 05:33.680 --> 05:36.640 See this is the same thing it has done over here. 79 05:37.120 --> 05:40.280 So this is the we can say solution. 80 05:43.360 --> 05:44.480 Provided by the. 81 05:47.160 --> 05:47.960 ChatGPT. 82 05:48.000 --> 05:48.480 Right. 83 05:51.560 --> 05:55.760 Now let's see whether it has provided any other solution. 84 05:55.760 --> 05:57.840 No it hasn't provided any other solution. 85 05:57.840 --> 05:58.240 Right. 86 05:58.840 --> 06:03.840 So this is how we can solve the problem using ChatGPT. 87 06:03.880 --> 06:06.360 We will see the next question into the next lecture. 88 06:06.800 --> 06:07.520 Thank you.