w3resource

Oracle: List the name (first and last name), hire date of all the employees who joined before or after 2005

Oracle Operator: Exercise-11 with Solution

Write a query to list the name (first and last name), hire date of all the employees who joined before or after 2005.

Sample table : employees


Sample Solution :-

Oracle Code :

SELECT first_name, last_name, hire_date  
FROM employees 
WHERE to_char (hire_date, 'YYYY') NOT IN ('2005');

Output:

FIRST_NAME           LAST_NAME                 HIRE_DATE
-------------------- ------------------------- ---------
Steven               King                      17-JUN-03
Lex                  De Haan                   13-JAN-01
Alexander            Hunold                    03-JAN-06
Bruce                Ernst                     21-MAY-07
Valli                Pataballa                 05-FEB-06
Diana                Lorentz                   07-FEB-07
Nancy                Greenberg                 17-AUG-02
Daniel               Faviet                    16-AUG-02
Jose Manuel          Urman                     07-MAR-06
Luis                 Popp                      07-DEC-07
Den                  Raphaely                  07-DEC-02

..........

78 rows selected.

Oracle Code :

SELECT first_name, last_name, hire_date  
FROM employees 
WHERE to_char (hire_date, 'YYYY')!='2005';

Output:

FIRST_NAME           LAST_NAME                 HIRE_DATE
-------------------- ------------------------- ---------
Steven               King                      17-JUN-03
Lex                  De Haan                   13-JAN-01
Alexander            Hunold                    03-JAN-06
Bruce                Ernst                     21-MAY-07
Valli                Pataballa                 05-FEB-06
Diana                Lorentz                   07-FEB-07
Nancy                Greenberg                 17-AUG-02
Daniel               Faviet                    16-AUG-02
Jose Manuel          Urman                     07-MAR-06
Luis                 Popp                      07-DEC-07
Den                  Raphaely                  07-DEC-02

.......

FIRST_NAME           LAST_NAME                 HIRE_DATE
-------------------- ------------------------- ---------
William              Gietz                     07-JUN-02

78 rows selected.

Oracle Code :

SELECT first_name, last_name, hire_date  
FROM employees 
WHERE to_char (hire_date, 'YYYY')<>'2005';

Output:

FIRST_NAME           LAST_NAME                 HIRE_DATE
-------------------- ------------------------- ---------
Steven               King                      17-JUN-03
Lex                  De Haan                   13-JAN-01
Alexander            Hunold                    03-JAN-06
Bruce                Ernst                     21-MAY-07
Valli                Pataballa                 05-FEB-06
Diana                Lorentz                   07-FEB-07
Nancy                Greenberg                 17-AUG-02
Daniel               Faviet                    16-AUG-02
Jose Manuel          Urman                     07-MAR-06
Luis                 Popp                      07-DEC-07
Den                  Raphaely                  07-DEC-02

.......

FIRST_NAME           LAST_NAME                 HIRE_DATE
-------------------- ------------------------- ---------
William              Gietz                     07-JUN-02

78 rows selected.

Oracle Code :

SELECT first_name, last_name, hire_date  
FROM employees 
WHERE to_char (hire_date, 'YYYY') NOT LIKE '2005';

Output:

FIRST_NAME           LAST_NAME                 HIRE_DATE
-------------------- ------------------------- ---------
Steven               King                      17-JUN-03
Lex                  De Haan                   13-JAN-01
Alexander            Hunold                    03-JAN-06
Bruce                Ernst                     21-MAY-07
Valli                Pataballa                 05-FEB-06
Diana                Lorentz                   07-FEB-07
Nancy                Greenberg                 17-AUG-02
Daniel               Faviet                    16-AUG-02
Jose Manuel          Urman                     07-MAR-06
Luis                 Popp                      07-DEC-07
Den                  Raphaely                  07-DEC-02

..........

FIRST_NAME           LAST_NAME                 HIRE_DATE
-------------------- ------------------------- ---------
William              Gietz                     07-JUN-02

78 rows selected.

Pictorial Presentation:

Pictorial: List the name (first and last name), hire date of all the employees who joined before or after 2005

Improve this sample solution and post your code through Disqus.

Previous: Write a query to list the first name, last name, Job id of all the employees except "IT_PROG " & " FI_ACCOUNT" in asc order of Salaries.
Next: Write query to list the name (first and last name), hire date of all the employees who joined on 1-JUL-2006, 24-MAR-2007, 04-JAN-2008 in ascending order of seniority.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.