Given the following SQL Schema:

Create table If Not Exists Employee (Id int, Name varchar(255), Department varchar(255), ManagerId int)
Truncate table Employee
insert into Employee (Id, Name, Department, ManagerId) values ('101', 'John', 'A', 'None')
insert into Employee (Id, Name, Department, ManagerId) values ('102', 'Dan', 'A', '101')
insert into Employee (Id, Name, Department, ManagerId) values ('103', 'James', 'A', '101')
insert into Employee (Id, Name, Department, ManagerId) values ('104', 'Amy', 'A', '101')
insert into Employee (Id, Name, Department, ManagerId) values ('105', 'Anne', 'A', '101')
insert into Employee (Id, Name, Department, ManagerId) values ('106', 'Ron', 'B', '101')

The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.

+------+----------+-----------+----------+
|Id    |Name 	  |Department |ManagerId |
+------+----------+-----------+----------+
|101   |John 	  |A 	      |null      |
|102   |Dan 	  |A 	      |101       |
|103   |James 	  |A 	      |101       |
|104   |Amy 	  |A 	      |101       |
|105   |Anne 	  |A 	      |101       |
|106   |Ron 	  |B 	      |101       |
+------+----------+-----------+----------+

Given the Employee table, write a SQL query that finds out managers with at least 5 direct report. For the above table, your SQL query should return:

+-------+
| Name  |
+-------+
| John  |
+-------+

Note:
No one would report to himself.

How does SQL Inner-Join Work?

The SQL Inner-Join (or often referred to as Join) can be easily illustrated using the following Venn Diagram:

Two tables are joined and the result is the intersection of two tables.

select Name from Employee as A
inner join (
    select ManagerId
    from Employee
    group by ManagerId
    having count(1) >= 5           
) as B on A.ID = B.ManagerId

As we can see, two tables (queries) are joined using the keyword inner join (case insensitive) or join in short. You also need to specify the columns that connect two tables using syntax On.

SQL Sub Query

As of this problem, we can also use the sub query to solve.

select Name from
Employee where Id in (
 select ManagerId from Employee
 group by ManagerId
 having count(ManagerId) >= 5
)

This works slightly differently, the IDs from a query are returned and are used as inputs to another query (more or less like the pipe).

–EOF (The Ultimate Computing & Technology Blog) —

推荐阅读：
糖尿病患者常有这些饮食误区，朋友们注意啦！　 网络上流传甚广的垃圾食品方便面有毒、致癌的传闻是真的吗？　 经常吃核桃仁可以补脑是真的吗 一天吃多少核桃才健康　 甘蓝汁食疗方法对胃病患者非常有益 疗效甚至超过单纯药物　 面部出现这些变化则是男人肾虚要进行饮食调理　 酱油吃多了会导致皮肤变黑吗？别再被忽悠啦！　 健康饮食：人们都说吃了“隔夜菜”致癌，这是真的吗？　 豆腐味美又养生，做一道家常菜让你胃口大开　 女性在特殊时期饮食需要注意，不能吃这些食物　 此菜肴脆嫩爽口肉香浓郁且色香味俱全，为冬季百吃不厌的佳肴