SQL Exercise: List the highest paid employees working under KAYLING

SQL subqueries on employee Database: Exercise-72 with Solution

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

72. From the following table, write a SQL query to find the highest paid employees work under KAYLING. Return complete information about the employees.

Sample table: employees

Sample Solution:

SELECT *
FROM employees
WHERE salary IN
    (SELECT max(salary)
     FROM employees
     WHERE manager_id IN
         (SELECT emp_id
          FROM employees
          WHERE emp_name = 'KAYLING'));

Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  65646 | JONAS    | MANAGER  |      68319 | 1991-04-02 | 2957.00 |            |   2001
(1 row)

Explanation:

The said query in SQL that retrieves all columns from the 'employees' table for all employees whose salary is equal to the maximum salary of employees who report to the manager with the name 'KAYLING'.

The WHERE clause filters the results to include only those employees whose salary is in the list of salaries obtained by the subquery.

The subquery that selects the maximum salary of all employees whose manager has the emp_name 'KAYLING'.

The WHERE clause in the outer subquery uses another subquery to obtain the emp_id of the employee whose emp_name is 'KAYLING'.

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: Highest paid PERTH employees before the latest grade 2.
Next SQL Exercise: Employees, net pay is greater or equal than any other.