CV10

SELECT jmeno, prijmeni FROM A_HR.zamestnanci
WHERE LEVEL = 3 CONNECT BY PRIOR zamestnanec_id = manazer_id START WITH zamestnanec_id = 101;
/%%/
SELECT jmeno, prijmeni, mzda, AVG(mzda) over (PARTITION BY oddeleni_id) FROM A_Hr.zamestnanci;
/%%/
SELECT jmeno, prijmeni, oddeleni_nazev, mzda, LAST_VALUE (mzda) over (PARTITION BY oddeleni_id ORDER BY
mzda rows BETWEEN unbounded preceding AND unbounded following) - mzda pod_max,
mzda - LAST_VALUE(mzda) over (PARTITION BY oddeleni_id ORDER BY mzda) nax_max
FROM A_hr.zamestnanci join A_hr.oddeleni using (oddeleni_id) ORDER BY oddeleni_nazev;

SELECT oddeleni_nazev, listagg(prijmeni || '(' || pozice_nazev || ')',',')
within GROUP (ORDER BY prijmeni)
FROM a_hr.zamestnanci join A_HR.oddeleni using (oddeleni_id) join A_Hr.prac_pozice using (pozice_id)
GROUP BY oddeleni_nazev;

/%%/
CREATE OR REPLACE FUNCTION ZK_predchudci_vek(p_id IN NUMBER)
RETURN VARCHAR2 AS
v_narozen DATE;
v_toReturn VARCHAR2(200);
BEGIN
SELECT narozen INTO v_narozen FROM A_clovek.lide WHERE id=p_id;
FOR x IN (
SELECT jmeno, prijmeni, TRUNC((TO_DATE('13.10.1988','DD.MM.YY')-narozen)/365.25) vek
FROM a_clovek.lide WHERE
LEVEL >=2
CONNECT BY id IN (PRIOR id_otce, PRIOR id_matky)
START WITH id=p_id ORDER BY 3
)
LOOP
v_toReturn := v_toReturn || x.prijmeni || ' ' || x.jmeno ||
' (' || x.vek || ');';
END LOOP;
IF
v_toReturn IS NULL THEN
v_toReturn := 'Zadni Predchudci';
END IF;
RETURN v_toReturn;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    raise_application_error(-20001,'Osoba v tabulce lide neexistuje!');
RETURN NULL;
END;