1
00:00.320 --> 00:02.280
Let's see another SQL problem.

2
00:02.680 --> 00:07.640
Find employees who have joined before their manager.

3
00:09.320 --> 00:11.520
So below is the problem statement.

4
00:11.680 --> 00:18.520
Given a single table of employees that contain their higher date and their manager's ID, write a query

5
00:18.520 --> 00:22.840
to find all employees who were hired before their direct manager.

6
00:25.160 --> 00:26.800
Okay, so let me.

7
00:28.960 --> 00:30.920
Copy this table structure in data.

8
00:31.560 --> 00:38.720
So I am having below table structure and data.

9
00:39.720 --> 00:42.040
I am asking the ChatGPT the same thing.

10
00:42.720 --> 00:43.680
Now let's see.

11
00:45.240 --> 00:48.480
Okay I am sharing my table structure and data with ChatGPT.

12
00:49.360 --> 00:56.400
And now given a single table of employees that contain their higher date and their manager's ID.

13
00:56.720 --> 01:00.160
Okay, so this is my problem statement.

14
01:02.930 --> 01:03.810
Find.

15
01:03.850 --> 01:07.170
Let me ask the ChatGPT about the same.

16
01:10.930 --> 01:20.370
And let's see whether our result is same as the solution provided by the ChatGPT or not.

17
01:20.810 --> 01:22.730
So I am copying this one.

18
01:26.810 --> 01:27.690
Okay, fine.

19
01:27.690 --> 01:36.210
So our this above one is our solution and this one is solution provided by ChatGPT.

20
01:36.250 --> 01:36.690
Right.

21
01:43.170 --> 01:43.770
Okay fine.

22
01:43.770 --> 01:45.370
So both are the same right.

23
01:45.530 --> 01:46.090
Both.

24
01:46.130 --> 01:47.490
The are both.

25
01:47.490 --> 01:51.610
The query is almost the same over here.

26
01:51.610 --> 01:53.530
They have used the join.

27
01:53.530 --> 01:56.490
It means it will be a default join which is inner join.

28
01:56.730 --> 02:00.330
And over here I have used the left join right.

29
02:00.330 --> 02:02.380
So let me execute both the query?

30
02:02.860 --> 02:03.940
Then I will explain.

31
02:03.940 --> 02:11.220
You see, only Alice is the employee who is joining before their manager, right?

32
02:13.020 --> 02:15.180
Who is joining before their manager.

33
02:16.220 --> 02:21.740
So now let me explain you in the Excel file, the same thing.

34
02:21.740 --> 02:26.260
So let me copy this data in the Excel file.

35
02:30.020 --> 02:32.140
And I am copying this data two times.

36
02:32.140 --> 02:32.620
Right.

37
02:34.500 --> 02:42.940
Because I want to because there are actually one table but which has given the two different aliases.

38
02:43.260 --> 02:43.540
Right.

39
02:43.540 --> 02:44.820
So this is we can say.

40
02:48.260 --> 02:51.020
Employee E and this one we can say.

41
02:54.020 --> 03:06.270
Employee M now let me copy this thing over here and let me copy this thing over here because I want

42
03:06.310 --> 03:09.430
the result set from both the tables.

43
03:09.430 --> 03:09.870
Right.

44
03:10.670 --> 03:12.110
This is my first table.

45
03:14.630 --> 03:16.710
And this is my second table.

46
03:17.830 --> 03:20.150
Actually, first table only, but different alias.

47
03:20.150 --> 03:20.670
Right.

48
03:20.950 --> 03:24.790
Now see how this table has been joined.

49
03:24.990 --> 03:25.950
That we have to check.

50
03:25.990 --> 03:26.430
Right.

51
03:26.590 --> 03:30.870
So it is joining on bass on this particular condition.

52
03:30.870 --> 03:34.790
And let me copy this condition as well over here.

53
03:34.790 --> 03:37.390
So C on E dot manager ID.

54
03:37.390 --> 03:39.350
So where is E is available.

55
03:39.710 --> 03:41.590
Employee E is available over here.

56
03:41.590 --> 03:41.870
Right.

57
03:41.870 --> 03:43.430
So E dot manager ID.

58
03:44.550 --> 03:50.110
So let me first copy this entire row from the employee table.

59
03:50.270 --> 03:52.030
And this E dot manager ID.

60
03:52.070 --> 03:58.790
We have to check this in this employee EMP table in employee ID column.

