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:

SELECT first_name, last_name, salary 
FROM employees 
WHERE department_id IN 
(SELECT department_id 
FROM departments 
WHERE department_name LIKE 'IT%') 
AND salary > (
SELECT avg(salary) 
FROM employees);

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.