-- kolokwium SQL wszystko:
-- czesc I

--zadanie 1 - pokaz wszystkie informajce o dostawcach
SELECT * FROM ZAJECIA.DOSTAWCY

--zadanie 2 - pokaz nazwy firm, adresy oraz miasta dostpnych dostawcĂłw
SELECT NAZWAFIRMY, ADRES, MIASTO FROM ZAJECIA.DOSTAWCY

--zadanie 3 - pokaz informacje o dostawcach posortowane według miejscowosc
SELECT * FROM ZAJECIA.DOSTAWCY
ORDER BY MIASTO

--zadanie 4 - Pokaz miasta, w ktorych znajduja sie lokalizacje dostawcow
SELECT DISTINCT MIASTO FROM ZAJECIA.DOSTAWCY
ORDER BY MIASTO

--zadanie 5 - Pokaz firmy pochodzace z Bytomia. Nazwa firmy ma byla zapisana duzymi literami (UPPER/LOWER). Pierwsze litery maja byc duze  (INITCAP)
SELECT * FROM ZAJECIA.DOSTAWCY
WHERE MIASTO LIKE 'Bytom'

SELECT Upper(NAZWAFIRMY) AS duze, LOWER(NAZWAFIRMY) AS male, INITCAP(NAZWAFIRMY) AS normalnie, MIASTO FROM ZAJECIA.DOSTAWCY
WHERE MIASTO LIKE 'Bytom'

--zadanie 6 - Pokaz firmy pohodzace z Bytomia i Katowic
SELECT NAZWAFIRMY, MIASTO FROM ZAJECIA.DOSTAWCY
WHERE MIASTO IN ('Bytom', 'Katowice')

--zadanie 7 - Pokaaz firmy, ktorych nazwy zaczynaja sie na litere B
SELECT * FROM ZAJECIA.DOSTAWCY
WHERE NAZWAFIRMY LIKE 'B%'

--zadanie 8 - Pokaz firmy, ktorych nazwa jest dluzsza niz  10 znakow
SELECT * FROM ZAJECIA.DOSTAWCY
WHERE LENGTH(NAZWAFIRMY)>10

--zadanie 9 - Pokaz firmy, ktorych dwie pierwsze cyfry kodu zaczynaja sie na 23
SELECT * FROM ZAJECIA.DOSTAWCY
WHERE SUBSTR(KODPOCZTOWY,1,2)=23

--zadanie 10 - Pokaz firmy, ktorych dwie ostatnie cyfry kodu pocztowego koncza sie na 33
SELECT * FROM ZAJECIA.DOSTAWCY
WHERE SUBSTR(KODPOCZTOWY,4,2)=23

--zadanie 11 - Pokaz towary, ktorych jest mniej niz minimum okreslone dla danego towaru
WHERE ilosc < STANMINIMUM

--zadanie 12 - Pokaz nazw towaru oraz ilosc, jaka jets niezbedna do zakupu, aby stan magazynowy przekroczyl minimalna ilosc
SELECT NAZWAPRODUKTU, ILOSC, STANMINIMUM, (STANMINIMUM-ILOSC+1) AS do_zakupu FROM ZAJECIA.PRODUKTY
WHERE ilosc < STANMINIMUM

--zadanie 13 -  Pokaz nazwe towaru, adres sprzedawcy oraz ilosc towarow, ktore musimy zakupic, aby stan na magazynie przekroczyl minimalna wartosc o 100 jednostek
SELECT NAZWAPRODUKTU,d.ADRES, ILOSC, STANMINIMUM, (STANMINIMUM-ILOSC+100) AS do_zakupu FROM  ZAJECIA.PRODUKTY p JOIN ZAJECIA.DOSTAWCY d ON p.IDDOSTAWCY=d.IDDOSTAWCY
WHERE STANMINIMUM>ILOSC

--zadanie 14 - pokaz produkty, ktore zostaly wycofane
SELECT * FROM ZAJECIA.PRODUKTY
WHERE WYCOFANY=1

--zadanie 15 - pokaz wszystkich pracownikow
SELECT * FROM ZAJECIA.PRACOWNICY

--zadanie 16 - pokaz pracownikow urodzonych przed 1960-01-01
SELECT * FROM ZAJECIA.PRACOWNICY p
WHERE EXTRACT(YEAR FROM p.DATAURODZENIA )<1960

--zadanie 17 - sprawdz czy data urodzenia pracownika n ie jest wieksza od daty zatrudniania
SELECT * FROM ZAJECIA.PRACOWNICY
WHERE DATAURODZENIA > DATAZATRUDNIENIA

--zadanie 18 - pokaz jaka jest srenia placa w firmie
SELECT round(avg(PLACA),2) AS srednie_zarobki FROM ZAJECIA.PRACOWNICY

--zadanie 19 - pokaz, jaka jest najmniejsza, najwieksza i srednia placa w firmie
SELECT min(PLACA) AS minimalna_placa, MAX(PLACA)AS placa_maksymalna, avg(placa) AS srednia_placa FROM ZAJECIA.PRACOWNICY

--zadanie 20 - pokaz srednie zarobki na stanowisku przedstawiciel handlowy.
--dodaj symbol dolara do wyniku
SELECT avg(PLACA) AS srednia_pensja, to_char(avg(placa), '$9,999.99') z_dolarem FROM ZAJECIA.PRACOWNICY
WHERE STANOWISKO LIKE 'Przedstawiciel%'

--zadanie 21 - pokaz srednie zarobki w kazdym miescie
SELECT avg(PLACA), MIASTO FROM ZAJECIA.PRACOWNICY
GROUP BY MIASTO

--zadanie 22 - pokaz ilosc pracownikow w kazdym miescie
SELECT count(IDPRACOWNIKA) AS ilsoc_pracownikow, MIASTO FROM ZAJECIA.PRACOWNICY
GROUP BY MIASTO

--zadanie 23 - pokaz miasta, w ktorych ilosc pracownikow jest wieksza niz 2

SELECT count(IDPRACOWNIKA) AS ilosc, MIASTO FROM ZAJECIA.PRACOWNICY
GROUP BY MIASTO
having count(IDPRACOWNIKA) > 2

--zadanie 24 - pokaz wiek kazdej osoby (Months_between + trunc)
SELECT imie, NAZWISKO, trunc(MONTHS_BETWEEN(CURRENT_DATE, DATAURODZENIA)/12) AS wiek FROM ZAJECIA.PRACOWNICY

-- zadanie 25 pokaz sredni wiek na kazdym stanowisku
SELECT avg(trunc(MONTHS_BETWEEN(CURRENT_DATE, DATAURODZENIA)/12)) AS sredni_wiek, STANOWISKO FROM ZAJECIA.PRACOWNICY
GROUP BY STANOWISKO

-- zadanie 26 pokaz wiek pracownikow za 10 miesiecy
SELECT NAZWISKO, trunc(MONTHS_BETWEEN(CURRENT_DATE, DATAURODZENIA)/12) AS wiek,
trunc((MONTHS_BETWEEN(CURRENT_DATE, DATAURODZENIA)+10)/12) AS wiek_za_10_mscy
FROM ZAJECIA.PRACOWNICY

--zadanie 27 - pokaz trzy najlepiej zarabiajace osoby w firmie
SELECT * FROM (
SELECT NAZWISKO, PLACA  FROM ZAJECIA.PRACOWNICY
ORDER BY PLACA DESC)
WHERE rownum <=3

--zadanie 28 - pokaz towary dostarczone przez XYZ
SELECT DISTINCT NAZWAPRODUKTU, d.NAZWAFIRMY FROM ZAJECIA.PRODUKTY p INNER JOIN ZAJECIA.DOSTAWCY d ON p.IDDOSTAWCY=d.IDDOSTAWCY
WHERE d.NAZWAFIRMY LIKE 'XYZ'

--zadanie 29 - pokaz produkty firmy Polinax, ktorych ilosc jest mniejsza niz 10
SELECT NAZWAPRODUKTU, d.NAZWAFIRMY FROM ZAJECIA.PRODUKTY p INNER JOIN ZAJECIA.DOSTAWCY d ON p.IDDOSTAWCY=d.IDDOSTAWCY
WHERE d.NAZWAFIRMY='Polinax' AND ILOSC<10

