DROP VIEW wszyscy_pracownicy;

DROP TABLE pracownicy;

DROP TABLE prac_archiw;

DROP TABLE stanowiska;

DROP TABLE dzialy;

CREATE TABLE dzialy (

 id_dzialu     NUMBER(2),

 nazwa         VARCHAR2(15),

 siedziba      VARCHAR2(15),

 CONSTRAINT dzialy_primary_key PRIMARY KEY (id_dzialu)

);

CREATE TABLE stanowiska (

 stanowisko    VARCHAR2(18),

 placa_min     NUMBER(7,2), 

 placa_max     NUMBER(7,2), 

 CONSTRAINT stan_primary_key PRIMARY KEY (stanowisko)

);

CREATE TABLE pracownicy (

 nr_akt        NUMBER(4),

 nazwisko      VARCHAR2(20),

 stanowisko    VARCHAR2(18),

 kierownik     NUMBER(4) CONSTRAINT prac_self_key REFERENCES pracownicy (nr_akt),

 data_zatr     DATE,

 data_zwol     DATE,

 placa         NUMBER(7,2), 

 dod_funkcyjny NUMBER(7,2),

 prowizja      NUMBER(7,2),

 id_dzialu     NUMBER(2),

 CONSTRAINT prac_primary_key PRIMARY KEY (nr_akt),

 CONSTRAINT prac_foreign_key FOREIGN KEY (id_dzialu) REFERENCES dzialy (id_dzialu)

);

CREATE TABLE prac_archiw (

 nr_akt        NUMBER(4),

 nazwisko      VARCHAR2(20),

 stanowisko    VARCHAR2(18),

 kierownik     NUMBER(4),

 data_zatr     DATE,

 data_zwol     DATE,

 placa         NUMBER(7,2), 

 dod_funkcyjny NUMBER(7,2) DEFAULT 0,

 prowizja      NUMBER(7,2) DEFAULT 0,

 id_dzialu     NUMBER(2)

 );

CREATE VIEW wszyscy_pracownicy

AS

 SELECT * FROM pracownicy

 UNION 

 SELECT * FROM prac_archiw;

INSERT INTO dzialy VALUES (10, 'ZARZĄD', 'CZĘSTOCHOWA');

INSERT INTO dzialy VALUES (20, 'PRODUKCJA', 'HONG KONG');

INSERT INTO dzialy VALUES (30, 'ZAOPATRZENIE', 'AMSTERDAM');

INSERT INTO dzialy VALUES (40, 'MARKETING', 'PARYŻ');

INSERT INTO dzialy VALUES (50, 'BADANIA', 'JAMAJKA');

INSERT INTO dzialy VALUES (60, 'KSIĘGOWOŚĆ',  'WIEDEŃ');

INSERT INTO dzialy VALUES (70, 'SPRZEDAŻ', 'CZĘSTOCHOWA');

INSERT INTO dzialy VALUES (80, 'MAGAZYN', 'KŁOBUCK');

INSERT INTO stanowiska VALUES ('PREZES',3000,7000);

INSERT INTO stanowiska VALUES ('CZŁONEK ZARZĄDU',2000,5000);

INSERT INTO stanowiska VALUES ('GŁÓWNY INFORMATYK',2000,4000);

INSERT INTO stanowiska VALUES ('DYREKTOR',2000,4000);

INSERT INTO stanowiska VALUES ('TECHNOLOG',1500,2500);

INSERT INTO stanowiska VALUES ('OPERATOR',1200,2000);

INSERT INTO stanowiska VALUES ('LABORANT', 1100, 1500);

INSERT INTO stanowiska VALUES ('INFORMATYK', 1200, 3000);

INSERT INTO stanowiska VALUES ('LOGISTYK', 1200, 2000);

INSERT INTO stanowiska VALUES ('MANAGER', 1500, 2500);

INSERT INTO stanowiska VALUES ('GRAFIK', 1200, 2200);

INSERT INTO stanowiska VALUES ('GŁÓWNY KSIĘGOWY', 2000,3500);

INSERT INTO stanowiska VALUES ('KSIĘGOWY', 1500, 2000);

INSERT INTO stanowiska VALUES ('REFERENT', 1200, 1500);

INSERT INTO stanowiska VALUES ('SPRZEDAWCA', 1000, 1200);

INSERT INTO stanowiska VALUES ('MANAGER GRUPY', 1800, 3000);

INSERT INTO stanowiska VALUES ('AKWIZYTOR', 900, 1200);

INSERT INTO stanowiska VALUES ('PRAKTYKANT', 500, 1000);

INSERT INTO pracownicy VALUES (8901, 'KRÓL', 'PREZES', NULL, To_date('1989/07/01','yyyy/mm/dd'), NULL, 5000, 4000, NULL, 10);

INSERT INTO pracownicy VALUES (8902, 'MICHALSKI', 'DYREKTOR', 8901, To_date('1989/08/15','yyyy/mm/dd'), NULL, 3000, 1500, NULL,  40);

INSERT INTO pracownicy VALUES (9121, 'KUKULSKI', 'DYREKTOR', 8901, To_date('1991/04/02','yyyy/mm/dd'), NULL,     3000, 1500, NULL, 30);

INSERT INTO pracownicy VALUES (9011, 'WIERZBICKI', 'INFORMATYK', 8902, To_date('1990/03/20','yyyy/mm/dd'), NULL, 2500, NULL, NULL, 40);

INSERT INTO pracownicy VALUES (9235, 'FIKUS', 'DYREKTOR', 8901, To_date('1994/09/16','yyyy/mm/dd'), NULL,  3200, 1500, NULL, 70);

INSERT INTO pracownicy VALUES (8904, 'SKALSKI', 'GŁÓWNY INFORMATYK', 8901, To_date('1989/08/18','yyyy/mm/dd'), NULL, 3800, 2500, NULL, 10);

INSERT INTO pracownicy VALUES (8910, 'MONIUSZKO', 'DYREKTOR', 8901, To_date('1989/09/01','yyyy/mm/dd'), NULL, 3100, 1500, NULL, 20);

INSERT INTO pracownicy VALUES (8911, 'WRZOSEK', 'OPERATOR', 8910, To_date('1989/11/10','yyyy/mm/dd'), NULL, 1200, NULL, NULL, 20);

INSERT INTO pracownicy VALUES (9411, 'LISIECKI', 'LABORANT', 8910, To_date('1994/09/10','yyyy/mm/dd'), NULL, 1300, NULL,  NULL, 20);

INSERT INTO pracownicy VALUES (8932, 'BRZÓZKA',       'GŁÓWNY KSIĘGOWY', 8901, To_date('1989/12/06','yyyy/mm/dd'), NULL, 3500, 2000, NULL, 60);

INSERT INTO pracownicy VALUES (8913, 'KOWALSKA', 'CZŁONEK ZARZĄDU', 8901, To_date('1989/11/15','yyyy/mm/dd'), NULL, 4500, 2000, NULL, 10);

INSERT INTO pracownicy VALUES (9010, 'WIŚNIEWSKA', 'GRAFIK',  8902, To_date('1990/02/12','yyyy/mm/dd'), NULL, 2200, NULL, NULL, 40);

INSERT INTO pracownicy VALUES (9025, 'MALIK', 'LOGISTYK', 9121, To_date('1990/06/01','yyyy/mm/dd'), NULL, 1300, NULL, NULL, 30);

INSERT INTO pracownicy VALUES (9332, 'PRUSIŃSKA', 'DYREKTOR', 8901, To_date('1997/07/15','yyyy/mm/dd'), NULL, 3500, 1500, NULL, 50);

INSERT INTO pracownicy VALUES (9027, 'GADUŁA', 'LOGISTYK', 9121, To_date('1990/06/20','yyyy/mm/dd'), NULL, 1200, NULL, NULL, 30);

INSERT INTO pracownicy VALUES (9028, 'LESZCZYŃSKI', 'SPRZEDAWCA', 9235, To_date('1990/08/10','yyyy/mm/dd'), NULL, 1000, NULL, 6000, 70);

INSERT INTO pracownicy VALUES (9102, 'KOWALCZYK', 'AKWIZYTOR',        9235, To_date('1991/01/10','yyyy/mm/dd'), NULL, 900, NULL, 12000, 70);

INSERT INTO pracownicy VALUES (9122, 'WÓJCIK', 'KSIĘGOWY', 8932, To_date('1991/05/10','yyyy/mm/dd'), NULL, 1800, NULL, NULL, 60);

INSERT INTO pracownicy VALUES (9130, 'LELIWA', 'MANAGER', 8902, To_date('1991/06/01','yyyy/mm/dd'), NULL, 2200, NULL, NULL, 40);

INSERT INTO pracownicy VALUES (8920, 'WÓJCIK', 'TECHNOLOG', 8910, To_date('1989/12/01','yyyy/mm/dd'), NULL, 1500, NULL, NULL, 20);

INSERT INTO pracownicy VALUES (9337, 'MAZUR', 'INFORMATYK', 8932, To_date('1993/07/12','yyyy/mm/dd'), NULL, 2200, NULL, NULL, 60);

INSERT INTO pracownicy VALUES (9345, 'SZCZĘSNY', 'OPERATOR', 8910, To_date('1993/10/05','yyyy/mm/dd'), NULL, 1300, NULL, NULL, 20);

INSERT INTO pracownicy VALUES (9403, 'BIELECKA', 'REFERENT', 8932, To_date('1994/03/01','yyyy/mm/dd'), NULL, 1400, NULL, NULL, 60);

INSERT INTO pracownicy VALUES (9120, 'RYBAK', 'TECHNOLOG', 8910, To_date('1991/01/20','yyyy/mm/dd'), NULL, 1600, NULL, NULL, 20);

INSERT INTO pracownicy VALUES (9641, 'MAŁYSZ', 'MANAGER GRUPY', 9235,To_date('1996/12/05','yyyy/mm/dd'), NULL, 1800, 500, 10000, 70);

INSERT INTO pracownicy VALUES (9731, 'NAWROCKI', 'LABORANT', 9332, To_date('1997/04/01','yyyy/mm/dd'), NULL, 1400, NULL, NULL, 50);

INSERT INTO pracownicy VALUES (9780, 'SAMOSIŃSKI', 'PRAKTYKANT', 9332, To_date('2006/04/01','yyyy/mm/dd'), NULL, 700, NULL, NULL, NULL);

INSERT INTO prac_archiw VALUES (9641, 'MAŁYSZ', 'SPRZEDAWCA', 9235, To_date('1992/12/05','yyyy/mm/dd'), To_date('1995/10/30','yyyy/mm/dd'), 1800, NULL, 5000, 70);

INSERT INTO prac_archiw VALUES (9332, 'PRUSIŃSKA', 'LABORANT', 8910, To_date('1993/03/15','yyyy/mm/dd'), To_date('1994/12/01','yyyy/mm/dd'), 1200, NULL, NULL, 20);

INSERT INTO prac_archiw VALUES (8912, 'SZCZERBA', 'OPERATOR', 8910, To_date('1989/11/15','yyyy/mm/dd'), To_date('1995/06/30','yyyy/mm/dd'), 1300, NULL, NULL, 20);

INSERT INTO prac_archiw VALUES (9350, 'KWIATKOWSKA', 'REFERENT', 8932, To_date('1994/01/10','yyyy/mm/dd'), To_date('1998/03/31','yyyy/mm/dd'), 1100, NULL, NULL, 60);

INSERT INTO prac_archiw VALUES (9440, 'BRodECKA', 'KSIĘGOWY', 8932, To_date('1994/10/10','yyyy/mm/dd'), To_date('1999/09/30','yyyy/mm/dd'), 1600, NULL, NULL, 60);

INSERT INTO prac_archiw VALUES (9153, 'MONETA',       'DYREKTOR', 8901, To_date('1992/04/10','yyyy/mm/dd'), To_date('1997/06/30','yyyy/mm/dd'), 3200, 1500, NULL, 50);

DESC wszyscy_pracownicy;

DESC pracownicy;

DESC prac_archiw;

DESC stanowiska;

DESC dzialy;

SELECT * FROM wszyscy_pracownicy;

SELECT * FROM stanowiska;

SELECT * FROM dzialy;

 

----------------------------------------------------------------------

--               LABORATORIUM 5

----------------------------------------------------------------------

--------------ROZSZERZENIE GRUPOWANIA----------------------------------

SELECT rok, gr_dziekan, Count(*)

  FROM student

  WHERE kierunek LIKE 'INFORM%' AND rok IS NOT NULL 

        AND gr_dziekan IS NOT NULL

  GROUP BY Rollup(rok, gr_dziekan)

  ORDER BY 1,2,3;

SELECT rok, gr_dziekan, Count(*)

  FROM student

  WHERE kierunek LIKE 'INFORM%' AND rok IS NOT NULL 

        AND gr_dziekan IS NOT NULL

  GROUP BY Cube(rok, gr_dziekan)

  ORDER BY 1,2,3;

SELECT rok, gr_dziekan, Count(*)

  FROM student

  WHERE kierunek LIKE 'INFORM%' AND rok IS NOT NULL 

        AND gr_dziekan IS NOT NULL

  GROUP BY GROUPING SETS((rok, gr_dziekan),(rok),())

  ORDER BY 1,2,3;

SELECT rok, gr_dziekan, grouping(rok), grouping(gr_dziekan), Count(*)

  FROM student

  WHERE kierunek LIKE 'INFORM%' AND rok IS NOT NULL 

        AND gr_dziekan IS NOT NULL

  GROUP BY GROUPING SETS((rok, gr_dziekan),(rok),())

  ORDER BY 1,2,3;

SELECT typ, model, marka, Count(*)

  FROM pojazdy

  WHERE typ IS NOT NULL AND model IS NOT NULL AND marka IS NOT NULL

  GROUP BY GROUPING SETS((typ, model, marka),(typ),())

  HAVING Count(*) > 30;

SELECT decode(grouping(rok),0,To_char(rok),'wszystkie lata'), 

       decode(grouping(gr_dziekan),0,To_char(gr_dziekan),'wszystkie grupy'), Count(*)

  FROM student

  WHERE kierunek LIKE 'INFORM%' AND rok IS NOT NULL 

        AND gr_dziekan IS NOT NULL

  GROUP BY GROUPING SETS((rok, gr_dziekan),(rok),())

  ORDER BY 1,2,3;

---------------------------ZLACZENIA------------------------------------

DESC pracownicy;

DESC dzialy;

DESC stanowiska;

SELECT * FROM pracownicy;

SELECT * FROM dzialy;

SELECT * FROM stanowiska;

-----------------------ILOCZYN KARTEZJANSKI-----------------------------

SELECT * FROM pracownicy, dzialy;

SELECT * FROM pracownicy CROSS JOIN dzialy;

-----------------------ZLACZENIA ROWNOSCIOWE----------------------------

SELECT * FROM pracownicy p, dzialy z WHERE p.id_dzialu=z.id_dzialu;

