sql queries urgent help required

[varshanswamy]

emp table

empno,
ename,
mgr,
sal,
deptno

dept table

deptno,
dname,
loc

I wanted the solution for the following queries.

List all Departments Names and the second highest salary for that particular department.

List employee name, his manager's name and salary of employee or manager whichever is higher.

[stmontgo]

homework sucks huh?

[kris109]

No.. It is sweet if someone else does it.. for me

[anupamasuresh]

Try this for the first query

SELECT d.dname,e.sal FROM dept d,emp e WHERE e.deptno = d.deptno AND e.deptno IN
(SELECT deptno FROM
(SELECT e.deptno,dense_rank() over (ORDER BY e.sal DESC) AS sal_rank FROM dept d,emp e
WHERE d.deptno=e.deptno GROUP BY e.deptno)WHERE sal_rank =2 ) AND e.deptno = d.deptno

[slimdave]

Originally posted by anupamasuresh
Try this for the first query

SELECT d.dname,e.sal FROM dept d,emp e WHERE e.deptno = d.deptno AND e.deptno IN
(SELECT deptno FROM
(SELECT e.deptno,dense_rank() over (ORDER BY e.sal DESC) AS sal_rank FROM dept d,emp e
WHERE d.deptno=e.deptno GROUP BY e.deptno)WHERE sal_rank =2 ) AND e.deptno = d.deptno

Better is ...

Select
   dname,
   sal
From
   (
   Select
      d.dname,
      e.sal,
      Dense_Rank() Over (
         Partition By d.dname
         Order by e.sal Desc) rnk
   From
      dept d,
      emp e,
   Where
      d.deptno = e.deptno
   )
Where
   rnk = 2
/