Analytical Functions in SQL Oracle

select ename,
sal,
sum(sal) over ( order by ename
range between
unbounded preceding and
current row ) running_total
from emp
order by ename
/
break on dname skip 1

select dname,
ename,
sal,
sum(sal) over ( partition by dname
order by dname, ename
range between
unbounded preceding and
current row ) dept_running_total
from emp, dept
where emp.deptno = dept.deptno
order by dname, ename
/
break on dname skip 1

select dname,
ename,
sal,
sum(sal) over ( partition by dname
order by dname, ename ) dept_running_total,
sum(sal) over ( order by dname, ename ) running_total
from emp, dept
where emp.deptno = dept.deptno
order by dname, ename
/
select ename,
sal,
lead( ename, 1, 'N/A' ) over ( order by sal ) next_ename,
lead( sal, 1, null ) over ( order by sal ) next_sal,
lead( ename, 2, 'N/A' ) over ( order by sal ) next_ename,
lead( sal, 2, null ) over ( order by sal ) next_sal
from emp
order by sal
/
select ename,
sal,
lag( ename, 1, 'N/A' ) over ( order by sal ) prev_ename,
lag( sal, 1, null ) over ( order by sal ) prev_sal
from emp
order by sal
/
break on dname skip 1

select deptno,
ename,
sal,
lead( ename, 1, 'N/A' ) over ( partition by deptno
order by sal ) next_ename,
lead( sal, 1, null ) over ( partition by deptno
order by sal ) next_sal
from emp
/
create table pages(
page_id number,
seq number )
/
insert into pages values ( 1, 10 );
insert into pages values ( 2, 20 );
insert into pages values ( 3, 30 );
insert into pages values ( 4, 40 );
commit;

select lag( page_id, 1, null )
over ( order by seq ) prev,
page_id,
lead( page_id, 1, null )
over ( order by seq ) next
from pages
where page_id = 3
/
select *
from ( select lag( page_id, 1, null )
over ( order by seq ) prev,
page_id,
lead( page_id, 1, null )
over ( order by seq ) next
from pages )
where page_id = 3
/
insert into pages values ( 5, 25 );
commit;
select *
from ( select lag( page_id, 1, null )
over ( order by seq ) prev,
page_id,
lead( page_id, 1, null )
over ( order by seq ) next
from pages )
where page_id = 3
/
connect scott/tiger
select rownum, x.*
from ( select ename,
hiredate
from emp
order by hiredate ) x
/
select rownum, ename, hiredate
from emp
order by hiredate
/
select rank() over ( order by hiredate ) "RANK",
dense_rank() over ( order by hiredate ) "DENSE_RANK",
ename,
hiredate
from emp
order by hiredate
/
select rank() over ( partition by to_char(hiredate,'yy')
order by hiredate ) "RANK",
ename,
hiredate
from emp
order by hiredate
/
clear breaks
break on deptno skip 1
select ename,
deptno,
sal,
sal - first_value( sal ) over ( partition by deptno
order by sal ) diff
from emp
order by deptno, sal
/
create table table_a(
id number )
/
insert into table_a
select rownum
from all_tables
where rownum < 7
/
select *
from table_a
/
create table table_b(
id number,
status varchar2(255) )
/
insert into table_b values( 1, 'NEW' );
insert into table_b values( 3, 'NEW' );
insert into table_b values( 5, 'NEW' );
select *
from table_b
/
merge into table_b b
using ( select *
from table_a ) a
on ( a.id = b.id )
when matched then
update set status = 'OLD'
when not matched then
insert values ( a.id, 'NEW' )
/

select *
from table_b
/
create table my_data(
id number,
data varchar2(100) )
/
insert into my_data
select rownum, to_char( to_date( rownum, 'J' ), 'JSP' )
from all_objects
where rownum < 6
/
select *
from my_data
/
insert into my_data values ( 6, 'SIC' );
declare
l_row my_data%rowtype;
begin
select *
into l_row
from my_data
where id = 6
for update;

update my_data
set data = 'SIX'
where id = 6;

commit;
exception
when NO_DATA_FOUND then
insert into my_data
values ( 6, 'SIX' );
end;
/
select *
from my_data
/
insert into my_data values( 7, 'SEVRN' );
select *
from my_data
/
merge into my_data
using ( select 7 x
from dual )
on ( id = x )
when matched then
update set data = to_char(to_date(7, 'J'), 'JSP' )
when not matched then
insert values ( 7, to_char(to_date(7, 'J')), 'JSP' )
/
select *
from my_data
/