SELECT * FROM pracownicy p JOIN dzialy z ON p.id_dzialu=z.id_dzialu;

SELECT * FROM pracownicy JOIN dzialy USING(id_dzialu);

SELECT nazwisko, nazwa, siedziba 

  FROM pracownicy p, dzialy z 

  WHERE p.id_dzialu=z.id_dzialu;

SELECT nazwa, Count(*) 

  FROM pracownicy p, dzialy z 

  WHERE p.id_dzialu=z.id_dzialu

  GROUP BY nazwa;

SELECT p.id_dzialu, nazwa, Sum(placa+Nvl(prowizja,0)+Nvl(dod_funkcyjny,0)) placa

  FROM pracownicy p, dzialy z 

  WHERE p.id_dzialu=z.id_dzialu

  GROUP BY p.id_dzialu,nazwa;

-----------------------ZLACZENIE NATURALNE------------------------------

SELECT * FROM pracownicy NATURAL JOIN dzialy;

SELECT nazwa,Min(placa)

  FROM pracownicy NATURAL JOIN dzialy

  GROUP BY nazwa;

-----------------------ZLACZENIA ZEWNETRZNE-----------------------------

SELECT nazwisko, nazwa, stanowisko 

  FROM pracownicy LEFT JOIN dzialy USING(id_dzialu);

SELECT nazwisko, nazwa, siedziba 

  FROM pracownicy RIGHT JOIN dzialy USING(id_dzialu);

SELECT nazwisko, nazwa, siedziba

  FROM pracownicy FULL JOIN dzialy USING(id_dzialu);

-----------------------ZLACZENIA NIEROWNOSCIOWE-------------------------

SELECT nazwisko||' na etacie '||p.stanowisko||' zarabia '||placa||

       ' tyle samo co '||s.stanowisko||' ('||placa_min||'-'||placa_max||')'

       wynagrodzenie

  FROM pracownicy p JOIN stanowiska s

    ON placa BETWEEN placa_min AND placa_max

  WHERE s.stanowisko <> p.stanowisko

  ORDER BY 1;

-----------------------SAMOZLACZENIE------------------------------------

SELECT p.nazwisko pracownik, k.nazwisko kierownik

  FROM pracownicy p, pracownicy k

  WHERE p.kierownik=k.nr_akt

  ORDER BY 2;

SELECT p.nazwisko pracownik, k.nazwisko kierownik

  FROM pracownicy p LEFT JOIN pracownicy k

  ON p.kierownik=k.nr_akt

  ORDER BY 2;

-----------------------SUMA---------------------------------------------

SELECT nazwisko, placa, 'Powyzej sredniej krajowej' zakres

  FROM pracownicy

  WHERE placa>2380.29

UNION

SELECT nazwisko, placa, 'Srednia krajowa' zakres

  FROM pracownicy

  WHERE placa=2380.29

UNION

SELECT nazwisko, placa, 'Ponizej sredniej krajowej' zakres

  FROM pracownicy

  WHERE placa<2380.29

ORDER BY 2 DESC;

----------------------------------------------------------------------

--               LABORATORIUM 6

----------------------------------------------------------------------

--Wybierz nazwisko i place pracownika, oraz dodaj komentarz, czy pracownik 

--zarabia powyzej, ponizej czy rowno z srednia krajowa.

SELECT nazwisko, placa, 

  CASE

    WHEN placa>2380.29 THEN 'Powyzej sredniej krajowej'

    WHEN placa=2380.29 THEN 'Srednia krajowa'

    WHEN placa<2380.29 THEN 'Ponizej sredniej krajowej'

  END zakres

  FROM pracownicy

  ORDER BY 2 DESC;

---------------------ROZNICA----------------------------------------------

--Podaj identyfikator dzialu, ktory nie zatrudnia pracownikow.

SELECT id_dzialu FROM dzialy

MINUS

SELECT id_dzialu FROM pracownicy

--------------------PRZECIECIE--------------------------------------------

--Podaj wspolne stanowiska dla dzialu 20 i 30.

SELECT stanowisko FROM pracownicy WHERE id_dzialu=20

INTERSECT

SELECT stanowisko FROM pracownicy WHERE id_dzialu=30;

--------------------ZLACZENIA WIELU TABEL---------------------------------

--Podaj nazwiska, nazwe dzialu, place, oraz minimalna i maksymalna place

--jaka moze zarobic pracownik na swoim stanowisku

SELECT nazwisko, nazwa, placa, placa_min, placa_max

  FROM pracownicy p, dzialy d, stanowiska s

  WHERE p.id_dzialu = d.id_dzialu AND p.stanowisko = s.stanowisko

        AND placa_min > 1500 AND placa_max > 3500;

SELECT nazwisko, nazwa, placa, placa_min, placa_max

  FROM (pracownicy p JOIN dzialy d ON p.id_dzialu = d.id_dzialu)

       JOIN stanowiska s ON p.stanowisko = s.stanowisko

  WHERE placa_min > 1500 AND placa_max > 3500;      

SELECT nazwisko, nazwa, placa, placa_min, placa_max

  FROM (pracownicy p JOIN dzialy d USING(id_dzialu))

       JOIN stanowiska USING(stanowisko)

  WHERE placa_min > 1500 AND placa_max > 3500;       

-------------------PODZAPYTANIA-------------------------------------------

--Podaj nazwisko, stanowisko i place pracownika, ktory zarabia najmniej

SELECT nazwisko, stanowisko, placa

  FROM pracownicy WHERE placa=(

    SELECT Min(placa) FROM pracownicy);

--Podaj nazwiska, stanowiska i place pracownikow, ktorzy zarabiaja ponizej

--sredniej w firmie

SELECT nazwisko, stanowisko, placa FROM pracownicy

  WHERE placa < (

    SELECT Avg(placa) FROM pracownicy);

--Podaj nazwiska i place pracownikow, ktorzy zarabiaja wiecej niz ktorykolwiek

--pracownik z zespolu 30

SELECT nazwisko, placa FROM pracownicy

  WHERE placa>ANY (

    SELECT placa FROM pracownicy WHERE id_dzialu=30);

--Podaj nazwiska i place pracownikow, ktorzy zarabiaja wiecej niz wszyscy

--pracownicy z zespolu 30

SELECT nazwisko, placa FROM pracownicy

  WHERE placa>ALL (

    SELECT placa FROM pracownicy WHERE id_dzialu=30);

--Wypisz nazwiska, imiona i rok studentów, ktorych imie ma srednia dlugosc

SELECT nazwisko, imiona, rok FROM student

  WHERE imiona IN (

    SELECT DISTINCT imiona FROM student

    WHERE Length(imiona)=(

      SELECT Trunc(Avg(Length(imiona))) FROM student));

--Podaj identyfikator i nazwe zespolu, ktory wydaje najwiecej na place

SELECT id_dzialu, nazwa FROM dzialy

  WHERE id_dzialu = (

    SELECT id_dzialu FROM pracownicy

    GROUP BY id_dzialu

    HAVING Sum(placa + Nvl(prowizja,0) + Nvl(dod_funkcyjny,0)) = (

      SELECT Max(Sum(placa + Nvl(prowizja,0) + Nvl(dod_funkcyjny,0))) 

        FROM pracownicy

        GROUP BY id_dzialu));

--Wypisz dane osob z grupy studenta o nazwisku podanym pierwszym parametrem. 

SELECT * FROM student 

  WHERE (rok, gr_dziekan, kierunek) IN (

    SELECT rok, gr_dziekan, kierunek FROM student

    WHERE nazwisko LIKE '&1');      

 

 

