SQL Subquery Exercises: Display the employee number, name and job title for all employees whose salary is more than any average salary of any department

SQL SUBQUERY: Exercise-20 with Solution

From the following table, write a SQL query to find those employees whose salaries are higher than the average for all departments. 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 AVG(salary)  
FROM employees  
GROUP BY department_id 
);

Sample Output:

employee_id	first_name	last_name	job_id
100		Steven		King		AD_PRES

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 more than any salary of those employees whose job title is 'PU_MAN'. Exclude job title 'PU_MAN'. Return employee ID, first name, last name, job ID.
Next: From the following table, write a SQL query to find any existence of those employees whose salary exceeds 3700. Return first name, last name and department ID.