SQL Exercises: Salary exceeds the average salary of any department

SQL SUBQUERY: Exercise-20 with Solution

From the following table, write a SQL query to find those employees whose salaries are higher than the average for all departments. Return employee ID, first name, last name, job ID.

Sample table: employees

Sample Solution:

SELECT employee_id, first_name, last_name, job_id  
FROM employees  
WHERE salary > ALL  
( SELECT AVG(salary)  
FROM employees  
GROUP BY department_id 
);

Sample Output:

employee_id	first_name	last_name	job_id
100		Steven		King		AD_PRES

Code Explanation:

The said query in SQL that retrieves the employee ID, first name, last name, and job ID of all employees who have a salary higher than the average salary of all employees in each department. In the "WHERE" clause, the "GROUP BY" clause and the "AVG" function are used to calculate the average salary of all employees in each department. Using the keyword "ALL" in the outer query, the salary of each employee is then compared with the result of the subquery.

Visual Presentation:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Employees whose salary is higher than title is PU_MAN.
Next SQL Exercise: Employees whose salary is more than 3700.