SQL Exercises: Salary exceeds the 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
SELECT employee_id, first_name, last_name, job_id FROM employees WHERE salary > ALL ( SELECT AVG(salary) FROM employees GROUP BY department_id );
employee_id first_name last_name job_id 100 Steven King AD_PRES
The said query in SQL that retrieves the employee ID, first name, last name, and job ID of all employees who have a salary higher than the average salary of all employees in each department. In the "WHERE" clause, the "GROUP BY" clause and the "AVG" function are used to calculate the average salary of all employees in each department. Using the keyword "ALL" in the outer query, the salary of each employee is then compared with the result of the subquery.
Contribute your code and comments through Disqus.
Previous SQL Exercise: Employees whose salary is higher than title is PU_MAN.
Next SQL Exercise: Employees whose salary is more than 3700.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
MySQL select 10 random rows from 600K rows fast:
SELECT name FROM random AS r1 JOIN (SELECT CEIL(RAND() * (SELECT MAX(id) FROM random)) AS id) AS r2 WHERE r1.id >= r2.id ORDER BY r1.id ASC LIMIT 1
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook