SQL Exercise: Average of the maximum and minimum salary of employees

SQL subqueries on employee Database: Exercise-34 with Solution

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

34. From the following table, write a SQL query to find those employees whose salary is equal or more to the average of maximum and minimum salary. Return complete information about the employees.

Sample table: employees

Sample Solution:

SELECT *
FROM employees
WHERE salary >=
    (SELECT (max(salary)+min(salary))/2
     FROM employees);

Sample Output:

 emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+-----------+------------+------------+---------+------------+--------
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001
(1 row)

Explanation:

The said query in SQL that retrieves all columns from the employees table for all employees whose salary is greater than or equal to the average of the maximum and minimum salary of all employees in the table.

The subquery within the WHERE clause calculates the average of the maximum and minimum salary of all employees in the employees table, and then the outer query selects all employees whose salary is greater than or equal to the average obtained from the subquery.

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: List the highest-paid employees in each department.
Next SQL Exercise: Managers salary exceeds the average of their employees.