1
00:01.120 --> 00:02.120
Hello everyone.

2
00:03.880 --> 00:04.840
Good morning all.

3
00:06.320 --> 00:07.920
So welcome back to my channel.

4
00:08.520 --> 00:10.720
So let's solve another SQL problem.

5
00:12.280 --> 00:19.760
As a data analyst on the Oracle Sales Operation team, you are given a list of sales people, deals

6
00:20.400 --> 00:22.840
and annual quotas they need to hit.

7
00:24.480 --> 00:32.320
Write a query that outputs each employee ID and whether whether they hit the quota or not.

8
00:33.600 --> 00:36.960
Order the results table by employee ID in ascending order.

9
00:37.520 --> 00:40.400
So here they have given us the definition of deal size.

10
00:40.840 --> 00:48.840
So deals acquired by a sales person in the year in each salesperson may have more than one deal in total

11
00:48.880 --> 00:52.160
annual quota for each sales person.

12
00:52.760 --> 00:55.120
So here they have given us the two different tables.

13
00:55.120 --> 01:03.680
One is deals table, in which employee ID and deal size is given, and another one is a sales quota

14
01:03.720 --> 01:04.240
table.

15
01:05.540 --> 01:09.380
in which an employee ID again is given and quota is given.

16
01:10.020 --> 01:13.500
So employee ID if you see it, is a common column.

17
01:16.060 --> 01:17.700
In both the tables right.

18
01:21.620 --> 01:24.420
So I have inserted the data into the same.

19
01:29.100 --> 01:32.700
So let's see both the tables deals table and quotas table.

20
01:33.260 --> 01:34.980
So employee ID is common right.

21
01:35.020 --> 01:41.260
So over here if you see multiple employee ID are repeating multiple times.

22
01:41.860 --> 01:45.460
So here we need to do the sum of deal size right.

23
01:47.260 --> 01:49.780
Sum of deal size per each employee ID.

24
01:49.820 --> 01:50.300
Right.

25
01:51.140 --> 01:51.540
So.

26
01:53.860 --> 01:54.820
So here I have.

27
01:56.860 --> 02:07.660
Done the inner join to calculate whether whether a deal size is less than quota or not.

28
02:08.600 --> 02:13.040
So if you see over here, I have done the sum.

29
02:14.960 --> 02:18.680
So over here for 101 you have seen over here right.

30
02:19.200 --> 02:20.480
For 101.

31
02:24.040 --> 02:26.520
It is four lakhs plus three lakh.

32
02:27.000 --> 02:28.720
So it is seven lakh, right?

33
02:33.080 --> 02:33.640
Yes, right.

34
02:33.640 --> 02:34.680
It is seven lakh.

35
02:35.200 --> 02:40.000
Now we need to see whether seven lakh is less than quota or not.

36
02:40.040 --> 02:40.480
So.

37
02:43.240 --> 02:47.200
For 101 it is seven lakh and 101 it is five lakh, right?

38
02:49.080 --> 02:53.760
So seven lakh is less than five lakh then.

39
02:56.720 --> 03:01.560
We can say quota made is yes.

40
03:01.840 --> 03:02.240
How?

41
03:05.760 --> 03:11.130
So if you run again both the queries here seven lakh.

42
03:11.130 --> 03:13.410
And then let me run this one also.

43
03:16.690 --> 03:18.290
Which is less than quota.

44
03:18.290 --> 03:18.730
Right.

45
03:19.610 --> 03:25.690
So what we have done, if seven lakhs is less than quota, then sorry.

46
03:25.730 --> 03:26.290
Deal size.

47
03:26.330 --> 03:26.810
Deal size.

48
03:26.810 --> 03:27.690
We need to check.

49
03:30.570 --> 03:30.770
Yeah.

50
03:30.810 --> 03:35.330
Deal size is five lakh and this is seven lakh.

51
03:38.050 --> 03:41.210
So deal size is less than this seven lakh, right?

52
03:41.890 --> 03:43.050
Then our result should be.

53
03:43.050 --> 03:43.570
Yes.

54
03:53.770 --> 03:54.370
Right.

55
03:56.610 --> 03:59.650
So this is how you can solve the SQL problem.

56
04:04.610 --> 04:11.050
I will put the script, create table script and insert table script into the description box of the

57
04:11.050 --> 04:11.610
video.

58
04:13.450 --> 04:14.170
Thank you.