SQL Exercise: Find the Highest Average Salary (Excluding PRESIDENT)

Last update on February 03 2025 11:24:23 (UTC/GMT +8 hours)

[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 highest average salary among all job roles, excluding '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.