SQL Tutorial: Find Employees Who Joined Before Their Managers
"In this SQL tutorial, we explore how to identify employees who joined the company before their managers. We'll create a sample table, insert data, and write a query to find employees who have been with the organization longer than their reporting managers. This is a common interview question and a practical query for HR analytics. Watch to learn how you can apply this query to your database!"
--mr. vijay ceo of a top hr firm was passing by the corridor when he heard one of the senior manager asking his
junior about the appraisal cycle near coffee machine. this made him curious about those employees who joined
earlier than their reporting managers. can you help mr. vijay in finding those employees who have joined before
their reporting managers
--1. Create the Table
CREATE TABLE EmployeeDetails (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(50),
ManagerID INT,
DateOfJoining DATE
);
--2. Insert Sample Data
INSERT INTO EmployeeDetails (EmpID, EmpName, ManagerID, DateOfJoining) VALUES
(1, 'Alice', NULL, '2015-01-01'), -- Alice is the CEO
(2, 'Bob', 1, '2016-03-15'), -- Bob reports to Alice
(3, 'Charlie', 2, '2017-06-20'), -- Charlie reports to Bob
(4, 'David', 1, '2016-02-10'), -- David reports to Alice
(5, 'Eva', 4, '2018-08-05'), -- Eva reports to David
(6, 'Frank', 2, '2016-01-10'); -- Frank reports to Bob
--3. Write the SQL Query
select * from EmployeeDetails
select emp.empid,emp.empname,emp.dateofjoining,mng.empname as managername,mng.dateofjoining as manager_doj
from EmployeeDetails emp
join EmployeeDetails mng on emp.managerid=mng.empid
where emp.dateofjoining (lessthan) mng.dateofjoining
Explanation
This query compares the joining dates of employees and their respective managers. It lists all employees
who joined the company before their managers.
Top 20% vs Bottom 20% of Students in SQL: NTILE vs TOP PERCENT
• Top 20% vs Bottom 20% of Students in SQL: ...
SQL Tricks: Find the 3rd Lowest Salary in Each Department with Two Powerful Methods!
• SQL Tricks: Find the 3rd Lowest Salary in ...
#SQLQueries
#EmployeeManagement
#DatabaseTutorial
#SQLJoin
#SQLDateFunctions
#DataAnalysis