SELECT AVG(salary),department_id
 FROM employees
  GROUP BY department_id;


SELECT *
  FROM
( SELECT salary,department_id
 FROM employees )
 pivot
 ( AVG(salary) FOR department_id IN (10,20,30,40,50,60,70,80,90,100) );



SELECT dep10,dep20,dep30,dep40,ROUND(dep50),dep60,dep70,
 ROUND(dep80),ROUND(dep90),ROUND(dep100)
  FROM
( SELECT salary,department_id
 FROM employees )
 pivot
 ( AVG(salary) FOR department_id IN
 (10 dep10,20 dep20,30 dep30,40 dep40,50 dep50,60 dep60,70 dep70,
 80 dep80,90 dep90,100 dep100) );


SELECT *
 FROM
( SELECT salary,department_name
 FROM employees
  join departments using(department_id))
pivot
 ( SUM(salary) FOR department_name IN ('IT','Executive','Sales'));


SELECT *
 FROM (
SELECT salary, EXTRACT(YEAR FROM hire_date) rok
 FROM employees )
 pivot
 ( MAX(salary) FOR rok IN (2001,2002,2003,2004,2005,2006,2007,2008));

/*
Wyswietl liczbe pracownikwo w miastach.
Miasta jako kolumny.
*/

SELECT DISTINCT city
 FROM employees
  join departments using(department_id)
   join locations using(location_id);


SELECT NVL(TO_CHAR(manager_id),'szef'),
       NVL(dep10,0),NVL(dep20,0),NVL(dep30,0),
       NVL(dep40,0),NVL(dep50,0)
 FROM (
SELECT salary,manager_id,department_id
 FROM employees )
pivot
(AVG(salary) FOR department_id IN (10 dep10,20 dep20 ,30 dep30,
40 dep40,50 dep50));

/* Wyswietl liczbie pracownikow,
nazwe departmentu, panstwo
*/

SELECT *
 FROM (
SELECT salary,manager_id
 FROM employees )
 pivot xml
 (  SUM(salary) FOR manager_id IN (SELECT DISTINCT manager_id
  FROM employees));

SELECT *
 FROM (
SELECT salary,manager_id
 FROM employees )
 pivot xml
 (  SUM(salary) FOR manager_id IN (ANY));


CREATE TABLE test_pivot AS
SELECT dep10,dep20,dep30,dep40,ROUND(dep50) dep50,dep60,dep70,
 ROUND(dep80) dep80,ROUND(dep90) dep90,ROUND(dep100) dep100
  FROM
( SELECT salary,department_id
 FROM employees )
 pivot
 ( AVG(salary) FOR department_id IN
 (10 dep10,20 dep20,30 dep30,40 dep40,50 dep50,60 dep60,70 dep70,
 80 dep80,90 dep90,100 dep100) );

SELECT *
FROM test_pivot
UNPIVOT
(
sal FOR dept_no IN
(dep10,dep20,dep30,dep40,dep50,dep60,dep70,dep80,dep90,dep100));

SELECT regexp_substr('abc123','[[:alpha:]]')
 FROM dual;

SELECT regexp_substr('abc123','[[:digit:]]')
 FROM dual;

SELECT regexp_substr('abc123','[[:alnum:]]')
 FROM dual;

SELECT regexp_substr('abc123','[[:alnum:]]{3}')
 FROM dual;

SELECT regexp_substr('ab1c123','[[:alpha:]]{3}')
 FROM dual;

SELECT regexp_substr('ab1c123','[[:alpha:]]{1,4}')
 FROM dual;

SELECT regexp_substr('ab1c123','[[:alpha:]]+')
 FROM dual;

--select regexp_substr('abc123','[[:alnum:]](3)')
-- from dual;

SELECT regexp_substr('abc_1234','([[:alpha:]]|_){4}')
 FROM dual;

SELECT regexp_substr('frefqwefwwea-bc_1234','([[:alpha:]]|_|-){4}')
 FROM dual;

SELECT regexp_substr('fr|efqwefwwea-bc_1234','([[:alpha:]]|_|-|\|){4}')
 FROM dual;

SELECT regexp_substr('fr\|efqwefwwea-bc_1234','([[:alpha:]]|_|-|\||\\){4}')
 FROM dual;

SELECT regexp_substr('fr\|efqwefwwea-bc_1234','([[:alpha:]]|.){4}')
 FROM dual;

SELECT regexp_substr('DQWDWQDEW@fr.efqwefwwea-bc_1234','@([[:alpha:]]|\.){4}')
 FROM dual;

SELECT regexp_substr(
'dwdwdw2dw dwde q  // abcd103@interia.pl wqdsqw     e w  qw
 wxcw // j.kowalski@gmail.com scdqwedqw3d2 qw2 // kubus_puchatek@altkom.pl
 xwqxsqwsdq ',
 '// ([[:alnum:]]|\.|_){3,20}@[[:alpha:]]{5,10}.[[:alpha:]]{2,3}',1,3)
 FROM dual;


SELECT LEVEL
 FROM dual
  CONNECT BY level<4;


SELECT LTRIM(reg,'// ')
 FROM (
SELECT regexp_substr(
'dwdwdw2dw dwde q  // abcd103@interia.pl wqdsqw     e w  qw
 wxcw // j.kowalski@gmail.com scdqwedqw3d2 qw2 // kubus_puchatek@altkom.pl
 xwqxsqwsdq ',
 '// ([[:alnum:]]|\.|_){3,20}@[[:alpha:]]{5,10}.[[:alpha:]]{2,3}',1,LEVEL) reg
 FROM dual
  CONNECT BY level<4);

'dewd2ed12c > (22)675-14-82 dqwdwdw cdwdw wdw > 444555888
dqwdw  dwqdwdw  ew2 > 567.123.987 ewdfw > 123 999 568'

SELECT TO_CHAR(salary/12, '9,999.99')
  FROM employees;


SELECT SUBSTR(last_name,-3)
 FROM employees;

SELECT last_name,INSTR(last_name,'a',-1)
 FROM employees;

SELECT regexp_instr('123ab456abc789','[[:alpha:]]{3}',1,3)
 FROM dual;

SELECT regexp_count('aaabb1ccc2','[[:digit:]]')
 FROM dual ;


 SELECT LTRIM(reg,'// ')
 FROM (
SELECT regexp_substr(
'dwdwdw2dw dwde q  // abcd103@interia.pl wqdsqw     e w  qw
 wxcw // j.kowalski@gmail.com scdqwedqw3d2 qw2 // kubus_puchatek@altkom.pl
 xwqxsqwsdq ',
 '// ([[:alnum:]]|\.|_){3,20}@[[:alpha:]]{5,10}.[[:alpha:]]{2,3}',1,LEVEL) reg
 FROM dual
  CONNECT BY level<=regexp_count(
  'dwdwdw2dw dwde q  // abcd103@interia.pl wqdsqw     e w  qw
 wxcw // j.kowalski@gmail.com scdqwedqw3d2 qw2 // kubus_puchatek@altkom.pl
 xwqxsqwsdq ',
 '// ([[:alnum:]]|\.|_){3,20}@[[:alpha:]]{5,10}.[[:alpha:]]{2,3}'));

SELECT regexp_replace('1-2-3','[[:digit:]]','hiszpanska_inkwizycja')
 FROM dual ;

SELECT phone_number
 FROM employees
  WHERE
regexp_like(phone_number,'[[:digit:]]{3}\.[[:digit:]]{3}\.[[:digit:]]{4}');


CREATE TABLE srednia_dep(
srednia NUMBER,
dep VARCHAR2(100));

CREATE TABLE suma_dep(
suma NUMBER,
dep VARCHAR2(100));

CREATE TABLE liczba_dep(
liczba NUMBER,
dep VARCHAR2(100));

INSERT ALL
 INTO srednia_dep(srednia,dep)
 VALUES(s1,department_name)
 INTO suma_dep(suma,dep)
 VALUES(s2,department_name)
 INTO liczba_dep(liczba,dep)
 VALUES (l1,department_name)
SELECT AVG(salary) s1,SUM(salary) s2,COUNT(employee_id) l1,
       department_name
   FROM employees
    join departments using(department_id)
     GROUP BY department_name;

INSERT ALL
 INTO srednia_dep(srednia,dep)
 VALUES(s1,department_name)
 INTO suma_dep(suma,dep)
 VALUES(s2,department_name)
 INTO liczba_dep(liczba,dep)
 VALUES (l1,department_name)
SELECT AVG(salary) s1,SUM(salary) s2,COUNT(employee_id) l1,
       department_name
   FROM employees
    join departments using(department_id)
     GROUP BY department_name;


CREATE TABLE elektrownia(
id INTEGER,
licznik NUMBER);

INSERT INTO elektrownia VALUES(1,5000);
INSERT INTO elektrownia VALUES(2,500);
INSERT INTO elektrownia VALUES(3,1500);
INSERT INTO elektrownia VALUES(4,2000);
INSERT INTO elektrownia VALUES(5,3000);
INSERT INTO elektrownia VALUES(6,200);

COMMIT ;

CREATE TABLE inkasent01 AS
 SELECT id , licznik*1.2 licznik
  FROM elektrownia
   WHERE id<4;

SELECT * FROM inkasent01;

merge INTO elektrownia using inkasent01
ON ( elektrownia.id = inkasent01.id)
WHEN matched THEN UPDATE SET
 elektrownia.licznik = INKASENT01.LICZNIK;

SELECT * FROM elektrownia;

COMMIT ;

INSERT INTO inkasent01 VALUES(7,1000);

merge INTO elektrownia using inkasent01
ON ( elektrownia.id = inkasent01.id)
WHEN matched THEN UPDATE SET
 elektrownia.licznik = INKASENT01.LICZNIK
WHEN NOT matched THEN INSERT
(elektrownia.id,elektrownia.licznik)
VALUES
(INKASENT01.id,INKASENT01.licznik);

COMMIT;

SELECT * FROM elektrownia;


merge INTO elektrownia e using inkasent01 i
ON ( e.id = i.id)
WHEN matched THEN UPDATE SET
 e.licznik = I.LICZNIK
WHEN NOT matched THEN INSERT
(e.id,e.licznik)
VALUES
(I.id,I.licznik);

COMMIT;

