how to navigate in self loop tables?

Question

Consider the following table

create table EMPLOYEE
(
  empno    NUMBER not null,
  ename    VARCHAR2(100),
  salary   NUMBER,
  hiredate DATE,
  manager  NUMBER
);

alter table EMPLOYEE add constraint PK_EMP primary key (EMPNO);

alter table EMPLOYEE 
   add constraint FK_MGR foreign key (MANAGER)
      references EMPLOYEE (EMPNO);

which is a self looped table i.e. every employee has a manager, except for the root.

I want to run the following query on this table:

find all the employees having more salary than their managers?

---

P.S.

There is only one root in the structure

consider the following query

SELECT LPAD(emp.ename, (LEVEL-1)*5 + LENGTH(emp.ename), ' ') AS "Hierarchy"
    FROM employee emp
    START WITH emp.manager IS NULL
    CONNECT BY manager = PRIOR empno;

the result would be something like this:

Alice
    Alex
    Abbey
Sarah
Jack
    Bill
    Jacob
    Valencia
Bob
    Babak
...

I made the following query

SELECT LPAD(emp.ename, (LEVEL-1)*5 + LENGTH(emp.ename), ' ') AS "Hierarchy"
  FROM employee emp
    START WITH empno IN (SELECT empno FROM employee)
    CONNECT BY PRIOR manager = empno; 

which makes a subtree for every employee in the employee table from bottom to top, but I don't know how to navigate through to get to the desired result!

Answer 1

Here is one way to do it

with fullemployee (empno, ename, salary, key)
as
(
  select A.empno, A.ename, A.salary, A.empno || '.' from 
      employee A
  where A.manager is null
  union all
  select C.empno, C.ename, C.salary, D.key || '.' || C.empno from 
      employee C
      inner join fullemployee D on C.manager = D.empno
)
select E.ename, F.ename as manager from fullemployee E
inner join fullemployee F on E.key like F.key || '%' and E.key <> F.key
where E.salary > F.salary

or equivalently

with fullemployee (empno, ename, salary, key)
as
(
   SELECT empno, ename, salary, SYS_CONNECT_BY_PATH(empno, '.') || '.'
   FROM employee
   START WITH manager is null
   CONNECT BY PRIOR empno = manager
)
select E.ename, F.ename as manager from fullemployee E
inner join fullemployee F on E.key like F.key || '%' and E.key <> F.key
where E.salary > F.salary

SQL Fiddle - http://sqlfiddle.com/#!4/37f4ae/35

Answer 2

This should do the work. Remove that or condition if you don't want the 'root' in your list.

select e.empno, e.ename, e.salary from employee e
    inner join employee mgr on mgr.empno = e.manager
where e.salary > mgr.salary
or (e.manager = mgr.empno)