w3resource

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 Expression: Display the name of the department, average salary and number of employees working in that department who got commission.

Relational Algebra Tree:

Relational Algebra Tree: Display the name of the department, average salary and number of employees working in that department who got commission.

Pictorial Presentation:

SQL Exercises: Display the name of the department, average salary and number of employees working in that department who got commission.

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: 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.

What is the difficulty level of this exercise?