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:

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.