--zadanie 2

--a) Napisz polecenie SQL zwracające siedziby, w których jest tylko jeden wydział.
select siedziby.id_siedziby from siedziby
join wydzialy on wydzialy.id_siedziby = siedziby.ID_SIEDZIBY
group by siedziby.ID_SIEDZIBY having count(wydzialy.id_wydzialu) = 1;
--b) Napisz polecenie SQL zwracające zamówieniamające tylko jedną pozycję.
select id_zamowienia from wiersze_zamowien
group by ID_ZAMOWIENIA having count(id_zamowienia) = 1
order by id_zamowienia;

select * from zamowienia z
where z.id_zamowienia in (
select id_zamowienia from wiersze_zamowien
group by ID_ZAMOWIENIA having count(id_zamowienia) = 1)
order by z.id_zamowienia;
--c) Napisz polecenie SQL zwracające pracowników, którzy nie mają podwładnych.
select * from pracownicy p
where p.id_pracownika not in (
select p1.id_szefa from pracownicy p1);

SELECT
    ID_PRACOWNIKA,
    IMIE,
    NAZWISKO
FROM
    PRACOWNICY

MINUS

SELECT
    ID_PRACOWNIKA,
    IMIE,
    NAZWISKO
FROM
    PRACOWNICY
WHERE
    ID_PRACOWNIKA IN (  SELECT
                            ID_SZEFA
                        FROM
                            PRACOWNICY);
--d) Napisz polecenie SQL zwracające wydziały, które nie mają pracowników.

select w.id_wydzialu,w.nazwa, count(p.id_pracownika) from wydzialy w
left join PRACOWNICY p on p.ID_WYDZIALU = w.id_wydzialu
group by w.id_wydzialu, w.nazwa having count(p.id_pracownika) < 1;

--e) Napisz polecenie SQL zwracające trzech najgorzej zarabiających pracowników.

select id_pracownika,imie,nazwisko,pensja from (
select id_pracownika,imie,nazwisko,pensja from pracownicy
order by pensja)
where rownum <=3;

--f) Napisz polecenie SQL zwracające zamówienia (identyfikator, data) od klientów pochodzących z miasta BOSTON.

select z.id_zamowienia,z.data_zamowienia from zamowienia z
join klienci k on k.id_klienta = z.id_klienta
where k.miasto = 'BOSTON';

--zadanie 3

--a) Wyświetlić nazwy wyrobów, cenę oraz dochód łączny ze sprzedaży tego wyrobu w poprzednim roku uwzględniając również wyroby, których jeszcze nie udało się sprzedać.

select w.id_produktu,w.opis,cn.cena_cennikowa,sum(z.wartosc) from wyroby w
left join CENY cn on w.id_produktu = cn.ID_PRODUKTU
right join WIERSZE_ZAMOWIEN wz on wz.ID_PRODUKTU = w.ID_PRODUKTU
inner join ZAMOWIENIA z on z.ID_ZAMOWIENIA = wz.ID_ZAMOWIENIA
where z.DATA_ZAMOWIENIA < TO_DATE('01/01/01','YY/MM/DD')
group by w.id_produktu,w.opis,cn.cena_cennikowa;

--b) Wyświetlić 3 najlepiej sprzedające się wyroby.
select id_produktu, opis,id_zamowienia as ilosc_zamowien from (select w.id_produktu,w.opis,count(z.id_zamowienia) as id_zamowienia from wyroby w
join WIERSZE_ZAMOWIEN wz on wz.ID_PRODUKTU = w.ID_PRODUKTU
join ZAMOWIENIA z on z.ID_ZAMOWIENIA = wz.ID_ZAMOWIENIA
group by w.id_produktu, w.opis order by count(z.id_zamowienia) desc)
where rownum <=3;
--c) Dla każdego miasta, z którego pochodzą klienci wyświetlić jego nazwę, liczbę zamówień oraz średnią kwotę zamówień. Należy przy tym uwzględnić nazwy miast, z których klienci jeszcze nic nie kupili.
select k.miasto,count(z.id_zamowienia),avg(z.wartosc) from klienci k
left join zamowienia z on k.id_klienta = z.ID_KLIENTA
group by k.miasto;

--d) Wyświetlić nazwisko, imię, nazwę wydziału oraz obroty (sumę zamówień) sprzedawcy, który miał najmniejsze obroty.
select nazwisko,imie,nazwa_wydzialu,suma_zamowien from
(select p.nazwisko,p.imie,w.nazwa as nazwa_wyrobu,sum(z.wartosc) as suma_zamowien from pracownicy p
join klienci k on k.ID_PRACOWNIKA = p.ID_PRACOWNIKA
join zamowienia z on z.id_klienta = k.id_klienta
join wydzialy w on w.id_wydzialu = p.id_wydzialu
group by p.nazwisko,p.imie,w.nazwa
order by sum(z.wartosc))
where rownum <= 1;

--e) Wyświetlić nazwisko, imię, nazwę wydziału oraz obroty (sumę zamówień) sprzedawcy, który miał największe obroty.
select nazwisko, imie, nazwa_wydzialu,obroty from
(select p.nazwisko,p.imie, w.nazwa as nazwa_wydzialu,sum(z.wartosc) as obroty from pracownicy p
join klienci k on k.id_pracownika = p.id_pracownika
join ZAMOWIENIA z on z.id_klienta = k.id_klienta
join wydzialy w on w.id_wydzialu = p.id_wydzialu
group by p.nazwisko,p.imie, w.nazwa order by sum(z.wartosc) desc)
where rownum <=1;
--f) Wyświetl średnią płacę pracowników pracujących na poszczególnych etatach. Lista powinna zawierać także etaty, na których nie pracuje żaden pracownik.
select avg(p.pensja),e.etat from pracownicy p
right join ETATY e on p.ID_ETATU = e.ID_ETATU
group by e.ETAT;

--zadanie 4
--a)Zmniejszyć o 5% płace sprzedawców, którzy sprzedali w ubiegłym miesiącu za mniej niż 500.
update pracownicy
set pensja = pensja * 0.95
where id_pracownika in (select p.id_pracownika from PRACOWNICY p
join klienci k on p.id_pracownika = k.id_pracownika
join ZAMOWIENIA z on z.id_klienta = k.id_klienta
group by p.id_pracownika having sum(z.wartosc) < 500);
--b) Zwiększyć o 2% prowizję sprzedawców, którzy mają przynajmniej jednego podwładnego.
update pracownicy
set prowizja = prowizja *1.02
where id_pracownika in (select p.id_szefa from pracownicy p);
--c) Zwiększyć o 1% prowizję pracownikom, którzy nie pracują w dziale "SALES".

update pracownicy
set prowizja = prowizja *1.01
where ID_PRACOWNIKA in(select p.id_pracownika from pracownicy p
join wydzialy w on p.id_wydzialu = w.id_wydzialu
where w.nazwa != 'SALES');

--d) Podwyższyć o 10% płacę wszystkim sprzedawcom, którzy mają więcej niż dwóch klientów.
update pracownicy
set pensja = pensja * 1.1
where id_pracownika in (select p.id_pracownika from pracownicy p
join klienci k on k.id_pracownika = p.id_pracownika
group by p.id_pracownika having count(k.id_klienta) >2);
--tutaj w havign nie wiem czy id_klienta czy id_pracownika

--e) Zwiększyć o 2% prowizję sprzedawców, którzy mają przynajmniej jednego klienta.
update pracownicy
set prowizja = prowizja*1.02
where id_pracownika in (
select p.id_pracownika from pracownicy p
join klienci k on p.id_pracownika = k.id_pracownika
group by p.id_pracownika having count(k.id_klienta)>1);
--f) Zwiększ limit kredytowy klientom o 10 za każde złożone zamówienie.
update klienci k1
set k1.limit_kredytowy = k1.limit_kredytowy+10*(select count(z.id_zamowienia) from zamowienia z
join klienci k on k.id_klienta = z.id_klienta
where k.id_klienta = k1.id_klienta
group by k.id_klienta);
--czy tutaj to group by jest konieczne?

***

--napisz polecenie sql zwracajace identyfikatory zamowien sprzed 2 kwietnia 1990
select z.id_zamowienia,z.data_zamowienia from zamowienia z
where z.data_zamowienia < TO_DATE('1990/04/02','YY/MM/DD')
order by z.data_zamowienia;
--napisz polecenie sql zwracajace regiony, w kotrych nie ma wydzialu "SALES" i "OPERATIONS"
select s.region,w.nazwa from siedziby s
join wydzialy w on w.id_siedziby = s.id_siedziby
where w.NAZWA != 'SALES' AND w.NAZWA != 'OPERATIONS';
--napisz polecenie sql zwracajace ilosc pozycji (wiersz) poszczegolnych zamowien (identyfikator, data,ilosc pozycji)
select wz.id_zamowienia,z.data_zamowienia,count(wz.id_produktu) as ilosc_pozycji from wiersze_zamowien wz
join zamowienia z on z.id_zamowienia = wz.id_zamowienia
group by wz.id_zamowienia,z.data_zamowienia;
--wyswietlic klienta którego pracownik  ma mniejsza pensja od wartości zamówienia
select k.id_klienta,k.nazwa from klienci k
join pracownicy p on p.id_pracownika = k.id_pracownika
join zamowienia z on z.id_klienta = k.id_klienta
where p.pensja < z.WARTOSC
group by k.id_klienta,k.nazwa;

--wyswietlic pracwonika ktorego data zatrudnienia bedzie wczesniejsza od najwyzszej pensji oraz bezdie on na tym samym wydziale co donald scott
select p.id_pracownika,p.imie,p.nazwisko,p.pensja,p.DATA_ZATRUDNIENIA from pracownicy p
where p.data_zatrudnienia <(select p1.data_zatrudnienia from pracownicy p1
where p1.id_pracownika in (select id_pracownika from (select p2.id_pracownika
from pracownicy p2
order by p2.pensja desc)
where rownum <=1)) AND p.id_etatu = (select id_etatu from pracownicy
where nazwisko = 'SCOTT' and imie = 'DONALD');

--prostsza wersja bez rownum i z mniejszą iloscia podzapytan
select p.id_pracownika,p.imie,p.nazwisko from pracownicy p
where p.DATA_ZATRUDNIENIA<(select data_zatrudnienia from pracownicy
where pensja = (select max(pensja) from pracownicy))
AND
p.id_etatu = (select id_etatu from pracownicy
where nazwisko = 'SCOTT' and IMIE = 'DONALD');


--podac cene minimalna wyrobu ktorego zamowiono najwiecej
select w.id_produktu,c.CENA_MINIMALNA from wyroby w
join CENY c on c.id_produktu = w.id_produktu
where w.id_produktu in(select id_produktu from(
select id_produktu from wiersze_zamowien
group by id_produktu
order by count(id_produktu) desc)
where rownum <= 1);
--klienci ktorzy zamowieli produkt z basketball w nazwie i pelna nazwa produktu oraz ilosc przekraza 100 a wartosc zamowienia 300

--tego nie wiem

--zamowienia ktorych wartosc jest wieksza od wartosci sredniej i zostaly wyslane 3 dni pozniej od daty zamowienia
select z.id_zamowienia,z.WARTOSC, (select avg(wartosc) from zamowienia) as srednia from zamowienia z
where z.wartosc > (select avg(wartosc) from zamowienia)
AND
z.DATA_WYSYLKI - z.DATA_ZAMOWIENIA > 3
group by z.id_zamowienia,z.wartosc ;