Employee Table having Manager & Salary Data
CREATE TABLE dbo.Employee (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(100),
Gender CHAR(1),
Department VARCHAR(50),
Salary INT,
JoiningDate DATE,
ManagerID INT,
IsActive BIT
);
GO
INSERT INTO [dbo].[Employee]
([EmpID], [EmpName], [Gender], [Department], [Salary], [JoiningDate], [ManagerID], [IsActive])
VALUES
(101, N'Shaun Abbot', N'M', N'IT', 88000, '2020-03-15', 106, 1),
(102, N'Stefee Taylor', N'F', N'HR', 88000, '2020-07-01', 105, 1),
(103, N'Mathew Bulmer', N'M', N'Finance', 75000, '2021-01-20', 104, 1),
(104, N'Jennat Warner', N'F', N'Finance', 98000, '2021-10-05', 103, 1),
(105, N'Jonathan Perry', N'M', N'HR', 95000, '2019-12-12', 104, 1),
(106, N'Lisa Devine', N'F', N'IT', 85000, '2021-06-11', 101, 1),
(107, N'Adam Trumen', N'M', N'IT', 50000, '2019-04-01', 101, 1),
(108, N'Sarah Mclean', N'F', N'Marketing', 95000, '2023-01-11', 104, 1),
(109, N'Steve Maxwell', N'M', N'IT', 75000, '2025-12-01', 101, 1),
(110, N'Georgia Havells', N'F', N'Finance', 45000, '2025-09-01', 104, 1);
GO
Query to Find Employees Having Salary Greater Than Their Manager:
SELECT
E.EmpID,
E.EmpName,
E.Department,
E.Salary AS EmployeeSalary,
M.EmpName AS ManagerName,
M.Salary AS ManagerSalary
FROM
dbo.Employee E
JOIN dbo.Employee M ON E.ManagerID = M.EmpID
WHERE
E.Salary > M.Salary;
Query Explanation:
- This query retrieves a list of employees who earn more than their managers.
- It uses a self-join on the dbo.Employee table:
- E represents the employee.
- M represents the manager (joined using E.ManagerID = M.EmployeeID).
- It selects the following columns:
- E.EmployeeID, E.EmployeeName, E.Department, and E.Salary as EmployeeSalary.
- M.EmployeeName as ManagerName and M.Salary as ManagerSalary.
- The WHERE clause filters only those records where the employee’s salary is greater than their manager’s salary.