61
03:58.790 --> 03:59.230
Right.

62
03:59.630 --> 04:03.030
This manager ID we have to check it over there.

63
04:03.030 --> 04:04.510
So it is available over here.

64
04:04.510 --> 04:04.950
Right.

65
04:05.270 --> 04:07.430
So let me copy this thing.

66
04:08.910 --> 04:09.310
Okay.

67
04:09.350 --> 04:10.110
Perfect.

68
04:10.390 --> 04:14.870
Now let's see for the second record.

69
04:14.870 --> 04:18.510
So for the second record it will be also same thing.

70
04:18.510 --> 04:18.830
Right.

71
04:18.830 --> 04:24.110
Because uh, Bob manager is also the three manager ID three is right.

72
04:24.350 --> 04:28.750
So let me copy this same thing over here for the next row as well.

73
04:29.110 --> 04:31.110
Now let's look for the Charlie.

74
04:31.350 --> 04:33.350
So Charlie manager ID is four.

75
04:33.870 --> 04:40.870
Now we have to identify for manager ID into the employee ID column of employee EMP table.

76
04:41.150 --> 04:43.150
So it is available over here right.

77
04:43.230 --> 04:48.110
So let me do copy over here for this one.

78
04:48.110 --> 04:48.830
It is null.

79
04:49.230 --> 04:53.270
So it will be null only right.

80
04:53.510 --> 04:58.070
And now for employee ID five manager ID is four.

81
04:58.070 --> 05:02.710
So again I have to copy employee ID for details over here.

82
05:02.800 --> 05:04.200
right now.

83
05:04.200 --> 05:09.040
What we have to do now, we have to take, consider, consider this condition.

84
05:11.000 --> 05:11.720
Right.

85
05:11.960 --> 05:19.960
And this condition is saying where e dot higher date where E dot higher date is less than M dot higher

86
05:19.960 --> 05:20.360
date.

87
05:20.920 --> 05:27.600
So 15th of January which is less than first of.

88
05:30.640 --> 05:31.120
August.

89
05:31.120 --> 05:31.360
Right.

90
05:31.360 --> 05:33.640
So we can consider this Alice.

91
05:34.240 --> 05:35.080
Alice record.

92
05:35.080 --> 05:40.560
Let me highlight it with green and check for the bob.

93
05:40.600 --> 05:41.720
It is 2023.

94
05:41.760 --> 05:43.720
It is not less than right.

95
05:43.760 --> 05:46.560
Again this one is not less than this one.

96
05:46.560 --> 05:47.520
We cannot compare.

97
05:47.520 --> 05:49.400
And this one is also not less than.

98
05:49.760 --> 05:50.640
This is greater than.

99
05:50.640 --> 05:51.080
Right.

100
05:51.360 --> 05:52.760
So only Alice.

101
05:53.240 --> 06:02.880
Alice is the employee whose higher date is less than his manager's higher date.

102
06:02.880 --> 06:03.320
Right?

103
06:06.130 --> 06:09.410
So our answer is perfect.

104
06:09.410 --> 06:10.570
One we can say right.

105
06:15.650 --> 06:17.170
Now let's see over here.

106
06:17.730 --> 06:20.250
Is there any other solution it has provided.

107
06:20.250 --> 06:20.890
It is.

108
06:21.450 --> 06:23.050
We are doing self-join over here.

109
06:23.050 --> 06:23.250
Right.

110
06:23.250 --> 06:24.410
That is perfect.

111
06:24.410 --> 06:28.170
Only Alice was hired before her manager, Charlie.

112
06:28.170 --> 06:28.650
Right.

113
06:29.330 --> 06:30.130
Perfect.

114
06:32.930 --> 06:37.770
So this is how we can solve the problem using ChatGPT.

115
06:37.770 --> 06:41.970
But ChatGPT will not explain you the way I have explained over here.

116
06:41.970 --> 06:42.410
Right?

117
06:43.010 --> 06:43.570
It is.

118
06:43.770 --> 06:45.610
And that is the limitation of ChatGPT.

119
06:45.650 --> 06:49.970
We can say right, but it has provided the correct solution.

120
06:49.970 --> 06:51.570
That is fine, right?

121
06:51.610 --> 06:58.250
So that you can use in your problems solution.

122
06:58.250 --> 06:58.730
Right.

123
06:59.330 --> 07:03.250
So we will see the next question into the next lecture.

124
07:03.250 --> 07:03.850
Thank you.