--zadania 30 - Pokaz produkty firm pochodzacych z bytomia
SELECT NAZWAPRODUKTU, d.NAZWAFIRMY, d.MIASTO FROM ZAJECIA.PRODUKTY p INNER JOIN ZAJECIA.DOSTAWCY d ON p.IDDOSTAWCY=d.IDDOSTAWCY
WHERE miasto='Bytom'


--zadanie 31- Pokaz srednia ilosc produktow kazdego dostawy w magazynie
SELECT NAZWAPRODUKTU, avg(ilosc) AS srednia_ilosc, d.NAZWAFIRMY FROM ZAJECIA.PRODUKTY p INNER JOIN ZAJECIA.DOSTAWCY d ON p.IDDOSTAWCY=d.IDDOSTAWCY
GROUP BY d.NAZWAFIRMY, NAZWAPRODUKTU

--zadanie 32 - pokaz ilosc zamowien kazdego klienta
SELECT count(IDZAMOWIENIA) AS ilosc_zamowien, IDKLIENTA FROM ZAJECIA.ZAMOWIENIA
GROUP BY IDKLIENTA
ORDER BY IDKLIENTA

--zadanie 33 - pokaz sumaryczne ilosc zamowien dla klientow aktywnych
SELECT sum(z.IDZAMOWIENIA) AS suma_zamowien FROM ZAJECIA.KLIENCI k INNER JOIN ZAJECIA.ZAMOWIENIA z ON k.IDKLIENTA=z.IDKLIENTA
WHERE AKTYWNY=1

--zadanie 34 - ilosc towarow dostarczone przez dostawcow

-- wszystkie towary
SELECT sum(ilosc) AS suma_towarow  FROM ZAJECIA.DOSTAWCY d INNER JOIN ZAJECIA.PRODUKTY p ON d.IDDOSTAWCY=p.IDDOSTAWCY

--ilosc toarow dostarczona przez kazdego dostawcow
SELECT sum(ilosc) AS suma, NAZWAFIRMY FROM ZAJECIA.DOSTAWCY d INNER JOIN ZAJECIA.PRODUKTY p ON d.IDDOSTAWCY=p.IDDOSTAWCY
GROUP BY NAZWAFIRMY

--zadanie 35 określ w jakim dniu roku 1997 firma zanotowała najwiekszy zysk
SELECT * FROM (
SELECT DATAZAMOWIENIA, (o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT)) AS zysk FROM ZAJECIA.ZAMOWIENIA z INNER JOIN ZAJECIA.OPISY_ZAMOWIEN o ON z.IDZAMOWIENIA=o.IDZAMOWIENIA
WHERE EXTRACT(YEAR FROM z.DATAZAMOWIENIA)=1997
ORDER BY zysk DESC)
WHERE rownum=1

--zadanie 36. Podaj sumaryczne przychody ze sprzedazy dla danego klienta
SELECT zajecia.KLIENCI.IDKLIENTA, sum((zajecia.OPISY_ZAMOWIEN.CENAJEDNOSTKOWA*zajecia.OPISY_ZAMOWIEN.ILOSC)*(1-zajecia.OPISY_ZAMOWIEN.RABAT)) AS przychody
FROM zajecia.KLIENCI JOIN ZAJECIA.ZAMOWIENIA ON zajecia.KLIENCI.IDKLIENTA=zajecia.ZAMOWIENIA.IDKLIENTA
JOIN ZAJECIA.OPISY_ZAMOWIEN ON zajecia.ZAMOWIENIA.IDZAMOWIENIA=zajecia.OPISY_ZAMOWIEN.IDZAMOWIENIA
GROUP BY zajecia.KLIENCI.IDKLIENTA

-- 37. Pokaz jak ksztaltuje sie wielkosc sprzedazy towarow w danym dniu miesiaca na przelomie wszystkich lat, ktore znajduja sie w bazie
SELECT DISTINCT EXTRACT(DAY FROM z.DATAZAMOWIENIA) AS dzien, sum(o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT)) AS suma FROM ZAJECIA.ZAMOWIENIA z INNER JOIN ZAJECIA.OPISY_ZAMOWIEN o ON z.IDZAMOWIENIA=o.IDZAMOWIENIA
GROUP BY EXTRACT(DAY FROM z.DATAZAMOWIENIA)
ORDER BY EXTRACT(DAY FROM z.DATAZAMOWIENIA)

--38. Okresl w jak ksztaltuje sie wielkosc sprzedazy towarow w danym dniu miesiaca na przelomie wszystkich lat dla towaru Tofu
SELECT DISTINCT EXTRACT(DAY FROM z.DATAZAMOWIENIA) AS dzien, sum(o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT)) AS suma FROM ZAJECIA.ZAMOWIENIA z INNER JOIN ZAJECIA.OPISY_ZAMOWIEN o ON z.IDZAMOWIENIA=o.IDZAMOWIENIA
INNER JOIN ZAJECIA.PRODUKTY p ON o.IDPRODUKTU=p.IDPRODUKTU
WHERE p.NAZWAPRODUKTU LIKE 'Tofu'
GROUP BY  EXTRACT(DAY FROM z.DATAZAMOWIENIA)
ORDER BY EXTRACT(DAY FROM z.DATAZAMOWIENIA)

-- 39 Pokaz sumaryczna kwote rabatow dla kazdego klienta
SELECT sum(CENAJEDNOSTKOWA*ILOSC*RABAT) AS suma, z.IDKLIENTA FROM ZAJECIA.OPISY_ZAMOWIEN p INNER JOIN  ZAJECIA.ZAMOWIENIA z ON p.IDZAMOWIENIA=z.IDZAMOWIENIA
GROUP BY z.IDKLIENTA

-- 40 Pokaz sumaryczna kwote rabatow dla kazdej kategorii towaru
SELECT sum(o.ILOSC*o.CENAJEDNOSTKOWA*o.RABAT) AS suma_rabatu,p.IDKATEGORII  FROM ZAJECIA.OPISY_ZAMOWIEN o INNER JOIN ZAJECIA.PRODUKTY p ON o.IDPRODUKTU=p.IDPRODUKTU
GROUP BY p.IDKATEGORII
ORDER BY p.IDKATEGORII

-- 41 Pokaz zamowienia, w ktorych cena sprzedazy jest wyzsza od ceny katalogowej ?
SELECT p.IDPRODUKTU, o.IDZAMOWIENIA, p.CENAJEDNOSTKOWA AS cena_sprzedazy, o.CENAJEDNOSTKOWA AS cena_katalogowa
FROM ZAJECIA.PRODUKTY p INNER JOIN ZAJECIA.OPISY_ZAMOWIEN o ON  p.IDPRODUKTU=o.IDPRODUKTU
WHERE p.CENAJEDNOSTKOWA>o.CENAJEDNOSTKOWA

-- 42 Pokaz klientow, ktorzy nie otrzymali jeszcze rabatow.
SELECT z.IDZAMOWIENIA, RABAT FROM ZAJECIA.OPISY_ZAMOWIEN o INNER JOIN ZAJECIA.ZAMOWIENIA z ON o.IDZAMOWIENIA=z.IDZAMOWIENIA
WHERE RABAT=0

-- 43 Pokaz zarobki z kaĹĽdego produktu
SELECT p.IDPRODUKTU, sum((p.CENAJEDNOSTKOWA-o.CENAJEDNOSTKOWA)*p.ilosc) AS zarobek
FROM ZAJECIA.OPISY_ZAMOWIEN o INNER JOIN ZAJECIA.PRODUKTY p ON o.IDPRODUKTU=p.IDPRODUKTU
GROUP BY p.IDPRODUKTU
ORDER BY p.IDPRODUKTU

-- 44 Pokaz zarobek na kazdym dostawcy
SELECT d.NAZWAFIRMY,  sum(o.ILOSC*o.CENAJEDNOSTKOWA*(1-o.RABAT)) AS zarobki
FROM ZAJECIA.OPISY_ZAMOWIEN o
INNER JOIN ZAJECIA.ZAMOWIENIA z ON o.IDZAMOWIENIA=z.IDZAMOWIENIA
INNER JOIN ZAJECIA.PRODUKTY p ON p.IDPRODUKTU=o.IDPRODUKTU
JOIN ZAJECIA.DOSTAWCY d ON p.IDDOSTAWCY=d.IDDOSTAWCY
GROUP BY d.NAZWAFIRMY

-- 45 Pokaz ilosc sprzedawanych produktow w roku 1997
SELECT sum(ILOSC) FROM ZAJECIA.OPISY_ZAMOWIEN o INNER JOIN ZAJECIA.ZAMOWIENIA z ON o.IDZAMOWIENIA=z.IDZAMOWIENIA
WHERE EXTRACT(YEAR FROM z.DATAZAMOWIENIA)=1997

-- 46 Okresl jaka jest najmniejsza ilosc towarow w danej kategorii
SELECT sum(p.STANMINIMUM) AS najmniejsza_ilosc_towaru,NAZWAKATEGORII FROM ZAJECIA.KATEGORIE k INNER JOIN ZAJECIA.PRODUKTY p ON k.IDKATEGORII=p.IDKATEGORII
GROUP BY k.NAZWAKATEGORII

-- 47 Pokaz te zamowienia, w ktorych stosunek rabatu do wartosci zamowienia jest wiekszy niz 20%
SELECT * FROM ZAJECIA.OPISY_ZAMOWIEN
WHERE (ILOSC*CENAJEDNOSTKOWA)/(RABAT*CENAJEDNOSTKOWA*ILOSC)>1/4

-- 48 Pokaz 3 produkty, ktore najczesciej wystepowaly na fakturach
SELECT * FROM
(SELECT p.NAZWAPRODUKTU, COUNT(IDZAMOWIENIA) AS ilosc_zamowien FROM ZAJECIA.OPISY_ZAMOWIEN o INNER JOIN ZAJECIA.PRODUKTY p ON o.IDPRODUKTU=p.IDPRODUKTU
GROUP BY p.NAZWAPRODUKTU
ORDER BY 2 DESC)
WHERE rownum<=3

 -- 49 Okresl, ktore firmy, jakie produkty oraz w jakiej ilosci najczesciej wystepowaly (3 rekordy)

-- 50 Okresl, jaka jest srednia roznica w dniach czasu wysylki towarow dla danego pracownika
SELECT IDPRACOWNIKA, trunc(avg(DATAWYSYLKI-DATAZAMOWIENIA)) AS średnia_rożnica FROM ZAJECIA.ZAMOWIENIA
GROUP BY IDPRACOWNIKA

-- 51 Wskaz 3 pracownikow, ktorzy obsluguja najmniejsza ilosc klientow aktywnych
SELECT * FROM (SELECT z.IDPRACOWNIKA, COUNT(z.IDKLIENTA) AS ilosc_klientow FROM ZAJECIA.ZAMOWIENIA z INNER JOIN  ZAJECIA.KLIENCI k ON z.IDKLIENTA=k.IDKLIENTA
WHERE k.AKTYWNY=1
GROUP BY IDPRACOWNIKA
ORDER BY 2 )
WHERE rownum<=3

-- 52 Okresl, jaki jest sredni czas wysylki towarow do klientow wedlug miejscowosci, do ktorej towar bedzie wysylany
SELECT avg(DATAWYSYLKI-DATAZAMOWIENIA) AS sredni_czas_wysylki, k.MIASTO FROM ZAJECIA.ZAMOWIENIA z INNER JOIN ZAJECIA.KLIENCI k ON z.IDKLIENTA=k.IDKLIENTA
GROUP BY k.MIASTO
ORDER BY 1

-- 53 Okresl dla ktorych pracownikow czas wysylki towaru od dnia zamowien ia jest wiekszy niz 20 dni.
SELECT DISTINCT IDPRACOWNIKA, trunc(DATAWYSYLKI-DATAZAMOWIENIA)
FROM ZAJECIA.ZAMOWIENIA
WHERE trunc(DATAWYSYLKI-DATAZAMOWIENIA) > 20
ORDER BY IDPRACOWNIKA ASC

-- 54 Dokonaj zsumowania tej ilsoci dla kazdego pracownika.
SELECT IDPRACOWNIKA, sum(trunc(DATAWYSYLKI-DATAZAMOWIENIA)) AS iosc_dni FROM ZAJECIA.ZAMOWIENIA
WHERE trunc(DATAWYSYLKI-DATAZAMOWIENIA)>20
GROUP BY IDPRACOWNIKA

-- 55 Okresl jaka jest srednia wieku pracownikow
SELECT round(avg(trunc(MONTHS_BETWEEN(CURRENT_DATE, DATAURODZENIA)/12))) AS sredni_wiek FROM ZAJECIA.PRACOWNICY

-- 56 Okresl, jaka jest srednia wieku pracownikow wedlug stanowisk
SELECT round(avg(MONTHS_BETWEEN(CURRENT_DATE, DATAURODZENIA)/12)) AS sredni_wiek, STANOWISKO FROM ZAJECIA.PRACOWNICY
GROUP BY STANOWISKO

-- 57 Podaj wiek, imie, nazwisko pracownika ,ktory przeprowadzil najwiecej transakcji
SELECT * FROM (
SELECT imie, NAZWISKO, (trunc(MONTHS_BETWEEN(CURRENT_DATE, DATAURODZENIA)/12)) AS wiek, COUNT(z.IDZAMOWIENIA) AS ilosc_transakcji  FROM ZAJECIA.PRACOWNICY p INNER JOIN ZAJECIA.ZAMOWIENIA z ON p.IDPRACOWNIKA=z.IDPRACOWNIKA
GROUP BY IMIE, NAZWISKO, trunc(MONTHS_BETWEEN(CURRENT_DATE, DATAURODZENIA)/12)
ORDER BY COUNT(z.IDZAMOWIENIA) DESC)
WHERE rownum=1

--58. pokaz produkty, ktore maja najwieksza cene jednostkowa
SELECT NAZWAPRODUKTU, CENAJEDNOSTKOWA FROM ZAJECIA.PRODUKTY
ORDER BY CENAJEDNOSTKOWA DESC

--59. Znajdz produkty, ktorych jest najmniej na magazynie
SELECT NAZWAPRODUKTU, ILOSC FROM ZAJECIA.PRODUKTY
ORDER BY ILOSC

--60. Ktorego napoju jest najmniej na magazynie?
SELECT * FROM (SELECT p.NAZWAPRODUKTU, p.ILOSC FROM ZAJECIA.KATEGORIE k INNER JOIN ZAJECIA.PRODUKTY p ON k.IDKATEGORII=p.IDKATEGORII
WHERE NAZWAKATEGORII='Napoje'
ORDER BY p.ILOSC )
WHERE rownum=1

--61. Znajdz produkty, ktorych jest mniej niz srednia ilosc napojow na magazynie
SELECT * FROM ZAJECIA.PRODUKTY p INNER JOIN ZAJECIA.KATEGORIE k on p.IDKATEGORII=k.IDKATEGORII
WHERE ILOSC< (SELECT avg(ILOSC) FROM ZAJECIA.PRODUKTY p INNER JOIN ZAJECIA.KATEGORIE k on p.IDKATEGORII=k.IDKATEGORII WHERE k.NAZWAKATEGORII='Napoje' )

--62. Znajdz pracownikow, ktorzy najmniej zarabiaja
SELECT * FROM ZAJECIA.PRACOWNICY
ORDER BY PLACA ASC

--63. Znajdz pracownikow, ktorzy najwiecej zarabiaja
SELECT * FROM ZAJECIA.PRACOWNICY
ORDER BY placa DESC

--64. Ktory klient otrzymal najwyzszy rabat?
select z.IDKLIENTA, sum(RABAT*CENAJEDNOSTKOWA*ILOSC) AS suma_rabatow FROM ZAJECIA.OPISY_ZAMOWIEN o INNER JOIN ZAJECia.ZAMOWIENIA z ON o.IDZAMOWIENIA=z.IDZAMOWIENIA
GROUP BY z.IDKLIENTA
ORDER BY sum(RABAT*CENAJEDNOSTKOWA*ILOSC) DESC

