w3resource

SQL Exercise: Departments with managers manage more than 4 employees

SQL SORTING and FILTERING on HR Database: Exercise-30 with Solution

30. From the following table, write a SQL query to find the departments where any manager manages four or more employees. Return department_id.

Sample table : employees


Sample Solution:

SELECT DISTINCT department_id
	FROM employees
		GROUP BY department_id, manager_id 
			HAVING COUNT(employee_id) >=4;

Sample Output:

 department_id
---------------
            80
            50
            60
           100
            30
(5 rows)

Code Explanation:

The said query in SQL that returns a list of unique department IDs from the 'employees' table where there are at least 4 employees with the same manager in each department.
The query starts by selecting only distinct "department_id" values from the 'employees' table, then groups the results by "department_id" and "manager_id".
The HAVING clause is used to specify a condition on the grouped result, in this case counting the number of "employee_id"s in each group and returning only those groups where the count is greater than or equal to 4.

Relational Algebra Expression:

Relational Algebra Expression: Display those departments where any manager is managing 4 or more employees.

Relational Algebra Tree:

Relational Algebra Tree: Display those departments where any manager is managing 4 or more employees.

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display those departments where any manager is managing 4 or more employees - Duration

Rows:

Query visualization of Display those departments where any manager is managing 4 or more employees - Rows

Cost:

Query visualization of Display those departments where any manager is managing 4 or more employees - Cost

Contribute your code and comments through Disqus.

Previous SQL Exercise: Average salary of each commission-based department.
Next SQL Exercise: Departments more than 10 employees who get commissions.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

Difference between natural join and inner join

One significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned-

Consider:

TableA                           TableB
+------------+----------+        +--------------------+    
|Column1     | Column2  |        |Column1  |  Column3 |
+-----------------------+        +--------------------+
| 1          |  2       |        | 1       |   3      |
+------------+----------+        +---------+----------+

The INNER JOIN of TableA and TableB on Column1 will return

SELECT * FROM TableA AS a INNER JOIN TableB AS b USING (Column1);
SELECT * FROM TableA AS a INNER JOIN TableB AS b ON a.Column1 = b.Column1;
+------------+-----------+---------------------+    
| a.Column1  | a.Column2 | b.Column1| b.Column3|
+------------------------+---------------------+
| 1          |  2        | 1        |   3      |
+------------+-----------+----------+----------+

The NATURAL JOIN of TableA and TableB on Column1 will return:

SELECT * FROM TableA NATURAL JOIN TableB
+------------+----------+----------+    
|Column1     | Column2  | Column3  |
+-----------------------+----------+
| 1          |  2       |   3      |
+------------+----------+----------+

Ref: https://bit.ly/3AG5CId

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook