w3resource

SQL Exercise: Employees working in the department who got commission

SQL JOINS on HR Database: Exercise-15 with Solution

15. From the following table, write a SQL query to calculate the average salary, the number of employees receiving commissions in that department. Return department name, average salary and number of employees.

Sample table: employees


Sample table: departments


Sample Solution:

-- Selecting specific columns (department_name, AVG(salary), COUNT(commission_pct)) from the 'departments' table
SELECT department_name, AVG(salary), COUNT(commission_pct) 

-- Performing an INNER JOIN between the 'departments' table and the 'employees' table using the common column 'department_id'
FROM departments 

JOIN employees USING (department_id) 

-- Grouping the result set by the 'department_name' column
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

Code Explanation:

The said query in SQL that retrieves the department name, average salary, and count of employees who receive commission for each department in the company. It does so by joining the 'departments' and 'employees' tables using the department_id column and then grouping the results by the department name.
The uses of JOIN keyword to join the departments and employees tables on their common column, which is "department_id" in this case.
Then groups the results by department name, so that the average salary and count of commission-receiving employees are calculated for each department separately.

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.

Visual Presentation:

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

Alternative Solutions:

Using INNER JOIN with Aliases and GROUP BY:


SELECT d.department_name, AVG(e.salary) AS average_salary, COUNT(e.commission_pct) AS commission_count
FROM departments d
JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

Explanation:

This query also employs an INNER JOIN and provides aliases (d for departments, e for employees). It calculates the average salary and counts the non-null commission percentages foreach department using GROUP BY.

Using INNER JOIN with Explicit Column Names and GROUP BY:


SELECT departments.department_name, AVG(employees.salary) AS average_salary, COUNT(employees.commission_pct) AS commission_count
FROM departments
JOIN employees ON departments.department_id = employees.department_id
GROUP BY departments.department_name;

Explanation:

This one employs an INNER JOIN and specifies column names explicitly. It calculates the average salary and counts the non-null commission percentages for each department using GROUP BY.

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 SQL Exercise: Jobs which started between two given dates.
Next SQL Exercise: Salary differences of employees in the department 80.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.