Exists

Exists:
-------------

The EXISTS condition is considered "to be met" if the subquery returns at least one row.

The syntax for the EXISTS condition is:

SELECT columns
FROM tables
WHERE EXISTS ( subquery );

Ex:
---

SELECT *
FROM dept
WHERE EXISTS
  (select *
    from emp
    where dept.deptno = emp.deptno);


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

Not Exists:


SELECT *
FROM dept
WHERE NOT EXISTS
  (select *
    from emp
    where dept.deptno = emp.deptno);


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

Merge:


create table student10 ( sno number(3),
sname varchar2(20),
marks number(3));

insert into student10 values ( 101, 'arun',30);
insert into student10 values ( 102, 'anil',40);
insert into student10 values ( 103, 'kiran',50);



create table student20 ( sno number(3),
sname varchar2(20),
marks number(3));


insert into student20 values ( 101, 'JOHN',30);
insert into student20 values ( 105, 'SMITH',50);



merge into student10 s1
using student20 s2
on ( s1.sno = s2.sno)
when matched
then update set sname=s2.sname, marks = s2.marks
when not matched
then insert (sno,sname,marks ) values (s2.sno,s2.sname,s2.marks);



------------------------------
Decode :

select empno,sal,job , decode (job, 'CLERK' , sal*2,
   'MANAGER', sal*3
            ,sal  ) new_sal
from emp;


select ename,sal, deptno, decode(deptno, 10, 'CA',
20, 'SCIENTIST',
   'EMPLOYEE') NEW_NAME
from emp;


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

Case :

select empno,sal,job , case job when 'CLERK' then sal*2
  when 'MANAGER' then sal*3
                                                  else sal
                                    end new_sal
from emp;


Case 2nd example:
------------------------
SELECT ename, sal, deptno,
       CASE deptno
          WHEN 10
             THEN 'ten'
          WHEN 20
             THEN 'twenty'
          WHEN 30
             THEN 'thirty'
          ELSE NULL
       END dept_no
  FROM emp;

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

Rank :

In Oracle/PLSQL, the rank function returns the rank of a value in a group of values.

The rank function can be used two ways - as an Aggregate function or as an Analytic function.

---
Rank used as aggregate function:

select rank (800) within  group (order by sal) rank from emp;

select rank (850) within  group (order by sal) rank from emp;

select rank (5000) within  group (order by sal) rank from emp;


Rank used as analytical function:

As an Analytic function, the rank returns the rank of each row of a query

select ename, sal,
rank() OVER (ORDER BY sal) rank
from emp;


--------------------------
NULLIF :

In Oracle/PLSQL, the NULLIF function compares expr1 and expr2. If expr1 and expr2 are equal, the NULLIF  function returns NULL. Otherwise, it returns expr1.

The syntax for the NULLIF function is:

    NULLIF( expr1, expr2 )



select NULLIF(12,12) from dual;   --  returns NULL

select NULLIF(12,13) from dual;   -- returns 12

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