SQL> SELECT TO_CHAR (SYSDATE, 'DD-MON-RRRR HH24:MI:SS') FROM DUAL;
TO_CHAR(SYSDATE,'DD-
--------------------
01-FEB-2011 15:24:12
SQL> ED
Wrote file afiedt.buf
1* SELECT SYSDATE, 'DD-MON-RRRR HH24:MI:SS' FROM DUAL
SQL> /
SYSDATE 'DD-MON-RRRRHH24:MI:SS
--------- ----------------------
01-FEB-11 DD-MON-RRRR HH24:MI:SS
1* SELECT TO_CHAR(SYSDATE,'DD-MON-RR') FROM DUAL
SQL> /
TO_CHAR(S
---------
01-FEB-11
SQL> ED
Wrote file afiedt.buf
1* SELECT TO_CHAR(SYSDATE,'DD-MON-RR') "SYSTEM DATE" FROM DUAL
SQL> /
SYSTEM DA
---------
01-FEB-11
SQL> ED
Wrote file afiedt.buf
1* SELECT TO_CHAR(SYSDATE,'DD-MON-RR') "SYS DATE" FROM DUAL
SQL> /
SYS DATE
---------
01-FEB-11
1* SELECT SYSDATE, HIREDATE, MONTHS_BETWEEN(SYSDATE, HIREDATE) "MONTH-B/W" FROM EMP
SQL> /
SYSDATE HIREDATE MONTH-B/W
--------- --------- ----------
01-FEB-11 17-DEC-80 361.504772
01-FEB-11 20-FEB-81 359.407998
01-FEB-11 22-FEB-81 359.343482
01-FEB-11 02-APR-81 357.988643
01-FEB-11 28-SEP-81 352.149934
01-FEB-11 01-MAY-81 357
01-FEB-11 09-JUN-81 355.762837
01-FEB-11 19-APR-87 285.440256
01-FEB-11 17-NOV-81 350.504772
01-FEB-11 08-SEP-81 352.795095
01-FEB-11 23-MAY-87 284.311224
01-FEB-11 03-DEC-81 349.956385
01-FEB-11 03-DEC-81 349.956385
01-FEB-11 23-JAN-82 348.311224
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1* SELECT SYSDATE, HIREDATE, ROUND(MONTHS_BETWEEN(SYSDATE, HIREDATE),0) "MONTH-B/W" FROM EMP
SQL> /
SYSDATE HIREDATE MONTH-B/W
--------- --------- ----------
01-FEB-11 17-DEC-80 362
01-FEB-11 20-FEB-81 359
01-FEB-11 22-FEB-81 359
01-FEB-11 02-APR-81 358
01-FEB-11 28-SEP-81 352
01-FEB-11 01-MAY-81 357
01-FEB-11 09-JUN-81 356
01-FEB-11 19-APR-87 285
01-FEB-11 17-NOV-81 351
01-FEB-11 08-SEP-81 353
01-FEB-11 23-MAY-87 284
01-FEB-11 03-DEC-81 350
01-FEB-11 03-DEC-81 350
01-FEB-11 23-JAN-82 348
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT SYSDATE, HIREDATE, ROUND(MONTHS_BETWEEN(SYSDATE, HIREDATE),0) "MONTH-B/W",
2* (SYSDATE-HIREDATE)/30 "MONTHSSS" FROM EMP
SQL> /
SYSDATE HIREDATE MONTH-B/W MONTHSSS
--------- --------- ---------- ----------
01-FEB-11 17-DEC-80 362 366.788296
01-FEB-11 20-FEB-81 359 364.621629
01-FEB-11 22-FEB-81 359 364.554963
01-FEB-11 02-APR-81 358 363.254963
01-FEB-11 28-SEP-81 352 357.288296
01-FEB-11 01-MAY-81 357 362.288296
01-FEB-11 09-JUN-81 356 360.988296
01-FEB-11 19-APR-87 285 289.654963
01-FEB-11 17-NOV-81 351 355.621629
01-FEB-11 08-SEP-81 353 357.954963
01-FEB-11 23-MAY-87 284 288.521629
01-FEB-11 03-DEC-81 350 355.088296
01-FEB-11 03-DEC-81 350 355.088296
01-FEB-11 23-JAN-82 348 353.388296
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT SYSDATE, HIREDATE, ROUND(MONTHS_BETWEEN(SYSDATE, HIREDATE),0) "MONTH-B/W",
2* ROUND((SYSDATE-HIREDATE)/30,0) "MONTHSSS" FROM EMP
SQL> /
SYSDATE HIREDATE MONTH-B/W MONTHSSS
--------- --------- ---------- ----------
01-FEB-11 17-DEC-80 362 367
01-FEB-11 20-FEB-81 359 365
01-FEB-11 22-FEB-81 359 365
01-FEB-11 02-APR-81 358 363
01-FEB-11 28-SEP-81 352 357
01-FEB-11 01-MAY-81 357 362
01-FEB-11 09-JUN-81 356 361
01-FEB-11 19-APR-87 285 290
01-FEB-11 17-NOV-81 351 356
01-FEB-11 08-SEP-81 353 358
01-FEB-11 23-MAY-87 284 289
01-FEB-11 03-DEC-81 350 355
01-FEB-11 03-DEC-81 350 355
01-FEB-11 23-JAN-82 348 353
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT SYSDATE, HIREDATE, ROUND(MONTHS_BETWEEN(SYSDATE, HIREDATE),0) "MONTH-B/W",
2* ROUND((SYSDATE-HIREDATE)/30,0) "MONTHSSS" FROM EMP
SQL> /
SYSDATE HIREDATE MONTH-B/W MONTHSSS
--------- --------- ---------- ----------
01-FEB-11 17-DEC-80 362 367
01-FEB-11 20-FEB-81 359 365
01-FEB-11 22-FEB-81 359 365
01-FEB-11 02-APR-81 358 363
01-FEB-11 28-SEP-81 352 357
01-FEB-11 01-MAY-81 357 362
01-FEB-11 09-JUN-81 356 361
01-FEB-11 19-APR-87 285 290
01-FEB-11 17-NOV-81 351 356
01-FEB-11 08-SEP-81 353 358
01-FEB-11 23-MAY-87 284 289
01-FEB-11 03-DEC-81 350 355
01-FEB-11 03-DEC-81 350 355
01-FEB-11 23-JAN-82 348 353
1* SELECT SYSDATE, NEXT_DAY(SYSDATE,'WEDNESDAY') FROM DUAL
SQL> /
SYSDATE NEXT_DAY(
--------- ---------
01-FEB-11 02-FEB-11
1* SELECT SYSDATE, LAST_DAY(SYSDATE) FROM DUAL
SQL> /
SYSDATE LAST_DAY(
--------- ---------
01-FEB-11 28-FEB-11
SQL> -- LAST_DAY,, RETURNS LASTDAY OF THE MONTH ----
SQL> SELECT SYSDATE, ADD_MONTHS(SYSDATE, 2) FROM DUAL;
SYSDATE ADD_MONTH
--------- ---------
01-FEB-11 01-APR-11
1* SELECT SYSDATE, ADD_MONTHS(SYSDATE, -2) FROM DUAL
SQL> /
SYSDATE ADD_MONTH
--------- ---------
01-FEB-11 01-DEC-10
SQL> SELECT '12' * 5, '01-FEB-11' FROM DUAL;
'12'*5 '01-FEB-1
---------- ---------
60 01-FEB-11
SQL> ED
Wrote file afiedt.buf
1* SELECT '12' * 5, '01-FEB-11', '3*5' FROM DUAL
SQL> /
'12'*5 '01-FEB-1 '3*
---------- --------- ---
60 01-FEB-11 3*5
SQL> ED
Wrote file afiedt.buf
1* SELECT '12' * 5, '01-FEB-11', 3*'5' FROM DUAL
SQL> /
'12'*5 '01-FEB-1 3*'5'
---------- --------- ----------
60 01-FEB-11 15
SQL> -- CONVERSION FUNCTION,,, 'IGNORES SINGLE CODE'-----
SQL> -- ALSO CALLED IMPLICIT CONVERSION --
1* select SYSDATE, TO_CHAR(SYSDATE, 'D, DD, DDD, DAY, DY, W, WWW') "FORMATS" FROM DUAL
SQL> /
SYSDATE FORMATS
--------- ----------------------------------
01-FEB-11 3, 01, 032, TUESDAY , TUE, 1, 051
SQL> ED
Wrote file afiedt.buf
1* select SYSDATE, TO_CHAR(SYSDATE, 'D, DD, DDD, DAY, DY, W, WWW') "FORMATS" FROM DUAL
SQL> /
SYSDATE FORMATS
--------- ----------------------------------
01-FEB-11 3, 01, 032, TUESDAY , TUE, 1, 051
SQL> ED
Wrote file afiedt.buf
1* select SYSDATE, TO_CHAR(SYSDATE, 'D, DD, DDD, DAY, DY, W, WWW, MM, MON, MONTH, RM') "FORMATS" FROM DUAL
SQL> /
SYSDATE FORMATS
--------- ------------------------------------------------------------
01-FEB-11 3, 01, 032, TUESDAY , TUE, 1, 051, 02, FEB, FEBRUARY , II
SQL> ed
Wrote file afiedt.buf
1* select SYSDATE, TO_CHAR(SYSDATE, 'DD-MM-RRRR SCC,CC') "FORMATS" FROM DUAL
SQL> /
SYSDATE FORMATS
--------- -----------------
01-FEB-11 01-02-2011 21,21
SQL> ED
Wrote file afiedt.buf
1* select SYSDATE, TO_CHAR(SYSDATE, 'DD-MM-RRRR SCC AD,CC B.C.') "FORMATS" FROM DUAL
SQL> /
SYSDATE FORMATS
--------- -------------------------
01-FEB-11 01-02-2011 21 AD,21 A.D.
SQL> -- TIME FORMAT---
Saturday, February 5, 2011
Oracle - Number Date Functions
SQL> --------NUMBER FUNCTIONS------------
SQL> SELECT ROUND(45,923) FROM DUAL;
ROUND(45,923)
-------------
45
SQL> ED
Wrote file afiedt.buf
1* SELECT ROUND(44.569) FROM DUAL
SQL> /
ROUND(44.569)
-------------
45
SQL> ED
Wrote file afiedt.buf
1* SELECT ROUND(44.569,2) FROM DUAL
SQL> /
ROUND(44.569,2)
---------------
44.57
SQL> ED
Wrote file afiedt.buf
1 SELECT ROUND(33.987),
2 ROUND(23.569,3),
3* ROUND(94.34,2) FROM DUAL
SQL> /
ROUND(33.987) ROUND(23.569,3) ROUND(94.34,2)
------------- --------------- --------------
34 23.569 94.34
SQL> ED
Wrote file afiedt.buf
1 SELECT ROUND(33.987),
2 ROUND(23.569,1),
3* ROUND(94.34,2) FROM DUAL
SQL> /
ROUND(33.987) ROUND(23.569,1) ROUND(94.34,2)
------------- --------------- --------------
34 23.6 94.34
SQL> ED
Wrote file afiedt.buf
1* SELECT ROUND(23.569) FROM DUAL
SQL> /
ROUND(23.569)
-------------
24
SQL> ED
Wrote file afiedt.buf
1* SELECT ROUND(23.569,2) FROM DUAL
SQL> /
ROUND(23.569,2)
---------------
23.57
SQL> ED
Wrote file afiedt.buf
1* SELECT ROUND(23.569,-1), ROUND(44.569,0) FROM DUAL
SQL> /
ROUND(23.569,-1) ROUND(44.569,0)
---------------- ---------------
20 45
SQL> ED
Wrote file afiedt.buf
1* SELECT ROUND(230.569,-2), ROUND(44.569,0) FROM DUAL
SQL> /
ROUND(230.569,-2) ROUND(44.569,0)
----------------- ---------------
200 45
SQL> ED
Wrote file afiedt.buf
1* SELECT TRUNC(230.569,1), ROUND(44.569,-1) FROM DUAL
SQL> /
TRUNC(230.569,1) ROUND(44.569,-1)
---------------- ----------------
230.5 40
SQL> ED
Wrote file afiedt.buf
1* SELECT TRUNC(230.769) FROM DUAL
SQL> /
TRUNC(230.769)
--------------
230
SQL> -- TRUNC USED FOR AS IT IS PRINTING WHILE ROUND FUNCTION SIGNIFICANTS THE NUMBER------------
SQL> ED
Wrote file afiedt.buf
1* SELECT TRUNC(230.769,3) FROM DUAL
SQL> /
TRUNC(230.769,3)
----------------
230.769
SQL> SELECT MOD (8,2) FROM DUAL;
MOD(8,2)
----------
0
SQL> ED
Wrote file afiedt.buf
1* SELECT MOD (9,2) FROM DUAL
SQL> /
MOD(9,2)
----------
1
SQL> ED
Wrote file afiedt.buf
1* SELECT MOD (14,3) FROM DUAL
SQL> /
MOD(14,3)
----------
2
SQL> -- MOD FUNCTION RETURNS THE REMINDER------
SQL> --CHECK LEAP YEAR OR NOT----
SQL> SELECT MOD (2012,4) FROM DUAL;
MOD(2012,4)
-----------
0
SQL> ED
Wrote file afiedt.buf
1* SELECT MOD (2011,4) FROM DUAL
SQL> /
MOD(2011,4)
-----------
3
SQL> -- RETURNS '0' INDICATE THAT ITS A LEAP YEAR--
SQL> --- DATE FUNCTIONS -----------
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
---------
01-FEB-11
SQL> ED
Wrote file afiedt.buf
1* SELECT SYSDATE, SYSDATE + 5 FROM DUAL
SQL> /
SYSDATE SYSDATE+5
--------- ---------
01-FEB-11 06-FEB-11
SQL> ED
Wrote file afiedt.buf
1* SELECT SYSDATE, SYSDATE + 5 "AFTER 5 DAYS" FROM DUAL
SQL> /
SYSDATE AFTER 5 D
--------- ---------
01-FEB-11 06-FEB-11
SQL> ED
Wrote file afiedt.buf
1* SELECT SYSDATE, SYSDATE + 5 "AFT_5DAY" SYSDATE-'365' FROM DUAL
SQL> /
SELECT SYSDATE, SYSDATE + 5 "AFT_5DAY" SYSDATE-'365' FROM DUAL
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> ED
Wrote file afiedt.buf
1 SELECT SYSDATE,
2 SYSDATE + 5 "AFT_5DAY",
3* SYSDATE-'365' FROM DUAL
SQL> /
SYSDATE AFT_5DAY SYSDATE-'
--------- --------- ---------
01-FEB-11 06-FEB-11 01-FEB-10
SQL> ED
Wrote file afiedt.buf
1 SELECT SYSDATE,
2 SYSDATE + 5 "AFT_5DAY",
3* SYSDATE-365 FROM DUAL
SQL> /
SYSDATE AFT_5DAY SYSDATE-3
--------- --------- ---------
01-FEB-11 06-FEB-11 01-FEB-10
SQL> ED
Wrote file afiedt.buf
1 SELECT SYSDATE,
2 SYSDATE + 5 "AFT_5DAY",
3* SYSDATE-365 "BEF_365DAY" FROM DUAL
SQL> /
SYSDATE AFT_5DAY BEF_365DA
--------- --------- ---------
01-FEB-11 06-FEB-11 01-FEB-10
SQL> ED
Wrote file afiedt.buf
1 SELECT SYSDATE,
2 SYSDATE + 5 "AFT_5DAY",
3* SYSDATE-365 "BEF_365DAY" FROM DUAL
SQL> SELECT ENAME, ENAME, SYSDATE, HIREDATE, SYSDATE-HIREDATE "EMP_DAYS TOTAL" FROM EMP;
ENAME ENAME SYSDATE HIREDATE EMP_DAYS TOTAL
---------- ---------- --------- --------- --------------
01-FEB-11
SMITH SMITH 01-FEB-11 17-DEC-80 11003.4951
ALLEN ALLEN 01-FEB-11 20-FEB-81 10938.4951
WARD WARD 01-FEB-11 22-FEB-81 10936.4951
JONES JONES 01-FEB-11 02-APR-81 10897.4951
MARTIN MARTIN 01-FEB-11 28-SEP-81 10718.4951
BLAKE BLAKE 01-FEB-11 01-MAY-81 10868.4951
CLARK CLARK 01-FEB-11 09-JUN-81 10829.4951
SCOTT SCOTT 01-FEB-11 19-APR-87 8689.49508
KING KING 01-FEB-11 17-NOV-81 10668.4951
TURNER TURNER 01-FEB-11 08-SEP-81 10738.4951
ADAMS ADAMS 01-FEB-11 23-MAY-87 8655.49508
JAMES JAMES 01-FEB-11 03-DEC-81 10652.4951
FORD FORD 01-FEB-11 03-DEC-81 10652.4951
MILLER MILLER 01-FEB-11 23-JAN-82 10601.4951
15 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT ENAME,
2 ENAME,
3 HIREDATE,
4* SYSDATE-HIREDATE "EMP_DAYS TOTAL" FROM EMP
SQL> /
ENAME ENAME HIREDATE EMP_DAYS TOTAL
---------- ---------- --------- --------------
SMITH SMITH 17-DEC-80 11003.4954
ALLEN ALLEN 20-FEB-81 10938.4954
WARD WARD 22-FEB-81 10936.4954
JONES JONES 02-APR-81 10897.4954
MARTIN MARTIN 28-SEP-81 10718.4954
BLAKE BLAKE 01-MAY-81 10868.4954
CLARK CLARK 09-JUN-81 10829.4954
SCOTT SCOTT 19-APR-87 8689.49544
KING KING 17-NOV-81 10668.4954
TURNER TURNER 08-SEP-81 10738.4954
ADAMS ADAMS 23-MAY-87 8655.49544
JAMES JAMES 03-DEC-81 10652.4954
FORD FORD 03-DEC-81 10652.4954
MILLER MILLER 23-JAN-82 10601.4954
15 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT ENAME,
2 ENAME,
3 HIREDATE,
4* ROUND(SYSDATE-HIREDATE) "EMP_DAYS TOTAL" FROM EMP
SQL> /
ENAME ENAME HIREDATE EMP_DAYS TOTAL
---------- ---------- --------- --------------
SMITH SMITH 17-DEC-80 11003
ALLEN ALLEN 20-FEB-81 10938
WARD WARD 22-FEB-81 10936
JONES JONES 02-APR-81 10897
MARTIN MARTIN 28-SEP-81 10718
BLAKE BLAKE 01-MAY-81 10868
CLARK CLARK 09-JUN-81 10829
SCOTT SCOTT 19-APR-87 8689
KING KING 17-NOV-81 10668
TURNER TURNER 08-SEP-81 10738
ADAMS ADAMS 23-MAY-87 8655
JAMES JAMES 03-DEC-81 10652
FORD FORD 03-DEC-81 10652
MILLER MILLER 23-JAN-82 10601
15 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT ENAME,
2 ENAME,
3 HIREDATE,
4* TRUNC(SYSDATE-HIREDATE) "EMP_DAYS TOTAL" FROM EMP
SQL> /
ENAME ENAME HIREDATE EMP_DAYS TOTAL
---------- ---------- --------- --------------
SMITH SMITH 17-DEC-80 11003
ALLEN ALLEN 20-FEB-81 10938
WARD WARD 22-FEB-81 10936
JONES JONES 02-APR-81 10897
MARTIN MARTIN 28-SEP-81 10718
BLAKE BLAKE 01-MAY-81 10868
CLARK CLARK 09-JUN-81 10829
SCOTT SCOTT 19-APR-87 8689
KING KING 17-NOV-81 10668
TURNER TURNER 08-SEP-81 10738
ADAMS ADAMS 23-MAY-87 8655
JAMES JAMES 03-DEC-81 10652
FORD FORD 03-DEC-81 10652
MILLER MILLER 23-JAN-82 10601
15 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT ENAME,
2 ENAME,
3 DEPTNO,
4 HIREDATE,
5 TRUNC(SYSDATE-HIREDATE) "EMP_DAYS TOTAL" FROM EMP
6* WHERE DEPTNO = 10
SQL> /
ENAME ENAME DEPTNO HIREDATE EMP_DAYS TOTAL
---------- ---------- ---------- --------- --------------
CLARK CLARK 10 09-JUN-81 10829
KING KING 10 17-NOV-81 10668
MILLER MILLER 10 23-JAN-82 10601
SQL> ED
Wrote file afiedt.buf
1 SELECT EMPNO,
2 ENAME,
3 JOB,
4 DEPTNO,
5 HIREDATE,
6 TRUNC(SYSDATE-HIREDATE) "EMP_DAYS TOTAL" FROM EMP
7* WHERE DEPTNO = 10
SQL> /
EMPNO ENAME JOB DEPTNO HIREDATE EMP_DAYS TOTAL
---------- ---------- --------- ---------- --------- --------------
7782 CLARK MANAGER 10 09-JUN-81 10829
7839 KING PRESIDENT 10 17-NOV-81 10668
7934 MILLER CLERK 10 23-JAN-82 10601
SQL> ED
Wrote file afiedt.buf
1 SELECT EMPNO,
2 ENAME,
3 JOB,
4 DEPTNO,
5 HIREDATE,
6 TRUNC(SYSDATE-HIREDATE) "EMP_DAYS TOTAL",
7 (SYSDATE-HIREDATE)/30
8 FROM EMP
9* WHERE DEPTNO = 10
SQL> /
EMPNO ENAME JOB DEPTNO HIREDATE EMP_DAYS TOTAL (SYSDATE-HIREDATE)/30
---------- ---------- --------- ---------- --------- -------------- ---------------------
7782 CLARK MANAGER 10 09-JUN-81 10829 360.983443
7839 KING PRESIDENT 10 17-NOV-81 10668 355.616777
7934 MILLER CLERK 10 23-JAN-82 10601 353.383443
SQL> ed
Wrote file afiedt.buf
1 SELECT EMPNO,
2 ENAME,
3 JOB,
4 DEPTNO,
5 HIREDATE,
6 TRUNC(SYSDATE-HIREDATE) "EMP_DAYS TOTAL",
7 TRUNC((SYSDATE-HIREDATE)/30) "NO_MONTHS"
8 FROM EMP
9* WHERE DEPTNO = 10
SQL> /
EMPNO ENAME JOB DEPTNO HIREDATE EMP_DAYS TOTAL NO_MONTHS
---------- ---------- --------- ---------- --------- -------------- ----------
7782 CLARK MANAGER 10 09-JUN-81 10829 360
7839 KING PRESIDENT 10 17-NOV-81 10668 355
7934 MILLER CLERK 10 23-JAN-82 10601 353
SQL> ED
Wrote file afiedt.buf
1 SELECT EMPNO,
2 ENAME,
3 JOB,
4 DEPTNO,
5 HIREDATE,
6 TRUNC(SYSDATE-HIREDATE) "EMP_DAYS TOTAL",
7 TRUNC((SYSDATE-HIREDATE)/12) "NO_YEARS",
8 TRUNC((SYSDATE-HIREDATE)/30) "NO_MONTHS"
9 FROM EMP
10* WHERE DEPTNO = 10
SQL> /
EMPNO ENAME JOB DEPTNO HIREDATE EMP_DAYS TOTAL NO_YEARS NO_MONTHS
---------- ---------- --------- ---------- --------- -------------- ---------- ----------
7782 CLARK MANAGER 10 09-JUN-81 10829 902 360
7839 KING PRESIDENT 10 17-NOV-81 10668 889 355
7934 MILLER CLERK 10 23-JAN-82 10601 883 353
SQL> ED
Wrote file afiedt.buf
1 SELECT EMPNO,
2 ENAME,
3 JOB,
4 DEPTNO,
5 HIREDATE,
6 TRUNC(SYSDATE-HIREDATE) "EMP_DAYS TOTAL",
7 TRUNC((SYSDATE-HIREDATE)/365) "NO_YEARS",
8 TRUNC((SYSDATE-HIREDATE)/30) "NO_MONTHS"
9 FROM EMP
10* WHERE DEPTNO = 10
SQL> /
EMPNO ENAME JOB DEPTNO HIREDATE EMP_DAYS TOTAL NO_YEARS NO_MONTHS
---------- ---------- --------- ---------- --------- -------------- ---------- ----------
7782 CLARK MANAGER 10 09-JUN-81 10829 29 360
7839 KING PRESIDENT 10 17-NOV-81 10668 29 355
7934 MILLER CLERK 10 23-JAN-82 10601 29 353
SQL> ED
Wrote file afiedt.buf
1 SELECT EMPNO,
2 ENAME,
3 JOB,
4 DEPTNO,
5 HIREDATE,
6 TRUNC(SYSDATE-HIREDATE) "EMP_DAYS TOTAL",
7 TRUNC((SYSDATE-HIREDATE)/365) "NO_YEARS",
8 TRUNC((SYSDATE-HIREDATE)/30) "NO_MONTHS"
9 FROM EMP
10* WHERE DEPTNO = 10
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
111 BB 122
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
15 rows selected.
SQL> DELETE empno = 111
2 ;
DELETE empno = 111
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> DELETE EMP WHERE EMPNO = 111;
1 row deleted.
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> SELECT EMPNO,
2 ENAME,
3 JOB,
4 DEPTNO,
5 HIREDATE,
6 TRUNC(SYSDATE-HIREDATE) "EMP_DAYS TOTAL",
7 TRUNC((SYSDATE-HIREDATE)/365) "NO_YEARS",
8 TRUNC((SYSDATE-HIREDATE)/30) "NO_MONTHS"
9 FROM EMP
10 WHERE DEPTNO = 10
11 /
EMPNO ENAME JOB DEPTNO HIREDATE EMP_DAYS TOTAL NO_YEARS NO_MONTHS
---------- ---------- --------- ---------- --------- -------------- ---------- ----------
7782 CLARK MANAGER 10 09-JUN-81 10829 29 360
7839 KING PRESIDENT 10 17-NOV-81 10668 29 355
7934 MILLER CLERK 10 23-JAN-82 10601 29 353
SQL> SELECT ROUND(45,923) FROM DUAL;
ROUND(45,923)
-------------
45
SQL> ED
Wrote file afiedt.buf
1* SELECT ROUND(44.569) FROM DUAL
SQL> /
ROUND(44.569)
-------------
45
SQL> ED
Wrote file afiedt.buf
1* SELECT ROUND(44.569,2) FROM DUAL
SQL> /
ROUND(44.569,2)
---------------
44.57
SQL> ED
Wrote file afiedt.buf
1 SELECT ROUND(33.987),
2 ROUND(23.569,3),
3* ROUND(94.34,2) FROM DUAL
SQL> /
ROUND(33.987) ROUND(23.569,3) ROUND(94.34,2)
------------- --------------- --------------
34 23.569 94.34
SQL> ED
Wrote file afiedt.buf
1 SELECT ROUND(33.987),
2 ROUND(23.569,1),
3* ROUND(94.34,2) FROM DUAL
SQL> /
ROUND(33.987) ROUND(23.569,1) ROUND(94.34,2)
------------- --------------- --------------
34 23.6 94.34
SQL> ED
Wrote file afiedt.buf
1* SELECT ROUND(23.569) FROM DUAL
SQL> /
ROUND(23.569)
-------------
24
SQL> ED
Wrote file afiedt.buf
1* SELECT ROUND(23.569,2) FROM DUAL
SQL> /
ROUND(23.569,2)
---------------
23.57
SQL> ED
Wrote file afiedt.buf
1* SELECT ROUND(23.569,-1), ROUND(44.569,0) FROM DUAL
SQL> /
ROUND(23.569,-1) ROUND(44.569,0)
---------------- ---------------
20 45
SQL> ED
Wrote file afiedt.buf
1* SELECT ROUND(230.569,-2), ROUND(44.569,0) FROM DUAL
SQL> /
ROUND(230.569,-2) ROUND(44.569,0)
----------------- ---------------
200 45
SQL> ED
Wrote file afiedt.buf
1* SELECT TRUNC(230.569,1), ROUND(44.569,-1) FROM DUAL
SQL> /
TRUNC(230.569,1) ROUND(44.569,-1)
---------------- ----------------
230.5 40
SQL> ED
Wrote file afiedt.buf
1* SELECT TRUNC(230.769) FROM DUAL
SQL> /
TRUNC(230.769)
--------------
230
SQL> -- TRUNC USED FOR AS IT IS PRINTING WHILE ROUND FUNCTION SIGNIFICANTS THE NUMBER------------
SQL> ED
Wrote file afiedt.buf
1* SELECT TRUNC(230.769,3) FROM DUAL
SQL> /
TRUNC(230.769,3)
----------------
230.769
SQL> SELECT MOD (8,2) FROM DUAL;
MOD(8,2)
----------
0
SQL> ED
Wrote file afiedt.buf
1* SELECT MOD (9,2) FROM DUAL
SQL> /
MOD(9,2)
----------
1
SQL> ED
Wrote file afiedt.buf
1* SELECT MOD (14,3) FROM DUAL
SQL> /
MOD(14,3)
----------
2
SQL> -- MOD FUNCTION RETURNS THE REMINDER------
SQL> --CHECK LEAP YEAR OR NOT----
SQL> SELECT MOD (2012,4) FROM DUAL;
MOD(2012,4)
-----------
0
SQL> ED
Wrote file afiedt.buf
1* SELECT MOD (2011,4) FROM DUAL
SQL> /
MOD(2011,4)
-----------
3
SQL> -- RETURNS '0' INDICATE THAT ITS A LEAP YEAR--
SQL> --- DATE FUNCTIONS -----------
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
---------
01-FEB-11
SQL> ED
Wrote file afiedt.buf
1* SELECT SYSDATE, SYSDATE + 5 FROM DUAL
SQL> /
SYSDATE SYSDATE+5
--------- ---------
01-FEB-11 06-FEB-11
SQL> ED
Wrote file afiedt.buf
1* SELECT SYSDATE, SYSDATE + 5 "AFTER 5 DAYS" FROM DUAL
SQL> /
SYSDATE AFTER 5 D
--------- ---------
01-FEB-11 06-FEB-11
SQL> ED
Wrote file afiedt.buf
1* SELECT SYSDATE, SYSDATE + 5 "AFT_5DAY" SYSDATE-'365' FROM DUAL
SQL> /
SELECT SYSDATE, SYSDATE + 5 "AFT_5DAY" SYSDATE-'365' FROM DUAL
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> ED
Wrote file afiedt.buf
1 SELECT SYSDATE,
2 SYSDATE + 5 "AFT_5DAY",
3* SYSDATE-'365' FROM DUAL
SQL> /
SYSDATE AFT_5DAY SYSDATE-'
--------- --------- ---------
01-FEB-11 06-FEB-11 01-FEB-10
SQL> ED
Wrote file afiedt.buf
1 SELECT SYSDATE,
2 SYSDATE + 5 "AFT_5DAY",
3* SYSDATE-365 FROM DUAL
SQL> /
SYSDATE AFT_5DAY SYSDATE-3
--------- --------- ---------
01-FEB-11 06-FEB-11 01-FEB-10
SQL> ED
Wrote file afiedt.buf
1 SELECT SYSDATE,
2 SYSDATE + 5 "AFT_5DAY",
3* SYSDATE-365 "BEF_365DAY" FROM DUAL
SQL> /
SYSDATE AFT_5DAY BEF_365DA
--------- --------- ---------
01-FEB-11 06-FEB-11 01-FEB-10
SQL> ED
Wrote file afiedt.buf
1 SELECT SYSDATE,
2 SYSDATE + 5 "AFT_5DAY",
3* SYSDATE-365 "BEF_365DAY" FROM DUAL
SQL> SELECT ENAME, ENAME, SYSDATE, HIREDATE, SYSDATE-HIREDATE "EMP_DAYS TOTAL" FROM EMP;
ENAME ENAME SYSDATE HIREDATE EMP_DAYS TOTAL
---------- ---------- --------- --------- --------------
01-FEB-11
SMITH SMITH 01-FEB-11 17-DEC-80 11003.4951
ALLEN ALLEN 01-FEB-11 20-FEB-81 10938.4951
WARD WARD 01-FEB-11 22-FEB-81 10936.4951
JONES JONES 01-FEB-11 02-APR-81 10897.4951
MARTIN MARTIN 01-FEB-11 28-SEP-81 10718.4951
BLAKE BLAKE 01-FEB-11 01-MAY-81 10868.4951
CLARK CLARK 01-FEB-11 09-JUN-81 10829.4951
SCOTT SCOTT 01-FEB-11 19-APR-87 8689.49508
KING KING 01-FEB-11 17-NOV-81 10668.4951
TURNER TURNER 01-FEB-11 08-SEP-81 10738.4951
ADAMS ADAMS 01-FEB-11 23-MAY-87 8655.49508
JAMES JAMES 01-FEB-11 03-DEC-81 10652.4951
FORD FORD 01-FEB-11 03-DEC-81 10652.4951
MILLER MILLER 01-FEB-11 23-JAN-82 10601.4951
15 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT ENAME,
2 ENAME,
3 HIREDATE,
4* SYSDATE-HIREDATE "EMP_DAYS TOTAL" FROM EMP
SQL> /
ENAME ENAME HIREDATE EMP_DAYS TOTAL
---------- ---------- --------- --------------
SMITH SMITH 17-DEC-80 11003.4954
ALLEN ALLEN 20-FEB-81 10938.4954
WARD WARD 22-FEB-81 10936.4954
JONES JONES 02-APR-81 10897.4954
MARTIN MARTIN 28-SEP-81 10718.4954
BLAKE BLAKE 01-MAY-81 10868.4954
CLARK CLARK 09-JUN-81 10829.4954
SCOTT SCOTT 19-APR-87 8689.49544
KING KING 17-NOV-81 10668.4954
TURNER TURNER 08-SEP-81 10738.4954
ADAMS ADAMS 23-MAY-87 8655.49544
JAMES JAMES 03-DEC-81 10652.4954
FORD FORD 03-DEC-81 10652.4954
MILLER MILLER 23-JAN-82 10601.4954
15 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT ENAME,
2 ENAME,
3 HIREDATE,
4* ROUND(SYSDATE-HIREDATE) "EMP_DAYS TOTAL" FROM EMP
SQL> /
ENAME ENAME HIREDATE EMP_DAYS TOTAL
---------- ---------- --------- --------------
SMITH SMITH 17-DEC-80 11003
ALLEN ALLEN 20-FEB-81 10938
WARD WARD 22-FEB-81 10936
JONES JONES 02-APR-81 10897
MARTIN MARTIN 28-SEP-81 10718
BLAKE BLAKE 01-MAY-81 10868
CLARK CLARK 09-JUN-81 10829
SCOTT SCOTT 19-APR-87 8689
KING KING 17-NOV-81 10668
TURNER TURNER 08-SEP-81 10738
ADAMS ADAMS 23-MAY-87 8655
JAMES JAMES 03-DEC-81 10652
FORD FORD 03-DEC-81 10652
MILLER MILLER 23-JAN-82 10601
15 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT ENAME,
2 ENAME,
3 HIREDATE,
4* TRUNC(SYSDATE-HIREDATE) "EMP_DAYS TOTAL" FROM EMP
SQL> /
ENAME ENAME HIREDATE EMP_DAYS TOTAL
---------- ---------- --------- --------------
SMITH SMITH 17-DEC-80 11003
ALLEN ALLEN 20-FEB-81 10938
WARD WARD 22-FEB-81 10936
JONES JONES 02-APR-81 10897
MARTIN MARTIN 28-SEP-81 10718
BLAKE BLAKE 01-MAY-81 10868
CLARK CLARK 09-JUN-81 10829
SCOTT SCOTT 19-APR-87 8689
KING KING 17-NOV-81 10668
TURNER TURNER 08-SEP-81 10738
ADAMS ADAMS 23-MAY-87 8655
JAMES JAMES 03-DEC-81 10652
FORD FORD 03-DEC-81 10652
MILLER MILLER 23-JAN-82 10601
15 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT ENAME,
2 ENAME,
3 DEPTNO,
4 HIREDATE,
5 TRUNC(SYSDATE-HIREDATE) "EMP_DAYS TOTAL" FROM EMP
6* WHERE DEPTNO = 10
SQL> /
ENAME ENAME DEPTNO HIREDATE EMP_DAYS TOTAL
---------- ---------- ---------- --------- --------------
CLARK CLARK 10 09-JUN-81 10829
KING KING 10 17-NOV-81 10668
MILLER MILLER 10 23-JAN-82 10601
SQL> ED
Wrote file afiedt.buf
1 SELECT EMPNO,
2 ENAME,
3 JOB,
4 DEPTNO,
5 HIREDATE,
6 TRUNC(SYSDATE-HIREDATE) "EMP_DAYS TOTAL" FROM EMP
7* WHERE DEPTNO = 10
SQL> /
EMPNO ENAME JOB DEPTNO HIREDATE EMP_DAYS TOTAL
---------- ---------- --------- ---------- --------- --------------
7782 CLARK MANAGER 10 09-JUN-81 10829
7839 KING PRESIDENT 10 17-NOV-81 10668
7934 MILLER CLERK 10 23-JAN-82 10601
SQL> ED
Wrote file afiedt.buf
1 SELECT EMPNO,
2 ENAME,
3 JOB,
4 DEPTNO,
5 HIREDATE,
6 TRUNC(SYSDATE-HIREDATE) "EMP_DAYS TOTAL",
7 (SYSDATE-HIREDATE)/30
8 FROM EMP
9* WHERE DEPTNO = 10
SQL> /
EMPNO ENAME JOB DEPTNO HIREDATE EMP_DAYS TOTAL (SYSDATE-HIREDATE)/30
---------- ---------- --------- ---------- --------- -------------- ---------------------
7782 CLARK MANAGER 10 09-JUN-81 10829 360.983443
7839 KING PRESIDENT 10 17-NOV-81 10668 355.616777
7934 MILLER CLERK 10 23-JAN-82 10601 353.383443
SQL> ed
Wrote file afiedt.buf
1 SELECT EMPNO,
2 ENAME,
3 JOB,
4 DEPTNO,
5 HIREDATE,
6 TRUNC(SYSDATE-HIREDATE) "EMP_DAYS TOTAL",
7 TRUNC((SYSDATE-HIREDATE)/30) "NO_MONTHS"
8 FROM EMP
9* WHERE DEPTNO = 10
SQL> /
EMPNO ENAME JOB DEPTNO HIREDATE EMP_DAYS TOTAL NO_MONTHS
---------- ---------- --------- ---------- --------- -------------- ----------
7782 CLARK MANAGER 10 09-JUN-81 10829 360
7839 KING PRESIDENT 10 17-NOV-81 10668 355
7934 MILLER CLERK 10 23-JAN-82 10601 353
SQL> ED
Wrote file afiedt.buf
1 SELECT EMPNO,
2 ENAME,
3 JOB,
4 DEPTNO,
5 HIREDATE,
6 TRUNC(SYSDATE-HIREDATE) "EMP_DAYS TOTAL",
7 TRUNC((SYSDATE-HIREDATE)/12) "NO_YEARS",
8 TRUNC((SYSDATE-HIREDATE)/30) "NO_MONTHS"
9 FROM EMP
10* WHERE DEPTNO = 10
SQL> /
EMPNO ENAME JOB DEPTNO HIREDATE EMP_DAYS TOTAL NO_YEARS NO_MONTHS
---------- ---------- --------- ---------- --------- -------------- ---------- ----------
7782 CLARK MANAGER 10 09-JUN-81 10829 902 360
7839 KING PRESIDENT 10 17-NOV-81 10668 889 355
7934 MILLER CLERK 10 23-JAN-82 10601 883 353
SQL> ED
Wrote file afiedt.buf
1 SELECT EMPNO,
2 ENAME,
3 JOB,
4 DEPTNO,
5 HIREDATE,
6 TRUNC(SYSDATE-HIREDATE) "EMP_DAYS TOTAL",
7 TRUNC((SYSDATE-HIREDATE)/365) "NO_YEARS",
8 TRUNC((SYSDATE-HIREDATE)/30) "NO_MONTHS"
9 FROM EMP
10* WHERE DEPTNO = 10
SQL> /
EMPNO ENAME JOB DEPTNO HIREDATE EMP_DAYS TOTAL NO_YEARS NO_MONTHS
---------- ---------- --------- ---------- --------- -------------- ---------- ----------
7782 CLARK MANAGER 10 09-JUN-81 10829 29 360
7839 KING PRESIDENT 10 17-NOV-81 10668 29 355
7934 MILLER CLERK 10 23-JAN-82 10601 29 353
SQL> ED
Wrote file afiedt.buf
1 SELECT EMPNO,
2 ENAME,
3 JOB,
4 DEPTNO,
5 HIREDATE,
6 TRUNC(SYSDATE-HIREDATE) "EMP_DAYS TOTAL",
7 TRUNC((SYSDATE-HIREDATE)/365) "NO_YEARS",
8 TRUNC((SYSDATE-HIREDATE)/30) "NO_MONTHS"
9 FROM EMP
10* WHERE DEPTNO = 10
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
111 BB 122
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
15 rows selected.
SQL> DELETE empno = 111
2 ;
DELETE empno = 111
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> DELETE EMP WHERE EMPNO = 111;
1 row deleted.
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> SELECT EMPNO,
2 ENAME,
3 JOB,
4 DEPTNO,
5 HIREDATE,
6 TRUNC(SYSDATE-HIREDATE) "EMP_DAYS TOTAL",
7 TRUNC((SYSDATE-HIREDATE)/365) "NO_YEARS",
8 TRUNC((SYSDATE-HIREDATE)/30) "NO_MONTHS"
9 FROM EMP
10 WHERE DEPTNO = 10
11 /
EMPNO ENAME JOB DEPTNO HIREDATE EMP_DAYS TOTAL NO_YEARS NO_MONTHS
---------- ---------- --------- ---------- --------- -------------- ---------- ----------
7782 CLARK MANAGER 10 09-JUN-81 10829 29 360
7839 KING PRESIDENT 10 17-NOV-81 10668 29 355
7934 MILLER CLERK 10 23-JAN-82 10601 29 353
Subscribe to:
Comments (Atom)