/*
zmien wszystkie nazwy miasta na seattle.
zatwierdz.
uzywajac as of timestamp utworz tabele ktora bedzie
zawierala stan tabeli locations z przed godziny.
zaktualizuj 1 tabele na podstawie 2.
*/


BEGIN
NULL;
END;

/
BEGIN
 DBMS_OUTPUT.put_line('Witaj ');
END;
/

/
BEGIN
 DBMS_OUTPUT.put_line(2+2);
END;
/

/
BEGIN
 DBMS_OUTPUT.put_line(POWER(3,2));
END;
/

/
BEGIN
 DBMS_OUTPUT.put_line('wynik :'||(2+2)||' hiszpanska inkwizycja');
END;
/

/
DECLARE
 v_1 NUMBER:=2;
 v_2 NUMBER:=2;
BEGIN
 DBMS_OUTPUT.put_line(v_1+v_2);
END;
/

/
DECLARE
 v_1 NUMBER:=2;
 v_2 NUMBER:=2;
BEGIN
 DBMS_OUTPUT.put_line(v_1+v_2);
END;
/

/
DECLARE
 v_1 NUMBER;
 v_2 NUMBER;
 wynik NUMBER;
BEGIN
 v_1:=5;
 v_2:=10;
 wynik:=v_1+v_2;
 DBMS_OUTPUT.put_line(wynik);
END;
/

/
DECLARE
 imie VARCHAR2(50):='Janusz';
BEGIN
 DBMS_OUTPUT.put_line('Witaj '||imie);
END;
/


/
DECLARE
 imie VARCHAR2(50):='&imie';
BEGIN
 DBMS_OUTPUT.put_line('Witaj '||imie);
END;
/

waga /(wzrost*wzrost)

/*napisz blok ktory bedzie przyjmowal jako
zmienne wage oraz wzrost,wyliczal bmi i
wyswietlal je na dbms_output */


/* napisz blok ktory wyswietli na
dbms liczbe dni, miesiecy , lat pomiedzy
datami ktore podamy jako zmienne */


/
BEGIN
 FOR x IN 1..10 LOOP
    DBMS_OUTPUT.put_line('jakis tekst');
 END LOOP;
END;
/

/
DECLARE
 y NUMBER;
BEGIN
 FOR x IN 1..10 LOOP
    y:=x;
 END LOOP;
 DBMS_OUTPUT.put_line(y);
END;
/

/
DECLARE
 v_start INTEGER:=10;
 v_end INTEGER:=100;
 y NUMBER:=0;
BEGIN
 FOR x IN v_start..v_end LOOP
     y:=y+2;
     DBMS_OUTPUT.put_line(y);
 END LOOP;
END ;
/

/*
napisz blok ktory wyswietli na dbms_output
pierwsze 20 poteg liczy ktora podamy jako zmienna
*/


/
DECLARE
 lokata NUMBER:=20000;
 rok NUMBER:=0;
BEGIN
 LOOP
  lokata:=lokata*1.02;
  rok:=rok+1;
  DBMS_OUTPUT.put_line(lokata||' '||rok);
  EXIT WHEN lokata>=40000;
 END LOOP;
END;
/

/*
Janusz wazy 104 kg, intensywnie cwiczac na silowni spala miesiecznie
2% swojej masy , po ilu miesiacach waga janusza spadnie pozniej
85 kg
*/

/
DECLARE
 lokata NUMBER:=20000;
 rok NUMBER:=0;
BEGIN
 WHILE (lokata <40000 )   LOOP
  lokata:=lokata*1.02;
  rok:=rok+1;
  DBMS_OUTPUT.put_line(lokata||' '||rok);
 END LOOP;
END;
/

/
DECLARE
  x NUMBER:=5;
BEGIN
  IF x=5 THEN
     DBMS_OUTPUT.put_line('ten x jest rowny 5 ');
  ELSIF x>5 THEN
     DBMS_OUTPUT.put_line('ten x jest wiekszy niz 5 ');
  ELSE
     DBMS_OUTPUT.put_line('ten x jest mniejszy niz 5 ');
  END IF;
END;
/


/
DECLARE
  x NUMBER:=50;
BEGIN
 IF x>100 THEN
    DBMS_OUTPUT.put_line('>100');
 ELSIF x>10 THEN
       DBMS_OUTPUT.put_line('>10');
 ELSIF x>1 THEN
       DBMS_OUTPUT.put_line('>1');
 END IF;
END ;
/

16,0 – wygłodzenie
16,0–16,99 – wychudzenie
17,0–18,49 – niedowagę
18,5–24,99 – wartość prawidłową
25,0–29,99 – nadwagę
30,0–34,99 – I stopień otyłości
35,0–39,99 – II stopień otyłości (otyłość kliniczna)
? 40,0 – III stopień otyłości (otyłość skrajna)

/*
Wyswietl liczby od 1 do 200 wraz z inforamacja
czy jest to liczba parzysta czy nie
*/

CASE WHEN x THEN 'x'
     WHEN y THEN 'y'
     ELSE 'z'
END CASE




/
BEGIN
 FOR x IN -10..10 LOOP
    DBMS_OUTPUT.put_line(2/x);
 END LOOP;
