﻿ SQL: Employees senior to most recently hired under KAYLING

# SQL Exercise: Employees senior to most recently hired under KAYLING

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

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

21. From the following table, write a SQL query to find those employees who are senior to those recently hired employee who worked under KAYLING. Return complete information about the employees.

Sample table: employees

Sample Solution:

``````SELECT *
FROM employees
WHERE hire_date <
(SELECT max(hire_date)
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
--------+----------+----------+------------+------------+---------+------------+--------
66928 | BLAZE    | MANAGER  |      68319 | 1991-05-01 | 2750.00 |            |   3001
65646 | JONAS    | MANAGER  |      68319 | 1991-04-02 | 2957.00 |            |   2001
63679 | SANDRINE | CLERK    |      69062 | 1990-12-18 |  900.00 |            |   2001
64989 | ADELYN   | SALESMAN |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001
65271 | WADE     | SALESMAN |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001
(5 rows)
```

Explanation:

The said query in SQL that selects all employees who were hired before the most recent hire date of any employee who has 'KAYLING' as their manager from the employees table.

The WHERE clause filters the results to include only those employees whose hire_date is less than the maximum hire_date of any employee whose manager_id is equal to the emp_id of an 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: List the grade 3 employees from Perth hired recently.
Next SQL Exercise: Employees within grade 3 to 5 and belongs to SYDNEY.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿

Follow us on Facebook and Twitter for latest update.