Important Aggregate Function Queries

Click on the following Link
http://www.folkstalk.com/2011/12/sql-queries-interview-questions-oracle.html

HOW TO CONVERT ROWS INTO COLUMNS IN ORACLE SQL

 SELECT * FROM (
 SELECT SAL, HIREDATE,(CASE DEPTNO WHEN 10 THEN 'ACCOUNTING' WHEN 20 THEN 'RESEARCH' ELSE 'MARKETING'  END) DNAME
   from EMP)
PIVOT
(
   MIN(SAL)
   FOR  HIREDATE IN ('17-DEC-80','20-FEB-81','17-NOV-81')
);

Difference between lexical and bind parameter

Bind parameters are used in where condition.
When u want to pass any value at runtime we use bind parameter as
we use '& 'in sql and pl/sql.
for eg ,
In reports
we give
select empno,ename from emp where deptno =:dno
this :dno acts as bind parameter.
Secondly,
Lexical parameter is used to pass an entire where condition at
runtime.
for eg,
select empno,ename from emp &X
At run time we can pass the entire where condition like
where deptno=30 and sal>3000 etc....

Find the Running Total in Oracle

1.create table rtotal ( db_amt number,cr_amt number,acc_no number);

2.Inserting data into table

INSERT INTO "SCOTT"."RTOTAL" (DB_AMT, CR_AMT, ACC_NO) VALUES ('100', '0', '1001')
INSERT INTO "SCOTT"."RTOTAL" (CR_AMT, ACC_NO) VALUES ('300', '1001')
INSERT INTO "SCOTT"."RTOTAL" (DB_AMT, ACC_NO) VALUES ('300', '1002')
INSERT INTO "SCOTT"."RTOTAL" (CR_AMT, ACC_NO) VALUES ('10000', '1001')
INSERT INTO "SCOTT"."RTOTAL" (CR_AMT, ACC_NO) VALUES ('200', '1002')
Commit Successful

3.Create the function as follow

create or replace function rt(rowid1 varchar2,P_acc_no number) return number as
cursor c1 is select  sum(db_Amt)-sum(cr_amt) from rtotal where rowid<=rowid1 and ACC_NO=P_acc_no;
bal_amt number;
begin
open c1;
loop
fetch c1 into bal_amt;
exit when c1%notfound;
end loop;
close c1;
return bal_amt;
end;

4.Query th records
select db_Amt,cr_amt,rt(rowid,1001) from rtotal where acc_no=1001;

output as follow