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
No comments:
Post a Comment