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:

Practice Online
Query Visualization:
Duration:

Rows:

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
- Exercises: Weekly Top 12 Most Popular Topics
- Pandas DataFrame: Exercises, Practice, Solution
- Conversion Tools
- JavaScript: HTML Form Validation
- SQL Exercises, Practice, Solution - SUBQUERIES
- C Programming Exercises, Practice, Solution : For Loop
- Python Exercises, Practice, Solution
- Python Data Type: List - Exercises, Practice, Solution
- C++ Basic: Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - exercises on Employee Database
- SQL Exercises, Practice, Solution - exercises on Movie Database
- SQL Exercises, Practice, Solution - exercises on Soccer Database
- C Programming Exercises, Practice, Solution : Recursion