1 00:00.360 --> 00:02.320 Okay, let's see the another question. 2 00:02.720 --> 00:05.600 List all employees in each department. 3 00:05.920 --> 00:07.800 So below is the problem statement. 4 00:07.840 --> 00:14.800 Given a table of employees and their departments, write a query to return a single row for each department 5 00:14.800 --> 00:20.280 that lists all employees in that department as a single comma separated string. 6 00:21.040 --> 00:26.200 So over here I am aware about the string aggregate function will be helpful in this particular case, 7 00:26.200 --> 00:26.640 right. 8 00:27.080 --> 00:30.360 So let me ask the ChatGPT about the same. 9 00:30.920 --> 00:33.880 Let's see what solutions it is providing. 10 00:34.280 --> 00:43.840 So I am having the table structure and data as per below. 11 00:44.640 --> 00:48.080 Okay, I am asking the same question every time. 12 00:52.600 --> 00:53.720 To ChatGPT. 13 00:53.920 --> 00:54.880 Now let's see. 14 00:58.600 --> 01:04.790 Okay, now I am Giving my table structure and data to the ChatGPT. 15 01:05.350 --> 01:07.790 And let me ask the question. 16 01:12.830 --> 01:19.350 Okay, this is my problem statement, I can say, and I'm asking this to ChatGPT. 17 01:22.470 --> 01:22.910 Okay. 18 01:22.950 --> 01:27.070 This is a string aggregation problem commonly asked in SQL interviews. 19 01:27.470 --> 01:35.190 You want to list all employees, all employees of each department in a comma separated string, one 20 01:35.190 --> 01:36.150 row per department. 21 01:36.150 --> 01:36.830 Perfect. 22 01:37.190 --> 01:38.830 Let's solve it step by step. 23 01:39.350 --> 01:39.950 Okay. 24 01:40.230 --> 01:40.630 Okay. 25 01:40.670 --> 01:42.190 This should be my output, right? 26 01:42.470 --> 01:46.670 So if you see over here in engineering department, these three employees are working. 27 01:46.990 --> 01:47.710 So Alice. 28 01:47.750 --> 01:48.350 Comma. 29 01:48.390 --> 01:49.030 Bob comma. 30 01:49.030 --> 01:49.670 Charlie. 31 01:49.710 --> 01:53.790 That should be my first output. 32 01:53.830 --> 01:57.150 And for HR department David in Eve is working. 33 01:57.150 --> 01:57.670 Right. 34 01:57.670 --> 02:01.460 So HR department David comma if should be my output. 35 02:02.020 --> 02:07.460 Okay, now it has provided the solution using SQL server. 36 02:07.460 --> 02:08.020 So in. 37 02:08.300 --> 02:14.060 I told right SQL server string aggregate function is available to. 38 02:16.780 --> 02:19.660 Do this kind of problem right. 39 02:20.660 --> 02:23.540 So it it will solve our problem right. 40 02:23.900 --> 02:26.420 Now this solution. 41 02:29.500 --> 02:33.180 Provided by ChatGPT. 42 02:33.220 --> 02:34.940 Solution provided by ChatGPT. 43 02:35.420 --> 02:36.060 Okay. 44 02:36.100 --> 02:38.060 Now let's see whether anything else. 45 02:38.980 --> 02:41.780 So see what string aggregate will do over here. 46 02:42.500 --> 02:45.620 Combines multiple values into a single string. 47 02:47.260 --> 02:49.900 Combines multiple values into a single string. 48 02:50.140 --> 02:51.300 Group by department. 49 02:51.300 --> 02:58.260 Ensures one result per department perfect, and the comma separator adds commas with space for readability. 50 02:58.900 --> 02:59.380 Right? 51 03:00.040 --> 03:03.440 Now this group is not available in SQL server. 52 03:03.800 --> 03:07.120 So this is available for MySQL. 53 03:07.160 --> 03:12.920 You can say so alternative solutions for other SQL engines right. 54 03:12.960 --> 03:17.280 Then for PostgreSQL again string aggregate will be available. 55 03:17.320 --> 03:18.280 Okay fine. 56 03:18.800 --> 03:21.560 For Oracle use list aggregate okay. 57 03:22.720 --> 03:26.440 So for all the different databases it has provided the solution. 58 03:26.440 --> 03:26.920 Right. 59 03:30.000 --> 03:31.840 Now at the end what it is saying. 60 03:31.880 --> 03:38.600 Would you like me to show how to sort the employee names alphabetically inside each department. 61 03:39.680 --> 03:40.400 Okay. 62 03:41.480 --> 03:45.520 So I think it is already sorted. 63 03:45.520 --> 03:45.920 Right. 64 03:45.920 --> 03:46.920 Let me see. 65 03:47.400 --> 03:49.040 Yes, it is already sorted. 66 03:50.120 --> 03:52.480 But still let me write. 67 03:52.480 --> 03:52.760 Yes. 68 03:52.760 --> 03:53.280 Over here. 69 03:53.280 --> 03:53.560 Right. 70 03:53.560 --> 03:56.840 Let me check what it is doing. 71 03:59.230 --> 04:00.950 So let's see. 72 04:02.310 --> 04:02.710 Um. 73 04:06.070 --> 04:07.790 See within group. 74 04:07.790 --> 04:11.070 Let me try whether it works in SQL server or not. 75 04:11.830 --> 04:12.950 String aggregate. 76 04:12.990 --> 04:15.950 That is fine within group order by EMP name. 77 04:15.950 --> 04:16.430 Right. 78 04:16.430 --> 04:19.630 So this is this is one additional thing over here. 79 04:24.870 --> 04:25.190 Okay. 80 04:25.230 --> 04:26.950 That is one additional thing over here. 81 04:26.950 --> 04:31.550 So let me check whether it works or not. 82 04:33.670 --> 04:33.950 Okay. 83 04:33.990 --> 04:34.390 Perfect. 84 04:34.390 --> 04:41.430 It is working right because my SQL server version version is, uh, greater than 2017. 85 04:41.430 --> 04:41.870 Right. 86 04:41.910 --> 04:43.750 That is why it is working over here. 87 04:46.910 --> 04:47.390 Right. 88 04:47.390 --> 04:53.630 So this is how we can use the ChatGPT in our day to day life if it is required. 89 04:53.790 --> 04:54.350 Right. 90 04:55.070 --> 04:56.390 So thank you so much. 91 04:56.390 --> 04:58.630 We will see the next question into the next lecture. 92 04:58.630 --> 04:59.310 Thank you.