Exercise 3:
1: Select the name, job and date of hire of the employees in department 20.
Display results as formatted below:
ENAME
------
SMITH JONES SCOTT ADAMS
FORD
JOB
------
CLERK
MANAGER ANALYST
CLERK
ANALYST
HIREDATE ------12/17/80 04/02/81 04/19/87 05/23/87 12/03/81
2: Select the name, job and date of hire of the employees in department 20.
Display results as formatted below:
ENAME JOB HIREDATE
------
SMITH CLERK WEDNESDAY DECEMBER 17, 1980
JONES MANAGER THURSDAY APRIL 02, 1981
SCOTT ANALYST SUNDAY APRIL 19, 1987
ADAMS CLERK SATURDAY MAY 23, 1987
FORD ANALYST THURSDAY DECEMBER 03, 1981
3: Select the name, job and date of hire of the employees in department 20.
Display results as formatted below:
ENAME JOB HIREDATE
------
SMITH CLERK WEDNESDAY 17th DECEMBER , 1980
JONES MANAGER THURSDAY 02nd APRIL , 1981
SCOTT ANALYST SUNDAY 19th APRIL , 1987
ADAMS CLERK SATURDAY 23rd MAY , 1987
FORD ANALYST THURSDAY 03rd DECEMBER , 1981
4: Select the name, job and date of hire of the employees in department 20.
Display results as formatted below:
ENAME JOB HIREDATE
------
SMITH CLERK DECEMBER 17 1980 12:12AM
JONES MANAGER APRIL 02 1981 12:04AM SCOTT ANALYST APRIL 19 1987 12:04AM ADAMS CLERK MAY 23 1987 12:05AM
FORD ANALYST DECEMBER 03 1981 12:12AM
5: Are there any employees who have worked for more than 20
years for the company ?
EMPNO ENAME DEPTNO
------Note that the actual
7369
7499 7521 7566 7698
SMITH
ALLEN
WARD
JONES BLAKE
20
30 30 20 30
data displayed will
depend upon the date the query is run. Look
at the data in emp to
7782
7844
CLARK
TURNER
10
30
k
h
h
6: Show the first day of the month in which each employee was hired.
ENAME
------
SMITH ALLEN
WARD
JONES
MARTIN
BLAKE CLARK SCOTT
KING
TURNER
ADAMS JAMES
FORD
MILLER
HIREDATE
------17-DEC-80 20-FEB-81 22-FEB-81 02-APR-81 28-SEP-81 01-MAY-81 09-JUN-81 19-APR-87 17-NOV-81 08-SEP-81 23-MAY-87 03-DEC-81 03-DEC-81 23-JAN-82
ADD_MONTH ------01-DEC-80 01-FEB-81 01-FEB-81 01-APR-81 01-SEP-81 01-MAY-81 01-JUN-81 01-APR-87 01-NOV-81 01-SEP-81 01-MAY-87 01-DEC-81 01-DEC-81 01-JAN-82
7: Show details of employee hire-dates and the date of their first payday.
Paydays occur on the last Friday of each month.
ENAME HIREDATE FIRSTPAYDAY
------
SMITH 17-DEC-80 FRIDAY 26 DECEMBER 1980 ALLEN 20-FEB-81 FRIDAY 27 FEBRUARY 1981 WARD 22-FEB-81 FRIDAY 27 FEBRUARY 1981
JONES 02-APR-81 FRIDAY 24 APRIL 1981
MARTIN 28-SEP-81 FRIDAY 25 SEPTEMBER 1981
BLAKE 01-MAY-81 FRIDAY 29 MAY 1981 CLARK 09-JUN-81 FRIDAY 26 JUNE 1981 SCOTT 19-APR-87 FRIDAY 24 APRIL 1987
KING 17-NOV-81 FRIDAY 27 NOVEMBER 1981 TURNER 08-SEP-81 FRIDAY 25 SEPTEMBER 1981
ADAMS 23-MAY-87 FRIDAY 29 MAY 1987
JAMES 03-DEC-81 FRIDAY 25 DECEMBER 1981 FORD 03-DEC-81 FRIDAY 25 DECEMBER 1981
MILLER 23-JAN-82 FRIDAY 29 JANUARY 1982