Hierarchical Queries

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):

You can use this script to create and populate the tables


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

Click here to see the solution


--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

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…

About Us

UpScale Analytics specializes in training solutions for organizations and individuals in the fields of data.

UpScale Analytics is also one of the largest platforms in the world for learning SQL by doing.

Hierarchical Queries

About Us

Popular Courses

Desicion Making with Basic SQL

PL/SQL Programming

Advanced Power BI

Quick Links