-----------------PODZAPYTANIA SKORELOWANE------------------------------------

--Wypisz nazwiska studentow, ktore sie powtarzaja

SELECT DISTINCT nazwisko FROM student p WHERE 1<(

  SELECT Count(*) FROM student

    WHERE nazwisko=p.nazwisko)

  ORDER BY 1;

SELECT nazwisko FROM student

  GROUP BY nazwisko

  HAVING Count(*)>1

  ORDER BY 1;

--Podaj identyfikator i nazwe dzialu, ktory nie zatrudnia pracownikow

SELECT DISTINCT id_dzialu, nazwa FROM dzialy z

  WHERE NOT EXISTS (

    SELECT 'cos' FROM pracownicy WHERE z.id_dzialu=id_dzialu);

-- Wypisz wszystkich pracownikow oprocz tego, ktory zarabia najmniej    

SELECT nazwisko, placa FROM pracownicy ORDER BY 2;

SELECT nazwisko, placa FROM pracownicy p

  WHERE 1<(

    SELECT Count(DISTINCT placa) FROM pracownicy

      WHERE placa<=p.placa)

  ORDER BY 2;

--Wypisz identyfikator dzialu, nazwisko i place pracownika, ktorzy zarabiaja 

--wiecej niz wynosi srednia placa w ich dziale.

SELECT id_dzialu, Avg(placa) FROM pracownicy

    GROUP BY id_dzialu;

SELECT id_dzialu, nazwisko, placa FROM pracownicy n

  WHERE placa>(

  SELECT Avg(placa) FROM pracownicy p

    WHERE p.id_dzialu = n.id_dzialu);  

SELECT n.id_dzialu, nazwisko, placa, srednia_placa 

  FROM pracownicy n, 

      (SELECT id_dzialu, Avg(placa) srednia_placa 

         FROM pracownicy GROUP BY id_dzialu) g

  WHERE g.id_dzialu = n.id_dzialu AND placa>(

  SELECT Avg(placa) FROM pracownicy p

    WHERE p.id_dzialu = n.id_dzialu);

-----------------------MIX---------------------------------------------------

--Wypisz identyfikatory i nazwy dzialow oraz ich srednia place

SELECT id_dzialu, nazwa, srednia_placa

  FROM dzialy JOIN (SELECT id_dzialu, Avg(placa) srednia_placa 

    FROM pracownicy GROUP BY id_dzialu) USING(id_dzialu);

--Od jakiej litery najczesciej rozpoczynaja sie imiona studentow.

SELECT Substr(imiona,1,1), Count(*) FROM student

  GROUP BY Substr(imiona,1,1)

    HAVING Count(*) = (SELECT Max(Count(*)) FROM student

      GROUP BY Substr(imiona,1,1));

--Ktory rok jest najliczniejszy pod wzgledem kobiet.

SELECT rok, Count(*) FROM student

  WHERE imiona LIKE '%A' OR imiona LIKE '%E'

  GROUP BY rok

    HAVING Count(*) = (SELECT Max(Count(*)) FROM student

      WHERE imiona LIKE '%A' OR imiona LIKE '%E'

      GROUP BY rok);      

--Wypisz nazwe dzialu oraz ilu zatrudnia pracownikow, uwzgledniajac takze

--dzial nie zatrudniajacy pracownikow (prz nim podaj wartosc 0)     

SELECT nazwa, Count(*)

  FROM pracownicy RIGHT JOIN dzialy USING(id_dzialu)

  WHERE nazwisko IS NOT NULL

  GROUP BY nazwa

UNION

SELECT nazwa, 0

  FROM pracownicy RIGHT JOIN dzialy USING(id_dzialu)

  WHERE nazwisko IS NULL;

SELECT nazwa, Count(nazwisko)

  FROM pracownicy RIGHT JOIN dzialy USING(id_dzialu)

  GROUP BY nazwa;

--W ktorym kwartale zatrudniono najwiecej pracownikow

SELECT To_char(data_zatr,'Q') kwartal, Count(*) FROM pracownicy

  GROUP BY To_char(data_zatr,'Q')

  HAVING Count(*) = (SELECT Max(Count(*)) FROM pracownicy

  GROUP BY To_char(data_zatr,'Q'));

--Wypisz nazwiska, stanowiska, place, staz pracy oraz nazwe dzialu, pracownikow

--ktorzy w danym dziale zarabiaja najmniej

SELECT nazwa, id_dzialu, Min(placa) FROM pracownicy NATURAL JOIN DZIALY

  GROUP BY nazwa,id_dzialu

  ORDER BY id_dzialu;

--(bledne wyniki)

SELECT nazwisko, stanowisko, placa,

  Trunc(Months_between(Sysdate, data_zatr)/12) AS "sta? pracy", nazwa 

  FROM pracownicy JOIN dzialy USING(id_dzialu)

  WHERE placa IN (SELECT Min(placa) FROM pracownicy 

  GROUP BY id_dzialu)

  ORDER BY id_dzialu;

--(poprawne wyniki)

SELECT nazwisko, stanowisko, placa,

  Trunc(Months_between(Sysdate, data_zatr)/12) AS "sta? pracy", nazwa 

  FROM pracownicy p JOIN dzialy d ON p.id_dzialu = d.id_dzialu

  WHERE placa = (SELECT Min(placa) FROM pracownicy 

    WHERE p.id_dzialu = id_dzialu)

  ORDER BY p.id_dzialu;

--Jakiego koloru auta osobowe najchetniej kupuja wlasciciele o imieniu Marianna 

--(kolor nie moze byc nieustalony)

SELECT kolor, Count(*) FROM pojazdy  JOIN kierowcy 

  ON wlasciciel = id_kier

  WHERE imie LIKE 'MARIANNA' AND kolor NOT LIKE 'NIEUSTALONY'

    AND typ like 'SAM.OSOBOWY'

  GROUP BY kolor

  HAVING Count(*) = (SELECT Max(Count(*)) FROM pojazdy  JOIN kierowcy 

    ON wlasciciel = id_kier

    WHERE imie LIKE 'MARIANNA' AND kolor NOT LIKE 'NIEUSTALONY'

    AND typ like 'SAM.OSOBOWY'

    GROUP BY kolor);

 

-- Prosze sprawdzic zawartosc tabeli cat i usunac zapisane w niej tablice.

SELECT * FROM cat;

DROP VIEW wszyscy_pracownicy;

DROP TABLE pracownicy;

DROP TABLE prac_archiw;

DROP TABLE taryfikator;

DROP TABLE stanowiska;

DROP TABLE dzialy;

-- Prosze utworzyc tablice pracownicy z polami nr_akt(liczba czterocyfrowa), nazwisko (pietnastoznakowe), 

stanowisko (osiemnastoznakowe), kierownik (liczba czterocyfrowa), data_zatr, placa (siedmiocyfrowa, 

z dwoma cyframi po przecinku), podobnie prowizja, zainteresowania (osiemnastoznakowe), plec (jeden znak),

id_dzialu (liczba dwucyfrowa).

CREATE TABLE pracownicy (

 nr_akt        NUMBER(4),

 nazwisko      VARCHAR2(15),

 stanowisko    VARCHAR2(18),

 kierownik     NUMBER(4),

 data_zatr     DATE,

 placa         NUMBER(7,2), 

 prowizja      NUMBER(7,2),

 zainteresownaia          VARCHAR2(18),

 plec           CHAR(1),

 id_dzialu     NUMBER(2)

 );

-- Prosze nalozyc na pole nr_akt ograniczenie, nie pozwalające na wpisanie tam wartości pustej.

