Oracle: ORACLE BUILT-IN

Showing posts with label ORACLE BUILT-IN. Show all posts

Saturday, May 4, 2013

ORACLE BITAND FUNCTION

BIT_AND FUNCTION DOES ANDING OF 2 BINARY VALUES

syntax: BIT_AND(EXPR1,EXPR2)

1 1 -> 1

1 0 -> 0

0 1 -> 0

0 0 -> 0

SQL> SELECT BITAND(1,0) FROM DUAL;

BITAND(1,0)
-----------
          0

SQL> SELECT BITAND(01,) FROM DUAL;
SELECT BITAND(01,) FROM DUAL
                 *
ERROR at line 1:
ORA-00936: missing expression

SQL> SELECT BITAND(0,1) FROM DUAL;

BITAND(0,1)
-----------
          0

SQL> SELECT BITAND(0,0) FROM DUAL;

BITAND(0,0)
-----------
          0

SQL> SELECT BITAND(1,1) FROM DUAL;

BITAND(1,1)
-----------
          1

ORACLE BIN_TO_NUM FUNCTION

                                                   bin_to_num converts binary value to Oracle Number.
SYNTAX: bin_to_num(expr1,expr2,.....,exprn);

QUERY: Display equivalent of 110 in decimal

SQL> select bin_to_num(1,1,0) from dual;

BIN_TO_NUM(1,1,0)
-----------------
                6

QUERY: Display equivalent of 1010 in decimal

SQL> select bin_to_num(1,0,1,0) from dual;

BIN_TO_NUM(1,0,1,0)
-------------------
                 10

QUERY: PL/SQL VARRAY has 5 binary numbers, converts into equivalent Number.

declare
type vab is varray(5) of number(1);
v_t vab:=vab(1,0,1,1,1);
n number;
begin
select bin_to_num(v_t(1),v_t(2),v_t(3),v_t(4),v_t(5)) into n from dual;
dbms_output.put_line(n);
end;
/

SQL> /
23

P/SQL procedure successfully completed.

ORACLE AVG FUNCTION

avg function calculates avg of the values supplied

QUERY: Calculate Average Weight of the Person.

SQL> desc person;
Name                                                  Null?    Type
----------------------------------------------------- -------- ------------
ID                                                             NUMBER(2)
NAME                                                           VARCHAR2(20)
DOB                                                            DATE
EMAIL                                                          VARCHAR2(20)
WEIGHT                                                         NUMBER(5,2)

SQL> select * from person;

        ID NAME                 DOB       EMAIL                    WEIGHT
---------- -------------------- --------- -------------------- ----------
         1 Peter                12-DEC-97 1@gmail.com                 100
         6 life style           11-SEP-97
         2 shyam purru          12-JAN-00 2@gmail.com                 111
         3 Jho                  01-NOV-02 3@gmail.com                 133
         4 Jhony                30-AUG-60 4@gmail.com              133.44
         5 pearso               21-APR-13 5@gmail.com              150.01

6 rows selected.

SQL> select avg(weight) from person;

AVG(WEIGHT)
-----------
     125.49

QUERY: GET AVERAGE SALARY FROM EACH DEPARTMENT

SQL> select DEPARTMENT_ID,AVG(SALARY) from hr.employees
2 group by DEPARTMENT_ID;

DEPARTMENT_ID AVG(SALARY)
------------- -----------
          100 8601.33333
           30        4150
                     7000
           90 19333.3333
           20        9500
           70       10000
          110       10154
           50 4376.08889
           80 8955.88235
           40        6500
           60        5760

           10        4400

12 rows selected.

ORACLE ABS FUNCTION

ABS FUNCTION RETURNS ABSOLUTE VALUE OF THE EXPRESSION

SYNTAX: abs(expr)

QUERY: get absolute value of -10

SQL> SELECT ABS(-10.22) FROM DUAL;

ABS(-10.22)
-----------
10.22

QUERY: get absolute value of '-10+20-30'

