-- ECBD lab 2

-- ex1
SELECT dname, ename, hiredate, DENSE_RANK() OVER (PARTITION BY dname ORDER BY hiredate ASC) pos_hire_date
FROM emp e INNER JOIN dept d ON e.deptno = d.deptno;

--ex2
SELECT deptno, job, ROUND(AVG(sal), 2), DENSE_RANK() OVER (PARTITION BY deptno ORDER BY ROUND(AVG(sal), 2) DESC) pos_sal
FROM emp
GROUP BY ROLLUP(deptno, job);

--ex3
SELECT deptno, job, ROUND(AVG(sal), 2),
DENSE_RANK() OVER (PARTITION BY deptno ORDER BY ROUND(AVG(sal), 2) DESC) pos_sal,
DENSE_RANK() OVER (PARTITION BY job ORDER BY ROUND(AVG(sal), 2) DESC) pos_sal
FROM emp
GROUP BY ROLLUP(deptno, job);

--ex4
SELECT * FROM (SELECT ename, sal, NVL(comm, 0), NVL(comm, 0)+sal,
DENSE_RANK() OVER (ORDER BY NVL(comm, 0)+sal DESC) pos_sal,
ROW_NUMBER() OVER (ORDER BY NVL(comm, 0)+sal DESC) nrcrt FROM emp) e
WHERE e.pos_sal <= 10;

--ex5
SELECT * FROM (SELECT ename, sal, NVL(comm, 0), NVL(comm, 0)+sal,
DENSE_RANK() OVER (ORDER BY NVL(comm, 0)+sal DESC) pos_sal,
ROW_NUMBER() OVER (ORDER BY NVL(comm, 0)+sal DESC) nrcrt,
ROUND(cume_dist() OVER (ORDER BY NVL(comm, 0)+sal), 2) dist
FROM emp) e
WHERE e.pos_sal <= 10;

--ex6
SELECT ename, sal, NVL(comm, 0), NVL(comm, 0)+sal,
DENSE_RANK() OVER (ORDER BY NVL(comm, 0)+sal DESC) pos_sal,
ROW_NUMBER() OVER (ORDER BY NVL(comm, 0)+sal DESC) nrcrt,
ntile(4) OVER (ORDER BY sal DESC) ntile4
FROM emp;