w3resource

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:

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

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 average salary of any department - Duration

Rows:

Query visualization of Display the employee number, name and job title for all employees whose salary is more than any average salary of any department - Rows

Cost:

Query visualization of Display the employee number, name and job title for all employees whose salary is more than any average salary of any department - 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.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

How to count occurrences of a column value in SQL?

Input table:

id | age
--------
0  | 25
1  | 25
2  | 23
SELECT age, count(age) 
  FROM Students 
 GROUP by age

Output:

id | age | count
----------------
0  | 25  | 2
1  | 25  | 2
2  | 23  | 1

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