w3resource

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:

employee database structure

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.