Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [ Developer Salary & the Manager Puzzle ] – Write a query which will find the developers with salary greater than their manager.

Sample Input

EmpID EmpName EmpSalary MgrID
1 Pawan 80000 4
2 Dheeraj 70000 4
3 Isha 100000 4
4 Joteep 90000 NULL
5 Suchita 110000 4

Expected Output

EmpID EmpName EmpSalary MgrID
3 Isha 100000 4
5 Suchita 110000 4

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Send your solution to pawankkmr@gmail.com
  • Do not post you solution in comments section

Script

Use the below script to generate the source table and fill them up with the sample data.


--Create table
CREATE TABLE [dbo].[EmpSalaryGreaterManager]
(
[EmpID] [int] NULL,
[EmpName] [varchar](50) NULL,
[EmpSalary] [bigint] NULL,
[MgrID] [int] NULL
)
GO

--Insert Data
INSERT INTO [EmpSalaryGreaterManager](EmpID,EmpName,EmpSalary,MgrID)
VALUES
(1,    'Pawan',      80000, 4),
(2,    'Dheeraj',    70000, 4),
(3,    'Isha',       100000,       4),
(4,    'Joteep',     90000, NULL),
(5,    'Suchita',    110000,       4)

--Verify Data
SELECT * FROM [dbo].[EmpSalaryGreaterManager]

Update May 14 | Solutions



--


---------------------------------------
--Sol 1 | Pawan Kumar Khowal
---------------------------------------


SELECT e1.EmpID,e1.EmpName,e1.EmpSalary,e1.MgrID, e.EmpName Mgr FROM EmpSalaryGreaterManager e
INNER JOIN EmpSalaryGreaterManager e1 ON e.EmpID = e1.MgrID
WHERE e1.EmpSalary > e.EmpSalary

--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com

Advertisements