This problem statement is a part of our SQL Interview Questions series. In this series we are committed to bring out a collection of real-world interview questions to help job seekers improve their coding skills and gain confidence.
To check all the interview questions available in this series, click here.
SQL Joins
SQL joins helps in combining records from two or more than two tables in the SQL database, based on the related column between them. The different types of Joins available in SQL are:
- Inner Join
- Left Outer Join/Left Join
- Right Outer Join/Right Join
- Full Outer Join/Full Join
- Self Join
- Cross Join
Self Join is used for joining a table with itself and hence forms a unary relation. It is mainly used to combine and compare the rows of the same table in the database.
Let’s take you through an example of using the self join in practice.
Problem Statement:
Find the Employee Id and Name of the managers who have minimum 7 employees directly reporting to them.
Table: Employee_Info
| EmpID | EmpName | Age | Sex | JobTitle | Salary | ManagerID |
|---|---|---|---|---|---|---|
| 1 | Allen Jack | 50 | F | Manager | 4000 | 1 |
| 2 | Joe Wang | 27 | M | Analyst | 200 | 1 |
| 3 | Henry Ted | 26 | M | Senior Analyst | 400 | 1 |
| 4 | Sam Aston | 22 | M | Analyst | 200 | 1 |
| 5 | Mike George | 21 | M | Analyst | 260 | 1 |
| 6 | Molly Sam | 23 | F | Analyst | 280 | 13 |
| 7 | Nicky Bell | 28 | F | Analyst | 280 | 13 |
| 8 | John Ford | 21 | M | Senior Analyst | 300 | 13 |
| 9 | Monika William | 28 | F | Analyst | 200 | 13 |
| 10 | Jennifer Dion | 29 | F | Analyst | 200 | 13 |
| 11 | Richerd Gear | 52 | M | Manager | 5000 | 1 |
| 12 | Shandler Bond | 23 | M | Accountant | 220 | 11 |
| 13 | Katty Bing | 51 | F | Manager | 3000 | 1 |
| 14 | Jason Tom | 18 | M | Accountant | 200 | 11 |
| 15 | Michale John | 39 | F | Accountant | 140 | 11 |
| 16 | Celine Anston | 22 | F | Accountant | 200 | 11 |
| 17 | Mick Berry | 39 | M | Senior Analyst | 440 | 11 |
| 18 | Laila Mark | 21 | F | Analyst | 200 | 11 |
| 19 | George Joe | 45 | M | Manager | 2000 | 1 |
| 20 | Sarrah Bicky | 26 | F | Senior Analyst | 400 | 19 |
| 21 | Suzan Lee | 29 | F | Analyst | 260 | 19 |
| 22 | Mandy John | 26 | F | Analyst | 260 | 19 |
| 23 | Britney Berry | 42 | F | Analyst | 240 | 19 |
| 24 | Smith Morris | 25 | M | Analyst | 260 | 19 |
| 25 | Jack Hopes | 24 | M | Analyst | 260 | 19 |
| 26 | Charles Ben | 38 | M | Analyst | 260 | 19 |
| 27 | Tom Fredy | 27 | M | Analyst | 240 | 1 |
| 28 | Morgan Matt | 25 | M | Analyst | 240 | 1 |
| 29 | Antoney Adams | 29 | M | Analyst | 260 | 1 |
| 30 | Mark Joe | 23 | M | Analyst | 240 | 1 |
Solution:
Solution for Microsoft SQL Server is shared below.
SELECT ManagerID, ManagerName
FROM (
SELECT E1.EmpID, E1.EmpName, E2.EmpName AS ManagerName, E2.EmpID AS ManagerID
FROM Employee_Info AS E1 INNER JOIN Employee_Info AS E2
ON E1.ManagerID = E2.EmpID) AS Table1
GROUP BY ManagerID, ManagerName
HAVING COUNT(EmpID) >= 7
