PostgreSQL Subquery: Find the name and salary, which is greater than the average salary of the employees

5. Write a SQL subquery to find the first_name, last_name and salary, which is greater than the average salary of the employees.

Sample Solution:

Code:

-- This SQL query retrieves the first name, last name, and salary of employees whose salary is greater than the average salary of all employees.

SELECT first_name, -- Selects the first_name column from the employees table
       last_name, -- Selects the last_name column from the employees table
salary -- Selects the salary column from the employees table
FROM employees -- Specifies the table from which to retrieve data, in this case, the employees table
WHERE salary >( -- Filters the rows to include only those where the salary is greater than the average salary of all employees
    SELECT AVG(salary) -- Subquery: Calculates the average salary of all employees
    FROM employees
);

Explanation:

• This SQL query retrieves the first name, last name, and salary of employees whose salary is greater than the average salary of all employees.
• The outer SELECT statement retrieves the first name, last name, and salary from the employees table.
• The WHERE clause filters the rows to include only those where the salary is greater than the average salary obtained from the subquery.
• The subquery calculates the average salary of all employees using the AVG() function.
• The subquery is enclosed in parentheses and executed before the outer query.

Sample table: employees

Output:

pg_exercises=# SELECT first_name, last_name, salary
pg_exercises-# FROM employees
pg_exercises-# WHERE salary > (
pg_exercises(# SELECT AVG(salary)
pg_exercises(# FROM employees);
 first_name  | last_name  |  salary
-------------+------------+----------
 Alexander   | Hunold     |  9000.00
 Den         | Raphaely   | 11000.00
 Steven      | King       | 24000.00
 Neena       | Kochhar    | 17000.00
 Janette     | King       | 10000.00
 Patrick     | Sully      |  9500.00
 Allan       | McEwen     |  9000.00
 Lindsey     | Smith      |  8000.00
 Louise      | Doran      |  7500.00
 Sarath      | Sewall     |  7000.00
 Clara       | Vishney    | 10500.00
 Mattea      | Marvins    |  7200.00
 Payam       | Kaufling   |  7900.00
 Shanta      | Vollman    |  6500.00
 Ellen       | Abel       | 11000.00
 Alyssa      | Hutton     |  8800.00
 John        | Russell    | 14000.00
 Karen       | Partners   | 13500.00
 Alberto     | Errazuriz  | 12000.00
 Gerald      | Cambrault  | 11000.00
 Eleni       | Zlotkey    | 10500.00
...			 |  ...       |  ...
 Hermann     | Baer       | 10000.00
 Shelley     | Higgins    | 12000.00
 William     | Gietz      |  8300.00
(50 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 first_name and last_name of the employees who are working as a manager.
Next: Write a SQL subquery to find the first_name, last_name and salary, which is equal to the minimum salary for this post, he/she is working on.