Monday, July 3, 2006

Group by ...

1.Display the manager number and the salary of the lowest paid employee for that manager.
Exclude whose manager is not known.
Exclude any group with min(sal)<1000>

SELECT mgr, MIN (sal)
FROM emp
WHERE mgr IS NOT NULL
GROUP BY mgr
HAVING MIN (sal) > 1000
ORDER BY MIN (sal) DESC

2. Joining year wise total employee break up:

SELECT COUNT (1),
SUM (DECODE (TO_CHAR (e.hiredate, 'YYYY'), 1980, 1, 0)) "1980",
SUM (DECODE (TO_CHAR (e.hiredate, 'YYYY'), 1981, 1, 0)) "1981",
SUM (DECODE (TO_CHAR (e.hiredate, 'YYYY'), 1982, 1, 0)) "1982",
SUM (DECODE (TO_CHAR (e.hiredate, 'YYYY'), 1983, 1, 0)) "1983"
FROM emp e

3.Create a matrix query to display job,sal for that job based on dept no.
and the total salary for job,for departments 10,30.

SELECT e.job, NVL (SUM (DECODE (e.deptno, 10, e.sal)), 0) "Dept 10",
NVL (SUM (DECODE (e.deptno, 30, e.sal)), 0) "Dept 30"
FROM emp e
GROUP BY e.job

No comments:

Post a Comment