SQL Exercise: List all the employees who joined before or after 1991

SQL employee Database: Exercise-51 with Solution

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

51. From the following table, write a SQL query to find those employees who joined before or after the year 1991. Return complete information about the employees.

Sample table: employees

Pictorial Presentation:

Sample Solution:

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

OR

SELECT *
FROM employees
WHERE to_char (hire_date,'YYYY') NOT LIKE '1991';

Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  67858 | SCARLET  | ANALYST  |      65646 | 1997-04-19 | 3100.00 |            |   2001
  63679 | SANDRINE | CLERK    |      69062 | 1990-12-18 |  900.00 |            |   2001
  68736 | ADNRES   | CLERK    |      67858 | 1997-05-23 | 1200.00 |            |   2001
  69324 | MARKER   | CLERK    |      67832 | 1992-01-23 | 1400.00 |            |   1001
(4 rows)

Explanation:

The given statement in SQL that returns all columns and rows from the employees table whose hire_date column's year value is not the same as the value of '1991'.

The to_char() function converts the hire_date value to a string with a four-digit year format ('YYYY'), which is then compared to the string '1991' using the NOT IN operator. The NOT IN operator excludes any rows where the year value in the hire_date column is equal to '1991'.

Practice Online

Sample Database: employee

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

Previous SQL Exercise: Employees, joined in a given month and salary range.
Next SQL Exercise: List the employees along with department name.