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

# 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 Solution:

``````SELECT *
FROM employees
WHERE hire_date IN
(SELECT min(hire_date)
FROM employees
WHERE emp_id IN
(SELECT emp_id
FROM employees e,
WHERE e.salary BETWEEN s.min_sal AND s.max_sal
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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