--  Lista 9

--1. Wypisać nazwiska oraz pensje tych pracowników, którzy (nie) są przełożonymi.

SELECT nazwisko,pensja
FROM pracownik p,pracownik_personalia pp
WHERE p.id_pracownika = pp.id_pracownika and p.id_pracownika not in
(SELECT id_przelozonego FROM pracownik WHERE id_przelozonego is not null);

--2. Podać stanowisko i średnią pensje na tym stanowisku, pod warunkiem że ta średnia jest unikatowa.

SELECT stanowisko, CEIL(AVG(pensja))
FROM pracownik p
GROUP BY stanowisko
HAVING AVG(pensja) <> all (SELECT AVG(pensja) FROM pracownik WHERE stanowisko <> p.stanowisko GROUP BY stanowisko);

--3. Podać nazwisko pracownika, który: przyjmował dostawę; nie ma najniższej pensji; najwięcej razy
-- pracował przy produkcji towaru.

SELECT nazwisko
FROM pracownik_personalia pp ,pracownik p
WHERE pp.id_pracownika = p.id_pracownika and p.id_pracownika = any (SELECT id_pracownika FROM dostawa)
and pensja > any (SELECT pensja FROM pracownik) and p.id_pracownika in (SELECT id_pracownika FROM pracownik_produkcja GROUP BY id_pracownika
HAVING count(*) >= all (SELECT count(*) FROM pracownik_produkcja GROUP BY id_pracownika));

--4. Podać stanowisko, na którym pracuje najwięcej osób,
-- które pracowały przy produkcji towaru;

SELECT stanowisko
FROM pracownik
WHERE stanowisko in (SELECT stanowisko FROM pracownik_produkcja WHERE id_pracownika in (SELECT id_pracownika FROM pracownik_produkcja))
GROUP BY stanowisko
having count(stanowisko) >= all (SELECT count(*) FROM pracownik WHERE id_pracownika in (SELECT id_pracownika FROM pracownik_produkcja)
GROUP BY stanowisko);

-- Lista 10

--1. Wypisać stanowisko i sumę zarobków pracowników z tego stanowiska, pod warunkiem że na tym
-- stanowisku pracuje również przełożony, któregokolwiek z tych pracowników

SELECT stanowisko, SUM(pensja) Zarobki
FROM pracownik p
WHERE exists (SELECT p1.id_pracownika FROM pracownik p1,pracownik p2
WHERE p1.id_przelozonego = p2.id_pracownika and p1.stanowisko = p2.stanowisko and p1.stanowisko = p.stanowisko)
GROUP BY stanowisko;

--2. Podać nazwę działu i nazwisko pracownika, jeżeli jako jedyny zarabia najwięcej w tym dziale

SELECT nazwa,nazwisko
FROM dzial d, pracownik_personalia pp, pracownik p
WHERE pp.id_pracownika = p.id_pracownika and d.id_dzialu = p.id_dzialu and not exists
(SELECT id_pracownika FROM pracownik WHERE pensja >= p.pensja and
id_dzialu = d.id_dzialu and id_pracownika <> p.id_pracownika);

--3. Podać stanowiska, na których wszystkie pensje są unikatowe.
SELECT stanowisko
FROM pracownik p
WHERE not exists (SELECT pensja FROM pracownik p1 WHERE p1.pensja <> p.pensja and stanowisko = p.stanowisko)
GROUP BY stanowisko;

-- Lista 11

--1. Podać nazwisko pracownika i liczbę osób pracujących na tym stanowisku, co ten pracownik (pod
-- warunkiem że liczba ta jest większa niż 5). Dane posortować malejąco względem liczby pracowników.

SELECT nazwisko, (SELECT count(*) FROM pracownik WHERE stanowisko = p.stanowisko) as ilosc
FROM pracownik_personalia pp, pracownik p
WHERE p.id_pracownika = pp.id_pracownika and (SELECT count(*) FROM pracownik WHERE stanowisko = p.stanowisko) > 5 order by
ilosc ASC;

--2. Sprawdzić dla każdego działu, jaki procent wszystkich pracowników stanowią pracownicy tego działu.
-- Podać nazwę działu i procent.

SELECT Distinct nazwa, ((SELECT count(*) FROM pracownik WHERE id_dzialu = d.id_dzialu)/(SELECT count(*) FROM
pracownik))*100 || '%' as Procent
FROM dzial d, pracownik p
WHERE d.id_dzialu = p.id_dzialu;

--3. Podać procent wykorzystania budżetu na produkcję towarów

SELECT (SELECT SUM(stawka*liczba_godzin) FROM pracownik_produkcja)/(SELECT SUM(budzet) FROM produkcja)*100 ||
'%' as Procent
FROM dual;