--65. Ktory klient zakupil najwieksza ilosc towarow?
SELECT * FROM (SELECT z.IDKLIENTA, sum(ILOSC)AS ilosc_towarow FROM ZAJECIA.OPISY_ZAMOWIEN o INNER JOIN ZAJECIA.ZAMOWIENIA z ON o.IDZAMOWIENIA=z.IDZAMOWIENIA
GROUP BY z.IDKLIENTA
ORDER BY sum(ILOSC)DESC)
WHERE rownum=1

--66. Okresl, ktory klient wykonal najwiecej zamowien
SELECT * FROM (SELECT IDKLIENTA, COUNT(IDZAMOWIENIA) AS ilosc_zamowien FROM ZAJECIA.ZAMOWIENIA
GROUP BY IDKLIENTA
ORDER BY COUNT(IDZAMOWIENIA) desc)
WHERE rownum=1

--67. Okresl, ktory pracownik wykonal najwiecej transakcji sprzedazy w roku 1996?

SELECT z.IDPRACOWNIKA, count(z.IDZAMOWIENIA) AS ilosc_transakcji FROM ZAJECIA.OPISY_ZAMOWIEN o INNER JOIN ZAJECIA.ZAMOWIENIA z ON o.IDZAMOWIENIA=z.IDZAMOWIENIA
WHERE EXTRACT(YEAR FROM z.DATAZAMOWIENIA)=1996
GROUP BY z.IDPRACOWNIKA
ORDER BY count(z.IDZAMOWIENIA) desc

--68. Okresl, w ktorej kategorii towarow jest srednio wiecej towarow niz w napojach?
SELECT NAZWAKATEGORII, avg(o.ILOSC) średnia FROM ZAJECIA.KATEGORIE k JOIN ZAJECIA.PRODUKTY o ON k.IDKATEGORII=o.IDKATEGORII
GROUP BY NAZWAKATEGORII
HAVING avg(o.ILOSC)> (SELECT avg(o.ILOSC) FROM ZAJECIA.KATEGORIE k JOIN ZAJECIA.PRODUKTY o ON k.IDKATEGORII=o.IDKATEGORII WHERE NAZWAKATEGORII= 'Napoje')

--69. Okresl, ktorzy pracownicy obsluguja wiecej klientow niz pracownik King?
SELECT COUNT(z.IDKLIENTA) AS ilosc_klientow, NAZWISKO FROM ZAJECIA.PRACOWNICY p INNER JOIN ZAJECIA.ZAMOWIENIA z  ON p.IDPRACOWNIKA=z.IDPRACOWNIKA
GROUP BY NAZWISKO
HAVING COUNT(z.IDKLIENTA) > (SELECT COUNT(z.IDKLIENTA) FROM ZAJECIA.PRACOWNICY p INNER JOIN ZAJECIA.ZAMOWIENIA z  ON p.IDPRACOWNIKA=z.IDPRACOWNIKA
WHERE NAZWISKO='King')
ORDER  BY COUNT(z.IDKLIENTA)

--70. Podaj miasta, w ktĂłrych mieszkajÄ… nasi pracownicy, a w ktorych nie znajduje sie zaden nasz klient

SELECT DISTINCT p.miasto FROM ZAJECIA.PRACOWNICY
WHERE p.MIASTO NOT IN (SELECT  MIASTO FROM ZAJECIA.KLIENCI)

--71. Podaj nazwy firm, które złożyły zamówienia na miesiąc przed najstarszym zamówieniem
SELECT IDKLIENTA, (SELECT max(DATAZAMOWIENIA) FROM ZAJECIA.ZAMOWIENIA)-z.DATAZAMOWIENIA AS roznica_dni  FROM ZAJECIA.ZAMOWIENIA z
WHERE (SELECT max(DATAZAMOWIENIA) FROM ZAJECIA.ZAMOWIENIA)-z.DATAZAMOWIENIA <=31

--72. Dla kazdego produktu podaj maksymalna liczbe zamowionych jednostek
SELECT IDPRODUKTU, max(ILOSC) FROM ZAJECIA.OPISY_ZAMOWIEN
GROUP BY IDPRODUKTU
ORDER BY 1

--73. Podaj, jaka byla najwieksza ilosc zamowionych towarow przypadajaca na jedno zamowienie w odpowiednich kategoriach towarow
SELECT idkategorii, max(o.ilosc)
FROM ZAJECIA.OPISY_ZAMOWIEN o
JOIN ZAJECIA.PRODUKTY p ON p.IDPRODUKTU=o.IDPRODUKTU
GROUP BY idkategorii

--74. Okresl najtanszy towar u danego dostawcy
SELECT min(CENAJEDNOSTKOWA), IDDOSTAWCY FROM ZAJECIA.PRODUKTY
GROUP BY IDDOSTAWCY
ORDER BY 1

--- czesc analityczna I

--1. Pokaz nazwe produktu, nazwe firmy, kategorie produktu oraz sume sprzedazy.
SELECT p.NAZWAPRODUKTU, k.nazwafirmy, p.IDKATEGORII, sum(o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT)) AS suma
FROM PRODUKTY p
INNER JOIN OPISY_ZAMOWIEN o ON o.IDPRODUKTU=p.IDPRODUKTU
INNER JOIN DOSTAWCY d ON d.IDDOSTAWCY=p.IDDOSTAWCY
INNER JOIN ZAMOWIENIA z ON z.IDZAMOWIENIA=o.IDZAMOWIENIA
INNER JOIN KLIENCI k ON k.IDKLIENTA=z.IDKLIENTA
GROUP BY p.NAZWAPRODUKTU, k.nazwafirmy, p.IDKATEGORII

--2. Dla kategorii 1 oraz produktu Chang pokaż jakie firmy go kupowały oraz dla każdej z nich pokaż sum wartości jej zakupów. Dodatkowo pokaż sum całkowitej sprzedaży.
SELECT p.NAZWAPRODUKTU, k.nazwafirmy, p.IDKATEGORII, sum(o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT)) AS suma
FROM PRODUKTY p
INNER JOIN OPISY_ZAMOWIEN o ON o.IDPRODUKTU=p.IDPRODUKTU
INNER JOIN DOSTAWCY d ON d.IDDOSTAWCY=p.IDDOSTAWCY
INNER JOIN ZAMOWIENIA z ON z.IDZAMOWIENIA=o.IDZAMOWIENIA
INNER JOIN KLIENCI k ON k.IDKLIENTA=z.IDKLIENTA
WHERE p.IDKATEGORII = 1 AND p.NAZWAPRODUKTU LIKE 'Chang'
GROUP BY ROLLUP (p.IDKATEGORII, p.NAZWAPRODUKTU, k.nazwafirmy)
ORDER BY 3, 1, 2 DESC

--3. Podaj nazwy firmy oraz nazwy produktow a takze sume wartosci sprzedazy dla nich. Nie ma byc generowane podsumowanie dla samych firm oraz samych nazw produktow.
SELECT k.NAZWAFIRMY, p.NAZWAPRODUKTU, sum(o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT)) AS suma_sprz
FROM PRODUKTY p
INNER JOIN OPISY_ZAMOWIEN o ON p.IDPRODUKTU=o.IDPRODUKTU
INNER JOIN ZAMOWIENIA z ON o.IDZAMOWIENIA=z.IDZAMOWIENIA
INNER JOIN KLIENCI k ON k.IDKLIENTA=z.IDKLIENTA
GROUP BY k.NAZWAFIRMY, rollup(p.NAZWAPRODUKTU)
ORDER BY 1, 3 DESC

--4.Wyknaj pol kostke dla produktow Tofu oraz Chai prezentujac nazwe produktu oraz klienta wedlug wartosci sprzedazy
SELECT k.NAZWAFIRMY, p.NAZWAPRODUKTU, sum(o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT)) AS suma_sprz
FROM PRODUKTY p
INNER JOIN OPISY_ZAMOWIEN o ON p.IDPRODUKTU=o.IDPRODUKTU
INNER JOIN ZAMOWIENIA z ON o.IDZAMOWIENIA=z.IDZAMOWIENIA
INNER JOIN KLIENCI k ON k.IDKLIENTA=z.IDKLIENTA
WHERE p.NAZWAPRODUKTU IN ('Tofu', 'Chai')
GROUP BY cube(k.NAZWAFIRMY, p.NAZWAPRODUKTU)
ORDER BY 1, 2

--5. Dokonaj takiego przeksztalcenia zadania 4 aby w miejscach gdzie wystepuja wartosci NULL w podsumowaniu pojawil sie napis "wszystkie produkty" lub "wszystkie firmy"
SELECT Decode (GROUPING(k.NAZWAFIRMY), 0, k.NAZWAFIRMY, '===WSZYSTKIE FIRMY===') AS nazwa_firmy,
	DECODE(GROUPING(p.NAZWAPRODUKTU), 0, p.NAZWAPRODUKTU, '===WSZYSTKIE PRODUKTY===') AS nazwa_produktu,
	sum(o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT)) AS suma_sprz
FROM PRODUKTY p
INNER JOIN OPISY_ZAMOWIEN o ON p.IDPRODUKTU=o.IDPRODUKTU
INNER JOIN ZAMOWIENIA z ON o.IDZAMOWIENIA=z.IDZAMOWIENIA
INNER JOIN KLIENCI k ON k.IDKLIENTA=z.IDKLIENTA
WHERE p.NAZWAPRODUKTU IN ('Tofu', 'Chai')
GROUP BY cube(k.NAZWAFIRMY, p.NAZWAPRODUKTU)
ORDER BY 1, 2

--6. Prowadzimy analize sprzedazy realizowanej przez naszych pracownikow z podzialem na klientow. Okresl wartosc sprzedazy pracownika Peacock z podzialem na poszczegolnych klientow. Chcemy uzyskac sume wartosci sprzedazy tego pracownika oraz sume wartosci sprzedazy dla kazdego z klientow.
SELECT NAZWISKO, k.NAZWAFIRMY, sum(o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT)) AS wartosc FROM ZAJECIA.PRACOWNICY p JOIN ZAJECIA.ZAMOWIENIA z ON p.IDPRACOWNIKA=z.IDPRACOWNIKA JOIN ZAJECIA.KLIENCI k ON z.IDKLIENTA=k.IDKLIENTA INNER JOIN ZAJECIA.OPISY_ZAMOWIEN o ON z.IDZAMOWIENIA=o.IDZAMOWIENIA
WHERE p.NAZWISKO='Peacock'
GROUP BY  GROUPING SETS ((NAZWISKO, k.NAZWAFIRMY), ())

-- 7. Prowadzimy analize sprzedazy realizowanej przez naszych pracownikow z podzialem na klientow. Okresl wartosc sprzedazy pracownika Peacock z podzialem na poszczegolnych klientow. Chcemy uzyskać sume wartosci sprzedazy tego pracownika oraz sume wartosci sprzedazy dla kazdego z klientow europejskich majacych status aktywny.

SELECT NAZWISKO, k.NAZWAFIRMY, k.KRAJ, sum(o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT)) AS sprzedaz
FROM ZAJECIA.PRACOWNICY p JOIN ZAJECIA.ZAMOWIENIA z ON p.IDPRACOWNIKA=z.IDPRACOWNIKA JOIN ZAJECIA.KLIENCI k ON z.IDKLIENTA=k.IDKLIENTA JOIN ZAJECIA.OPISY_ZAMOWIEN o ON z.IDZAMOWIENIA=o.IDZAMOWIENIA
WHERE NAZWISKO='Peacock' AND k.AKTYWNY=1 AND k.KRAJ NOT IN ('USA', 'Brazylia', 'Argentyna', '%anada')
GROUP BY  GROUPING SETS ((NAZWISKO, k.NAZWAFIRMY, k.kraj), ())

-- 8.Prowadzimy analize sprzedazy realizowanej przez naszych pracownikow z podzialem na sprzedawane produkty. Okresl jakie produkty sprzedawal pracownik Peacock. Chcemy uzyskac sume wartosci sprzedazy tego pracownika oraz sume wartosci sprzedazy dla kazdego z produktow.
SELECT NAZWISKO, pr.NAZWAPRODUKTU, sum(pr.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT)) AS wartosc_sprzedazy
FROM ZAJECIA.PRACOWNICY p  JOIN ZAJECIA.ZAMOWIENIA z ON p.IDPRACOWNIKA=z.IDPRACOWNIKA JOIN ZAJECIA.OPISY_ZAMOWIEN o ON z.IDZAMOWIENIA=o.IDZAMOWIENIA JOIN ZAJECIA.PRODUKTY pr ON o.IDPRODUKTU=pr.IDPRODUKTU
WHERE NAZWISKO='Peacock'
GROUP BY GROUPING SETS ((NAZWISKO, pr.NAZWAPRODUKTU), ())

--9.rowadzimy analize sprzedazy realizowanej przez naszych pracownikow z podzialem na sprzedawane produkty. Okresl jakie produkty sprzedawal pracownik Peacock. Chcemy uzyskac sume wartosci sprzedazy tego pracownika oraz sume wartosci sprzedazy dla kazdego z produktow, ale tylko dla napojow i transakcji z rozku 1997.

SELECT p.NAZWISKO, pr.NAZWAPRODUKTU
FROM ZAJECIA.PRACOWNICY p JOIN ZAJECIA.ZAMOWIENIA z ON p.IDPRACOWNIKA=z.IDPRACOWNIKA JOIN ZAJECIA.OPISY_ZAMOWIEN o ON z.IDZAMOWIENIA=o.IDZAMOWIENIA JOIN ZAJECIA.PRODUKTY pr ON o.IDPRODUKTU=pr.IDPRODUKTU JOIN ZAJECIA.KATEGORIE k ON pr.IDKATEGORII=k.IDKATEGORII
WHERE p.NAZWISKO='Peacock' AND k.NAZWAKATEGORII='Napoje' AND extract(YEAR FROM z.DATAZAMOWIENIA)=1997
GROUP BY GROUPING SETS((p.NAZWISKO, pr.NAZWAPRODUKTU), ())

--10.Zbuduj kostke ukazujaca informacje o ilosci transakcji oraz wartosci transakcji z podzialem na kategorie produktu, kraj klienta

SELECT count(z.IDZAMOWIENIA) AS ilosc_transakcji, sum(p.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT)) AS suma_wartosc_transakcji, k.IDKATEGORII, kl.KRAJ
FROM ZAJECIA.ZAMOWIENIA z JOIN ZAJECIA.OPISY_ZAMOWIEN o ON z.IDZAMOWIENIA=o.IDZAMOWIENIA JOIN ZAJECIA.PRODUKTY p ON o.IDPRODUKTU=p.IDPRODUKTU JOIN ZAJECIA.KATEGORIE k ON p.IDKATEGORII=k.IDKATEGORII JOIN ZAJECIA.KLIENCI kl ON kl.IDKLIENTA=z.IDKLIENTA
GROUP BY cube(k.IDKATEGORII, kl.KRAJ)

-- 11.Zbuduj kostke ukazujaca ilosc transakcji oraz wartosc transakcji z podzialem na dany rok oraz stanowisko pracownika obslugujacego transakcje
SELECT count(z.IDZAMOWIENIA)AS ilsoc_zamowien, sum(o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT)) AS wartosc, EXTRACT(YEAR FROM z.DATAZAMOWIENIA) AS rok_zmowienia, p.STANOWISKO
FROM ZAJECIA.ZAMOWIENIA z JOIN ZAJECIA.OPISY_ZAMOWIEN o ON z.IDZAMOWIENIA=o.IDZAMOWIENIA JOIN ZAJECIA.PRACOWNICY p ON z.IDPRACOWNIKA=p.IDPRACOWNIKA
GROUP BY CUBE(extract(YEAR FROM z.DATAZAMOWIENIA), p.STANOWISKO)

-- funkcje analityczne rankingu

-- 1. Zdefiniuj rankin srednich zarobkow pracownikow na danym stanowsko pracy w kolejnosci malejacej
SELECT STANOWISKO, avg(placa) AS srednia_płaca, RANK() over(ORDER BY avg(placa) DESC) AS ranking_płac
FROM ZAJECIA.PRACOWNICY
GROUP BY STANOWISKO

-- 2. znajdz maksymalnie dwa pordukty z danej kategorii, ktorych stan magazynowy jest ponizej minimalnego wolumenu produktu na magazynie i zaproponuj wielkos zakupu tak aby stan magazynowy zrownal sie ze stanem minimum
SELECT * FROM (SELECT NAZWAPRODUKTU, k.NAZWAKATEGORII,STANMINIMUM-ILOSC AS ile_kupic,
rank() OVER(PARTITION BY k.NAZWAKATEGORII ORDER BY (STANMINIMUM-ilosc) ASC)AS ranking  FROM ZAJECIA.PRODUKTY p, ZAJECIA.KATEGORIE k
WHERE p.IDKATEGORII=k.IDKATEGORII AND ILOSC<STANMINIMUM)
WHERE ranking <=2
ORDER BY 2

-- 3. pokaz 3 naljepiej zarabiajace osoby w firmie
SELECT * FROM (
SELECT NAZWISKO, PLACA, rank() OVER (ORDER BY PLACA DESC) ranking FROM ZAJECIA.PRACOWNICY)
WHERE ranking<=2

-- 4. pokaz 3 produkty ktore najczejciej wystepowaly na fakturach

SELECT IDPRODUKTU, COUNT(IDZAMOWIENIA) AS ile, RANK() over(ORDER BY COUNT(IDZAMOWIENIA) DESC) RANK FROM ZAJECIA.OPISY_ZAMOWIEN
GROUP BY IDPRODUKTU

-- 5. zbuduj ranking wartosci sprzedazy produktow. Ma pojawic sie wartosc sprzedazy nazwa produktu, oraz pozycja w rankingu
SELECT NAZWAPRODUKTU, (CENAJEDNOSTKOWA*ILOSC) AS sprzedaz, rank() over(ORDER BY (CENAJEDNOSTKOWA*ILOSC) desc) ranking FROM ZAJECIA.PRODUKTY

--6. pokaz 3 produkty, ktorych wartosc sprzedazy w pierwszym kwartale 1997 byla najwieksza

SELECT p.NAZWAPRODUKTU, sum(o.ILOSC*o.CENAJEDNOSTKOWA*(1-o.RABAT)) AS sprzedaz,
RANK() OVER(ORDER BY sum(o.ILOSC*o.CENAJEDNOSTKOWA*(1-o.RABAT)) DESC ) RANK
FROM ZAJECIA.ZAMOWIENIA z JOIN ZAJECia.OPISY_ZAMOWIEN o ON z.IDZAMOWIENIA=o.IDZAMOWIENIA JOIN ZAJECIA.PRODUKTY p ON o.IDPRODUKTU=p.IDPRODUKTU
WHERE EXTRACT(YEAR FROM z.DATAZAMOWIENIA)= 1997 AND EXTRACT(MONTH FROM z.DATAZAMOWIENIA) BETWEEN 1 AND 3
GROUP BY p.NAZWAPRODUKTU

-- 7. pokaz 3 pracownikoww ktorzy w roku 1998 mieli najwieksza wartosc sprzedanych napojow
SELECT NAZWISKO, sum(o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT))AS sprzedaz, RANK()OVER(ORDER BY sum(o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT)) DESC )RANK
FROM ZAJECIA.PRACOWNICY p JOIN ZAJECIA.ZAMOWIENIA z ON p.IDPRACOWNIKA=z.IDPRACOWNIKA JOIN ZAJECIA.OPISY_ZAMOWIEN o ON z.IDZAMOWIENIA=o.IDZAMOWIENIA JOIN ZAJECIA.PRODUKTY pr ON pr.IDPRODUKTU=o.IDPRODUKTU JOIN ZAJECIA.KATEGORIE k ON pr.IDKATEGORII=k.IDKATEGORII
WHERE extract( YEAR FROM z.DATAZAMOWIENIA)=1998 AND k.NAZWAKATEGORII='Napoje'
GROUP BY NAZWISKO

-- 8. pokaz 3 dostawcow ktorzy w roku 1997 dostarczyli towary o najwyzszej sumarycznej wartosci

SELECT d.NAZWAFIRMY, sum(o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT)) AS sprzedaz,
RANK() over(ORDER BY sum(o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT))DESC) rank
FROM ZAJECIA.DOSTAWCY d JOIN ZAJECIA.PRODUKTY p ON d.IDDOSTAWCY=p.IDDOSTAWCY JOIN ZAJECIA.OPISY_ZAMOWIEN o ON p.IDPRODUKTU=o.IDPRODUKTU JOIN ZAJECIA.ZAMOWIENIA z ON o.IDZAMOWIENIA=z.IDZAMOWIENIA
WHERE  EXTRACT(YEAR FROM z.DATAZAMOWIENIA)=1997
GROUP BY NAZWAFIRMY

-- 9. Pokaz 3 produkty, dla ktorych wartosc rabatow w roku 1999 byla najwysza
SELECT NAZWAPRODUKTU, sum(o.ILOSC*o.CENAJEDNOSTKOWA*o.RABAT) AS suma_rabatu, RANK()over(ORDER BY sum(o.ILOSC*o.CENAJEDNOSTKOWA*o.RABAT)DESC) rank
FROM ZAJECIA.OPISY_ZAMOWIEN o JOIN ZAJECIA.PRODUKTY p ON o.IDPRODUKTU=p.IDPRODUKTU JOIN ZAJECIA.ZAMOWIENIA z ON o.IDZAMOWIENIA=z.IDZAMOWIENIA
WHERE EXTRACT(YEAR FROM z.DATAZAMOWIENIA )=1999
GROUP BY p.NAZWAPRODUKTU

-- 10. Pokaz 3 produkty wycofane, ktore w roku 1996 sprzedawano w najmniejszej ilosci
SELECT p.NAZWAPRODUKTU, o.ILOSC, RANK()OVER(ORDER BY o.ilosc asc) rank
FROM ZAJECIA.PRODUKTY p JOIN ZAJECIA.OPISY_ZAMOWIEN o ON p.IDPRODUKTU=o.IDPRODUKTU JOIN ZAJECIA.ZAMOWIENIA z ON o.IDZAMOWIENIA=z.IDZAMOWIENIA
WHERE EXTRACT(YEAR FROM z.DATAZAMOWIENIA)=1996 AND p.WYCOFANY=1

-- OKNA

-- --1. Aktualnie prowadzimy analizy sprzedazy naszych produktow w podziale na klientow.
-- Potrzebujemy zestawienia ukazujacego klienta, date zamowienia, kwote za lata 1996,1997, 1998.
-- Dodatkowo w tabeli ma pojawic sie pole ukazujace kroczaca zmiane salda w kolejnych okresach ukazujace saldo na koniec kazdego dnia.

SELECT k.NAZWAFIRMY,
z.DATAZAMOWIENIA,
(p.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT)) AS wartosc_zamowienia,
sum(p.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT)) OVER (PARTITION BY k.NAZWAFIRMY ORDER BY z.DATAZAMOWIENIA)saldo
FROM ZAJECIA.PRODUKTY p INNER JOIN OPISY_ZAMOWIEN o ON p.IDPRODUKTU=o.IDPRODUKTU
INNER JOIN ZAMOWIENIA z  ON o.IDZAMOWIENIA=z.IDZAMOWIENIA
INNER JOIN KLIENCI k ON z.IDKLIENTA=k.IDKLIENTA
ORDER  BY 2,1 asc

--- zad 2. Analizujemy sprzedaz naszych pracownikow. Interesuje nas caly okres sprzedazy produktow. Ktory pracownik w ujeciu polrocznym
-- (3 miesiace naprzod i 3 miesiace wstecz) od daty sprzedazy wykaza sie najwieksza sprzedaza produktow w ujeciu ilosciowym?

SELECT IDPRACOWNIKA,
	DATAZAMOWIENIA,
	COUNT(*) OVER (PARTITION BY IDPRACOWNIKA ORDER BY DATAZAMOWIENIA
		RANGE BETWEEN INTERVAL '3' MONTH PRECEDING
		AND INTERVAL '3' MONTH FOLLOWING) AS count6_6
FROM ZAMOWIENIA
ORDER BY 1


---zad 3.  Dla poszczegolnych pracownikow przedstaw zmiany ilosci transakcji ktore obluzyli w roku 1997 w ujeciu miesiecznym.
-- Raport ma pokazywac nazwisko, miesiac, ilosc transakcji w danym miesiace oraz kolumne z suma narastajaca.

SELECT p.NAZWISKO, EXTRACT(MONTH FROM z.DATAZAMOWIENIA) AS miesiac, COUNT(o.IDZAMOWIENIA) AS ilosc,
sum(COUNT(o.IDZAMOWIENIA))over(PARTITION BY p.NAZWISKO ORDER BY EXTRACT(MONTH FROM z.DATAZAMOWIENIA)) suma
FROM PRACOWNICY p INNER JOIN ZAMOWIENIA z ON p.IDPRACOWNIKA=z.IDPRACOWNIKA INNER JOIN OPISY_ZAMOWIEN o ON z.IDZAMOWIENIA=o.IDZAMOWIENIA
WHERE EXTRACT(YEAR FROM z.DATAZAMOWIENIA)=1997
GROUP BY GROUPING SETS((p.NAZWISKO, EXTRACT(MONTH FROM z.DATAZAMOWIENIA)), ())
ORDER BY 1,2

--  zad 4. Dla poszczegolnych kategorii towarow przedstaw zmiany wartosci sprzedazy w ujeci miesiecznym w roku 1998.
--  Na ekranie ma pojawic sie nazwa kategorii

-- saldo z miesiaca na miesiac
SELECT k.NAZWAKATEGORII,
EXTRACT(MONTH FROM z.DATAZAMOWIENIA) AS miesiac,
sum(o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT)) AS sprzedaz,
sum(sum(o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT)))over(PARTITION BY k.NAZWAKATEGORII ORDER BY EXTRACT(MONTH FROM z.DATAZAMOWIENIA))saldo
FROM OPISY_ZAMOWIEN o INNER JOIN ZAMOWIENIA z ON o.IDZAMOWIENIA=z.IDZAMOWIENIA INNER JOIN PRODUKTY p ON o.IDPRODUKTU=p.IDPRODUKTU INNER JOIN KATEGORIE k ON p.IDKATEGORII=k.IDKATEGORII
WHERE EXTRACT(YEAR FROM z.DATAZAMOWIENIA)=1998
GROUP BY GROUPING SETS((k.NAZWAKATEGORII, EXTRACT(MONTH FROM z.DATAZAMOWIENIA)), ())

-- saldo z transakcji na transakcje
SELECT k.NAZWAKATEGORII,
	EXTRACT(MONTH FROM z.DATAZAMOWIENIA) AS miesiac,
	o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT) AS sprz,
	sum(o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT))
		OVER (PARTITION BY k.NAZWAKATEGORII ORDER BY EXTRACT(MONTH FROM z.DATAZAMOWIENIA)) AS saldo
FROM KATEGORIE k
INNER JOIN PRODUKTY p ON p.IDKATEGORII=k.IDKATEGORII
INNER JOIN OPISY_ZAMOWIEN o ON o.IDPRODUKTU=p.IDPRODUKTU
INNER JOIN ZAMOWIENIA z ON z.IDZAMOWIENIA=o.IDZAMOWIENIA
WHERE EXTRACT(YEAR FROM z.DATAZAMOWIENIA)=1998

-- zad 5 dla poszczególnych klientow przedstaw zmiany wartosci sprzedazy w ujeciu miesiecznym w roku 1997. na ekranie ma sie pojawić nazwa klienta, miesiac, wartosc sprzedazy i narastajaca suma wartosci spredazy

-- z miesiaca na miesiac
SELECT k.NAZWAFIRMY,
EXTRACT(MONTH FROM z.DATAZAMOWIENIA) AS miesiac,
sum(o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT)) AS suma,
sum(sum(o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT)))over(PARTITION BY k.NAZWAFIRMY ORDER BY EXTRACT(MONTH FROM z.DATAZAMOWIENIA)) zmiany
FROM KLIENCI k INNER JOIN ZAMOWIENIA z ON k.IDKLIENTA=z.IDKLIENTA INNER JOIN OPISY_ZAMOWIEN o ON z.IDZAMOWIENIA=o.IDZAMOWIENIA
WHERE EXTRACT(YEAR FROM z.DATAZAMOWIENIA)=1997
GROUP BY GROUPING SETS((k.NAZWAFIRMY,EXTRACT(MONTH FROM z.DATAZAMOWIENIA)), ())
ORDER BY 1,2

-- po kazdej tran
SELECT k.NAZWAFIRMY,
EXTRACT(MONTH FROM z.DATAZAMOWIENIA) AS miesiac,
o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT) AS suma,
sum(o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT))over(PARTITION BY k.NAZWAFIRMY ORDER BY EXTRACT(MONTH FROM z.DATAZAMOWIENIA)) zmiany
FROM KLIENCI k INNER JOIN ZAMOWIENIA z ON k.IDKLIENTA=z.IDKLIENTA INNER JOIN OPISY_ZAMOWIEN o ON z.IDZAMOWIENIA=o.IDZAMOWIENIA
WHERE EXTRACT(YEAR FROM z.DATAZAMOWIENIA)=1997
ORDER BY 1,2

--- FUNCKJE RAPORTUJACE

--1. Aktualnie prowadzimy analizy sprzedazy naszych produktow w podziale na klientow.
--  Potrzebujemy zestawienia ukazujacego klienta, date zamowienia, kwote tego zamowienia za lata 96-98.
---  Dodatkowo w tabeli ma sie pojawic pole kazujace sumaryczne saldo w calym okresie.

SELECT k.NAZWAFIRMY, z.DATAZAMOWIENIA, o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT)AS kwota,
sum( o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT))over(PARTITION BY k.NAZWAFIRMY ORDER BY z.DATAZAMOWIENIA ) saldo
FROM KLIENCI k INNER JOIN ZAMOWIENIA z ON k.IDKLIENTA=z.IDKLIENTA INNER JOIN OPISY_ZAMOWIEN o ON z.IDZAMOWIENIA=o.IDZAMOWIENIA
WHERE EXTRACT(YEAR FROM z.DATAZAMOWIENIA) BETWEEN 1996 AND 1998
ORDER BY 1,2

-- --2. Analizujac zadanie pierwsz dokonaj takiej zmiany,
-- aby zamiast klienta pojawila sie informacji o naszym pracowniku i wartosc sprzedanych produktow tylko w roku 1998
SELECT p.NAZWISKO, z.DATAZAMOWIENIA, o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT) AS sprzedaz,
sum(o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT))OVER(PARTITION BY p.NAZWISKO ORDER BY z.DATAZAMOWIENIA)wartosc
FROM PRACOWNICY p INNER JOIN ZAMOWIENIA z ON p.IDPRACOWNIKA=z.IDPRACOWNIKA INNER JOIN OPISY_ZAMOWIEN o ON z.IDZAMOWIENIA=o.IDZAMOWIENIA
WHERE EXTRACT(YEAR FROM z.DATAZAMOWIENIA)=1998

-- 3. Przygotuj zestawienie ukazujace ilosc transakcji jakie zrealizowal pracownik w danym miesiacu w roku 1997
-- oraz odchylenie od sredniej ilosci transakcji z calego roku. W raporcie ma pojawic sie nazwisko pracownika, miesiac, ilosc transakcji z tego miesiaca oraz kolumna ukazujaca sume wszystkich transakcji z calego roku podzielona prze 12.
SELECT p.NAZWISKO, EXTRACT(MONTH FROM z.DATAZAMOWIENIA) AS miesiac, COUNT(z.IDZAMOWIENIA) AS ilosc,
sum(COUNT(z.IDZAMOWIENIA))OVER(PARTITION BY p.NAZWISKO ORDER BY EXTRACT(MONTH FROM z.DATAZAMOWIENIA)) sumka,
sum(COUNT(z.IDZAMOWIENIA))OVER(PARTITION BY p.NAZWISKO ORDER BY EXTRACT(MONTH FROM z.DATAZAMOWIENIA))/12 wtf,
( sum(COUNT(z.IDZAMOWIENIA))OVER(PARTITION BY p.NAZWISKO ORDER BY EXTRACT(MONTH FROM z.DATAZAMOWIENIA)) - sum(COUNT(z.IDZAMOWIENIA))OVER(PARTITION BY p.NAZWISKO ORDER BY EXTRACT(MONTH FROM z.DATAZAMOWIENIA))/12 ) AS wtf2
FROM PRACOWNICY p INNER JOIN ZAMOWIENIA z ON p.IDPRACOWNIKA=z.IDPRACOWNIKA INNER JOIN OPISY_ZAMOWIEN o ON z.IDZAMOWIENIA=o.IDZAMOWIENIA
WHERE EXTRACT(YEAR FROM z.DATAZAMOWIENIA)=1997
GROUP BY GROUPING SETS((p.NAZWISKO,EXTRACT(MONTH FROM z.DATAZAMOWIENIA)), ())

-- LAG/LEAD

--1. Pokaz  zestawienie sprzedazy produktow z podzialem na firmy, ukazujace okresy w jakich saldo sprzedazy dla poszczegolnych klientow nie uleglo zmianie
SELECT * FROM (SELECT k.NAZWAFIRMY, z.DATAZAMOWIENIA AS od_dnia,
lead(z.DATAZAMOWIENIA,1)OVER(PARTITION BY k.NAZWAFIRMY ORDER BY z.DATAZAMOWIENIA) do_dnia,
sum(o.CENAJEDNOSTKOWA*o.ILOSC)over(PARTITION BY k.NAZWAFIRMY ORDER BY z.DATAZAMOWIENIA)saldo
FROM OPISY_ZAMOWIEN o  INNER JOIN ZAMOWIENIA z ON o.IDZAMOWIENIA=z.IDZAMOWIENIA INNER JOIN KLIENCI k ON z.IDKLIENTA=k.IDKLIENTA
ORDER BY z.DATAZAMOWIENIA)zestawienie
WHERE zestawienie.od_dnia<>zestawienie.do_dnia

-- FIRST/LAST

-- dla kazdego roku znajdz dni w ktorych dany pracownik dokonał najwiekszej transakcji
SELECT p.IMIE, p.NAZWISKO, to_char(z.DATAZAMOWIENIA, 'yyyy') rok,
max(z.DATAZAMOWIENIA)keep (DENSE_RANK FIRST ORDER BY (o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT)) desc) najlepszy_dzien,
ROUND(max(o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT)),2) AS najwyzsza_wartosc
FROM PRACOWNICY p INNER JOIN ZAMOWIENIA z ON p.IDPRACOWNIKA=z.IDPRACOWNIKA INNER JOIN OPISY_ZAMOWIEN o on z.IDZAMOWIENIA=o.IDZAMOWIENIA
GROUP BY IMIE, NAZWISKO, to_char(z.DATAZAMOWIENIA, 'yyyy')
order BY 1,2,3,4

-- Funkcje rankingu hipotetycznego

-- 1 Podczas procesu rekrutacji zapytano przyszlego pracownika jakiego wynagrodzenia sie spodziewa. Odpowiedzial iz 3900$. Okresl na ktorym miejscu w rankingu plac w firmie by sie znalazl jezeli otrzymalby taka pensje

SELECT RANK(3900) WITHIN GROUP (ORDER BY PLACA desc) RANK FROM PRACOWNICY

-- 2 Jestesmy ciekawi na jakiej pozycji w rankingu klientow znajdzie sie firma ktĂłra dokonala 31 transakcji kupna
 SELECT RANK(31) WITHIN GROUP (ORDER BY COUNT(IDZAMOWIENIA)DESC)rank FROM ZAMOWIENIA
 GROUP BY IDKLIENTA

-- 3 Na jakiej pozycji znajdzie sie pracownik ktorego wartosc sprzedazy produktow wyniosla 1000

 SELECT RANK(1000) WITHIN GROUP (ORDER BY suma DESC)rank FROM
 (SELECT p.NAZWISKO, sum(o.ILOSC*o.CENAJEDNOSTKOWA) AS suma
 FROM PRACOWNICY p INNER JOIN ZAMOWIENIA z ON p.IDPRACOWNIKA=z.IDPRACOWNIKA INNER JOIN OPISY_ZAMOWIEN o ON z.IDZAMOWIENIA=o.IDZAMOWIENIA
 GROUP BY p.NAZWISKO
 ORDER BY suma desc)

 -- WIDTH BUCKET

 -- 1 dla naszych klientów przewidzieliśmy rabaty na zakupy produktów. Chcemy aby rabaty naliczały si na bazie okresów minionych.System ma podzielić ilość zlecać na każcego klienta na 4 równoliczne grupy z przedziału 0-20 oraz ostatnia grupa ma stanowić klientów których ilość zleceń przekroczyła 20

 select NAZWAFIRMY, COUNT(*) AS ilosc_tran, WIDTH_BUCKET(COUNT(*),0,20,4)*0.1 FROM KLIENCI k INNER JOIN ZAMOWIENIA z ON k.IDKLIENTA=z.IDKLIENTA
 GROUP BY NAZWAFIRMY
 ORDER BY 3

 -- 2 nasi pracownicy zasluzyli na premie. Sprawdz w bazie ilosc zlecen jakie zrealizowali w roku 1997 i podziel pracownikĂłw na 3 grupy. Za kazdy przedzal wyzej pracownik ma otrzymac 500 zl premii.
 SELECT NAZWISKO, COUNT(z.IDZAMOWIENIA) AS ilosc, WIDTH_BUCKET(COUNT(z.IDZAMOWIENIA),0,200,3)AS  grupa,
 WIDTH_BUCKET(COUNT(z.IDZAMOWIENIA),0,200,3)*500 AS premia
 FROM PRACOWNICY p INNER JOIN ZAMOWIENIA z ON p.IDPRACOWNIKA=z.IDPRACOWNIKA INNER JOIN OPISY_ZAMOWIEN o ON z.IDZAMOWIENIA=o.IDZAMOWIENIA
 WHERE EXTRACT(YEAR FROM z.DATAZAMOWIENIA)=1997
 GROUP BY NAZWISKO

 -- 3 przygotuj identyczny raport jak w zad.2 jednak w tym zadaniu zastosuj nie ilosc zlecen, a ich wartosc

 SELECT p.NAZWISKO, sum(o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT))AS wartosc, WIDTH_BUCKET(sum(o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT)),0,100000,3) AS grupa,
 WIDTH_BUCKET(sum(o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT)),0,100000,3)*500 AS premia
 FROM PRACOWNICY p INNER JOIN ZAMOWIENIA z ON p.IDPRACOWNIKA=z.IDPRACOWNIKA INNER JOIN OPISY_ZAMOWIEN o ON z.IDZAMOWIENIA=o.IDZAMOWIENIA
 WHERE EXTRACT(YEAR FROM z.DATAZAMOWIENIA)=1997
 GROUP BY p.NAZWISKO

 -- przygotuj kod sumujÄ…cy premie z zadan 2 i 3 oraz przygotuj zestawienie zsumowanÄ… premi jakÄ… uzyska pracownik za rok 1997
  SELECT p.NAZWISKO, sum(o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT))AS wartosc, WIDTH_BUCKET(sum(o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT)),0,100000,3) AS grupa_za_T,
 WIDTH_BUCKET(sum(o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT)),0,100000,3)*500 AS premia_za_wartosc_trans,
 WIDTH_BUCKET(COUNT(z.IDZAMOWIENIA),0,200,3)AS  grupa_za_ilosc_zam,
 WIDTH_BUCKET(COUNT(z.IDZAMOWIENIA),0,200,3)*500 AS premia_za_ilosc,
 WIDTH_BUCKET(COUNT(z.IDZAMOWIENIA),0,200,3)*500+  WIDTH_BUCKET(sum(o.CENAJEDNOSTKOWA*o.ILOSC*(1-o.RABAT)),0,100000,3)*500 AS CA�KOWITA_PREMIA
 FROM PRACOWNICY p INNER JOIN ZAMOWIENIA z ON p.IDPRACOWNIKA=z.IDPRACOWNIKA INNER JOIN OPISY_ZAMOWIEN o ON z.IDZAMOWIENIA=o.IDZAMOWIENIA
 WHERE EXTRACT(YEAR FROM z.DATAZAMOWIENIA)=1997
 GROUP BY p.NAZWISKO