IDASCV10

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) nad_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 NUMBER) RETURN VARCHAR2 AS
i_narozen DATE;
i_vysledek VARCHAR2(300);
BEGIN
SELECT narozen INTO i_narozen FROM a_clovek.lide WHERE id = p_id;
FOR X IN (SELECT jmeno, prijmeni, ROUND(((i_narozen-narozen) /365.25),2) 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
i_vysledek := i_vysledek || x.prijmeni || ' ' || x.jmeno || '(' || x.vek || ');';
END LOOP;
IF i_vysledek IS NULL THEN
i_vysledek := 'Nejsou zadni predchudci';
END IF;
RETURN i_vysledek;
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error(-20001, 'Zadany clovek neexistuje');
END zk_predchudci_VEK;