1
00:02.160 --> 00:04.160
So let's solve another SQL problem.

2
00:04.920 --> 00:15.080
You are you are tasked with identify subject matter experts at Accenture based on their work experience

3
00:15.080 --> 00:16.560
in specific domains.

4
00:17.520 --> 00:23.320
An employee qualifies as an SME if they meet either of the following criteria.

5
00:24.240 --> 00:31.680
They have eight or more years of work experience in a single domain, and they have 12 or more years

6
00:31.680 --> 00:34.200
of experience across two different domains.

7
00:35.000 --> 00:41.360
Write a query to return the employee IDs of all the subject matter experts at Accenture.

8
00:42.680 --> 00:52.480
Assumption an employee can only be considered an SME if they meet the experience requirements in either

9
00:52.480 --> 00:54.040
1 or 2 domains.

10
00:55.000 --> 01:02.350
Cases where an employee has experience in more than two domains can be disregarded.

11
01:02.470 --> 01:12.790
Okay, so over here we need to identify if the employee is having experience in a single domain with

12
01:12.790 --> 01:15.110
more than eight years of experience.

13
01:17.350 --> 01:24.550
And if the employee is having experience in two different domains at that time, they should have experience

14
01:24.550 --> 01:28.510
of 12 years or more.

15
01:28.710 --> 01:35.350
But we do not need to consider if the employee is having the experience in two or more different domains.

16
01:38.030 --> 01:44.750
An employee can only be considered an SME if they meet the experience requirement in either 1 or 2 domains.

17
01:46.510 --> 01:47.510
Yeah, right.

18
01:47.510 --> 01:48.670
This is the assumption.

19
01:49.070 --> 01:55.350
Now I have a table create table employee expertise table.

20
01:55.390 --> 02:01.220
Right right I have this particular Table and let me insert the data into the same.

21
02:03.540 --> 02:05.700
Let me insert the data into the same.

22
02:06.860 --> 02:07.580
Now.

23
02:09.940 --> 02:10.780
Over here.

24
02:10.780 --> 02:15.820
This should be our first criteria that.

25
02:18.620 --> 02:21.740
So let me just select this particular table.

26
02:21.740 --> 02:25.620
First select from.

27
02:29.940 --> 02:32.300
This table employee table.

28
02:32.780 --> 02:36.420
So see let me do order by.

29
02:43.420 --> 02:44.980
Order by employee ID.

30
02:45.460 --> 02:52.900
So see this 101 is having a year of experience of nine years in single domain.

31
02:53.220 --> 03:01.530
Now 102 His experience of, uh, 12 or more years in two different domains.

32
03:02.090 --> 03:04.250
Now, 103 cannot be considered.

33
03:04.450 --> 03:09.930
Right now, 104 is having also cannot be considered.

34
03:09.930 --> 03:14.010
105 also cannot be considered, and 106.

35
03:16.850 --> 03:17.810
Can be considered.

36
03:17.810 --> 03:28.250
Right now, if you see over here how many employee ID is having more than eight years of experience

37
03:28.250 --> 03:32.530
in a single domain, so we can say 101.

38
03:35.170 --> 03:41.570
Then 102 okay, cannot be considered 103 cannot be considered 104.

39
03:44.650 --> 03:46.570
So it should be.

40
03:47.850 --> 03:49.810
Let me run both of the query.

41
03:50.490 --> 03:53.130
So it should be 101 and 106.

42
03:53.130 --> 03:56.040
Right 101 and 106.

43
03:56.640 --> 03:58.480
Now the second criteria.

44
03:58.800 --> 04:03.120
Let's see the second criteria.

45
04:03.720 --> 04:16.120
So over here we are considering those employee who has more than 12 hours of experience in a two different

46
04:16.120 --> 04:16.560
domains.

47
04:16.560 --> 04:17.000
Right.

48
04:17.040 --> 04:24.400
Having count of distinct domain equal to two and sum of year of experience greater than or equal to

49
04:24.480 --> 04:24.880
12.

50
04:25.560 --> 04:27.280
So that is.

51
04:32.680 --> 04:36.160
102 only.

52
04:36.160 --> 04:37.280
Right 102.

53
04:41.120 --> 04:41.400
Right.

54
04:41.400 --> 04:43.360
So total it will be three employees.

55
04:43.360 --> 04:43.800
Right.

56
04:44.280 --> 04:48.360
Now let's see what is our requirement.

57
04:50.120 --> 04:52.040
Our requirement is total.

58
04:52.080 --> 04:54.630
How many employees we are getting over here.

59
04:55.430 --> 05:04.350
So the condition which is satisfying the first criteria is the two employees 101 and 106.

60
05:05.110 --> 05:12.110
Whereas condition which is satisfying the second criteria which is having the one employee employee

61
05:12.110 --> 05:14.030
ID 102.

62
05:14.750 --> 05:18.910
So our result set should be having three different rows right.

63
05:20.190 --> 05:21.350
Three different rows.

64
05:22.190 --> 05:22.710
Right.

65
05:23.590 --> 05:25.670
So this will be our result set.

66
05:25.990 --> 05:36.270
So over here we have used the multiple city concept as well as the aggregate with the having close concept.

67
05:36.270 --> 05:38.790
And as well as we have used the union all concept.

68
05:38.790 --> 05:39.230
Right.

69
05:42.070 --> 05:43.710
So thank you so much.

70
05:43.950 --> 05:49.230
We will solve the another few more queries in coming sessions.

71
05:49.390 --> 05:50.110
Thank you.