w3resource

SQL Exercise: Maximum salary for each job name except for PRESIDENT

SQL subqueries on employee Database: Exercise-75 with Solution

[An editor is available at the bottom of the page to write and execute the scripts.]

75. From the following table, write a SQL query to find the maximum average salary drawn for each job except for PRESIDENT.

Sample table: employees


Sample Solution:

SELECT max(myavg)
FROM
  (SELECT avg(salary) myavg
   FROM employees
   WHERE job_name != 'PRESIDENT'
   GROUP BY job_name) a;

Sample Output:

          max
-----------------------
 3100.0000000000000000
(1 row)

Explanation:

The said query in SQL that selects the maximum average salary among all job positions except for 'PRESIDENT' from the 'employees' table.

The inner query selects the average salary for each job position from the 'employees' table and the 'WHERE' clause excludes the 'PRESIDENT' job position. The results are grouped by the job name.

The outer query selects the maximum average salary from the results of the inner query and returns it as a single value.

Practice Online


Structure of employee Database:

employee database structure

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Employees who earn more than their managers.
Next SQL Exercise: Count the number of employees performing manager duties.

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.