ALTER TABLE pracownicy

  MODIFY (nr_akt NUMBER(4) CONSTRAINT prac_na_nn  NOT NULL);

-- Prosze nalozyc na pole data_zatr wartosc domyslna w postaci daty systemowej.

ALTER TABLE pracownicy

  MODIFY (data_zatr DATE DEFAULT Sysdate);

-- Prosze nalozyc na pole plec ograniczenie, tak by pole to przyjmowalo jedynie wartosci 'k' lub 'm';

ALTER TABLE pracownicy

  ADD CONSTRAINT prac_pl_ch CHECK (plec IN('k','m'));

-- Usun tabele pracownicy;

DROP TABLE pracownicy;

-- Utworz ponownie tablice pracownicy, dodajac ograniczenia juz w definicji. Dodatkowo utworz ograniczenie

-- na pole placa, tak by miescila miedzy 900 a 10000 zlotych, ograniczenie to podaj na koncu definicji tabeli.

CREATE TABLE pracownicy (

 nr_akt        NUMBER(4) 

   CONSTRAINT prac_na_nn  NOT NULL,

 nazwisko      VARCHAR2(15),

 stanowisko    VARCHAR2(18),

 kierownik     NUMBER(4),

 data_zatr     DATE 

   DEFAULT Sysdate,

 placa         NUMBER(7,2), 

 prowizja      NUMBER(7,2),

 zainteresowania    VARCHAR2(18),

 plec           CHAR(1) 

   CONSTRAINT prac_pl_ch  CHECK (plec IN('k','m')),

 id_dzialu     NUMBER(2),

   CONSTRAINT prac_placa_ch  CHECK (placa >= 900 AND placa < 10000)

 );

DESC pracownicy

-- Wstaw do tablicy dwie krotki, w jednej pole data_zatr wypelnij wartoscia domyslna.

INSERT INTO pracownicy VALUES (8902, 'MICHALSKI', 'DYREKTOR', 8902, 

  To_date('89/08/15','yy/mm/dd'), 3000, NULL, NULL, 'm',40);

INSERT INTO pracownicy VALUES (8903, 'KOWALSKI', 'ASYSTENT', 8902, 

  Default, 1500, NULL, 'wędkarstwo', 'm',40);

SELECT * FROM pracownicy;

-- Prosze dodac do tabeli pracownicy pola  data_zwol, dod_funkcyjny (pole siedmiocyfrowe, 

-- z dwoma cyframi po przecinku).

ALTER TABLE pracownicy

  ADD (data_zwol DATE, dod_funkcyjny NUMBER(7,2));

-- Zmniejsz dlugosc pola nazwisko na 8 znakow. Czy zawsze jest to mozliwe?

ALTER TABLE pracownicy

  MODIFY nazwisko VARCHAR2(8);

-- Wydluz pole nazwisko do 20 znakow, zas zainteresowania do 30.

ALTER TABLE pracownicy

  MODIFY (nazwisko VARCHAR2(20),

          zainteresowania    VARCHAR2(30));

-- Zmien nazwe kolumny zainteresowania na hobby.       

ALTER TABLE pracownicy

  RENAME COLUMN zainteresowania TO hobby;

-- Usun kolumne hobby.

ALTER TABLE pracownicy

  DROP COLUMN hobby;

-- Usun kolumne plec.  

ALTER TABLE pracownicy

  DROP COLUMN plec;

-- By usunac obie kolumny naraz:

ALTER TABLE pracownicy

  DROP (hobby , plec);

-- Dodaj do tabeli pracownicy klucz glowny na polu nr_akt.  

ALTER TABLE pracownicy

  ADD CONSTRAINT prac_primary_key PRIMARY KEY (nr_akt);

-- Usun ograniczenie tyczace sie placy.

ALTER TABLE pracownicy

  DROP CONSTRAINT prac_placa_ch;

-- Usun zawartosc tabeli pracownicy;

DELETE FROM pracownicy;

-- Wstaw podane wartosci do tabeli.

INSERT INTO pracownicy VALUES (8901, 'KRÓL', 'PREZES', NULL,  To_date('89/07/01','yy/mm/dd'), 

  5000, 4000, 10,NULL,1000);

INSERT INTO pracownicy (nr_akt, nazwisko, stanowisko, kierownik, data_zatr, placa, id_dzialu) 

  VALUES (8902, 'MICHALSKI', 'DYREKTOR', 8901, To_date('89/08/15','yy/mm/dd'), 3000, 40);

SELECT * FROM pracownicy;

-- Utworz tabele dzialy z polami id_dzialu (liczba dwucyfrowa), nazwa (pietnastoznakowa), 

-- siedziba (pietnastoznakowa), naloz klucz glowny na pole id_dzialu.

CREATE TABLE dzialy (

 id_dzialu     NUMBER(2),

 nazwa         VARCHAR2(15),

 siedziba      VARCHAR2(15),

 CONSTRAINT dzialy_primary_key PRIMARY KEY (id_dzialu)

 );

-- Wstaw do tabeli dzialy podane wartosci.

INSERT INTO dzialy VALUES (10, 'ZARZAD', 'CZĘSTOCHOWA');

INSERT INTO dzialy VALUES (40, 'MARKETING', 'PARYŻ');

SELECT * FROM dzialy;

-- Dodaj to tabeli pracownicy klucz obcy laczacy ja z tabela dzialy za pomoca pol id_dzialu.

ALTER TABLE pracownicy

  ADD CONSTRAINT prac_iddz_fk FOREIGN KEY (id_dzialu) 

        REFERENCES dzialy (id_dzialu); 

-- Dodaj do tabel podane wiersze.

INSERT INTO pracownicy VALUES (9121, 'KUKULSKI', 'DYREKTOR', 8901, 

  To_date('91/04/02','yy/mm/dd'), 3000, 1500, 30,NULL,NULL);

INSERT INTO dzialy VALUES (30, 'ZAOPATRZENIE', 'AMSTERDAM');

INSERT INTO pracownicy VALUES (9121, 'KUKULSKI', 'DYREKTOR', 8901, 

  To_date('91/04/02','yy/mm/dd'), 3000, 1500, 30,NULL,NULL);

-- Usun z tabeli dzialy dzial 30.

DELETE FROM dzialy WHERE id_dzialu = 30;

-- Usun z tabeli pracownicy klucz obcy.

ALTER TABLE pracownicy

  DROP CONSTRAINT prac_iddz_fk; 

-- Utworz na nowo powiazanie miedzy tabelami z opcja ON DELETE CASCADE lub ON DELETE SET NULL.

ALTER TABLE pracownicy

  ADD CONSTRAINT prac_iddz_fk FOREIGN KEY (id_dzialu) 

        REFERENCES dzialy (id_dzialu) ON DELETE CASCADE; 

--ON DELETE SET NULL

-- Usun z tabeli dzialy dzial 30.

DELETE FROM dzialy WHERE id_dzialu = 30;

SELECT * FROM pracownicy;

SELECT * FROM dzialy;

 

 

-- Utwórz tabele pracownice, na bazie danych zwrotnych z zapytania wybierającego

-- wszystkie dane pracowników, których nazwiska kończą się na 'SKA'.

CREATE TABLE pracownice AS 

 SELECT * FROM pracownicy

   WHERE nazwisko LIKE '%SKA'

   ORDER BY nazwisko;

-- Usuń tabelę pracownicy

DROP TABLE pracownice;

-- Utworz tabele produkty z polami id(czterocyfrowe) - klucz główny, nazwa

-- (trzydziestoznakowe), ilosc(czterocyfrowe).

CREATE TABLE produkty(

  id NUMBER(4),

  nazwa VARCHAR2(30),

  ilosc NUMBER(4),

  CONSTRAINT prod_idt_pk PRIMARY KEY(id)

);

-- Utwórz tabelę sprzedaz z polami id_sprzedazy(czterocyfrowe) - klucz główny, 

-- id_towaru(czterocyfrowe) będące referencją do tablicy produkty (wykorzystaj 

-- ON DELETE CASCADE lub ON DELETE SET NULL), ilosc (czterocyfrowe), data.

CREATE TABLE sprzedaz (

  id_sprzedazy NUMBER(4),

  id_towaru NUMBER(4),

  ilosc NUMBER(4),

  data DATE DEFAULT Sysdate,

  CONSTRAINT sprzedaz_ids_pk PRIMARY KEY(id_sprzedazy),

  CONSTRAINT sprzedaz_idt_fk FOREIGN KEY(id_towaru)

    REFERENCES produkty(id) ON DELETE CASCADE

);

-- Wypełnij powyższe tabele danymi tak, by jeden z towarów w tabeli sprzedaz 

-- w ogóle nie był ujęty, a niektóre towary były sprzedane kilkakrotnie.

INSERT INTO produkty VALUES (1, 'banan', 200);

INSERT INTO produkty VALUES (2, 'cytryna', 300);

INSERT INTO produkty VALUES (3, 'kiwi', 100);

INSERT INTO produkty VALUES (4, 'jabłko', 500);

INSERT INTO sprzedaz VALUES (1, 2, 10, DEFAULT);

INSERT INTO sprzedaz VALUES (2, 2, 20, DEFAULT);

INSERT INTO sprzedaz VALUES (3, 1, 5, DEFAULT);

INSERT INTO sprzedaz VALUES (4, 3, 3, DEFAULT);

SELECT * FROM produkty;

SELECT * FROM sprzedaz;

--  Dokonaj aktualizacji danych w tabeli produkty na bazie sprzedazy.

UPDATE produkty p SET ilosc = ilosc-(

 SELECT Sum(ilosc) FROM sprzedaz WHERE id_towaru=p.id)

 WHERE id IN (SELECT DISTINCT id_towaru FROM sprzedaz);

SELECT * FROM produkty;

-- Usuń najpierw tabelę produkty, a potem tabelę sprzedaz.

DROP TABLE produkty CASCADE CONSTRAINTS;

DROP TABLE sprzedaz;

-- Zaladuj skrypt baza.sql i obejrzyj strukture i zawartosc tabel

DESC wszyscy_pracownicy;

DESC pracownicy;

DESC prac_archiw;

DESC stanowiska;

DESC dzialy;

DESC taryfikator;

SELECT * FROM pracownicy ORDER BY id_dzialu;

SELECT * FROM prac_archiw;

SELECT * FROM stanowiska;

SELECT * FROM dzialy;

SELECT * FROM taryfikator;

-- Proszę usunąć z tablicy pracownika o numerze 8902 (szefa).

DELETE FROM pracownicy WHERE nr_akt=8902;

-- Proszę umieścić pracownika 8902 w tabeli prac_archiw

INSERT INTO prac_archiw 

  SELECT * FROM pracownicy WHERE nr_akt=8902;

SELECT * FROM prac_archiw;

UPDATE prac_archiw SET data_zwol=To_date('06/12/30','yy/mm/dd') 

  WHERE nr_akt=8902;

SELECT * FROM prac_archiw;

-- Proszę dodać nowego pracownika.

INSERT INTO pracownicy VALUES 

  (8903, 'WITKOWSKI', 'DYREKTOR', 8901, 

  To_date('06/01/01','yy/mm/dd'), NULL, 3000, 1500, NULL, 40);

-- Zmienic szefa na nowego tym , ktorzy jako kierownika maja pracownika 

-- o numerze 8902

UPDATE pracownicy SET kierownik=8903

  WHERE kierownik=8902;

-- Proszę usunąć z tablicy pracownika o numerze 8902.

DELETE FROM pracownicy WHERE nr_akt=8902;

-- Dodaj do tablicy pracownicy w dziale 50 pracownika na stanowisku stażysty

INSERT INTO stanowiska VALUES (50, 'STAŻYSTA');

INSERT INTO pracownicy   

  (nr_akt,nazwisko,stanowisko,kierownik,

   data_zatr,data_zwol,placa,id_dzialu)

  VALUES (9732, 'KOWALSKI', 'STAŻYSTA', 9332, Sysdate, NULL, 1000, 50);

-- Zwiększ szefom dodatek funkcyjny o 100 złotych.

UPDATE pracownicy SET dod_funkcyjny=NVL(dod_funkcyjny,0)+100 

  WHERE nr_akt IN(

    SELECT DISTINCT kierownik FROM pracownicy WHERE kierownik IS NOT NULL);

-- Proszę zwiększyć płacę pracownikom pracującym dłużej niż 15 lat o 20% 

-- w stosunku do średniej w ich dziale.

UPDATE pracownicy p SET placa = placa +(

    SELECT Avg(placa)*0.2 FROM pracownicy

      WHERE id_dzialu=p.id_dzialu)

  WHERE months_between(sysdate,data_zatr) > (15*12);

SELECT * FROM pracownicy ORDER BY id_dzialu, placa, dod_funkcyjny;

-- Proszę usunąć pracownika nie pracującego w żadnym dziale

DELETE FROM pracownicy WHERE id_dzialu IS NULL;

 

-------------------------------PERSPEKTYWY----------------------------

-- Utworz perspektywy studentki i studenci na bazie tabeli student.

CREATE OR REPLACE VIEW studentki

 AS 

 SELECT * FROM student

   WHERE imiona LIKE '%A' OR imiona LIKE '%E'

   ORDER BY nazwisko;

DESC studentki;

CREATE OR REPLACE VIEW studenci

 AS 

 SELECT * FROM student

   WHERE imiona NOT LIKE '%A' AND imiona NOT LIKE '%E'

   ORDER BY nazwisko;

DESC studenci;

-- Wybierz nazwisko, imiona i numer indeksu studentek z grupy 4, roku 3.

SELECT nazwisko,imiona, nr_albumu FROM studentki

  WHERE rok = 3 AND gr_dziekan = 4;

-- Czy mozliwe jest wstawienie danych do powyzszych perspektyw?

-- Utworz perspektywe pracownice, na bazie tabeli pracownicy.

CREATE OR REPLACE VIEW pracownice

 AS 

 SELECT * FROM pracownicy

   WHERE nazwisko LIKE '%SKA' OR nazwisko LIKE '%CKA' 

 WITH CHECK OPTION;

DESC pracownice;

-- Wstaw wiersz do perspektywy pracownice. 

INSERT INTO pracownice VALUES (9000, 'KOWNACKA','ASYSTENTKA', 8901, 

  Sysdate, NULL, 1500, NULL, NULL, 10);

-- Utworz perspektywe tylko do odczytu, o nazwie miejsce_pracy, zawierajaca 

-- nazwisko pracownika i siedzibe dzialu, w ktorym pracuje.

CREATE OR REPLACE VIEW miejsce_pracy

  (pracownik, adres_pracy)

  AS 

  SELECT nazwisko, siedziba 

    FROM pracownicy p, dzialy d

    WHERE p.id_dzialu=d.id_dzialu

  WITH READ ONLY;

SELECT * FROM miejsce_pracy;

-- Wstaw dane do perspektywy wszyscy_pracownicy.

INSERT INTO wszyscy_pracownicy VALUES (9737, 'SŁOCIŃSKI', 'LABORANT', 9332, 

  Sysdate, NULL, 1400, NULL, NULL, 50);

-- Utworz perspektywe dzial10, zawierajaca, numer akt, nazwiska, stanowiska 

-- i numer dzialu, pracownikow z dzialu 10, z opcja WITH CHECK OPTION.

CREATE OR REPLACE VIEW dzial10

  AS 

  SELECT nr_akt, nazwisko, stanowisko,  kierownik, id_dzialu

    FROM pracownicy

    WHERE id_dzialu=10

  WITH CHECK OPTION;

-- Wstaw dane do perspektywy dzial10, sprobuj podac inny numer dzialu niz 10.

INSERT INTO dzial10 VALUES (9090, 'KUKULSKI', 'GŁÓWNY INFORMATYK',8901,20);

INSERT INTO dzial10 VALUES (9090, 'KUKULSKI', 'GŁÓWNY INFORMATYK',8901,10);

-- Utworz perspektywe szefowie zawierajaca numery akt, nazwiska, liczbe 

-- podwladnych, date zatrudnienia, place, dodatki funkcyjne, prowizje 

-- i identyfikatory dzialow wszystkich szefow z tabeli pracownicy.

CREATE OR REPLACE VIEW szefowie

AS

SELECT nr_akt, nazwisko, l_podwladnych, data_zatr, placa, 

  dod_funkcyjny,prowizja,id_dzialu

  FROM (

    SELECT kierownik, Count(*) l_podwladnych

      FROM pracownicy

      WHERE kierownik IS NOT NULL

      GROUP BY kierownik) a,

    pracownicy b

  WHERE a.kierownik = b.nr_akt;

-- Utworz perspektywe szef_small na bazie perspektywy szefowie 

-- zawierajaca jedynie nazwiska i numery akt.

CREATE OR REPLACE VIEW szef_small

AS

SELECT nr_akt, nazwisko  FROM szefowie;

-- Usun perspektywe szef_small.

DROP VIEW szef_small;

SELECT * FROM USER_VIEWS;

----------------------------------INDEKSY------------------------------------

-- Utworz indeks zlozony na pola stanowisko i id_dzialu w tabeli pracownicy.  

CREATE INDEX prac_standzial_index ON pracownicy (stanowisko,id_dzialu);

-- Utworz indeks bitmapowy na pole kierwonik w tabeli pracownicy.

CREATE BITMAP INDEX prac_kier_index ON pracownicy (kierownik);

SELECT * FROM USER_INDEXES;

---------------------------------SEKWENCJE-----------------------------------

--Utworz tabele numer, zawierajaca jedno pole liczba (czterocyfrowe);

CREATE TABLE numer(

  liczba NUMBER(4)

);

-- Utworz sekwencje liczba_seq zaczynajaca sie od 100 majaca minimalna 

-- wartosc 0, maksymalna 125, zwiekszajaca sie co 5, posiadajaca cykl.

CREATE SEQUENCE liczba_seq

  INCREMENT BY 25

  START WITH 100

  MINVALUE 0

  MAXVALUE 125

  CYCLE; //NOCYCLE;

-- Wsatw 6 wierszy do tabeli numer uzywajac sekwencji liczba_seq.  

INSERT INTO numer VALUES (liczba_seq.NEXTVAL);

-- Usun powyzsza sekwencje.

DROP SEQUENCE liczba_seq;

-- Utworz sekwencje nr_akt_sek zwiekszajaca sie o 1, zaczynajaca sie od liczby 

-- wiekszej niz najwiekszy numer akt w atebli pracownicy.

SELECT Max(nr_akt) FROM pracownicy;

CREATE SEQUENCE nr_akt_seq

  INCREMENT BY 1

  START WITH 9781;

-- Sprawdz aktualna i nastepna wartosc utworzonej wlasnie sekwencji.

SELECT nr_akt_seq.nextval FROM dual;

SELECT nr_akt_seq.currval FROM dual;

-- Zmien wartosc skoku sekwencji nr_akt_seq na 2.

ALTER SEQUENCE nr_akt_seq INCREMENT BY 2;

SELECT * FROM ALL_TABLES WHERE OWNER='&1';

SELECT * FROM USER_TABLES;

SELECT * FROM USER_VIEWS;

SELECT * FROM USER_UPDATABLE_COLUMNS;

SELECT * FROM USER_INDEXES;

SELECT * FROM USER_IND_COLUMNS;

SELECT * FROM USER_SEQUENCES;

SELECT * FROM USER_CATALOG;

----------------------------------PL/SQL---------------------------------

-- Utwórz tabelę dziennik składającą się z pól: tabela (piętnastoznakowe),

-- data, l_wierszy (czterocyfrowe), komunikat (trzystuznakowe).

CREATE TABLE dziennik(

  tabela VARCHAR2(15),

  data DATE,

  l_wierszy NUMBER(4),

  komunikat VARCHAR2(300)

);

-- Zadeklaruj blok anonimowy aktualizujący tabelę pracownicy poprzez dodanie 

-- szefom premii, której wartość przechowana jest w zmiennej. Następnie policz 

-- ile zmieniono wierszy i wstaw odpowiedni komentarz do tabeli dziennik.

DECLARE

  zmienione NUMBER;

  premia CONSTANT NUMBER := 500;

BEGIN

  UPDATE pracownicy SET dod_funkcyjny=dod_funkcyjny+premia 

    WHERE nr_akt IN(

      SELECT DISTINCT kierownik FROM pracownicy 

        WHERE kierownik IS NOT NULL);

  zmienione:=SQL%ROWCOUNT;

  INSERT INTO dziennik VALUES ('pracownicy',Sysdate,zmienione,

    'Wprowadzono dodatek funkcyjny w wysokosci '||premia);

END;

-- Zadeklaruj blok anonimowy usuwający z tabeli pracownicy pracownika o numerze 

-- 8902.Następnie policz ile zmieniono wierszy i wstaw odpowiedni komentarz do 

-- tabeli dziennik. Wyłap ewentualne błędy i wstaw odpowiedni komentarz do tabeli 

-- dziennik.

DECLARE

  komunikat VARCHAR2(300);

  zmienione NUMBER;

BEGIN

  DELETE FROM pracownicy WHERE nr_akt=8902;

  zmienione:=SQL%ROWCOUNT;

  EXCEPTION

    WHEN OTHERS THEN

      komunikat:='Błąd nr'||SQLCODE||': '||Substr(SQLERRM,1,30);

      INSERT INTO dziennik VALUES ('pracownicy',Sysdate,NVL(zmienione,0),

        komunikat);

END;

-- Zadeklaruj blok anonimowy wstawiający do tabeli pracownicy pracownika 

-- o numerze otrzymanym z sekwencji nr_akt_seq. Następnie policz ile zmieniono 

-- wierszy i wstaw komentarz do tabeli dziennik. W komentarzu ujmij numer 

-- pracownika, którego wstawiono.

DECLARE

  nr pracownicy.nr_akt%type;

  zmienione NUMBER;

  komunikat VARCHAR2(300);

BEGIN

  INSERT INTO pracownicy VALUES 

    (nr_akt_seq.nextval,  'WITKOWSKI' ,  'DYREKTOR' , 8901, 

     To_date( '05/01/01' , 'yy/mm/dd' ), NULL, 3000, 1500, NULL, 40)

    RETURNING nr_akt INTO nr;

  zmienione:=SQL%ROWCOUNT;

  komunikat := 'Wstawiono pracownika numer: ' || nr;

  INSERT INTO dziennik VALUES ('pracownicy',Sysdate,NVL(zmienione,0),

        komunikat);

