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:
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?
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://w3resource.com/oracle-exercises/operator/oracle-operator-exercise-11.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics