w3resource

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.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming