add

About Me

My photo
Oracle Apps - Techno Functional consultant

Wednesday, October 23

Advance SQL Commands



------ RollUP ---------
SELECT Deptno, SUM(SAL) TotSal
FROM Emp
GROUP BY ROLLUP(Deptno);
/
SELECT Job, SUM(Sal)
FROM Emp
GROUP BY ROLLUP(Job);
/
SELECT NVL(TO_CHAR(Deptno), 'All Departments') Deptno,
SUM(Sal) SUMSal,
AVG(SAL) AVGSal,
MAX(Sal) MaxSal,
MIN(Sal) MinSal
FROM Emp
GROUP BY ROLLUP(Deptno);
/
SELECT NVL(TO_CHAR(Deptno), 'All Departments') Deptno,NVL(Job,'ALL JOBS') Job,
SUM(Sal) SUMSal,
AVG(SAL) AVGSal,
MAX(Sal) MaxSal,
MIN(Sal) MinSal
FROM Emp
GROUP BY ROLLUP(Deptno,Job);
/
---------- Cube --------------
SELECT
NVL(TO_CHAR(Deptno), 'All Departments') Deptno,
NVL(Job, 'All Jobs') Job,
SUM(Sal) Salary
FROM Emp
GROUP BY CUBE(Deptno, Job)
ORDER BY Deptno;
--------- Grouping -----------
SELECT GROUPING(Deptno),
Deptno, SUM(Sal)
FROM Emp
GROUP BY ROLLUP(Deptno);
/
SELECT
DECODE(GROUPING(Deptno),
        1, 'All Departments',
        Deptno) Departments,
        SUM(Sal)
FROM Emp
GROUP BY ROLLUP(Deptno);
/
SELECT
DECODE(GROUPING(Deptno),
        1, 'All Departments',
        Deptno) Departments,
DECODE(GROUPING(Job),
        1, 'All Designations',
        Job) Designations,
SUM(Sal)
FROM Emp
GROUP BY ROLLUP(Deptno, Job);
/
SELECT
DECODE(GROUPING(Deptno),
  1 , 'All Departments',
  Deptno) Departments,
DECODE(GROUPING(Job),
  1, 'All Designations',
  Job) Designations,
SUM(Sal)
FROM Emp
GROUP BY CUBE(Deptno, Job)
ORDER BY Deptno;
/
--------- Dence_Rank ------
SELECT ename,sal,dense_rank()
OVER (order by sal desc) rank
FROM emp

------- Rank ------
SELECT ename,sal,rank()
OVER (order by sal desc) rank
FROM emp

-------- Rank with group ------
SELECT ename,deptno,sal,rank()
OVER (partition by deptno order by sal desc) rank
FROM emp;

------- Nth Salary -----------

SELECT * FROM
(SELECT deptno,ename,sal,dense_rank()
OVER (order by sal desc) e
FROM emp )
WHERE e = 2

--------- Cummulative Frequency --------

SELECT deptno,sal,SUM(sal)
OVER (order by sal ROWS UNBOUNDED PRECEDING) cummulative
FROM emp;

SELECT deptno,sal,SUM(sal)
OVER (PARTITION BY deptno order by sal ROWS UNBOUNDED PRECEDING) cummulative
FROM emp;


SELECT deptno,sal,SUM(sal)
OVER (order by sal ROWS 2 PRECEDING) cummulative2
FROM emp;

No comments: