SQL Exercise: Employees who joined in 1991 as the most senior of 1991

SQL subqueries on employee Database: Exercise-24 with Solution

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

24. From the following table, write a SQL query to find those employees who joined in 1991 in a designation same as the most senior person of the year 1991. Return complete information about the employees.

Sample table: employees

Sample Solution:

SELECT *
FROM employees
WHERE job_name IN
    (SELECT job_name
     FROM employees
     WHERE hire_date IN
         (SELECT min(hire_date)
          FROM employees
          WHERE to_char(hire_date,'YYYY') ='1991'));

Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  64989 | ADELYN   | SALESMAN |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001
  65271 | WADE     | SALESMAN |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001
  66564 | MADDEN   | SALESMAN |      66928 | 1991-09-28 | 1350.00 |    1500.00 |   3001
  68454 | TUCKER   | SALESMAN |      66928 | 1991-09-08 | 1600.00 |       0.00 |   3001
(4 rows)

Explanation:

The said query in SQL that selects all the employees of the 'employees' table who were hired on the earliest date in the year 1991 and have the same job name as those employees.

The subquery selects the minimum hire date of employees hired in the year 1991 and the "to_char" function extracts the year from the "hire_date" column and compare it to the string '1991'. It then selects the "job_name" of those employees.

The outer query then selects all rows from the 'employees' table where the "job_name" is equal to the value returned by the subquery.

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 details of senior employees as on year 1991.
Next SQL Exercise: Senior employees with grades over 3 work under KAYLING.