SQL Exercise: Managers senior to KAYLING and junior to SANDRINE

SQL subqueries on employee Database: Exercise-64 with Solution

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

64. From the following table, write a SQL query to find those managers who are senior to KAYLING and who are junior to SANDRINE. Return complete information about the employees.

Sample table: employees

Sample Solution:

SELECT *
FROM employees
WHERE emp_id IN
    (SELECT manager_id
     FROM employees
     WHERE hire_date<
         (SELECT hire_date
          FROM employees
          WHERE emp_name = 'KAYLING' )
       AND hire_date >
         (SELECT hire_date
          FROM employees
          WHERE emp_name = 'SANDRINE'))
  AND manager_id IS NOT NULL;

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
(1 row)

Explanation:

The said query in SQL that retrieves all employees from the 'employees' table where the employee's ID is in a subquery that selects the manager ID of employees hired between the hire dates of employees named 'SANDRINE' and 'KAYLING', and the employee's manager ID is not null.

The WHERE clause filters the results to include only those employees where the employee ID is in the list of a manager ID obtained from a subquery.

The subquery selects the manager ID of employees hired between the hire dates of employees named 'SANDRINE' and 'KAYLING'.

The AND operator further filters the results to include only those rows where the manager ID of the employee is not null.

Practice Online

Structure of employee Database:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Employees in department 1001 with salaries over ADELYN.
Next SQL Exercise: Employees belong to the department where KAYLING works.