SQL Exercise: Average salary of each commission-based department

Last update on March 31 2023 06:07:46 (UTC/GMT +8 hours)

SQL SORTING and FILTERING on HR Database: Exercise-29 with Solution

29. From the following table, write a SQL query to calculate the average salary of employees who receive a commission percentage for each department. Return department id, average salary.

Sample table: employees

Sample Solution:

SELECT department_id, AVG(salary) 
	FROM employees 
		WHERE commission_pct IS NOT NULL 
			GROUP BY department_id;

N.B.: The table's structure has modified for necessary reasons. The query had been utilised for the commission pct column's NULL value.

Sample Output:

 department_id |          avg
---------------+------------------------
            90 |     19333.333333333333
            20 |  9500.0000000000000000
           100 |  8600.0000000000000000
            40 |  6500.0000000000000000
           110 | 10150.0000000000000000
            80 |  8955.8823529411764706
            70 | 10000.0000000000000000
            50 |  3475.5555555555555556
            60 |  5760.0000000000000000
            30 |  4150.0000000000000000
            10 |  4400.0000000000000000
             0 |  7000.0000000000000000
(12 rows)

Code Explanation:

The said query in SQL that calculates the average salary of employees in each department where the commission percentage is not null. The query will return two columns: "department_id" and the average salary for that department. The query groups the results by "department_id".

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Employees who are Sales Representatives or Sales Men.
Next SQL Exercise: Departments with managers manage more than 4 employees.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

SQL: Tips of the Day

Difference between natural join and inner join

One significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned-

Consider:

TableA                           TableB
+------------+----------+        +--------------------+    
|Column1     | Column2  |        |Column1  |  Column3 |
+-----------------------+        +--------------------+
| 1          |  2       |        | 1       |   3      |
+------------+----------+        +---------+----------+

The INNER JOIN of TableA and TableB on Column1 will return

SELECT * FROM TableA AS a INNER JOIN TableB AS b USING (Column1);
SELECT * FROM TableA AS a INNER JOIN TableB AS b ON a.Column1 = b.Column1;

+------------+-----------+---------------------+    
| a.Column1  | a.Column2 | b.Column1| b.Column3|
+------------------------+---------------------+
| 1          |  2        | 1        |   3      |
+------------+-----------+----------+----------+

The NATURAL JOIN of TableA and TableB on Column1 will return:

SELECT * FROM TableA NATURAL JOIN TableB
+------------+----------+----------+    
|Column1     | Column2  | Column3  |
+-----------------------+----------+
| 1          |  2       |   3      |
+------------+----------+----------+

Ref: https://bit.ly/3AG5CId

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook