﻿ SQL: Salary is less than manager but more than colleagues

# SQL Exercise: Salary is less than manager but more than colleagues

## SQL subqueries on employee Database: Exercise-36 with Solution

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

36. From the following table, write a SQL query to find those employees whose salary is less than the salary of his manager but more than the salary of any other manager. Return complete information about the employees.

Sample table: employees

Sample Solution:

``````SELECT *
FROM employees w,
employees m
WHERE w.manager_id = m.emp_id
AND w.salary < m.salary
AND w.salary > ANY
(SELECT salary
FROM employees
WHERE emp_id IN
(SELECT manager_id
FROM employees));
``````

OR

``````SELECT DISTINCT W.emp_id,
W.emp_name,
W.salary
FROM
(SELECT w.emp_id,
w.emp_name,
w.salary
FROM employees w,
employees m
WHERE w.manager_id = m.emp_id
AND w.salary<m.salary) W,

(SELECT *
FROM employees
WHERE emp_id IN
(SELECT manager_id
FROM employees)) A
WHERE W.salary > A.salary;
``````

Sample Output:

``` emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id | emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission  | dep_id
--------+----------+----------+------------+------------+---------+------------+--------+--------+----------+-----------+------------+------------+---------+-------------+--------
66928 | BLAZE    | MANAGER  |      68319 | 1991-05-01 | 2750.00 |            |   3001 |  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |		  |   1001
65646 | JONAS    | MANAGER  |      68319 | 1991-04-02 | 2957.00 |            |   2001 |  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |             |   1001
(2 rows)
```

Explanation:

The said query in SQL that retrieves all employees from the 'employees' table who have a manager, and whose salary is less than their manager's salary, but greater than any salary of their peers who are managed by the same manager.

The 'employees' table joins with itself based on the "manager_id" and the "emp_id" columns.

The WHERE clause ensures that the employee's salary is less than their manager's salary.

The query is also checks whether the employee's salary is greater than any salary of employees whose "emp_id" is in the list of "manager_id"s in the 'employees' table.

## Practice Online

Structure of employee Database:

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

Previous SQL Exercise: Managers salary exceeds the average of their employees.
Next SQL Exercise: List average salary of employees in department wise.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