w3resource

SQL SORTING and FILTERING on HR Database: Display the average salary of employees for each department who gets a commission percentage

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

29. From the following table, write a SQL query to calculate average salary of those employees for each department who get a commission percentage. 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;

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)

Relational Algebra Expression:

Relational Algebra Expression: Display the average salary of employees for each department who gets a commission percentage.

Relational Algebra Tree:

Relational Algebra Tree: Display the average salary of employees for each department who gets a commission percentage.

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display the average salary of employees for each department who gets a commission percentage - Duration

Rows:

Query visualization of Display the average salary of employees for each department who gets a commission percentage - Rows

Cost:

Query visualization of Display the average salary of employees for each department who gets a commission percentage - Cost

Contribute your code and comments through Disqus.

Previous: From the following table, write a SQL query to find all those employees who are either Sales Representative or Salesman. Return first name, last name and hire date.
Next: From the following table, write a SQL query to find those departments where a manager can manage four or more employees. Return department_id.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



SQL: Tips of the Day

MySQL export schema without data

mysqldump -h yourhostnameorIP -u root -p --no-data dbname > schema.sql

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