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

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

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿

## SQL: Tips of the Day

How to drop all tables from a database with one SQL query?

```USE Databasename

SELECT  'DROP TABLE [' + name + '];'
FROM    sys.tables
```

Ref: https://bit.ly/3PIUmPL

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook