SELECT SUM(salary), department_id
FROM e2
GROUP BY department_id;

--cost 5056

SELECT * FROM user_segments
WHERE segment_name='E2';

--18432 bloki bazodanowe

DELETE FROM e2
WHERE department_id<>10; --usuwamy 1,7kk rekordow

COMMIT;

SELECT SUM(salary), department_id
FROM e2
GROUP BY department_id; --koszt tego zapytania jest minimalnie mniejszy

SELECT * FROM user_segments
WHERE segment_name='E2'; -- ilsoc blokow taka sama

ALTER TABLE e2 move;

COMMIT;

EXECUTE DBMS_STATS.gather_table_stats('HR','E2'); --odswiezenie statystyk





--tabele tymczasowe - przechowuja dane do konca sesji albo transakcji
CREATE global temporary TABLE tymczasowa -- do konca transakcji
AS SELECT * FROM employees; --przy tworzeniu auto commit, dlatego zawartosc tabeli jest pusta (zostaje tylko struktura)

SELECT * FROM tymczasowa;

CREATE global temporary TABLE tymczasowa2 ON COMMIT preserve rows --w tym przypadku dane czyszczone, kiedy konczymy sesje uzytkownika
AS SELECT * FROM employees;

SELECT * FROM tymczasowa2;



CREATE global temporary TABLE tmp2(x NUMBER) ON COMMIT preserve rows;

INSERT INTO tmp2 VALUES(1);

SELECT * FROM tmp2;

SELECT * FROM user_tables --podglad tabeli tymczasowych
WHERE temporary='Y';



--tabele przestawne

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

--pivot, przestawne
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 ROUND(dep10), ROUND(dep20), ROUND(dep30), ROUND(dep40), ROUND(dep50),
    ROUND(dep60), ROUND(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));
--nie mozna tu korzystac z funkcji jednowierszowych


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)
);



/*  wysw liczbe pracownikow w miastach.
miasta jako kolumny.
*/
SELECT * FROM (
SELECT employee_id, city
FROM employees join departments using(department_id)
join locations using(location_id)
)
pivot (
COUNT(employee_id) FOR city IN ('London' London,'Seattle' Seattle,'Munich' Munich,
    'South San Francisco' "South San Francisco",'Toronto' Toronto,'Southlake' Southlake,'Oxford' Oxford)
);



SELECT NVL(TO_CHAR(manager_id), 'szef') 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));



/* wysw liczbe pracownikow,
nazwe dep, panstwa
*/

SELECT *
FROM (
SELECT employee_id, department_name, country_name
FROM employees
    join departments using (department_id)
    join  locations using(location_id)
    join countries using(country_id)
)
pivot
(COUNT(employee_id) FOR country_name IN ('United Kingdom','United States of America','Germany','Canada'));


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 ROUND(dep10), ROUND(dep20), ROUND(dep30), ROUND(dep40), ROUND(dep50),
    ROUND(dep60), ROUND(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));


--wyrazenia regularne
                        -- zrodlo, wzorzec
SELECT regexp_substr('abc123','[[:alpha:]]') --znak tekstowy
FROM dual;

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


SELECT regexp_substr('abc123','[[:alnum:]]') --literry i cyfry
FROM dual;

SELECT regexp_substr('ab1c123','[[:alnum:]]{3}') /* bez {} wyswietla pierwsze wystpienie, z liczba w {} pokaze ciag
(ale tylko zgodny, np 2 znaki 1 niezgodny 1 znak - pokaze tylko 2 pierwsze) */
FROM dual;


SELECT regexp_substr('ab1c123','[[:alpha:]]{1,4}') --od x do y znakow
FROM dual;

SELECT regexp_substr('ab1c123','[[:alpha:]]+') --conajmniej 1 znak
FROM dual;

SELECT regexp_substr('abc_123','([[:alpha:]]|_){4}') --4 znaki tekstowe + znak za konkatenacja pojedyncza |, czyli podkreslenie _, te dodatkowe znaki moga byc ale nie musza
FROM dual;

SELECT regexp_substr('a-bc_123','([[:alpha:]]|_|-){5}') -- 5 znakow tekstowych i _ oraz -
FROM dual;

