1 00:01.360 --> 00:02.280 Hello everyone. 2 00:03.040 --> 00:04.040 Good morning all. 3 00:05.080 --> 00:08.120 So let's have solve another SQL queries. 4 00:09.360 --> 00:11.680 They've given us the table employee table. 5 00:12.120 --> 00:16.160 And in the employee table employee ID and team ID column is given. 6 00:16.840 --> 00:19.480 Employee ID is the primary key for this table. 7 00:20.240 --> 00:25.760 Each row of this table contains the ID of each employee and their respective team. 8 00:26.760 --> 00:27.040 Right. 9 00:27.080 --> 00:31.400 A solution to find the team size of each of the employees. 10 00:33.440 --> 00:35.680 Return the result table in any order. 11 00:37.120 --> 00:39.920 The result format is in the following example. 12 00:41.880 --> 00:48.200 Here what we need to find right solution to find the team size of each of the employee. 13 00:49.960 --> 00:52.880 Now if you see the sample employee table is given. 14 00:53.400 --> 00:56.440 So employee ID 123456 is given in. 15 00:56.480 --> 00:58.240 Team ID is given. 16 00:58.840 --> 01:03.340 So now we need to find the team size of each of the employee. 17 01:04.100 --> 01:12.620 So if you see the team ID eight, so team ID eight is available three times, right. 18 01:13.220 --> 01:15.100 For employee ID one, two and three. 19 01:15.340 --> 01:20.140 So what is the team size of employee ID one? 20 01:20.620 --> 01:27.180 We can say that team size of employee ID one, two and three is three because it is coming three times 21 01:27.180 --> 01:28.820 over here right now. 22 01:28.860 --> 01:30.660 Team size for employee ID four. 23 01:32.820 --> 01:43.340 Is one because seven is coming only one time in team size for employee ID five and six is two because. 24 01:45.420 --> 01:48.740 Employee ID team ID nine is coming twice. 25 01:52.100 --> 01:52.660 Right. 26 01:53.060 --> 01:55.180 So below is the output. 27 01:55.500 --> 01:57.140 Employee ID one, two and three. 28 01:57.140 --> 02:02.750 Team size is 333, employee ID for team size is one and employee ID five and six. 29 02:03.270 --> 02:05.470 Team size is two. 30 02:05.510 --> 02:09.110 Because team ID nine is coming twice, right? 31 02:11.190 --> 02:16.030 So now I have created the table and inserted the data into the same. 32 02:16.470 --> 02:20.350 I will put the script into the description box of the video. 33 02:20.990 --> 02:23.270 Now how we can solve this problem? 34 02:24.150 --> 02:30.590 We just need to do the group by team ID and count of team ID because we want. 35 02:33.630 --> 02:38.070 How all of the team ID how many times it is repeating, right. 36 02:38.310 --> 02:39.870 So that is our requirement. 37 02:40.150 --> 02:45.950 So teams ID seven is coming once team ID eight is coming three times and team ID nine is coming two 38 02:45.950 --> 02:46.550 times. 39 02:47.310 --> 02:58.470 Now what we need to do now, we need to do the join of this script with our main employee table with 40 02:58.470 --> 03:03.050 based on the team ID column, so that only I have done over here. 41 03:05.170 --> 03:09.970 I have done the join between team ID of the employee table in inner query. 42 03:11.690 --> 03:12.250 Right? 43 03:12.770 --> 03:15.050 So based on the team ID column. 44 03:15.490 --> 03:20.090 So now if I run the query now it will give me for team ID one. 45 03:21.010 --> 03:24.490 It is two team ID two it is two team ID three. 46 03:25.250 --> 03:27.130 The team size will be three like that. 47 03:27.130 --> 03:28.010 It is giving. 48 03:30.850 --> 03:33.610 So this will be my output right. 49 03:36.290 --> 03:39.090 Now let's see the another SQL problem. 50 03:44.250 --> 03:49.770 So here they have given us the departments table and students table. 51 03:50.570 --> 03:53.890 So in the department table ID column is there. 52 03:53.890 --> 03:55.170 And name column is there. 53 03:56.290 --> 04:02.970 ID is the primary key for this table in the in the students table ID name in department ID column is 54 04:03.030 --> 04:03.470 given. 55 04:03.870 --> 04:08.350 So ID is the primary key in the students table as well. 56 04:08.350 --> 04:14.550 And the department ID will be foreign key which is matching with the department which is matching with 57 04:14.550 --> 04:16.910 the ID column of department table. 58 04:19.190 --> 04:26.230 Now find the ID in the name of all the students who are enrolled in the department that no longer exist. 59 04:28.550 --> 04:34.950 Here we need to find the ID and name of all the students who enrolled in departments that no longer 60 04:34.950 --> 04:35.390 exist. 61 04:35.390 --> 04:37.550 Return the result table in any order. 62 04:38.910 --> 04:41.350 So now this is the sample data over here. 63 04:41.870 --> 04:45.550 Departments table and students table. 64 04:48.870 --> 04:50.590 Now one, seven and 13. 65 04:51.110 --> 04:57.110 One, seven and 13 are there where they have enrolled. 66 04:59.590 --> 05:01.270 But now our requirement is. 67 05:04.410 --> 05:09.890 Now our requirement is we need to find 1477 74. 68 05:13.450 --> 05:14.250 In 33. 69 05:14.250 --> 05:14.570 Right. 70 05:14.570 --> 05:19.050 Because in that no students has been enrolled. 71 05:19.650 --> 05:23.450 So I have created the table and inserted the data into the same. 72 05:24.530 --> 05:24.890 Here. 73 05:24.890 --> 05:29.530 We just need to do left join between student table and department table. 74 05:29.530 --> 05:30.650 And we need to find. 75 05:33.970 --> 05:37.170 That d.id will be null because. 76 05:39.290 --> 05:44.970 That student's department ID should not be exist in the departments ID right. 77 05:44.970 --> 05:46.570 So that is our requirement. 78 05:47.290 --> 05:51.370 So here we need to do the left join between student and departments table. 79 05:51.370 --> 05:53.970 And then where d.id is null. 80 05:54.330 --> 06:02.970 So student ID two, four, three and seven has not been enrolled in any of the departments. 81 06:02.970 --> 06:03.450 Right. 82 06:06.980 --> 06:08.700 Which is no longer exist. 83 06:10.180 --> 06:13.420 So if you see one of the example from here, depart. 84 06:13.860 --> 06:15.260 Sorry, student I did two. 85 06:15.820 --> 06:16.740 So let me see. 86 06:16.780 --> 06:25.020 Student ID two is department ID is 14 but 14 Department does not exist that. 87 06:25.220 --> 06:27.060 So that is our requirement right. 88 06:29.020 --> 06:32.060 Now let's see for another example three. 89 06:32.700 --> 06:33.820 So three is two. 90 06:34.340 --> 06:36.980 Now what is the department ID of three. 91 06:38.300 --> 06:40.740 So department ID of three is. 92 06:45.380 --> 06:46.340 74. 93 06:47.220 --> 06:47.740 Right. 94 06:47.740 --> 06:50.740 So 74 does not exist in the department table. 95 06:51.220 --> 06:54.020 So that is our requirement right. 96 06:55.300 --> 06:57.180 So thank you so much everyone. 97 06:57.540 --> 07:02.020 We will meet again in the another SQL problem as soon as possible. 98 07:03.340 --> 07:04.100 Thank you.