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?
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics