w3resource

SQL SORTING and FILTERING on HR Database: Display the manager ID and number of employees managed by the manager

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

26. From the following table, write a SQL query to count the number of employees worked under each manager. Return manager ID and number of employees.

Sample table: employees


Sample Solution:

SELECT manager_id, COUNT(*) 
	FROM employees 
		GROUP BY manager_id;

Sample Output:

 manager_id | count
------------+-------
        205 |     1
        122 |     8
        120 |     8
        101 |     5
        103 |     4
        108 |     5
        145 |     6
        100 |    14
        201 |     1
        124 |     8
        114 |     5
        121 |     8
        123 |     8
        102 |     1
        146 |     6
        147 |     6
        148 |     6
        149 |     6
          0 |     1
(19 rows)

Relational Algebra Expression:

Relational Algebra Expression: Display the manager ID and number of employees managed by the manager.

Relational Algebra Tree:

Relational Algebra Tree: Display the manager ID and number of employees managed by the manager.

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display the manager ID and number of employees managed by the manager - Duration

Rows:

Query visualization of Display the manager ID and number of employees managed by the manager - Rows

Cost:

Query visualization of Display the manager ID and number of employees managed by the manager - Cost

Contribute your code and comments through Disqus.

Previous: From the following table, write a SQL query to count the number of cities in each country has. Return country ID and number of cities.
Next: From the following table, write a SQL query to find all jobs. Sort the result-set in descending order by job title. Return all fields.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



SQL: Tips of the Day

MySQL export schema without data

mysqldump -h yourhostnameorIP -u root -p --no-data dbname > schema.sql

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