SQL Exercise: List the details of senior employees as on year 1991

SQL subqueries on employee Database: Exercise-23 with Solution

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

23. From the following table, write a SQL query to find those employees who are senior employees as of year 1991. Return complete information about the employees.

Sample table: employees

Sample Solution:

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

OR

SELECT *
FROM employees
WHERE hire_date =
    (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
(1 row)

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.

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'.

The "IN" operator in the main query then selects all rows from the 'employees' table where the "hire_date" 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: Employees within grade 3 to 5 and belongs to SYDNEY.
Next SQL Exercise: Employees who joined in 1991 as the most senior of 1991.