w3resource
SQL exercises

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. Write a query in SQL to display the name of the department, average salary and number of employees working in that department who got commission.

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

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display the name of the department, average salary and number of employees working in that department who got commission - Duration

Rows:

Query visualization of Display the name of the department, average salary and number of employees working in that department who got commission - Rows

Cost:

Query visualization of Display the name of the department, average salary and number of employees working in that department who got commission - Cost

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

Previous: Write a query in SQL to display job title, full name (first and last name ) of employee, and the difference between maximum salary for the job and salary of the employee.
Next: Write a query in SQL to display the full name (first and last name ) of employee, and job title of those employees who is working in the department which ID is 80.

What is the difficulty level of this exercise?



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