oracle sql and dba tutorial logo

Oracle DBA

Learn Oracle 11g / 12c Database Admin step by step



Oracle SQL

Tutorial for Oracle SQL

 

Oracle DBA Interview Questions

Most asked Oracle DBA Interview Questions.

60 Technical Questions
42 Backup & Recovery Questions

Unix For Oracle DBA 20 Questions


Download Oracle 11g / 12 c Software

Links to Download Oracle 12c / 11g for Linux, Windows etc.



Miscellaneous SQL Functions in Oracle

Miscellaneous Single Row Functions

COALESCE

 Coalesce function returns the first not null value in the expression list.

 Example.

The following query returns salary+commision, if commission is null then returns salary, if salary is also null then returns 1000.

select empno,ename,salary,comm,coalesce(salary+comm,salary,1000) “Net Sal” from emp;

ENAME     SALARY    COMM NET SAL
-----     ------    ---- -------
SMITH     1000      100  1100
SAMI      3000           3000
SCOTT                    1000
RAVI               200   1000

DECODE

    DECODE(expr, searchvalue1, result1,searchvalue2,result2,..., defaultvalue)

Decode functions compares an expr with search value one by one. If the expr does not match any of the search value then returns the default value. If the default value is omitted then returns null.

Example

The following query returns the department names according the deptno. If the deptno does not match any of the search value then returns “Unknown Department”

select decode(deptno,10,'Sales',20,'Accounts,30,'Production,
          40,'R&D','Unknown Dept') As DeptName from emp;

DEPTNAME
----------
Sales
Accounts
Unknown Dept.
Accounts
Production
Sales
R&D
Unknown Dept.

 

GREATEST

    GREATEST(expr1, expr2, expr3,expr4...)

Returns the greatest expr from a expr list.

Example

select greatest(10,20,50,20,30) from dual;

GREATEST
--------
50

 

select greatest('SAMI','SCOTT','RAVI','SMITH','TANYA') from dual;

GREATEST
--------
TANYA

LEAST

     LEAST(expr1, expr2, expr3,expr4...)

It is simillar to greatest. It returns the least expr from the expression list.

select least(10,20,50,20,30) from dual;

LEAST
--------
10

select least('SAMI','SCOTT','RAVI','SMITH','TANYA') from dual;

LEAST
--------
RAVI

NVL

          NVL2(expr1,expr2)

This function is oftenly used to check null values. It  returns  expr2 if the  expr1 is null, otherwise returns expr1.

Example

The following query returns commission if commission is null then returns 'Not Applicable'.

Select ename,nvl(comm,'Not Applicable') “Comm” from dual;

ENAME     COMM
------    ----
Scott     300
Tiger     450
Sami      Not Applicable
Ravi      300
Tanya     Not Applicable

NVL2

       NVL2(expr1,expr2,expr3)

NVL2 returns  expr2 if expr1 is not null, otherwise return expr3.

Example

The following query returns salary+comm if comm is not null, otherwise just returns salary.

select salary,comm,nvl2(comm,salary+comm,salary) “Income” from emp;

SALARY    COMM      INCOME
------    ----      ------
1000      100       1100
2000                2000
2300      200       2500
3400                3400

NULLIF

         NULLIF(expr1, expr2)

Nullif compares expr1 with expr2. If they are equal then returns null, otherwise return expr1.

Example.

The following query shows old jobs of those employees  who have changed their jobs in the company by comparing the current job with old job in oldemp table.

Select ename,nullif(e.job,o.job) “Old Job” from emp e, oldemp o where e.empno=o.empno;

ENAME         OLD JOB
-----         -------
SMITH         CLERK
SAMI                   
SCOTT         MANAGER

UID

Returns the current session ID of user logged on.

Example

select uid from dual;

UID
----
20

USER

Returns the username of the current user logged on.

select user from dual;

USER
---------
SCOTT

 

SYS_CONTEXT

SYS_CONTEXT returns the value of parameter associated with the context namespace. You can use this function in both SQL and PL/SQL statements.

EXAMPLE

The following query returns the username of the current user.

Select sys_context('USERENV','SESSION_USER') “Username” from dual;

USERNAME
---------
SCOTT

Similar to SESSION_USER parameter for namespace USERENV the other important parameters are

ISDBA            :To check whether the current user is having DBA privileges or not.

HOST              :Returns the name of  host machine from which the client is connected.

INSTANCE    :The instance identification number of the current instance

IP_ADDRESS: IP address of the machine from which the client is connected.

DB_NAME    :Name of the database as specified in the DB_NAME initialization parameter

VSIZE

         VSIZE(expr)

Returns the internal representation of expr in bytes.

Example

The following query return the representation of  ename in bytes.

select ename,vsize(ename) as Bytes from emp;

ENAME     BYTES
------    ------
SCOTT     5
SAMI      4
RAVI      4
KIRAN     5

 

 

 


HomeContact Us

Data Loader

Data Loader is a simple yet powerful tool to
export and import Data between many common database formats


Forms Data Loader

Tool to load data into Oracle E-Business Suite R12 / Oracle Apps using Macros and Forms Record and Playback

Interface Computers Academy © 2007-2017 All Rights Reserved