Tags
Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL, SQL 2012, SQL Challenge, SQL Challenges, SQL pl/sql puzzles, SQL Puzzles, SQL SERVER Interview questions, SQL SERVER2005/2008, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
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
Improving my SQL BI Skills 
Possible solution using a subquery as well.
select * from dbo.EmpSalaryGreaterManager e1
where EmpSalary > (select EmpSalary from dbo.EmpSalaryGreaterManager e2 where e1.MgrID = e2.EmpID);
LikeLiked by 1 person
SELECT * FROM [dbo].[EmpSalaryGreaterManager]
WHERE [EmpSalaryGreaterManager].EmpSalary >
(
SELECT EmpSalary FROM [dbo].[EmpSalaryGreaterManager]
where mgrid is null
) and mgrid is not null
LikeLike