w3resource
SQL exercises

SQL JOINS on HR Database: Display the department name, department ID, and number of employees in each of the department

SQL JOINS on HR Database: Exercise-26 with Solution

26. Write a query in SQL to display the department name, department ID, and number of employees in each of the department.

Sample table: departments


Sample table: employees


Sample Solution:

SELECT d.department_name,
       e.*
FROM departments d
JOIN
  (SELECT count(employee_id),
          department_id
   FROM employees
   GROUP BY department_id) e USING (department_id);;

Sample Output:

department_name		no_of_employees	department_id
Administration		1		10
Marketing		2		20
Purchasing		6		30
Human Resources		1		40
Shipping		45		50
IT			5		60
Public Relations	1		70
Sales			34		80
Executive		3		90
Finance			6		100
Accounting		2		110

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display the department name and number of employees in each of the department - Duration

Rows:

Query visualization of Display the department name and number of employees in each of the department - Rows

Cost:

Query visualization of Display the department name and number of employees in each of the department - Cost

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

Previous: Write a query in SQL to display full name(first and last name), job title, starting and ending date of last jobs for those employees with worked without a commission percentage.
Next: Write a query in SQL to display the full name (firt and last name ) of employee with ID and name of the country presently where (s)he is working.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming