﻿ SQL - Employees whose salary is higher than title is PU_MAN

# 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
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'
);

## Query Visualization:

Duration:

Rows:

Cost:

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

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿

## SQL: Tips of the Day

What's the difference between VARCHAR and CHAR?

VARCHAR is variable-length.

CHAR is fixed length.

If your content is a fixed size, you'll get better performance with CHAR.

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

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