SQL> select abs(-10+20-30) from dual;

ABS(-10+20-30)
--------------
20

QUERY: get absolute value of dates.

SQL> select abs(months_between(to_date('1973-12-12','yyyy-dd-mm'),sysdate)) from dual;

MONTHS_BETWEEN(TO_DATE('1973-12-12','YYYY-DD-MM'),SYSDATE)
----------------------------------------------------------
472.7595

QUERY: Display absolute values of variable.

SQL>declare n number;

SQL>exec :n:=-10;

SQL>select :n,abs(:n) from dual;

:n abs(:n)

------ -------------------

-10 10

Friday, May 3, 2013

ORACLE ROUND FUNCTION

Round function rounds the number into nearest integer

round(decimal number, n);

SQL>select round(17.7) from dual; --rounds to 18

SQL> select round(17.49) from dual; --rounds to 17.

Note: the number >.5 will be rounded to next integer <0.5 rounded to lowest number.

SQL>select round(17.7,1) from dual;

SQL> select round(17.71,1) from dual;

ROUND(17.71,1)
--------------
          17.7

SQL> select round(17.7,1) from dual;

ROUND(17.7,1)
-------------
         17.7

SQL> select round(17.7,10) from dual;

ROUND(17.7,10)
--------------
          17.7

SQL> select round(17.9,10) from dual;

ROUND(17.9,10)
--------------
          17.9

SQL> select round(17.99,10) from dual;

ROUND(17.99,10)
---------------
          17.99

SQL> select round(17.999,1) from dual;

ROUND(17.999,1)
---------------
             18

SQL> select round(17.9,1) from dual;

ROUND(17.9,1)
-------------
         17.9

SQL> select round(17.99,1) from dual;

ROUND(17.99,1)
--------------
            18

SQL> select round(17.56,1) from dual;

ROUND(17.56,1)
--------------
          17.6

SQL> select round(17.55,1) from dual;

ROUND(17.55,1)
--------------
          17.6

SQL> select round(17.49,1) from dual;

ROUND(17.49,1)
--------------
          17.5

ORACLE SYSTIMESTAMP

systimestamp returns OS Date & time along with timezone on which oracle DB runs.

--Display Date and Time with timezone

SQL>select systimestamp from dual;

--Display only Date from systimestamp

SQL> select trunc(systimestamp) from dual;

SQL> select to_char(systimestamp,'yyyy-mm-dd') from dual;

--Display only Time from systimestamp

SQL> select systimestamp-trunc(systimestamp) from dual;

oracle trunc number function

truncates the specified value to specified decimal places.

SYNTAX: trunc(n1,n2)

it truncates n1 by n2 decimal places

for ex: trunc(15.71, 1) ->outputs 15.7

SQL> select trunc(15.71) from dual;

TRUNC(15.71)
------------
15

*Default 0.

SQL> select trunc(15.71,-1) from dual;

TRUNC(15.71)
------------
10

GET Fractional PORTION OF THE REAL VALUE.

--------------------------------------------

SQL> select 15.71-trunc(15.71,0) from dual;

15.71-TRUNC(15.71,0)
--------------------
.71

ORACLE CONCAT FUNCTION

conact FUNCTION CONCATENATE 2 STRINGS.

syntax: CONCAT(STR1,STR2);

SQL>SELECT CONCAT('HELLO',' WORLD') FROM DUAL;

CONCAT('HELLO',' WORLD')

---------------------------------------

HELLO WORLD

SQL>
SELECT CONCAT('HELLO',NULL) FROM DUAL;

CONCAT('HELLO',NULL)

---------------------------------

HELLO

SQL>SELECT CONCAT(NULL,' WORLD') FROM DUAL;

CONCAT(NULL,' WORLD')

---------------------------------------

WORLD

CONCATENATE 'I' , 'LOVE','PARIS' USING CONCAT

SQL> SELECT CONCAT(CONCAT('I','LOVE'),'PARIS') FROM DUAL;

