SQL Exercises: Employees whose salary is higher than title is PU_MAN

SQL SUBQUERY: Exercise-19 with Solution

From the following table, write a SQL query to find those employees whose salary exceeds the salary of all those employees whose job title is "PU_MAN". Exclude job title ‘PU_MAN’. 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 salary  
FROM employees  
WHERE job_id = 'PU_MAN' )  
AND job_id <> 'PU_MAN';

Sample Output:

employee_id	first_name	last_name	job_id
100		Steven		King		AD_PRES
101		Neena		Kochhar		AD_VP
102		Lex		De Haan		AD_VP
108		Nancy		Greenberg	FI_MGR
145		John		Russell		SA_MAN
146		Karen		Partners	SA_MAN
147		Alberto		Errazuriz	SA_MAN
168		Lisa		Ozer		SA_REP
201		Michael		Hartstein	MK_MAN
205		Shelley		Higgins		AC_MGR

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 all employees with the job ID "PU_MAN", and do not have the job ID "PU_MAN" themselves. A subuery in the "WHERE" clause is used to retrieve the salaries of all employees who have the job ID "PUq_MAN" assigned to their job title. Using the keyword "ALL" in the outer query, the salary of each employee is then compared with the result of the subquery. The additional condition "AND job_id <> 'PU_MAN'", excludes employees with the job ID "PU_MAN" from the result set.

Visual Presentation:

Alternative Statements:

Using JOIN:

SELECT e1.employee_id, e1.first_name, e1.last_name, e1.job_id 
FROM employees e1
JOIN employees e2 ON e1.salary > e2.salary
WHERE e2.job_id = 'PU_MAN' AND e1.job_id <> 'PU_MAN';

Using EXISTS:

SELECT employee_id, first_name, last_name, job_id 
FROM employees e1
WHERE EXISTS (
    SELECT 1
    FROM employees e2
    WHERE e1.salary > e2.salary
    AND e2.job_id = 'PU_MAN'
    AND e1.job_id <> 'PU_MAN'
);

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Employees whose job title is MK_MAN, exclude MK_MAN.
Next SQL Exercise: Salary exceeds the average salary of any department.