bd7
SELECT '' AS "Zad_1_FETCH_FIRST" FROM dual;
SELECT nazwisko, placa_pod
FROM pracownicy
ORDER BY placa_pod DESC
FETCH FIRST 3 ROWS ONLY;
SELECT '' AS "Zad_1_ROWNUM" FROM dual;
SELECT nazwisko, placa_pod
FROM (
SELECT nazwisko, placa_pod
FROM pracownicy
ORDER BY placa_pod DESC
)
WHERE ROWNUM <= 3;
SELECT '' AS "Zad_2_OFFSET" FROM dual;
SELECT nazwisko, placa_pod
FROM pracownicy
ORDER BY placa_pod DESC
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;
SELECT '' AS "Zad_2_ROWNUM" FROM dual;
SELECT nazwisko, placa_pod
FROM (
SELECT nazwisko, placa_pod, ROWNUM AS rnum
FROM (
SELECT nazwisko, placa_pod
FROM pracownicy
ORDER BY placa_pod DESC
)
)
WHERE rnum BETWEEN 6 AND 10;
SELECT '' AS "Zad_3" FROM dual;
WITH SredniaPlaca AS (
SELECT id_zesp, AVG(placa_pod) AS srednia_placa
FROM pracownicy
GROUP BY id_zesp
)
SELECT p.nazwisko, p.placa_pod, p.placa_pod - s.srednia_placa AS roznica
FROM pracownicy p
JOIN SredniaPlaca S ON p.id_zesp = s.id_zesp
WHERE p.placa_pod > s.srednia_placa
ORDER BY p.nazwisko;
SELECT '' AS "Zad_4" FROM dual;
WITH Lata AS (
SELECT EXTRACT(YEAR from zatrudniony) AS rok, COUNT(*) AS liczba
FROM pracownicy
GROUP BY EXTRACT(YEAR from zatrudniony)
)
SELECT * FROM Lata
ORDER BY liczba DESC;
SELECT '' AS "Zad_5" FROM dual;
WITH Lata AS (
SELECT EXTRACT(YEAR from zatrudniony) AS rok, COUNT(*) AS liczba
FROM pracownicy
GROUP BY EXTRACT(YEAR from zatrudniony)
)
SELECT * FROM Lata
WHERE liczba = (SELECT MAX(liczba) FROM Lata);
SELECT '' AS "Zad_6" FROM dual;
WITH Asystenci AS (
SELECT nazwisko, etat, id_zesp
FROM pracownicy
WHERE etat = 'ASYSTENT'
),
Piotrowo AS (
SELECT id_zesp, nazwa, adres
FROM zespoly
WHERE adres = 'PIOTROWO 3A'
)
SELECT a.nazwisko, a.etat, p.nazwa, p.adres
FROM Asystenci a
JOIN Piotrowo p ON a.id_zesp = p.id_zesp;
SELECT '' AS "Zad_7" FROM dual;
WITH SumaPlac AS (
SELECT id_zesp, SUM(placa_pod) AS maks_suma_plac
FROM pracownicy
GROUP BY id_zesp
)
SELECT z.nazwa, MAX(s.maks_suma_plac) AS maks_suma_plac
FROM SumaPlac s JOIN zespoly z ON s.id_zesp = z.id_zesp
GROUP BY z.nazwa ORDER BY maks_suma_plac DESC
FETCH FIRST 1 ROWS ONLY;
SELECT '' AS "Zad_8" FROM dual;
WITH Hierarchia (nazwisko, id_prac, id_szefa, pozycja_w_hierarchii) AS (
SELECT nazwisko, id_prac, id_szefa, 1 AS pozycja_w_hierarchii
FROM pracownicy WHERE nazwisko = 'BRZEZINSKI'
UNION ALL
SELECT p.nazwisko, p.id_prac, p.id_szefa, h.pozycja_w_hierarchii + 1 as pozycja_w_hierarchii
FROM pracownicy p
JOIN Hierarchia h ON P.id_szefa = H.id_prac
)
SELECT nazwisko, pozycja_w_hierarchii
FROM Hierarchia;
SELECT '' AS "Zad_8_2" FROM dual;
SELECT nazwisko, LEVEL AS pozycja_w_hierarchii
FROM pracownicy
START WITH nazwisko = 'BRZEZINSKI'
CONNECT BY PRIOR id_prac = id_szefa
ORDER BY pozycja_w_hierarchii;
SELECT '' AS "Zad_9" FROM dual;
WITH Hierarchia (nazwisko, id_prac, id_szefa, pozycja_w_hierarchii) AS (
SELECT nazwisko, id_prac, id_szefa, 1 AS pozycja_w_hierarchii
FROM pracownicy WHERE nazwisko = 'BRZEZINSKI'
UNION ALL
SELECT p.nazwisko, p.id_prac, p.id_szefa, h.pozycja_w_hierarchii + 1 as pozycja_w_hierarchii
FROM pracownicy p
JOIN Hierarchia h ON P.id_szefa = H.id_prac
)
SELECT RPAD(' ', pozycja_w_hierarchii - 1) || nazwisko AS nazwisko, pozycja_w_hierarchii
FROM Hierarchia ORDER BY pozycja_w_hierarchii;
SELECT '' AS "Zad_9_2" FROM dual;
SELECT RPAD(' ', LEVEL - 1) || nazwisko AS nazwisko, LEVEL AS pozycja_w_hierarchii
FROM pracownicy
START WITH nazwisko = 'BRZEZINSKI'
CONNECT BY PRIOR id_prac = id_szefa
ORDER BY pozycja_w_hierarchii;
SELECT '' AS "Zad_10" FROM dual;