SELECT * FROM emp;
SELECT * FROM sales;
SELECT * FROM salgrade;

SELECT a.ename, a.job, a.sal, a.deptno, b.grade FROM emp a JOIN salgrade b ON a.sal BETWEEN b.losal AND b.hisal;


-- ex1

SELECT MAX(a.sal) as max,MIN(a.sal) as min, ROUND(AVG(a.sal),2) as avg, job, b.grade as grade
FROM emp a JOIN salgrade b ON a.sal BETWEEN b.losal AND b.hisal GROUP BY grouping sets(a.job, b.grade);

-- ex 1
SELECT MAX(a.sal) as max,MIN(a.sal) as min, ROUND(AVG(a.sal),2) as avg, job, b.grade as grade
FROM emp a JOIN salgrade b ON a.sal BETWEEN b.losal AND b.hisal GROUP BY rollup(a.job, b.grade);

--ex3

SELECT MAX(a.sal) as max, MIN(a.sal) as min, ROUND(AVG(a.sal),2) as avg,
SUM(a.sal) as sum, a.job,  b.ename as manager
FROM emp a JOIN emp b ON a.mgr=b.empno GROUP BY rollup(a.job, b.ename);

--ex4

SELECT MAX(a.sal) as max, MIN(a.sal) as min, ROUND(AVG(a.sal),2) as avg,
SUM(a.sal) as sum, a.job, GROUPING(a.job), b.ename as manager, GROUPING(b.ename)
FROM emp a JOIN emp b ON a.mgr=b.empno GROUP BY rollup(a.job, b.ename);

--ex5

SELECT * FROM emp a JOIN salgrade b on a.sal BETWEEN b.losal AND b.hisal;
SELECT * FROM emp a JOIN emp b on a.mgr = b.empno;

SELECT MAX(a.sal) as max, MIN(a.sal) as min, ROUND(AVG(a.sal),2) as avg,
SUM(a.sal), a.grade as grade,
DECODE(GROUPING(a.grade), 1, 'TOTAL', 0) as GRD_TOTAL,
b.manager as manager,
DECODE(GROUPING(b.manager), 1, 'TOTAL', 0) as MGR_TOTAL
FROM (SELECT emp.empno as id, emp.ename, emp.sal as sal, salgrade.grade as grade
FROM emp JOIN salgrade on emp.sal BETWEEN salgrade.losal AND salgrade.hisal) a
JOIN
(SELECT e.empno as id, e.ename as name, m.ename as manager
FROM emp e JOIN emp m on e.mgr = m.empno) b
ON a.id=b.id GROUP BY ROLLUP(a.grade, b.manager);

--ex6