END;

-- Zadeklaruj blok anonimowy wstawiający do dziennika komentarz o ilości 

-- zatrudnionych pracowników w 1989 roku na bazie obsługi błędów.

DECLARE

  nazw pracownicy.nazwisko%type;

  stan pracownicy.stanowisko%type;

  komunikat VARCHAR2(300);

BEGIN

  SELECT nazwisko, stanowisko INTO nazw, stan

    FROM pracownicy

    WHERE to_char(data_zatr,'yyyy') LIKE '1989';

  komunikat := 'Zatrudniono '||nazw;  

  INSERT INTO dziennik VALUES ('pracownicy',Sysdate,0,komunikat);

  EXCEPTION

    WHEN NO_DATA_FOUND THEN

      komunikat := 'Nikogo nie zatrudniono';

      INSERT INTO dziennik VALUES ('pracownicy',Sysdate,0,komunikat);

    WHEN TOO_MANY_ROWS THEN

      komunikat := 'Wiecej niz jeden zatrudniony';  

      INSERT INTO dziennik VALUES ('pracownicy',Sysdate,0,komunikat);

    WHEN OTHERS THEN

      komunikat:='Blad nr'||SQLCODE||': '||Substr(SQLERRM,1,30);

      INSERT INTO dziennik VALUES ('pracownicy',Sysdate,0,komunikat);

END;

-- Zadeklaruj blok anonimowy wstawiający do dziennika komentarz o długości

-- zatrudnienia parcwonika z numerem 8902 (dłużej, czy krócej niż 15 lat).

DECLARE

  nazw pracownicy.nazwisko%type;

  stan pracownicy.stanowisko%type;

  dz pracownicy.data_zatr%type;

  komunikat VARCHAR2(300);

BEGIN

  SELECT nazwisko, stanowisko, data_zatr

    INTO nazw, stan, dz

    FROM pracownicy

    WHERE nr_akt = 8902;

  CASE 

    WHEN dz < To_date( '1991/01/01' , 'yyyy/mm/dd' ) THEN

      komunikat:='Pracownik '||nazw ||' zatrudniony dluzej niz 15 lat';

    WHEN dz > To_date( '1991/01/01' , 'yyyy/mm/dd' ) THEN

      komunikat:='Pracownik '||nazw || ' zatrudniony krócej niz 15 lat'; 

    ELSE komunikat:='Pracownik '||nazw || ' zatrudniony 15 lat';

  END CASE;

  INSERT INTO dziennik VALUES ('pracownicy',Sysdate,0,komunikat);

 EXCEPTION

    WHEN OTHERS THEN

      komunikat:='Blad nr'||SQLCODE||': '||Substr(SQLERRM,1,30);

      INSERT INTO dziennik VALUES ('pracownicy',Sysdate,0,komunikat);

END;

-- Zadeklaruj blok anonimowy wykonujący pętle od wartości licznika 3 do 7, 

-- wypisujący na ekran wartości licznika oraz komentarze: "poczatek" dla 

-- wartości 3, "srodek" dla 5 i "koniec" dla 7.

SET SERVEROUTPUT ON;

DECLARE

  i NUMBER(2);

BEGIN

  FOR i IN 3..7 LOOP

    dbms_output.put_line(i || ' ');

    IF (i = 3) THEN

      dbms_output.put_line('poczatek');

    ELSIF (i = 5) THEN

      dbms_output.put_line('srodek');

    ELSIF (i = 7) THEN 

      dbms_output.put_line('koniec');

    END IF;

  END LOOP;

END;

-- Wykorzystując sekwencję liczba_seq oraz tablicę numer, zadeklaruj blok 

-- anonimowy wstawiający 20 kolejnych wartości sekwencji do tablicy numer.

DECLARE

  i NUMBER(2);

BEGIN

  FOR i IN 1..20 LOOP

    INSERT INTO numer VALUES (liczba_seq.nextval);

  END LOOP;

END;

-- Utwórz procedurę, podwyższającą płacę dla danego argumentem działu 

-- o określony drugim argumentem procent. Wprowadź domyślne wartości 

-- dla argumentów, a także odpowiedni komentarz do dziennika.

CREATE OR REPLACE PROCEDURE podwyzka_plac (id pracownicy.id_dzialu%type := 0, 

                                           ile_proc NUMBER := 0) IS

  zmienione NUMBER;

BEGIN

  IF (id = 0) THEN

    UPDATE pracownicy SET placa=placa+placa*(ile_proc/100);

  ELSE

    UPDATE pracownicy SET placa=placa+placa*(ile_proc/100)

      WHERE id_dzialu = id;

  END IF;

  zmienione:=SQL%ROWCOUNT;

  INSERT INTO dziennik VALUES ('pracownicy',Sysdate,zmienione,

    'Wprowadzono podwyzke o '||ile_proc||' procent.');

END;

SHOW ERRORS;

LIST 1;

CALL podwyzka_plac(0, 5);

SELECT * FROM user_source;

-- Zadeklaruj funkcję zwracającą udział procentowy działu w budżecie firmy.

-- Wywołaj ją wewnątrz zapytania.

CREATE OR REPLACE FUNCTION udzial_w_budzecie(id IN NUMBER) RETURN NUMBER

  IS

    budzet_dz NUMBER; 

    budzet NUMBER;

  BEGIN

    SELECT Sum(Nvl(placa,0)+Nvl(dod_funkcyjny,0)+Nvl(prowizja,0)) INTO budzet_dz

      FROM pracownicy WHERE id_dzialu=id;

    SELECT Sum(Nvl(placa,0)+Nvl(dod_funkcyjny,0)+Nvl(prowizja,0)) INTO budzet

      FROM pracownicy; 

    RETURN Round(100*budzet_dz/budzet,2);

  END;

SELECT DISTINCT id_dzialu, udzial_w_budzecie(id_dzialu) FROM pracownicy 

  WHERE id_dzialu IS NOT NULL;

 

DROP TABLE sprzedaz;

DROP TABLE produkty;

CREATE TABLE produkty(

  id NUMBER(4),

  nazwa VARCHAR2(30),

  ilosc NUMBER(6,2),

  cena NUMBER(5,2),

  CONSTRAINT prod_idt_pk PRIMARY KEY(id)

);

CREATE TABLE sprzedaz (

  id_sprzedazy NUMBER(4),

  id_towaru NUMBER(4),

  ilosc NUMBER(6,2),

  data DATE DEFAULT Sysdate,

  CONSTRAINT sprzedaz_ids_pk PRIMARY KEY(id_sprzedazy),

  CONSTRAINT sprzedaz_idt_fk FOREIGN KEY(id_towaru)

    REFERENCES produkty(id) ON DELETE CASCADE

);

INSERT INTO produkty VALUES (1, 'banan', 200, 4.50);

INSERT INTO produkty VALUES (2, 'cytryna', 300, 4.00);

INSERT INTO produkty VALUES (3, 'kiwi', 100, 4.90);

INSERT INTO produkty VALUES (4, 'jablko', 500, 2.40);

INSERT INTO sprzedaz VALUES (1, 2, 10, DEFAULT);

INSERT INTO sprzedaz VALUES (2, 2, 20, DEFAULT);

INSERT INTO sprzedaz VALUES (3, 1, 5, DEFAULT);

INSERT INTO sprzedaz VALUES (4, 3, 3, DEFAULT);

SELECT * FROM produkty;

SELECT * FROM sprzedaz;