SELECT regexp_substr('a|-bc_123','([[:alpha:]]|_|-|\|){5}') --kiedy wyszukujemy w tekscie znak specjalny, musimy wczesniej wpisac \
FROM dual;

SELECT regexp_substr('a-bc_123','([[:alpha:]]|.){5}') -- 5 znakow tekstowych i wszystkie znaki spejclane (oznaczane .)
FROM dual;

SELECT regexp_substr('a-bc@_aaa123','@([[:alpha:]]|_|-){3}') -- znaki tesktowe z _ i -, ale szukac zaczynamy od @
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,2) reg
 FROM dual;                                                                     -- 2 oznacza 2 wystapienie

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) reg
 FROM dual;


SELECT LEVEL
FROM dual
CONNECT BY LEVEL < 4;

--wyszukiwanie adresow e-mail
SELECT LTRIM(reg,'// ') reg2
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
 );



/* wyciagnij numery telefonow, sa one podane po > */
SELECT LTRIM(reg,'> ') reg2
FROM (
SELECT regexp_substr(
'dewd2ed12c > (22)675-14-82 dqwdwdw cdwdw wdw > 444555888
dqwdw  dwqdwdw  ew2 > 567.123.987 ewdfw > 123 999 568',
'> ([[:digit:]]|\(|\)|-|\.| ){8,15}'
,1,LEVEL) reg
FROM dual
CONNECT BY LEVEL <= regexp_count(   --sam oblicza ilosc wystpien, nie zawsze da sie to samemu okreslic
'dewd2ed12c > (22)675-14-82 dqwdwdw cdwdw wdw > 444555888
dqwdw  dwqdwdw  ew2 > 567.123.987 ewdfw > 123 999 568',
'> ([[:digit:]]|\(|\)|-|\.| ){8,15}')
);




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

SELECT last_name, INSTR(last_name,'a',-1) --pierwsze wystapienie 'a' od konca
FROM employees;

SELECT SUBSTR(last_name,-3) --3 ostatnie znaki
FROM employees;


SELECT regexp_instr('123ab456abc789','[[:alpha:]]{3}') --od ktorego znaku zaczyna sie ciag 3 liter
FROM dual;


SELECT regexp_count( --liczba wystpapien wzorca, ktory nas interesuje
'aaabb1ccc2','[[:digit:]]')
FROM dual;



SELECT LTRIM(reg,'// ') reg2
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}'); --regex sluzacy tylko do filtrowania




--insert wielotabelowy
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,dep)
INTO suma_dep(suma,dep)
VALUES(s2,dep)
INTO liczba_dep(liczba,dep)
VALUES(11,dep)
SELECT ROUND(AVG(salary),2) s1,SUM(salary) s2, COUNT(employee_id) l1, department_name dep
FROM employees join departments using(department_id)
GROUP BY department_name
ORDER BY department_name;


--merge - jednoczesne wstawianie i modyfikacja rekordow

CREATE TABLE elektrownia(
id INTEGER,
licznik NUMBER);

INSERT INTO elektrownia VALUES(1,5000);
INSERT INTO elektrownia VALUES(2,12000);
INSERT INTO elektrownia VALUES(3,3700);
INSERT INTO elektrownia VALUES(4,2137);
INSERT INTO elektrownia VALUES(5,9344);
INSERT INTO elektrownia VALUES(6,1122);


COMMIT;

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

SELECT * FROM elektrownia;
SELECT * FROM inkasent01;
DELETE FROM inkasent01 WHERE id=1;

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

COMMIT;


INSERT INTO inkasent01 VALUES(7,6996);

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);


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);




/*  zamien wszystkie nazwy miasta na seattle.

zatwierdz.

uzywajac as of timestamp utworz tabele, ktora bedzie
zawierala stan tabeli locations sprzed godziny.

zaktualizuj 1 tabele na podstawie 2.
*/



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

SELECT * FROM locations;

UPDATE locations
SET city='Seattle';

COMMIT;


CREATE TABLE locations2 AS
SELECT * FROM locations AS OF TIMESTAMP TO_TIMESTAMP('10-05-2019 14:28:00','dd-mm-yyyy hh24:mi:ss');

COMMIT;


merge INTO locations l using locations2 l2
ON (l.location_id=l2.location_id)
WHEN matched THEN UPDATE SET l.city=l2.city;

SELECT * FROM locations;

COMMIT;