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

推荐阅读：
wordpress自动显示图片exif信息插件-Display Exif　 为wordpress设置一个简单的后台登陆地址　 免插件自动生成wordpress文章二维码图片　 wordpress主题更新style.css样式表文件后不生效的解决方法　 如何在WordPress文章末尾自动添加作者简介　 如何让WordPress分类归档排除子分类文章　 如何让WordPress显示即将发布的文章列表　 wordpress头像缓存插件：nix-gravatar-cache 修改版　 如何获取WordPress当前分类文章的所有标签　 存在的理由作文100字