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.