SQL subqueries on employee Database: List the employees in department 1001 whose salary is more than the average salary of employees in department 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)

Practice Online

Structure of employee Database:

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

Previous: From the following table, write a SQL query to compute the total salary of employees of grade 3. Return total salary.
Next: From the following table, write a SQL query to find those departments where maximum number of employees work. Return department ID, department name, location and number of employees.