EXCEPTION
 WHEN ZERO_DIVIDE THEN
     DBMS_OUTPUT.put_line(' nie dziel przez 0 ');
 WHEN OTHERS THEN
     DBMS_OUTPUT.put_line('jakis inny wyjatek ');
END;
/

/
DECLARE
 v_data1 DATE:=TO_DATE('01-01-2010','dd-mm-yyyy');
 v_data2 DATE:=SYSDATE;
 dni NUMBER;
 e_zla_kolejnosc EXCEPTION;
BEGIN
 IF v_data1<v_data2 THEN
    RAISE e_zla_kolejnosc;
 END IF;
 dni:=v_data1-v_data2;
 EXCEPTION
 WHEN e_zla_kolejnosc THEN
    DBMS_OUTPUT.put_line('pierwsza data musi byc wieksza ');
END;
/

/*
1 wzrost w metrach,nie w cm
2 za_maly_zrost
3 za_duza_waga
4 za_mala_waga
*/


CREATE TABLE reg AS SELECT * FROM regions;
truncate TABLE reg;



/
DECLARE
 v_region_id INTEGER:=10;
 v_region_name VARCHAR2(50):='Europa';
BEGIN
 INSERT INTO reg VALUES(v_region_id,v_region_name);
 COMMIT;
END;
/

CREATE sequence reg_seq
 START WITH 1;

/
DECLARE
 v_region_name VARCHAR2(50):='Europa';
BEGIN
 INSERT INTO reg VALUES(reg_seq.NEXTVAL,v_region_name);
 COMMIT;
END;
/

SELECT * FROM reg;

/*
utworz kopie tabeli departments bez danych.
utworz sekwencje ktora wystartuje od 10 i bedzie zwiekszala
sie o 10.
napisz blok ktory bedzie dodawal wiersze do tej tabeli.
department_id ma byc uzuepelniane automatycznie przez sekwencje.
*/


/
DECLARE
 v_region_name VARCHAR2(50):='Europa';
BEGIN
 INSERT INTO reg VALUES(reg_seq.NEXTVAL,v_region_name);
 DBMS_OUTPUT.put_line('dodano '||sql%rowcount);
 COMMIT;
 DBMS_OUTPUT.put_line('dodano '||sql%rowcount);
END;
/


CREATE TABLE raport1(
id INTEGER primary key,
imiona VARCHAR2(100),
nazwiska VARCHAR2(100),
zarobki NUMBER,
dep VARCHAR2(100));


/
DECLARE
 v_city VARCHAR2(100):='Seattle2';
 e_zla_nazwa EXCEPTION;
BEGIN
 INSERT INTO raport1
 SELECT employee_id,first_name,last_name,salary,department_name
  FROM employees
   join departments using(department_id)
    join locations using(location_id)
     WHERE UPPER(city)=UPPER(v_city);
  IF sql%rowcount=0 THEN
     RAISE e_zla_nazwa;
  END IF;
  DBMS_OUTPUT.put_line('dodano '||sql%rowcount||' pracownikow ');
EXCEPTION
 WHEN e_zla_nazwa THEN
    DBMS_OUTPUT.put_line(' niepoprawna nazwa miasta  ');
END;
/

SELECT * FROM raport1;

SELECT s1.NEXTVAL,liczba,department_id
 FROM (
SELECT COUNT(*) liczba ,department_id
 FROM employees
  GROUP BY department_id );


/*
utworz tabele z koumanmi
id,suma zarobkow, srednie zarrobi, nazwa zawodu.
utworz sekwenecje ktora wystartuje od 1 i bedzie zwiekszac sie o 1.
napisz blok ktory zaladuj do tabeli dane
z departmentu ktorego nazwe podamy jako parametr.
*/

SELECT * FROM reg;

CREATE TABLE countries2
 AS SELECT * FROM countries
  WHERE 1=2;

/
DECLARE
 v_reg VARCHAR2(50):='Europa';
 v_region_id INTEGER;
 v_country_name VARCHAR2(50):='Polska';
BEGIN
 INSERT INTO reg VALUES(s1.NEXTVAL,v_reg)
 returning region_id INTO v_region_id;

 INSERT INTO countries2 VALUES(
 UPPER(SUBSTR(v_country_name,1,2)),
 v_country_name,v_region_id);
END ;
/

SELECT * FROM reg;
SELECT * FROM countries2;

CREATE TABLE emp3 AS SELECT *
 FROM employees;


/
DECLARE
 v_kasa NUMBER:=1000;
 v_employee_id INTEGER:=2000;
 e_brak_pracownika EXCEPTION;
