SQL Exercise 3 Solutions
Note: Oracle and MySQL use different functions to format dates. Oracle uses the TO_CHAR function and MySQL uses the DATE_FORMAT function. You may want to look at the following links to familiarize yourself with the respective details of the functions:
MySQL DATE_FORMAT() function
Oracle TO_CHAR(datetime) function
1) -name, job, date of hire of dept 20 employees.
Format the date AS mm/dd/yy
Oracle:
SELECT ename,job,TO_CHAR(hiredate,'mm/dd/yy') AS hiredate
FROM emp
WHERE deptno=20
ORDER BY hiredate DESC;
MySQL:
SELECT ename,job,date_format(hiredate,'%m/%d/%y') AS hiredate
FROM emp
WHERE deptno=20
ORDER BY hiredate DESC;
Results:
ENAME JOB HIREDATE
------
SMITH CLERK 12/17/80
JONES MANAGER 04/02/81
SCOTT ANALYST 12/09/82
ADAMS CLERK 01/12/83
FORD ANALYST 12/03/81
2)-as above but get the day AS well (don't know why we didn't do this first time!)
Oracle:
SELECT ename,job,UPPER(TO_CHAR(hiredate,'DAY MONTHDD, YYYY')) AS hiredate
FROM emp
WHERE deptno=20
ORDER BY hiredate;
MySQL:
SELECT ename,job,UPPER(date_format(hiredate,'%W %M %d, %Y')) AS hiredate
FROM emp
WHERE deptno=20
ORDER BY hiredate DESC;
Results:
ENAME JOB HIREDATE
------
JONES MANAGER THURSDAY APRIL 02, 1981
FORD ANALYST THURSDAY DECEMBER 03, 1981
SCOTT ANALYST THURSDAY DECEMBER 09, 1982
SMITH CLERK WEDNESDAY DECEMBER 17, 1980
ADAMS CLERK WEDNESDAY JANUARY 12, 1983
3)-as above but format the date differently (ordinal dates) - can't find this in w3schools!
Oracle:
SELECT ename,job, UPPER(TO_CHAR(hiredate,'DAY DDth MONTH, YYYY')) AS hiredate
FROM emp
WHERE deptno=20;
Results:
ENAME JOB HIREDATE
------
JONES MANAGER THURSDAY 02ND APRIL , 1981
SCOTT ANALYST THURSDAY 09TH DECEMBER , 1982
FORD ANALYST THURSDAY 03RD DECEMBER , 1981
SMITH CLERK WEDNESDAY 17TH DECEMBER , 1980
ADAMS CLERK WEDNESDAY 12TH JANUARY , 1983
4) -as above but including times
Oracle:
SELECT ename,job,UPPER(TO_CHAR(hiredate,'MONTHDDYYYY HH:MIAM')) AS hiredate
FROM emp
WHERE deptno=20;
Results:
ENAME JOB HIREDATE
------
JONES MANAGER APRIL 02 1981 12:00 AM
SCOTT ANALYST DECEMBER 09 1982 12:00 AM
FORD ANALYST DECEMBER 03 1981 12:00 AM
SMITH CLERK DECEMBER 17 1980 12:00 AM
ADAMS CLERK JANUARY 12 1983 12:00 AM
5) -anyone worked here for over 20 years? (is there a "years_between" function?)
Oracle:
SELECT empno,ename,deptno, MONTHS_BETWEEN(sysdate,hiredate)/12 AS years_service
FROM emp
WHEREMONTHS_BETWEEN(sysdate,hiredate)/12 >20
ORDER BY empno;
Results:
EMPNO ENAME DEPTNO YEARS_SERVICE
------
7369 SMITH 20 23.7681002
7499 ALLEN 30 23.5933691
7521 WARD 30 23.5879927
7566 JONES 20 23.4750895
7654 MARTIN 30 22.9885303
7698 BLAKE 30 23.3944443
7782 CLARK 10 23.2896056
7839 KING 10 22.8514336
7844 TURNER 30 23.0422938
7900 JAMES 30 22.8057346
7902 FORD 20 22.8057346
7934 MILLER 10 22.6666667
(Data above will vary in your own table depending on when you run the query)
6) -show first day of month in which each employee was hired
(bit of a "fudge" this. Is there a function for first day of month? I know about last_day)
Oracle:
SELECT ename,hiredate,TO_CHAR(hiredate,'"01"-MON-YY') AS add_month
FROM emp;
SELECT ename,hiredate,lASt_day(hiredate) AS add_month
FROM emp;
Results:
ENAME HIREDATE ADD_MONTH
------
SMITH 17-DEC-80 01-DEC-80
ALLEN 20-FEB-81 01-FEB-81
WARD 22-FEB-81 01-FEB-81
JONES 02-APR-81 01-APR-81
BLAKE 01-MAY-81 01-MAY-81
CLARK 09-JUN-81 01-JUN-81
TURNER 08-SEP-81 01-SEP-81
MARTIN 28-SEP-81 01-SEP-81
KING 17-NOV-81 01-NOV-81
FORD 03-DEC-81 01-DEC-81
JAMES 03-DEC-81 01-DEC-81
MILLER 23-JAN-82 01-JAN-82
SCOTT 09-DEC-82 01-DEC-82
ADAMS 12-JAN-83 01-JAN-83
7) -show hiredates and first paydays (last friday of hiredate month!)
Find the last day of the month, subtract seven from it, then find the next Friday.
Oracle:
SELECT ename, hiredate,
TO_CHAR(next_day(lASt_day(hiredate)-7,'FRIDAY'),'DAY DD MONTH YYYY') AS PAYDAY
FROM emp;
Results:
ENAME HIREDATE PAYDAY
------
KING 17-NOV-81 FRIDAY 27 NOVEMBER 1981
BLAKE 01-MAY-81 FRIDAY 29 MAY 1981
CLARK 09-JUN-81 FRIDAY 26 JUNE 1981
JONES 02-APR-81 FRIDAY 24 APRIL 1981
SCOTT 01-APR-87 FRIDAY 24 APRIL 1987
FORD 03-DEC-81 FRIDAY 25 DECEMBER 1981
SMITH 17-DEC-80 FRIDAY 26 DECEMBER 1980
ALLEN 20-FEB-81 FRIDAY 27 FEBRUARY 1981
WARD 22-FEB-81 FRIDAY 27 FEBRUARY 1981
MARTIN 28-SEP-81 FRIDAY 25 SEPTEMBER 1981
TURNER 08-SEP-81 FRIDAY 25 SEPTEMBER 1981
ADAMS 01-MAY-87 FRIDAY 29 MAY 1987
JAMES 03-DEC-81 FRIDAY 25 DECEMBER 1981
MILLER 23-JAN-82 FRIDAY 29 JANUARY 1982