PostgreSQL Subquery: Display some information of all employees whose salary is above the average salary for their departments
14. Write a SQL subquery to find the employee ID, first name, last name and salary of all employees whose salary is above the average salary for their departments.
Sample Solution:
Code:
SELECT employee_id, first_name
FROM employees AS A
WHERE salary >
( SELECT AVG(salary)
FROM employees
WHERE department_id = A.department_id);
Sample table: employees
Sample table: departments
Output:
pg_exercises=# SELECT employee_id, first_name pg_exercises-# FROM employees AS A pg_exercises-# WHERE salary > pg_exercises-# ( SELECT AVG(salary) pg_exercises(# FROM employees pg_exercises(# WHERE department_id = A.department_id); employee_id | first_name -------------+------------ 103 | Alexander 104 | Bruce 114 | Den 100 | Steven 141 | Trenna 156 | Janette 157 | Patrick 158 | Allan 162 | Clara 122 | Payam 123 | Shanta 124 | Kevin 137 | Renske 174 | Ellen 145 | John 146 | Karen 147 | Alberto 148 | Gerald 149 | Eleni 150 | Peter 151 | David 184 | Nandita 185 | Alexis 188 | Kelly 152 | Peter 189 | Jennifer 192 | Sarah 193 | Britney 120 | Matthew 121 | Adam 108 | Nancy 109 | Daniel 168 | Lisa 169 | Harrison 170 | Tayler 201 | Michael 205 | Shelley (37 rows)
Practice Online
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: Write a SQL subquery to find the employee ID, first name, last name and department names of all employees.
Next: Write a subquery to find the 5th maximum salary of all salaries.
What is the difficulty level of this exercise?
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework