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.
Test your Programming skills with w3resource's quiz.
What is the difficulty level of this exercise?
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
- 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