SQL Subquery Exercises: Display the employee number, name and job title for all employees whose salary is more than any salary of those employees whose job title is PU_MAN. Exclude job title PU_MAN

SQL SUBQUERY : Exercise-19 with Solution

Write a query to display the employee number, name( first name and last name ) and job title for all employees whose salary is more than any salary of those employees whose job title is PU_MAN. Exclude job title PU_MAN.

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

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous: Write a query to display the employee number, name( first name and last name ) and job title for all employees whose salary is smaller than any salary of those employees whose job title is MK_MAN. Exclude Job title MK_MAN.
Next: Write a query to display the employee number, name( first name and last name ) and job title for all employees whose salary is more than any average salary of any department.