MySQL Subquery Exercises: Find the name and salary of the employees who earn a salary that is higher than the salary of all the Shipping Clerk

MySQL Subquery: Exercise-11 with Solution

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.

Sample table: employees

Code:

SELECT first_name,last_name, job_id, salary 
FROM employees 
WHERE salary > 
ALL (SELECT salary FROM employees WHERE job_id = 'SH_CLERK') ORDER BY salary;

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 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 whose salary is greater than the average salary of all departments.
Next:Write a query to find the name (first_name, last_name) of the employees who are not supervisors.