SQL Exercise: Count the number of employees performing manager duties

SQL subqueries on employee Database: Exercise-76 with Solution

[An editor is available at the bottom of the page to write and execute the scripts.]

76. From the following table, write a SQL query to count the number of employees who work as a manager. Return number of employees.

Sample table: employees

Sample Solution:

SELECT count(*)
FROM employees
WHERE emp_id IN
    (SELECT manager_id
     FROM employees);

OR

SELECT count(DISTINCT m.emp_id)
FROM employees w,
     employees m
WHERE w.manager_id = m.emp_id ;

Sample Output:

 count
-------
     6
(1 row)

Explanation:

According to first example the said query in SQL that selects the number of employees who are also managers in the 'employees' table.

The inner query selects all the 'manager_id' values from the 'employees' table.

The outer query selects the count of all employees whose 'emp_id' values match any of the 'manager_id' values returned by the inner query.

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Structure of employee Database:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Maximum salary for each job name except for PRESIDENT.
Next SQL Exercise: List the department where there are no employees.