-- Sample To Illustrate Table Functions And Cursor Expressions

-- “YOUNG MANAGERS” SCENARIO

-- Find those managers in the employees table, the majority of whose direct reports were hired
-- before the manager

-- PURE SQL APPROACH

-- This scenario needs the hr sample schema that comes along with Oracle9i database

-- Connect hr/hr@9i
set serveroutput on

drop view report_hire_dates;

drop view young_managers;

create or replace view report_hire_dates as
  select managers.employee_id manager_employee_id,
         managers.hire_date manager_hire_date,
         reports.hire_date report_hire_date
    from employees managers,

         employees reports
    where managers.employee_id = reports.manager_id;   

create or replace view young_managers as
  select manager_employee_id from ( 
    select manager_employee_id,
      sum (
            Decode (
                     sign ( report_hire_date - manager_hire_date ),

                     /* when */ -1, /* then */ 1,
                     /* when */  0, /* then */ 1,
                     /* else */ -1
                   )
          ) s
      from report_hire_dates
      group by manager_employee_id )
    where s > 0;