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

Visual Presentation:

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

Query Visualization:

Duration:

Rows:

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.