MySQL Subquery Exercises: Find the name and salary of the employees whose salary is greater than the average salary of all departments

MySQL Subquery: Exercise-10 with Solution

Write a query to find the name (first_name, last_name), and salary of the employees whose salary is greater than the average salary of all departments.

Sample table: employees

Code:

SELECT * FROM employees 
WHERE salary > 
ALL(SELECT avg(salary)FROM employees GROUP BY department_id);

Explanation:

MySQL Subquery with ALL :

Syntax :

operand comparison_operator ALL (subquery)

The word ALL, which must follow a comparison operator, means "return TRUE if the comparison is TRUE for ALL of the values in the column that the subquery returns." For example:

SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);

Suppose that there is a row in table t1 containing (10). The expression is TRUE if table t2 contains (-5,0,+5) because 10 is greater than all three values in t2. The expression is FALSE if table t2 contains (12,6,NULL,-100) because there is a single value 12 in table t2 that is greater than 10. The expression is unknown (that is, NULL) if table t2 contains (0,NULL,1).

Finally, the expression is TRUE if table t2 is empty. So, the following expression is TRUE when table t2 is empty :

SELECT * FROM t1 WHERE 1 > ALL (SELECT s1 FROM t2);

MySQL AVG() function calculates the average value of a set of values or an expression.

MySQL Code Editor:

Structure of 'hr' database :

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

Previous:Write a query to find the name (first_name, last_name), and salary of the employees who earn the same salary as the minimum salary for all departments.
Next:Write a query to find the name (first_name, last_name) and salary of the employees who earn a salary that is higher than the salary of all the Shipping Clerk (JOB_ID = 'SH_CLERK'). Sort the results of the salary of the lowest to highest.