Another Question I came across few days ago. This question tests your understanding of SQL Hierarchical Queries and SELF JOIN. Given the following Employees table:
Note that this table has a certain hierarchy of employees and managers, such that a certain employee may also be a manager. For example, employee number 7 (Merlin Evans) is also the manager of Elroy Wilson, Charles Thomas, and Rudolph Roberts.
First Task – Create a query to display the name of all employees, and the name of their manager.
Second Task – Create a query to display the hierarchical relationship between a certain employee and his direct and indirect managers.
For example, for employee number 7, the query will display the following result (Merlin reports to Jose, and Jose reports to Alvin):

For employee number 10, the query will display the following result (Rudolph reports to Merlin, Merlin reports to Jose, and Jose reports to Alvin):
CREATE TABLE [dbo].[employees](
[employee_id] [int] NOT NULL,
[First_Name] [varchar](25) NULL,
[Last_Name] [varchar](25) NULL,
[manager_id] [int] NULL
)
GO
INSERT [dbo].[employees] ([employee_id], [First_Name], [Last_Name], [manager_id]) VALUES (1, N'Alvin', N'Smith', NULL)
GO
INSERT [dbo].[employees] ([employee_id], [First_Name], [Last_Name], [manager_id]) VALUES (2, N'Jose', N'Jones', 1)
GO
INSERT [dbo].[employees] ([employee_id], [First_Name], [Last_Name], [manager_id]) VALUES (3, N'Amado', N'Taylor', 1)
GO
INSERT [dbo].[employees] ([employee_id], [First_Name], [Last_Name], [manager_id]) VALUES (4, N'Stuart', N'Williams', 1)
GO
INSERT [dbo].[employees] ([employee_id], [First_Name], [Last_Name], [manager_id]) VALUES (5, N'Demarcus', N'Brown', 2)
GO
INSERT [dbo].[employees] ([employee_id], [First_Name], [Last_Name], [manager_id]) VALUES (6, N'Mark', N'Davies', 2)
GO
INSERT [dbo].[employees] ([employee_id], [First_Name], [Last_Name], [manager_id]) VALUES (7, N'Merlin', N'Evans', 2)
GO
INSERT [dbo].[employees] ([employee_id], [First_Name], [Last_Name], [manager_id]) VALUES (8, N'Elroy', N'Wilson', 7)
GO
INSERT [dbo].[employees] ([employee_id], [First_Name], [Last_Name], [manager_id]) VALUES (9, N'Charles', N'Thomas', 7)
GO
INSERT [dbo].[employees] ([employee_id], [First_Name], [Last_Name], [manager_id]) VALUES (10, N'Rudolph', N'Roberts', 7)
GO
--Q1-- SELECT E1.First_Name AS EmpName, E2.First_Name AS MangerName FROM employees E1, employees E2 WHERE E1.manager_id= E2.employee_id --SOL2 SQL-- WITH ManagerHierarchyTable(EmpID,EmpFirstName,ManagerID) AS ( SELECT e1.employee_id,e1.First_Name,e1.manager_id FROM employees e1 WHERE e1.employee_id = 10 UNION ALL SELECT e.employee_id, e.First_Name, e.manager_id FROM Employees e, ManagerHierarchyTable mht WHERE e.employee_id = mht.ManagerID ) SELECT * FROM ManagerHierarchyTable