--5. Podać nazwisko pracownika i jego pensję, jeżeli zarabia on najwięcej na swoim stanowisku

SELECT nazwisko, pensja
FROM pracownik_personalia pp, pracownik p, (SELECT stanowisko,MAX(pensja) as maks_pensja FROM pracownik group by stanowisko) tab1
WHERE pp.id_pracownika = p.id_pracownika and pensja = maks_pensja and p.stanowisko = tab1.stanowisko;

--6. 6. Spośród dostawców, którzy dostarczyli co najmniej 5 produktów, wybrać tych, którzy byli obsługiwani
-- kiedykolwiek przez pracownika zatrudnionego najpóźniej. Podać nazwę dostawcy.

--SELECT nazwa
--FROM dostawca d, (SELECT id.dostawcy as id_dos FROM dostawa GROUP BY id_dostawcy having (*) > 5)
-- Niedokończone

-- Lista 12

--1. Utworzyć perspektywę o nazwie pracownik_dane na podstawie zapytania wypisującego imię,
-- nazwisko, stanowisko i pensję pracownika. Korzystając z utworzonej perspektywy, wpisać dane
-- pracowników, którzy zarabiają powyżej średniej.

CREATE VIEW pracownik_dane11 as
SELECT imie,nazwisko,stanowisko,pensja
FROM pracownik p,pracownik_personalia pp
WHERE p.id_pracownika = pp.id_pracownika;

SELECT * FROM pracownik_dane11;

SELECT imie,nazwisko,pensja
FROM pracownik_dane11
WHERE pensja > (SELECT AVG(pensja) FROM pracownik_dane11);

--2. Utworzyć perspektywę wypisującą imię i nazwisko przełożonego oraz jego podwładnego (nazwy pól:
-- przelozony_imie, przelozony_nazwisko, podwladny_imie, podwladny_nazwisko).

CREATE VIEW przelozeni111 (imie_przelozonego,nazwisko_przelozonego,imie_podwladnego,nazwisko_podwadnego) as
SELECT pp1.imie,pp1.nazwisko,pp2.imie,pp2.nazwisko
FROM pracownik_personalia pp1,pracownik p2,pracownik_personalia pp2
WHERE p2.id_przelozonego = pp1.id_pracownika and pp2.id_pracownika = p2.id_pracownika;

--3. Podać imiona i nazwiska przełożonych, którzy mają więcej niż trzech podwładnych. Wykorzystać
-- perspektywę z zadania 2.

SELECT imie_przelozonego, nazwisko_przelozonego
FROM przelozeni111
group by imie_przelozonego, nazwisko_przelozonego
HAVING count(*) > 3;

--4. Utworzyć perspektywę, która dla każdego stanowiska wyznaczy maksymalną pensję.

CREATE VIEW z4perss (stanowisko,pensja) as
SELECT stanowisko,MAX(pensja)
FROM pracownik p
GROUP BY stanowisko;

--6. Podać stanowisko, nazwisko i pensję pracownika zarabiającego najwięcej na swoim stanowisku (pod
-- warunkiem że jest tylko jeden taki pracownik na danym stanowisku). Wykorzystać perspektywę z zadania 4.

SELECT p1.stanowisko,nazwisko,p2.pensja
FROM pracownik_dane11 p1,z4perss p2
WHERE p1.pensja = p2.pensja and p1.stanowisko = p2.stanowisko and
(SELECT count(*) FROM pracownik_dane11 WHERE stanowisko = p1.stanowisko and pensja = p2.pensja) = 1;

--7. Sprawdzić dla każdego klienta, jaki procent dochodów firmy w danym miesiącu stanowią jego zakupy.
-- Podać imię i nazwisko klienta oraz procent. Wykorzystać odpowiednie perspektywy.

CREATE VIEW zad7pers (id_klienta,miesiac,suma) as
SELECT id_klienta,TO_CHAR(data,'MM'), SUM(cena*zz.ilosc)
FROM zamowienie z,zamowienie_szczegoly zz,towar t
WHERE z.id_zamowienia = zz.id_zamowienia and t.nr_towaru = zz.nr_towaru
GROUP BY id_klienta,TO_CHAR(data,'MM');

SELECT imie,nazwisko,(SELECT suma FROM zad7pers WHERE id_klienta = k.id_klienta and miesiac = to_char(data,'MM'))
/(SELECT suma FROM zad7pers WHERE miesiac = 12)
FROM klient k, zamowienie z
WHERE k.id_klienta = z.id_klienta and to_char(data,'MM') = 12;

--Lista 13

--1. Podać imiona i nazwiska klientów, którzy złożyli zamówienie, jeżeli jego kwota jest większą niż 100
--a. lub liczba zamówionych, różnych produktów jest większą niż 10;
--b. i liczba zamówionych, różnych produktów jest większą niż 10;
--c. i liczba zamówionych, różnych produktów jest nie większą niż 10.

