20 Jan 11:30
BSD 6b
SELECT '' as Zad_1 from dual;
SELECT z.id_zesp, z.nazwa, z.adres
FROM zespoly z
WHERE NOT EXISTS (
SELECT 1
FROM pracownicy p
WHERE p.id_zesp = z.id_zesp
);
SELECT '' as Zad_2 from dual;
SELECT p.nazwisko, p.placa_pod, e.nazwa AS ETAT
FROM pracownicy p
JOIN etaty e ON p.etat = e.nazwa
WHERE p.placa_pod > (
SELECT AVG(placa_pod)
FROM pracownicy
WHERE etat = p.etat
)
ORDER BY p.placa_pod DESC;
SELECT '' as Zad_3 from dual;
SELECT p.nazwisko, p.placa_pod
FROM pracownicy p
WHERE p.placa_pod >= 0.75 * (
SELECT placa_pod
FROM pracownicy
WHERE id_prac = p.id_szefa
)
ORDER BY p.nazwisko;
SELECT '' as Zad_4 from dual;
SELECT p.nazwisko
FROM pracownicy p
WHERE p.etat = (SELECT nazwa FROM etaty WHERE nazwa = 'PROFESOR')
AND NOT EXISTS (
SELECT 1
FROM pracownicy p2
WHERE p2.id_szefa = p.id_prac
AND p2.etat = (SELECT nazwa FROM etaty WHERE nazwa = 'STAZYSTA')
);
SELECT '' as Zad_5 from dual;
SELECT z.nazwa, SUM(p.placa_pod) AS MAKS_SUMA_PLAC
FROM zespoly z
JOIN pracownicy p ON p.id_zesp = z.id_zesp
GROUP BY z.nazwa
HAVING SUM(p.placa_pod) = (
SELECT MAX(suma)
FROM (
SELECT SUM(placa_pod) AS SUMA
FROM pracownicy
GROUP BY id_zesp
) S
);
SELECT '' as Zad_6 from dual;
SELECT p.nazwisko, p.placa_pod
FROM pracownicy p
WHERE p.placa_pod IN (
SELECT placa_pod
FROM (
SELECT placa_pod
FROM pracownicy
ORDER BY placa_pod DESC
)
WHERE ROWNUM <= 3
)
ORDER BY p.placa_pod DESC;
SELECT '' as "Zad_7 SELECT" from dual;
SELECT p.nazwisko, p.placa_pod,
p.placa_pod - (
SELECT AVG(placa_pod)
FROM pracownicy
WHERE id_zesp = p.id_zesp
) AS ROZNICA
FROM pracownicy p
ORDER BY nazwisko;
SELECT '' as "Zad_7 FROM" from dual;
SELECT p.nazwisko, p.placa_pod, p.placa_pod - s.avg_placa AS ROZNICA
FROM pracownicy p
JOIN (
SELECT id_zesp, AVG(placa_pod) AS AVG_PLACA
FROM pracownicy
GROUP BY id_zesp
) s ON p.id_zesp = s.id_zesp
ORDER BY nazwisko;
SELECT '' as "Zad_8 SELECT" from dual;
SELECT p.nazwisko, p.placa_pod,
p.placa_pod - (
SELECT AVG(placa_pod)
FROM pracownicy
WHERE id_zesp = p.id_zesp
) AS ROZNICA
FROM pracownicy p
WHERE p.placa_pod > (
SELECT AVG(placa_pod)
FROM pracownicy
WHERE id_zesp = p.id_zesp
) ORDER BY nazwisko;
SELECT '' as "Zad_8 FROM" from dual;
SELECT p.nazwisko, p.placa_pod, p.placa_pod - s.avg_placa AS ROZNICA
FROM pracownicy p
JOIN (
SELECT id_zesp, AVG(placa_pod) AS AVG_PLACA
FROM pracownicy
GROUP BY id_zesp
) s ON p.id_zesp = s.id_zesp
WHERE p.placa_pod > s.avg_placa
ORDER BY nazwisko;
SELECT '' AS "Zadanie 9" FROM dual;
SELECT p.nazwisko, COUNT(s.id_prac) AS PODWLADNI
FROM pracownicy p LEFT JOIN pracownicy s ON s.id_szefa = p.id_prac
JOIN zespoly z ON p.id_zesp = z.id_zesp
WHERE p.etat = 'PROFESOR' AND z.adres = 'PIOTROWO 3A'
GROUP BY p.nazwisko ORDER BY PODWLADNI DESC;
SELECT '' as Zad_10 from dual;
SELECT z.nazwa,
AVG(p.placa_pod) AS SREDNIA_W_ZESPOLE,
(SELECT ROUND(AVG(placa_pod), 2) FROM pracownicy) AS SREDNIA_OGOLNA,
CASE
WHEN AVG(p.placa_pod) >= (SELECT AVG(PLACA_POD) FROM Pracownicy) THEN ':)'
WHEN AVG(p.placa_pod) < (SELECT AVG(PLACA_POD) FROM Pracownicy) THEN ':('
ELSE '???'
END AS NASTROJE
FROM zespoly z
LEFT JOIN pracownicy p ON p.id_zesp = z.id_zesp
GROUP BY z.nazwa ORDER BY z.nazwa;
SELECT '' as Zad_11 from dual;
SELECT e.nazwa, e.placa_min, e.placa_max
FROM etaty e
ORDER BY (
SELECT COUNT(*)
FROM pracownicy p
WHERE p.etat = e.nazwa
) DESC, e.nazwa;