w3resource

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

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

Pictorial Presentation:

SQL Subqueries: 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.

Practice Online


Query Visualization:

Duration:

Query visualization of 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 - Duration

Rows:

Query visualization of 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 - Rows

Cost:

Query visualization of 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 - Cost

Contribute your code and comments through Disqus.

Previous: From the following table, write a SQL query to find those employees whose salary is lower than any salary of those employees whose job title is 'MK_MAN'. Exclude employees of Job title 'MK_MAN'. Return employee ID, first name, last name, job ID.
Next: From the following table, write a SQL query to find those employees whose salary is more than average salary of any department. Return employee ID, first name, last name, job ID.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



SQL: Tips of the Day

"where 1=1" statement?

It's usually when folks build up SQL statements.

When you add and value = "Toyota" you don't have to worry about whether there is a condition before or just WHERE.

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