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;