﻿ SQL - Salary exceeds the average salary of any department

# 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
```

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:

## Query Visualization:

Duration:

Rows:

Cost:

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

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿

## SQL: Tips of the Day

MySQL select 10 random rows from 600K rows fast:

```SELECT name
FROM random AS r1 JOIN
(SELECT CEIL(RAND() *
(SELECT MAX(id)
FROM random)) AS id)
AS r2
WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 1
```

Ref: https://bit.ly/3GdCTM3

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook