SQL Exercise: Senior employees with grades over 3 work under KAYLING

SQL subqueries on employee Database: Exercise-25 with Solution

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

25. From the following table, write a SQL query to find the most senior employee of grade 4 or 5, work under KAYLING. Return complete information about the employees.

Sample table: employees

Sample table: salary_grade

Sample Solution:

SELECT *
FROM employees
WHERE hire_date IN
    (SELECT min(hire_date)
     FROM employees
     WHERE emp_id IN
         (SELECT emp_id
          FROM employees e,
               salary_grade s
          WHERE e.salary BETWEEN s.min_sal AND s.max_sal
            AND s.grade IN (4,
                            5)))
  AND 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 returns employees from the employees table who were hired on the earliest date and have a salary that falls within the range of salary grades 4 or 5 and have 'KAYLING' as their manager.

The subquery selects the "emp_id" of employees who have a salary that falls within the range of salary grades 4 or 5. It does this by joining the 'employees' table with the 'salary_grade' table on the condition that the "salary" of an employee falls within the "min_sal" and "max_sal" range of the corresponding salary grade. It then selects the minimum hire date of these employees.

The outer query then selects all rows from the 'employees' table where the "hire_date" is equal to the value returned by the subquery and their "manager_id" is the "emp_id" of an employee whose 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: Employees who joined in 1991 as the most senior of 1991.
Next SQL Exercise: Find the total salary given to the MANAGER.