SQL Exercise: Employees in department 1001, salary higher than 2001

SQL subqueries on employee Database: Exercise-28 with Solution

[An editor is available at the bottom of the page to write and execute the scripts.]

28. From the following table, write a SQL query to find those employees of department 1001 and whose salary is more than the average salary of employees in department 2001. Return complete information about the employees.

Sample table: employees

Sample Solution:

SELECT *
FROM employees
WHERE dep_id =1001
  AND salary >
    (SELECT AVG (salary)
     FROM employees
     WHERE dep_id = 2001);

Sample Output:

 emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+-----------+------------+------------+---------+------------+--------
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001
  67832 | CLARE    | MANAGER   |      68319 | 1991-06-09 | 2550.00 |            |   1001
(2 rows)

Explanation:

The said query in SQL that retrieves all the employees table from the employees who belong to the department with "dep_id" 1001 and have a salary greater than the average salary of the employees in the department with "dep_id" 2001.

The main query retrieves all the employees who belong to the department with id 1001 and have a salary greater than the result obtained from the subquery.

The subquery calculates the average salary of employees in the department with id 2001.

The subquery is executed first before the main query is executed.

Practice Online

Structure of employee Database:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Display the total salary of employees with grade 3.
Next SQL Exercise: Departments where maximum number of employees work.