BEGIN
 UPDATE emp3
  SET salary=salary+v_kasa
   WHERE employee_id = v_employee_id;
 IF sql%rowcount=0 THEN
    RAISE  e_brak_pracownika;
 END IF;
 DBMS_OUTPUT.put_line('pracownik od id '||v_employee_id||' dostal
  '||v_kasa||' podwyzki ');
 EXCEPTION
  WHEN e_brak_pracownika THEN
       DBMS_OUTPUT.put_line('nie ma pracownik '||v_employee_id);
END;
/

/*
napisz blok ktory bedzie dawal 10% podwyzki
pracownikom zatrudnionym pomiedzy datami
ktore podamy jako parametry
w zwodzie(job_title) ktorego nazwe podamy jako parametr.
Wyswietl komunikat ilu pracownikow dostalo podwyzke.
*/


CREATE TABLE logi1(
id INTEGER,
liczba_podwyzek NUMBER,
suma_podwyzke NUMBER,
rok_zatrudnienia INTEGER,
kiedy DATE);

/
DECLARE
 v_kasa NUMBER:=1000;
 v_rok INTEGER:=1999;
 v_liczba_podwyzek INTEGER;
 e1 EXCEPTION;
BEGIN
 UPDATE emp3
  SET salary=salary+v_kasa
   WHERE EXTRACT(YEAR FROM hire_date)=v_rok;
 IF  sql%rowcount=0 THEN
     RAISE e1;
 END IF;
 v_liczba_podwyzek:=sql%rowcount;
 INSERT INTO logi1 VALUES(s1.NEXTVAL,v_liczba_podwyzek,v_liczba_podwyzek*v_kasa,
 v_rok,SYSDATE);

 EXCEPTION
  WHEN e1 THEN
       DBMS_OUTPUT.put_line('w tym roku nie zatrudniono pracownikow ');
END;
/

SELECT * FROM logi1;

/*
napisz blok ktory usunie pracownikow z panstwa
ktorego nazwe podamy jako zmienna
*/


/
DECLARE
 v_srednia NUMBER;
 v_suma NUMBER;
 v_maks NUMBER;
 v_department_id NUMBER:=90;
BEGIN
 SELECT AVG(salary),SUM(salary),MAX(salary)
     INTO v_srednia,v_suma,v_maks
  FROM employees
   WHERE department_id=v_department_id;
 DBMS_OUTPUT.put_line('srednia : '||v_srednia);
 DBMS_OUTPUT.put_line('suma : '||v_suma);
 DBMS_OUTPUT.put_line('maks : '||v_maks);
END;
/

/*
napisz blok ktory pobierze i wyswietli na dbms
liczbe dni, lat pomiedzy  01-01-2014 a maxymalna data zatrudnienia
w miescie ktorego nazwe podamy jako zmienna
*/

/
DECLARE
 v_first_name VARCHAR2(50);
 v_last_name VARCHAR2(50);
 v_employee_id INTEGER:=0;
BEGIN
 SELECT first_name,last_name INTO  v_first_name,v_last_name
  FROM employees
   WHERE employee_id = v_employee_id;
 DBMS_OUTPUT.put_line(v_first_name||' '||v_last_name);
EXCEPTION
 WHEN TOO_MANY_ROWS THEN
     DBMS_OUTPUT.put_line('wiecej niz 1 wiersz ');
 WHEN NO_DATA_FOUND THEN
     DBMS_OUTPUT.put_line(' nie ma takiego pracownika ');
END;
/

/*
napisz blok ktory pobierze zarobki pracownika ktorego id podamy
jako zmienna.
blok ma pobrac rowniez srednie zarobki w calej firmie.
jesli pracownik zarabia mniej niz srednia
daj mu podyzke o kwote ktora podamy jako zmienna.
*/


/
DECLARE
 v_region_name regions.region_name%TYPE;
BEGIN
 INSERT INTO reg VALUES(s1.NEXTVAL,v_region_name);
END;
/

CREATE TABLE eeee AS SELECT * FROM employees;
truncate TABLE eeee;
/
DECLARE
 w employees%ROWTYPE;
 v_employee_id employees.employee_id%TYPE:=100;
BEGIN
 SELECT * INTO w
  FROM employees
   WHERE employee_id=v_employee_id;
 INSERT INTO eeee VALUES w;
 DBMS_OUTPUT.put_line(w.first_name||' '||w.last_name||' '||w.salary);
END;
/

/
DECLARE
 TYPE rek IS RECORD(
 srednia NUMBER,
 max_date DATE,
 min_date DATE,
 zawod VARCHAR2(100));
 r rek;
 v_zawod VARCHAR2(100):='Programmer';

BEGIN
 SELECT AVG(salary),MAX(hire_date),MIN(hire_date),job_title INTO r
   FROM employees
    join jobs using(job_id)
     WHERE job_title=v_zawod
      GROUP BY job_title;

 DBMS_OUTPUT.put_line(r.srednia||' '||r.max_date||' '||r.min_date||'
  '||r.zawod);
END;
/


/*
Utworz tabele z kolumnami employee_id,salary,first_name,last_name,
department_id.
Napisz blok ktory pobierze najlepiej zarabijacego
pracownika w departamencie ktorego nazwe podamy jako zmienna.
blok ma zaladowac dane do wczesniej utworzonej tabeli,
oraz wyswietli je na dbms
*/

SELECT employee_id,salary,first_name,last_name,department_id
 FROM (
SELECT employee_id,salary,first_name,last_name,department_id,
       ROW_NUMBER() over (ORDER BY salary DESC ) rank1
   FROM employees
    join departments using(department_id)
     WHERE department_name='IT')
      WHERE rank1=1;

/
DECLARE
 min_emp INTEGER;
 max_emp INTEGER;
 w employees%ROWTYPE;
BEGIN
 SELECT  MIN(employee_id),MAX(employee_id) INTO min_emp,max_emp
  FROM employees;

 FOR x IN min_emp..max_emp LOOP
   SELECT * INTO w
    FROM employees
     WHERE employee_id=x;
 END LOOP;
END ;
/


/
DECLARE
  CURSOR k IS SELECT first_name,last_name
                FROM employees;
  v_first_name VARCHAR2(50);
  v_last_name VARCHAR2(50);
BEGIN
 OPEN k;
    LOOP
    FETCH k INTO v_first_name,v_last_name;
    EXIT WHEN k%notfound;
    DBMS_OUTPUT.put_line(v_first_name||' '||v_last_name);
    END LOOP;
 CLOSE k;
END;
/

/
DECLARE
 CURSOR k IS SELECT * FROM departments;
 w departments%ROWTYPE;
BEGIN
 OPEN k;
    LOOP
    FETCH k INTO w;
    EXIT WHEN k%notfound;
    DBMS_OUTPUT.put_line(w.department_id||' '||w.department_name);
    END LOOP;
 CLOSE k;
END;
/

/
DECLARE
 CURSOR k IS SELECT MAX(hire_date)-MIN(hire_date),
                   TRUNC(MONTHS_BETWEEN(MAX(hire_date),MIN(hire_date))),
                   TRUNC(MONTHS_BETWEEN(MAX(hire_date),MIN(hire_date))/12),
                   department_name
                FROM employees
                 join departments using(department_id)
                   GROUP BY department_name;
TYPE rek IS RECORD(
dni NUMBER,
mies NUMBER,
lata NUMBER,
dep VARCHAR2(100));
r rek;
BEGIN
 OPEN k;
   LOOP
     FETCH k INTO r;
     EXIT WHEN k%notfound;
     IF r.dni >0 THEN
        DBMS_OUTPUT.put_line(r.dni||' '||r.mies||' '||r.lata||' '||r.dep);
     END IF;
   END LOOP;
 CLOSE k;
END;
/

/*
Napisz blok ktory wypisze na dbms liczbe praconikow,rok zatrudnienia,
ranking po liczbie pracownikow malejaco.
*/


/*
utworz 3 tabele z kolumnami numer_pracownika, staz_w_latach.
tabela maja sie nazywac ponad15lat,ponad12lat i ponad10 lat.
napisz blok ktory pobierze numery pracownikow , liczbe lat w firmie
i rozrzuci ich do odpowiednich tabel.
*/

/
DECLARE
 CURSOR k IS SELECT first_name,last_name
                FROM employees;
BEGIN
  FOR w IN k LOOP
  EXIT WHEN w.last_name='King';
      DBMS_OUTPUT.put_line(w.first_name||' '||w.last_name);
  END LOOP;
END;
/

/
DECLARE
  CURSOR k IS SELECT employee_id,salary/12 miesieczne
                 FROM employees;
BEGIN
 FOR w IN k LOOP
     DBMS_OUTPUT.put_line(w.employee_id||' '||w.miesieczne);
 END LOOP;
END ;
/

/*
napisz blok ktory wypisze na dbms_output
sume zarobkow , liczbe pracownikow, level , naze departmentu
*/


/*
utworz 2 kopie tabeli employees.
napisz blok ktory wstawi do 1 tabeli pracownikow
zarabijacych wiecej niz srednie zarobki w firmie,
do drugiej tych ktorzy zarabiaja mniej
*/

CREATE TABLE e10 AS SELECT * FROM employees WHERE 1=2;
CREATE TABLE e11 AS SELECT * FROM employees WHERE 1=2;

/
DECLARE
 CURSOR k IS SELECT * FROM employees;
 srednia NUMBER;
BEGIN
 SELECT AVG(salary) INTO srednia
  FROM employees;

 FOR w IN k LOOP
 DBMS_OUTPUT.put_line(k%rowcount);
  IF w.salary>srednia THEN
     INSERT INTO e10 VALUES w;
  ELSE
    INSERT INTO e11 VALUES w;
  END IF;
 END LOOP;
COMMIT;
END;
/

/
DECLARE
 v_start_date DATE:=TO_DATE('01-01-2011','dd-mm-yyyy');
 v_end_date DATE:=TO_DATE('01-01-2013','dd-mm-yyyy');
 CURSOR k IS SELECT *
               FROM employees
                WHERE hire_date BETWEEN v_start_date
                 AND  v_end_date;
 e_1 EXCEPTION;
 e_2 EXCEPTION;
 i NUMBER:=0;
BEGIN
 IF v_start_date>v_end_date THEN
    RAISE e_1;
 END IF;

 FOR w IN k LOOP
     i:=i+1;
     DBMS_OUTPUT.put_line(w.employee_id||' '||w.hire_Date);
 END LOOP;

 IF i=0 THEN
    RAISE e_2;
 END IF;

EXCEPTION
 WHEN e_1 THEN
      DBMS_OUTPUT.put_line('pierwsza data musi byc mniejsza ');
 WHEN e_2 THEN
      DBMS_OUTPUT.put_line('nie zatrudniono pracownikow w tym zakresie ');
END;
/


/
DECLARE
CURSOR k1 IS SELECT * FROM departments;
CURSOR k2 IS SELECT * FROM employees;
x BOOLEAN:=TRUE;
BEGIN
 <<n1>>
 FOR w1 IN k1 LOOP
     FOR w2 IN k2 LOOP
         DBMS_OUTPUT.put_line(w2.last_name);
         EXIT n1 WHEN w2.last_name='King';
     END LOOP;
 END LOOP;
END ;
/

/
DECLARE
 CURSOR k(v_department_id INTEGER,v_manager_id INTEGER) IS
       SELECT * FROM employees WHERE department_id=v_department_id
       AND manager_id = v_manager_id ;
BEGIN
 FOR w IN k(90,100) LOOP
    DBMS_OUTPUT.put_line(w.last_name||' '||w.salary);
 END LOOP;
END ;
/

SELECT * FROM employees FOR UPDATE;
ROLLBACK ;


/
DECLARE
 CURSOR k IS SELECT * FROM employees FOR UPDATE;
BEGIN
  FOR w IN k LOOP
      UPDATE employees
       SET salary=salary+1000
        WHERE CURRENT OF k;
  END LOOP;
END;
/
ROLLBACK;

/
CREATE FUNCTION razy2(x NUMBER ) RETURN NUMBER IS
BEGIN
 RETURN x*2;
END;
/

SELECT razy2(salary) FROM employees;
SELECT razy2(5000) FROM dual;

/
CREATE OR REPLACE FUNCTION razy3(x NUMBER ) RETURN NUMBER IS
BEGIN
 RETURN x*3;
END;
/

/
CREATE OR REPLACE FUNCTION years_between(p_data1 DATE,p_data2 DATE)
RETURN NUMBER IS
BEGIN
 RETURN TRUNC(MONTHS_BETWEEN(p_data1,p_data2)/12);
END;
/

SELECT years_between(SYSDATE,hire_date)
 FROM employees;

/*napisz funckje years_between2 ktora bedzie liczyla
liczbe lat dla parametrow typu tesktowego */


/
CREATE OR REPLACE FUNCTION daj_srednia RETURN NUMBER IS
srednia NUMBER;
BEGIN
 SELECT AVG(salary) INTO srednia
  FROM employees;
 RETURN srednia;
END;
/
SELECT daj_srednia FROM dual;
SELECT *
 FROM employees WHERE salary>daj_srednia;


/
CREATE OR REPLACE FUNCTION daj_srednia2(p_department_name VARCHAR2)
RETURN NUMBER IS
srednia NUMBER;
BEGIN
 SELECT AVG(salary) INTO srednia
  FROM employees
  join departments using(department_id)
   WHERE UPPER(department_name)=UPPER(p_department_name);
 RETURN NVL(srednia,0);
END;
/
SELECT daj_srednia2('it2') FROM dual ;

/*
napisz funkcje ktora zwroci liczbe lat pomiedzy maxymalna,a
minimalna data zatrudnienia w miescie ktorego nazwe podamy
jako parametr funkcji
*/

/*
napisz funkcje ktora zwroci employee_id
najwczesniej zatrudnionego pracownika
w panstwie ktorego nazwe podamy jako zmienna
*/

/
CREATE OR REPLACE FUNCTION daj_id(p_country_name VARCHAR2)
RETURN NUMBER IS
 v_employee_id INTEGER;
BEGIN
SELECT employee_id INTO v_employee_id
 FROM (
SELECT employee_id,
       ROW_NUMBER() over (ORDER BY hire_date ) r1
 FROM employees
  join departments using(department_id)
   join locations using(location_id)
    join countries using(country_id)
     WHERE country_name='Germany')
      WHERE r1=1;
RETURN  v_employee_id;
END ;
/

SELECT daj_id('Germany') FROM dual ;

/*
Napisz funkcje ktora zwroci jak % zarobkow
w calej firmie stanowia zarobki w departamencie
ktoreg nazwe podamy jako parametr funkcji.
*/

show errors;

/
CREATE OR REPLACE PROCEDURE pisz(x VARCHAR2) IS
BEGIN
  DBMS_OUTPUT.put_line(x);
END;
/

/
BEGIN
 pisz('hello');
END;
/

EXECUTE  pisz('hello2');
exec  pisz('hello2');

CREATE TABLE dep2 AS SELECT * FROM departments
 WHERE 1=2;

CREATE sequence dep2_seq START WITH 10
 increment BY 10;

/
CREATE OR REPLACE PROCEDURE add_department(
p_department_name VARCHAR2,p_manager_id NUMBER,p_location_id NUMBER) IS
e1 EXCEPTION;
e2 EXCEPTION;
e3 EXCEPTION;
i NUMBER;
BEGIN
 SELECT COUNT(*) INTO i
   FROM departments
    WHERE department_name=p_department_name;
 IF i>0 THEN
    RAISE e1;
 END IF;
 SELECT COUNT(*) INTO i
   FROM employees
    WHERE manager_id = p_manager_id;
 IF i=0 THEN
    RAISE e2;
 END IF;
 SELECT COUNT(*) INTO i
   FROM locations
    WHERE location_id = p_location_id;
 IF i=0 THEN
    RAISE e3;
 END IF;

 INSERT INTO dep2
 VALUES(dep2_seq.NEXTVAL,p_department_name,p_manager_id,p_location_id);

EXCEPTION
 WHEN e1 THEN
      pisz('jest juz department o takiej nazwie');
 WHEN e2 THEN
      pisz('nie ma takiego managera ');
  WHEN e3 THEN
      pisz('nie ma takiej lokalizacji ');
END;
/

EXECUTE add_department('IT3',100,1801);

SELECT *
 FROM USER_DEPENDENCIES
  WHERE name='ADD_DEPARTMENT';



/
CREATE OR REPLACE PROCEDURE podwzyka(p_employee_id NUMBER ) IS
PRAGMA autonomous_transaction ;
e_brak_pracownika EXCEPTION;
BEGIN
  UPDATE employees
    SET salary=salary+100
     WHERE employee_id = p_employee_id;
  IF sql%rowcount=0 THEN
     RAISE e_brak_pracownika;
  END IF;

  COMMIT;
  EXCEPTION
   WHEN  e_brak_pracownika THEN
        pisz('brak pracownika');
        ROLLBACK;
END ;
/

UPDATE departments
 SET department_name='IT';
EXECUTE podwzyka(99);
ROLLBACK ;
SELECT * FROM departments;

/*
Napisz procedure ktora bedzie dawac podwyzke o kwote
ktora podamy jako parametr najslabiej zarabiajacemu
pracowniwkowi w roku ktory podamy jako parametr.
obasluz wyjatki ujemna_podyzke i brak_pracownikow.
procedura ma zawierac transakcje amutomiczna.
*/

/
CREATE OR REPLACE PROCEDURE daj(kwota NUMBER,rok NUMBER)  IS
PRAGMA autonomous_transaction;
e_ujemna EXCEPTION;
e_zly_rok EXCEPTION;
BEGIN

IF kwota<0 THEN
   RAISE e_ujemna;
END IF;

UPDATE employees
 SET salary=salary+kwota
  WHERE employee_id=(
SELECT employee_id
 FROM (
SELECT employee_id,
     ROW_NUMBER() over (ORDER BY salary) rank1
  FROM employees
   WHERE EXTRACT(YEAR FROM hire_date)=rok)
   WHERE rank1=1 );

IF sql%rowcount=0 THEN
   RAISE e_zly_rok;
END IF;
COMMIT;
EXCEPTION
 WHEN  e_ujemna THEN pisz('ujemna ');
 ROLLBACK;
 WHEN  e_zly_rok THEN pisz(' brak pracownikow ');
 ROLLBACK;
END;
/

513-120-171
/
CREATE OR REPLACE PROCEDURE wypisz IS
CURSOR k IS SELECT * FROM employees;
BEGIN
 FOR w IN k LOOP
   pisz(w.first_name||' '||w.last_name);
 END LOOP;
END;
/

EXECUTE wypisz;

/
CREATE OR REPLACE PROCEDURE test123(x IN NUMBER) IS
BEGIN
 NULL;
END;
/

/
CREATE OR REPLACE PROCEDURE liczba_rok(rok IN INTEGER, liczba OUT NUMBER) IS
BEGIN
 SELECT COUNT(*) INTO liczba
   FROM employees
    WHERE EXTRACT(YEAR FROM hire_date)=rok;
 pisz('w roku '||rok||' zatrudniono '||liczba);
END;
/

/
DECLARE
 rok NUMBER:=2005;
 liczba NUMBER;
BEGIN
 liczba_rok(rok,liczba);
 pisz(liczba);
END;
/

/*
napisz procedure ktora przyjmie jako parameter wejscia nazwe zawodu
a zwroci sume zarobkow w tym zawodzie powiekszona o 23%.
*/


/
DECLARE
 plik UTL_FILE.file_type;
BEGIN
 plik:=UTL_FILE.fopen('DANE2','plik.txt','a');
 UTL_FILE.put_line(plik,'jaki tekst');
 UTL_FILE.fclose(plik);
END;
/


/
DECLARE
 plik UTL_FILE.file_type;
 CURSOR k IS SELECT first_name,last_name,ROUND(salary/12,2) miesieczne,
                    department_name
                FROM employees
                 join departments using(department_id);
BEGIN
 plik:=UTL_FILE.fopen('DANE2','raport1.csv','a');
   FOR w IN k LOOP
       UTL_FILE.put_line(plik,w.first_name||';'||w.last_name||';'||w.miesieczne||';'||w.department_name);
   END LOOP;
 UTL_FILE.fclose(plik);
END;




































































































































































































CREATE OR REPLACE
PROCEDURE reset_seq( p_seq_name IN VARCHAR2 )
IS
    l_val NUMBER;
BEGIN
    EXECUTE IMMEDIATE
    'select ' || p_seq_name || '.nextval from dual' INTO l_val;

    EXECUTE IMMEDIATE
    'alter sequence ' || p_seq_name || ' increment by -' || l_val ||
                                                          ' minvalue 0';

    EXECUTE IMMEDIATE
    'select ' || p_seq_name || '.nextval from dual' INTO l_val;

    EXECUTE IMMEDIATE
    'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0';
END;



















































































/
BEGIN
 DBMS_OUTPUT.put_line('Witaj '|||'Swiecie');
END;
/