PostgreSQL Subquery: Find the name and salary of the employees who earn more than the average salary and works in any of the IT departments

7. Write a SQL Subquery to find the first_name, last_name and salary of the employees who earn more than the average salary and works in any of the IT departments.

Sample Solution:

Code:

-- This SQL query retrieves the first name, last name, and salary of employees who belong to departments with names starting with 'IT' and have a salary 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 department_id IN ( -- Filters the rows to include only those where the department_id is in the set of department_ids obtained from the subquery
    SELECT department_id -- Subquery: Selects the department_id of departments with names starting with 'IT'
    FROM departments -- Specifies the table from which to retrieve data, in this case, the departments table
    WHERE department_name LIKE 'IT%' -- Filters departments whose names start with 'IT'
) 
AND salary >( -- Further 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 who belong to departments with names starting with 'IT' and have a salary greater than the average salary of all employees.
• The outermost 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 department_id is in the set of department_ids obtained from the subquery and the salary is greater than the average salary obtained from another subquery.
• The first subquery selects the department_id of departments with names starting with 'IT' from the departments table.
• The second subquery calculates the average salary of all employees using the avg() function.

Sample table: employees

Sample table: departments

Output:

pg_exercises=# SELECT first_name, last_name, salary
pg_exercises-# FROM employees
pg_exercises-# WHERE department_id IN
pg_exercises-# (SELECT department_id
pg_exercises(# FROM departments
pg_exercises(# WHERE department_name LIKE 'IT%')
pg_exercises-# AND salary > (
pg_exercises(# SELECT avg(salary)
pg_exercises(# FROM employees);
 first_name | last_name | salary
------------+-----------+---------
 Alexander  | Hunold    | 9000.00
(1 row)

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, last_name and salary, which is equal to the minimum salary for this post, he/she is working on.
Next: Write a SQL subquery to find the first_name, last_name and salary of the employees who draw a more salary than the employee, which the last name is Bell.