CONCAT(CON
----------
ILOVEPARIS

ORACLE init cap function

InitCAP functions makes first letter in each word as Upper case.

Synatx: InitCap(char expr);

SQL>select initcap('hello world') from dual;

initcap('hello world')

--------------------------

Hello World

SQL> select initcap(first_name),initcap(last_name),initcap(first_name||' '||last_name) from employees

it makes first_name ,last_name,concatenating name and displays Upper case in each word.

ORACLE ASCII FUNCTION

oracle ascii function finds ascii value of the char.

SYNTAX: ascii(char)

SQL>select ascii('a') from dual;

ascii('a')

--------------------

SQL> select ascii('ab') from dual;

ASCII('AB')
-----------
97

Note: ascii value of ab , it takes only first character. i.e a

Ascii value of blank is 32.

SQL>select ascii(' ') from dual;

ascii(' ')

--------------

DISPLAY ASCII VALUES OF THE STRING

------------------------------------------------------------

declare
v varchar2(20):='hello oracle!';
c char;
begin
for i in 1..length(v)
loop
c:=substr(v,i,1);
dbms_output.put_line(c||'='||ascii(c));
end loop;
end;

SQL> /
h=104
e=101
l=108
l=108
o=111
=32
o=111
r=114
a=97
c=99
l=108
e=101
!=33

PL/SQL procedure successfully completed.

ASCII values for a-z : 97-122

ASCII values for A-Z: 65-90

ASCII values for 0-9: 48-57

ORACLE MIN FUNCTION

                                 MIN function finds MINIMUM value in the column, ignores null values.

SYNTAX: MIN(expr)

MINIMUM SALARY IN THE COMPANY.
----------------------------------------------------
SQL> select min(salary) from employees;

min(SALARY)
-----------
      24000
Get MINIMUM SALARY DRAWN FROM EACH DEPT.
------------------------------------------------------------------------
SQL> select department_ID,min(Salary) from employees
2 group by department_ID;

DEPARTMENT_ID min(SALARY)
------------- -----------
          100       12008
           30       11000
                     7000
           90       24000
           20       13000
           70       10000
          110       12008
           50       11808
           80       14000
           40        6500
           60        9000
           10        4400

12 rows selected.

Note: Group by department and then find min(salary)

GET min SALARY DRAWN ON EACH JOB CATEGORY
---------------------------------------------------------------------------
SQL> select job_ID,min(SALARY) from employees
2 group by job_id;

JOB_ID     min(SALARY)
---------- -----------
IT_PROG           9000
AC_MGR           12008
AC_ACCOUNT        8300
ST_MAN           11808
PU_MAN           11000
AD_ASST           4400
AD_VP            17000
SH_CLERK          4200
FI_ACCOUNT        9000
FI_MGR           12008
PU_CLERK          3100
SA_MAN           14000
MK_MAN           13000
PR_REP           10000
AD_PRES          24000
SA_REP           11500
MK_REP            6000
ST_CLERK          5184
HR_REP            6500

19 rows selected.
Note: Group by Job category and then find min(salary)

GET SECOND HIGHEST SALARY
---------------------------------------------------
SQL> select min(salary) from employees where salary > (select min(salary) from employees);

min(SALARY)
-----------
      17000
Note: First find min salary then in the remaining values find again min salary(second minimum).

ORACLE MAX FUNCTION

max function finds max value in the column, ignores null values.

SYNTAX: MAX(expr)

MAXIMUM SALARY IN THE COMPNAY.

----------------------------------------------------

SQL> select max(salary) from employees;

MAX(SALARY)
-----------
24000

Get MAXIMUM SALARY DRAWN FROM EACH DEPT.

------------------------------------------------------------------------

SQL> select department_ID,max(Salary) from employees
2 group by department_ID;

DEPARTMENT_ID MAX(SALARY)
------------- -----------
          100       12008
           30       11000
                     7000
           90       24000
           20       13000
           70       10000
          110       12008
           50       11808
           80       14000
           40        6500
           60        9000
           10        4400

12 rows selected.

Note: Group by department and then find max(salary)

GET MAX SALARY DRAWN ON EACH JOB CATEGORY

---------------------------------------------------------------------------

SQL> select job_ID,MAX(SALARY) from employees
2 group by job_id;

JOB_ID     MAX(SALARY)
---------- -----------
IT_PROG           9000
AC_MGR           12008
AC_ACCOUNT        8300
ST_MAN           11808
PU_MAN           11000
AD_ASST           4400
AD_VP            17000
SH_CLERK          4200
FI_ACCOUNT        9000
FI_MGR           12008
PU_CLERK          3100
SA_MAN           14000
MK_MAN           13000
PR_REP           10000
AD_PRES          24000
SA_REP           11500
MK_REP            6000
ST_CLERK          5184
HR_REP            6500

19 rows selected.

Note: Group by Job category and then find max(salary)

GET SECOND HIGHEST SALARY

---------------------------------------------------

SQL> select max(salary) from employees where salary < (select Max(salary) from employees);

MAX(SALARY)
-----------
17000

Note: First find MAX salary then in remaining values find MAX salary.

Sunday, March 31, 2013

ORACLE ADD_MONTHS DATETIME FUNCTION

Oracle Add_months adds number of months to a Date type and returns Date Type.

Syntax:

Add_months(Date,[number of months]) returns [new Date object]

Get Next Month:

select add_months(sysdate,1) "next month" from dual;

Get Previous Month:

select add_months(sysdate,-1) "previous month" from dual;

Get Next Year Same Day:

SQL>select add_months(sysdate,12) "Next Yr Same Day" from dual;

Add 5 yrs to Sysdate.

SQL> select add_months(sysdate,5*12) "After 5 years date is" from dual;

Add 3 quarters to a Date.

SQL> select add_months(sysdate,3*4) "After 3 quarters date is" from dual;

TAGS: get previous month in Oracle, Get Next Month in Oracle, get Next year same day, get n years after. add_months oracle date time.

Tuesday, February 26, 2013

Oracle NVL function

         NVL function replaces null value with same type data,

for ex: If employee has no comm , instead of displaying NULL , u can display string like 'No Commission'

Display Employees getting no comm as zero.
SQL> select ename,nvl(comm,0) from emp;
ENAME      NVL(COMM,0)

---------- ----------------------------------------

SMITH      0

ALLEN      300

WARD       500

JONES      0

MARTIN     1400

BLAKE      0

CLARK      0

SCOTT      0

KING       0

TURNER     0

ADAMS      0

JAMES      0

FORD       0

MILLER     0

14 rows selected.

.
Query:

SQL> select ename,nvl(to_char(comm),'no comm') from emp;

ENAME      NVL(TO_CHAR(COMM),'NOCOMM')
---------- ----------------------------------------
SMITH      no comm
ALLEN      300
WARD       500
JONES      no comm
MARTIN     1400
BLAKE      no comm
CLARK      no comm
SCOTT      no comm
KING       no comm
TURNER     0
ADAMS      no comm

ENAME      NVL(TO_CHAR(COMM),'NOCOMM')
---------- ----------------------------------------
JAMES      no comm
FORD       no comm
MILLER     no comm

14 rows selected.

Ex2: Get total salary of each department and Grand Total of employees,title total sum as "Grand Total".

SQL> select nvl(to_char(deptno),'Grand Total'), sum(sal) from emp group by rollup(deptno);

O/P:

NVL(TO_CHAR(DEPTNO),'GRANDTOTAL')          SUM(SAL)
---------------------------------------- ----------
10                                             8750
20                                            10875
30                                             9400
Grand Total                                   29025

Note: In this example, we grouping by department and then calculating sum(sal) for each department and then applying rollup to produce grand total.

If you don't use NVL, Grand Total will be null value. So NVL replacing null values with String.

Tags:Oracle NVL function,Oracle 11g NVL function, Replace Null values with String using NVL,Using NVL in Oracle 11g.