20 Jan 11:27
BSD 4
select '' as Zadanie_1 from dual;
SELECT MIN(PLACA_POD) AS minimum, MAX(PLACA_POD) AS maksimum, MAX(PLACA_POD) - MIN(PLACA_POD) AS roznica
FROM PRACOWNICY;
select '' as Zadanie_2 from dual;
SELECT etat, avg(placa_pod) AS srednia FROM pracownicy
GROUP BY etat
ORDER BY avg(placa_pod) DESC;
select '' as Zadanie_3 from dual;
SELECT COUNT(etat) AS profesorowie FROM PRACOWNICY
WHERE etat = 'PROFESOR';
select '' as Zadanie_4 from dual;
SELECT id_zesp, SUM(placa_pod) + SUM(placa_dod) AS sumaryczne_place FROM pracownicy
GROUP BY id_zesp
ORDER BY id_zesp;
select '' as Zadanie_5 from dual;
SELECT MAX(SUM(placa_pod) + SUM(placa_dod)) AS maks_sum_placa FROM pracownicy
GROUP BY id_zesp
ORDER BY id_zesp;
select '' as Zadanie_6 from dual;
SELECT id_szefa, MIN(placa_pod) AS minimalna FROM PRACOWNICY
WHERE id_szefa IS NOT NULL
GROUP BY ID_SZEFA
ORDER BY MIN(placa_pod) DESC, id_szefa;
select '' as Zadanie_7 from dual;
SELECT id_zesp, COUNT(ID_PRAC) AS ilu_pracuje FROM PRACOWNICY
GROUP BY id_zesp
ORDER BY COUNT(id_prac) DESC;
select '' as Zadanie_8 from dual;
SELECT id_zesp, COUNT(ID_PRAC) AS ilu_pracuje FROM PRACOWNICY
HAVING COUNT(ID_PRAC) > 3
GROUP BY id_zesp
ORDER BY COUNT(id_prac) DESC;
select '' as Zadanie_9 from dual;
SELECT COUNT(id_prac) FROM PRACOWNICY
HAVING COUNT(id_prac) > 1
GROUP BY id_prac;
select '' as Zadanie_10 from dual;
SELECT etat, avg(placa_pod) AS srednia, COUNT(id_prac) AS liczba FROM PRACOWNICY
WHERE zatrudniony < TO_DATE('01-JAN-1990')
GROUP BY etat
ORDER BY etat;
select '' as Zadanie_11 from dual;
SELECT
id_zesp,
etat,
round(avg(placa_pod + COALESCE(placa_dod, 0)), 0) AS srednia,
round(MAX(placa_pod + COALESCE(placa_dod, 0)), 0) AS maksymalna
FROM pracownicy
WHERE etat IN ('ASYSTENT', 'PROFESOR')
GROUP BY id_zesp, etat
ORDER BY id_zesp, etat;
select '' as Zadanie_12 from dual;
SELECT
EXTRACT(YEAR FROM zatrudniony) AS rok,
COUNT(id_prac) AS ILU_PRACOWNIKOW FROM PRACOWNICY
GROUP BY EXTRACT(YEAR FROM zatrudniony)
ORDER BY EXTRACT(YEAR FROM zatrudniony);
select '' as Zadanie_13 from dual;
SELECT LENGTH(nazwisko) as ILE_LITER, COUNT(id_prac) as w_ilu_nazwiskach FROM PRACOWNICY
GROUP BY LENGTH(nazwisko)
ORDER BY LENGTH(nazwisko);
select '' as Zadanie_14 from dual;
SELECT COUNT(id_prac) AS "Ile nazwisk z A" FROM PRACOWNICY
WHERE LOWER(nazwisko) LIKE '%a%';
select '' as Zadanie_15 from dual;
SELECT
COUNT((CASE WHEN LOWER(nazwisko) LIKE '%a%' THEN nazwisko END))
AS ile_nazwisk_z_a,
COUNT((CASE WHEN LOWER(nazwisko) LIKE '%e%' THEN nazwisko END))
AS ile_nazwisk_z_e
FROM pracownicy;
select '' as Zadanie_16 from dual;
SELECT
id_zesp,
SUM(placa_pod) AS suma_plac,
listagg(nazwisko || ':' || PLACA_POD, ';')
WITHIN GROUP (ORDER BY nazwisko) AS pracownicy
FROM PRACOWNICY
GROUP BY id_zesp
ORDER BY id_zesp;