TSQL – Interview Questions – 1

Interview Question 1:
How to get the complete employee hierarchy
First create the Employee Base table

Create table EmpBase
(
EmpID int primary key identity,
EmpName nvarchar(20),
ManagerID int
)
GO

Now, Insert some records in EmpBase table

Insert into EmpBase values ('Shane', NULL)
Insert into EmpBase values ('Sara', NULL)
Insert into EmpBase values ('Ryan', NULL)
Insert into EmpBase values ('Kimberly', NULL)
Insert into EmpBase values ('Dave', NULL)
Insert into EmpBase values ('Adam', NULL)
Insert into EmpBase values ('Ana', NULL)
Insert into EmpBase values ('John', NULL)
Insert into EmpBase values ('Neomi', NULL)
Insert into EmpBase values ('Scott', NULL)
Insert into EmpBase values ('Carlos', NULL)
GO
Update EmpBase Set ManagerID = 4 Where EmpName IN ('Shane', 'Sara', 'Ryan')
Update EmpBase Set ManagerID = 7 Where EmpName IN ('Kimberly', 'Dave','Adam')
Update EmpBase Set ManagerID = 9 Where EmpName IN ('Ana','John')
Update EmpBase Set ManagerID = 11 Where EmpName IN ('Neomi', 'Scott')
GO


Select * from EMPBase

Get the complete organisation Hierarchy for EMPID=2

Method 1:
Using Loop

DECLARE @EMPID INT=2
DECLARE @ID INT =@EMPID
DECLARE @Hierarcy TABLE
(Hierarcy varchar(50))
WHILE (@ID IS NOT NULL)
BEGIN
INSERT INTO @Hierarcy
Select EmpName from EmpBase
where EmpID =(Select ManagerID from EmpBase where EmpID=@ID)
SET @ID=(Select ManagerID from EmpBase where EmpID=@ID)
END
Select EmpName from EmpBase where EmpID=@EMPID
UNION ALL
Select * from @Hierarcy

Method 2:
Using Recursive CTE

Declare @EMPID int
Set @EMPID = 2;

WITH EmpCTE AS
(
Select EmpID, EmpName, ManagerID
From EmpBase
Where EmpID = @EMPID

UNION ALL

Select EmpBase.EmpID , EmpBase.EmpName, EmpBase.ManagerID
From EmpBase
JOIN EmpCTE
ON EmpBase.EmpID = EmpCTE.ManagerID
)

Select E1.EmpName,E2.EmpName as ManagerName
From EmpCTE E1
LEFT Join EmployeeCTE E2
ON E1.ManagerID = E2.EmpID

Interview Question 2:
Fetch the list of employees name and their managers name from below table


Select EE.EMPName [Employee Name],E.EMPName as [Manager Name]
From EMPBase E
Right JOIN EMPBase EE
ON E.EMPID=EE.ManagerID

Interview Question 3:
Fetch nth highest salary from Emp table

find 5th highest salary
There are three different ways to find 5th highest salary

Select top 1 Emp_Salary from
(Select top 5 Emp_Salary from EMP order by Emp_Salary desc) Salary
Order by Emp_Salary

Select Min(Emp_Salary) Emp_Salary from
(Select top 5 Emp_Salary from EMP order by Emp_Salary desc) Salary;

WITH SalaryCTE
AS
(
Select Emp_salary, Row_Number() OVER (Order by Emp_Salary desc)
as RN from EMP
)
Select Emp_Salary from SalaryCTE where RN=5

What if we have two employee with same salary and we have to find out distinct nth salary. In Emp table 8th highest and 9th highest has same salary so now if we want distinct 9th highest salary then our query should fetch the 10th highest salary . This can also be done in different ways

Select top 1 Emp_Salary from
(Select Distinct top 9 Emp_Salary from EMP order by Emp_Salary desc) Salary
Order by Emp_Salary

Or we can use DENSE_RANK() function

WITH SalaryCTE
AS
(
Select Emp_salary, DENSE_RANK() OVER (Order by Emp_Salary desc) as RN from EMP
)
Select Emp_Salary from SalaryCTE where RN=9


Happy Learning…!!!

Author: Sauras Pandey

Sauras Pandey is an MCP, SQL Server DBA/Architect with 7+ years of experience working in a variety of environments

Leave a Reply

Your email address will not be published.