Compute Group Salary

---

-- DATA SETUP --

create table emp_t(empno number,
                   ename varchar2(100),
                   mgr   number,
                   sal   number);

Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7839,'KING',null,1000);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7698,'BLAKE',7839,700);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7782,'CLARK',7839,500);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7566,'JONES',7839,800);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7788,'SCOTT',7566,200);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7902,'FORD',7566,100);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7369,'SMITH',7902,75);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7499,'ALLEN',7698,100);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7521,'WARD',7698,200);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7654,'MARTIN',7698,150);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7844,'TURNER',7698,150);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7876,'ADAMS',7788,50);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7900,'JAMES',7698,100);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7934,'MILLER',7782,300);

commit;

---

-- Query 1 --

select sum(sal)
from EMP_T
start with ENAME = 'FORD'
connect by PRIOR empno=mgr;

select EMPNO, ENAME, MGR, SAL,(select sum(sal)
                                from EMP_T
                                start with ENAME = a.ENAME
                                connect by PRIOR empno=mgr) group_sal
from emp_t a;

---

-- Query 2 --

CREATE OR REPLACE FUNCTION get_group_sal (
    pin_ename VARCHAR
) RETURN NUMBER AS
    lv_grp_sal   NUMBER;
BEGIN
    SELECT SUM(sal)
    INTO   lv_grp_sal
    FROM   emp_t
    START WITH ename = pin_ename
    CONNECT BY PRIOR empno = mgr;

    RETURN lv_grp_sal;
END;
/
select EMPNO, ENAME, MGR, SAL,get_group_sal(a.ename) group_sal
from emp_t a;

---

-- Query 3 --

with FUNCTION fn_group_sal (
    pin_ename VARCHAR
) RETURN NUMBER AS
    lv_grp_sal   NUMBER;
BEGIN
    SELECT SUM(sal)
    INTO   lv_grp_sal
    FROM   emp_t
    START WITH ename = pin_ename
    CONNECT BY PRIOR empno = mgr;

    RETURN lv_grp_sal;
END;
select EMPNO, ENAME, MGR, SAL,fn_group_sal(ename) group_sal
from emp_t a;

---