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. Write a query in SQL to display the name of the department, average salary and number of employees working in that department who got commission.
Sample table: employees
Sample table: departments
SELECT department_name, AVG(salary), COUNT(commission_pct) FROM departments JOIN employees USING (department_id) GROUP BY department_name;
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
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: Write a query in SQL to display job title, full name (first and last name ) of employee, and the difference between maximum salary for the job and salary of the employee.
Next: Write a query in SQL to display the full name (first and last name ) of employee, and job title of those employees who is working in the department which ID is 80.
What is the difficulty level of this exercise?