SELECT imie,nazwisko
FROM klient k, zamowienie z , zamowienie_szczegoly zs, towar t
WHERE k.id_klienta = z.id_klienta and t.nr_towaru = zs.nr_towaru and zs.id_zamowienia = z.id_zamowienia
GROUP BY k.imie,k.nazwisko , z.id_zamowienia
HAVING SUM(zs.ilosc*t.cena) > 100

INTERSECT

SELECT imie,nazwisko
FROM klient k, zamowienie z , zamowienie_szczegoly zs, towar t
WHERE k.id_klienta = z.id_klienta and t.nr_towaru = zs.nr_towaru and zs.id_zamowienia = z.id_zamowienia
GROUP BY k.imie, k.nazwisko , z.id_zamowienia
HAVING count(*) <= 10;

--2. Wypisać dane pracowników według następującego schematu: imię, nazwisko, typ. Pole typ przyjmuje
-- następujące wartości: 2 – pracownik nie ma nad sobą przełożonych, 1 – pracownik jest przełożonym,
-- 0 – pracownik jest podwładnym. Dane posortować malejąco względem typu pracownika.

SELECT imie,nazwisko, 2 typ
FROM pracownik_personalia pp, pracownik p
WHERE p.id_pracownika = pp.id_pracownika and p.id_przelozonego is null

UNION

SELECT imie,nazwisko, 1 typ
FROM pracownik_personalia pp, pracownik p
WHERE p.id_pracownika = pp.id_pracownika and p.id_pracownika in (SELECT id_przelozonego FROM pracownik)

UNION

SELECT imie,nazwisko, 0 typ
FROM pracownik_personalia pp , pracownik p
WHERE p.id_pracownika = pp.id_pracownika and p.id_przelozonego is not null order by typ;

--3. Podać stanowisko, jeżeli żaden pracownik z tego stanowiska nigdy nie brał udziału przy dostawie.

SELECT stanowisko
FROM pracownik p

MINUS

SELECT stanowisko
FROM pracownik p,dostawa d
WHERE p.id_pracownika = d.id_pracownika;

--4. Podać nazwę towaru, który był zamawiany co najmniej pięć razy i składa się z co najwyżej trzech
-- składników (, jeżeli każdy z tych towarów, tj. towarów składających się z trzech składników, zawsze był
-- zamawiany przez innego klienta).

SELECT nazwa
FROM towar t,zamowienie_szczegoly zs
WHERE zs.nr_towaru = t.nr_towaru
GROUP BY nazwa
HAVING count(*) >= 5

INTERSECT

(SELECT nazwa
FROM towar t, towar_sklad ts
WHERE ts.nr_towaru = t.nr_towaru
GROUP BY t.nazwa,ts.nr_towaru
HAVING count(*) <= 3

MINUS

SELECT nazwa
FROM towar t, zamowienie z, zamowienie_szczegoly zs
WHERE t.nr_towaru = zs.nr_towaru and zs.id_zamowienia = z.id_zamowienia
GROUP BY nazwa,z.id_klienta
HAVING count(*) > 1);

--1. Utworzyć pomocniczą tabelę, zawierającą następujące dane: identyfikator, imię, nazwisko, pensja oraz
-- stanowisko pracownika. Kolejne zadania wykonać na nowo utworzonej tabeli.

CREATE TABLE daned as
SELECT p.id_pracownika, imie , nazwisko, stanowisko,pensja
FROM pracownik p, pracownik_personalia pp
WHERE p.id_pracownika = pp.id_pracownika;

SELECT id_pracownika FROM daned p
WHERE pensja = (SELECT MIN(pensja)
FROM daned WHERE stanowisko = p.stanowisko);

--2. Zwiększyć o 5% pensje pracowników zarabiających najmniej na swoich stanowiskach.

UPDATE daned p
SET pensja = pensja*1.05
WHERE pensja=(SELECT MIN(pensja)
FROM daned WHERE stanowisko = p.stanowisko);

SELECT nazwisko,pensja
FROM daned;

ALTER TABLE daned ADD przedzial NUMBER (1,0);

UPDATE daned
SET przedzial = (SELECT id_przedzialu
FROM przedzial_zarobkow
WHERE pensja between dolna_granica and gorna_granica);

DELETE daned
WHERE id_pracownika in (SELECT id_pracownika
FROM pracownik
WHERE premia is not null);

ROLLBACK;

DELETE daned WHERE id_pracownika in (SELECT p1.id_pracownika FROm pracownik p1, pracownik p2,pracownik_personalia pp
WHERE p1.id_przelzonego = p2.id_pracownika and p2.id_pracownika = pp.id_pracownika and imie || ' ' || nazwisko = '&przelozony');