SQL JOINS on HR Database: Display the name of the department, average salary and number of employees working in that department who got commission

SQL JOINS on HR Database: Exercise-15 with Solution

15. From the following table, write a SQL query to compute the average salary, number of employees received commission in that department. Return department name, average salary and number of employees.

Sample table: employees

Sample table: departments

Sample Solution:

SELECT department_name, AVG(salary), COUNT(commission_pct) 
	FROM departments 
		JOIN employees USING (department_id) 
GROUP BY department_name;

Sample Output:

department_name		avg		count
Shipping	3475.5555555555555556	45
Sales		8955.8823529411764706	34
IT		5760.0000000000000000	5
Administration	4400.0000000000000000	1
Finance		8600.0000000000000000	6
Purchasing	4150.0000000000000000	6
Marketing	9500.0000000000000000	2
Public Relations10000.0000000000000000	1
Accounting	10150.0000000000000000	2
Executive	19333.333333333333	3
Human Resources	6500.0000000000000000	1

Relational Algebra Expression:

Relational Algebra Tree:

Pictorial Presentation:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous: From the following tables, write a SQL query to find the difference between maximum salary of the job and salary of the employees. Return job title, employee name, and salary difference.
Next: From the following tables, write a SQL query to compute the difference between maximum salary and salary of all the employees who works the department of ID 80. Return job title, employee name and salary difference.