savalSQL.~sql

------------------------------------------------------------------------------------------------------------------------------------------
                    --------------------------------------Ãëàâà 1. Íà÷àëî------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------

-------------------Ïîñëåäîâàòåëüíîñè-------------
DROP SEQUENCE m_seq;

CREATE SEQUENCE m_seq
INCREMENT BY 1
START WITH 1
MAXVALUE 9999
NOCYCLE;

--------------------Ôóíêöèè-----------------------
CREATE OR REPLACE FUNCTION get_id RETURN VARCHAR IS
BEGIN
   RETURN SUBSTR(CONCAT('000', m_seq.nextval), -4);
END;
---------------Óäàëåíèå òàáëèö-------------------
DROP TABLE staff; 
DROP TABLE shops;
----------------Ñîçäàíèå òàáëèö-------------------
CREATE TABLE shops(
id_shop VARCHAR(4) NOT NULL,
city VARCHAR(20) NOT NULL,
open_time DATE,
close_time DATE,
PRIMARY KEY (id_shop)
);


CREATE TABLE staff(
id_worker VARCHAR(4) NOT NULL,
id_shop VARCHAR(4) NOT NULL,
salary INTEGER,
wname VARCHAR(15) NOT NULL,
age INTEGER,
PRIMARY KEY (id_worker),
CONSTRAINT work_in FOREIGN KEY(id_shop) REFERENCES shops
);
---------------Çàïîëíåíèå òàáëèö-----------------
INSERT INTO shops VALUES(get_id(), 'Moscow',       to_date('08:00', 'hh24:mi'), to_date('18:00', 'hh24:mi'));
INSERT INTO shops VALUES(get_id(), 'Rostov',       to_date('09:00', 'hh24:mi'), to_date('20:00', 'hh24:mi'));
INSERT INTO shops VALUES(get_id(), 'Samara',       to_date('08:00', 'hh24:mi'), to_date('18:00', 'hh24:mi'));
INSERT INTO shops VALUES(get_id(), 'Omsk',         to_date('09:00', 'hh24:mi'), to_date('20:00', 'hh24:mi'));
INSERT INTO shops VALUES(get_id(), 'Volgograd',    to_date('09:30', 'hh24:mi'), to_date('22:00', 'hh24:mi'));
INSERT INTO shops VALUES(get_id(), 'Krasnodar',    to_date('08:00', 'hh24:mi'), to_date('18:00', 'hh24:mi'));
INSERT INTO shops VALUES(get_id(), 'Strange_city', to_date('05:00', 'hh24:mi'), to_date('23:00', 'hh24:mi'));

INSERT INTO staff VALUES(get_id(), (SELECT id_shop FROM shops WHERE city = 'Rostov'),          12000,       'Îëåã',    57 );
INSERT INTO staff VALUES(get_id(), (SELECT id_shop FROM shops WHERE city = 'Samara'),          43000,       'Aíÿ',     18 );
INSERT INTO staff VALUES(get_id(), (SELECT id_shop FROM shops WHERE city = 'Krasnodar'),       46000,       'Êèðèëë',  36 );
INSERT INTO staff VALUES(get_id(), (SELECT id_shop FROM shops WHERE city = 'Samara'),          23000,       'Ñàøà',    45 );
INSERT INTO staff VALUES(get_id(), (SELECT id_shop FROM shops WHERE city = 'Volgograd'),       16000,       'Äàøà',    34 );
INSERT INTO staff VALUES(get_id(), (SELECT id_shop FROM shops WHERE city = 'Rostov'),          65000,       'Ëåíà',    34 );
INSERT INTO staff VALUES(get_id(), (SELECT id_shop FROM shops WHERE city = 'Moscow'),          32000,       'Âèêà',    37 );
INSERT INTO staff VALUES(get_id(), (SELECT id_shop FROM shops WHERE city = 'Omsk'),            54000,       'Íèêèòà',  28 );
INSERT INTO staff VALUES(get_id(), (SELECT id_shop FROM shops WHERE city = 'Moscow'),          32000,       'Îëåã',    45 );
INSERT INTO staff VALUES(get_id(), (SELECT id_shop FROM shops WHERE city = 'Rostov'),          12000,       'Ñàøà',    30 );
INSERT INTO staff VALUES(get_id(), (SELECT id_shop FROM shops WHERE city = 'Samara'),          43000,       'Îëåã',    20 );
INSERT INTO staff VALUES(get_id(), (SELECT id_shop FROM shops WHERE city = 'Volgograd'),       32000,       'Íàäÿ',    19 );

-----------------Ïðåäñòàâëåíèÿ-----------------
CREATE OR REPLACE VIEW view_shops AS
SELECT id_shop, city, to_char(open_time, 'hh24:mi') OPEN_IN, to_char(close_time, 'hh24:mi') CLOSE_IN
FROM shops;
---------------Âûâîä òàáëèö----------------------
SELECT * FROM view_shops;
SELECT * FROM staff ORDER BY id_shop;
SELECT * FROM staff NATURAL JOIN view_shops;



--------------------------------------------------SELECT òåñòû------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------

--DISTINCT
-- Âûâåñòè ñïèñîê òåõ èìåí, êîòîðûå èìþòñÿ ó ñîòðóäíèêîâ
SELECT DISTINCT wname AS Èìåíà_ñîòðóäíèêîâ
FROM staff;

--COUNT(colomn_name), INNER JOIN, UNION, NOT IN, GROUP BY, ORDER BY
-- Âûâåñòè îáùåå êîëè÷åñòâî ñîòðóäíèêîâ â êàæäîì ãîðîäå.
-- Ñîðòèðîâàòü ïî óáûâàíèþ êîë-âà ñîòðóäíèêîâ
SELECT city, COUNT(id_worker) Êîëè÷åñòâî_ñîòðóäíèêîâ
FROM shops s INNER JOIN staff w ON s.id_shop = w.id_shop
GROUP BY city
UNION 
SELECT city, 0
FROM shops
WHERE id_shop NOT IN (SELECT DISTINCT id_shop FROM staff)
ORDER BY 2 DESC;

--NATURAL JOIN
-- Âûâåñòè âñåõ ñîòðóäíèêîâ ñ ïîëíîé èíôîðìàöèåé î ìåñòå èõ ðàáîòû
SELECT *
FROM staff  NATURAL JOIN view_shops 
ORDER BY city;


--HAVING
-- Äëÿ âñåõ ìàãàçòíîâ â êîòîðûõ ñðåäíèé âîçðàñò ñîòðóäíèêîâ íå ïðåâûøàåò 30 ëåò 
-- Âûâåñòè: Ãîðîä â êîòîðîì íàõîäèòüñÿ ìàãàçèí, ñðåäíèé âîçðàñò ñ òî÷íîñòü äâóõ çíàêîâ ïîñëå çàïÿòîé
SELECT city, CAST(AVG(age) AS NUMERIC(5,2)) avg_age
FROM shops s, staff w
WHERE s.id_shop = w.id_shop
GROUP BY city
HAVING AVG(age) <= 30;


--GROUPING SETS
-- Âûâåñòè ñðåäíþþ ç/ï ñîòðóäíèêà äëÿ êàæäîãî îòäåëüíî âçÿòîãî ìàãàçèíà
-- è ñðåäíþþ ç/ï äëÿ âñåõ ñîòðóäíèêîâ êîìïàíèè
SELECT COALESCE(city, 'Ñðåä. ç\ï âñåõ') || ' :' AS city, CAST(AVG(salary) AS NUMERIC(10)) AS avg_salary
FROM staff NATURAL JOIN shops
GROUP BY 
GROUPING SETS ((city, id_shop),());

--ROLLUP
-- ???
SELECT wname, open_time, close_time, COUNT(*)
FROM staff NATURAL JOIN shops
GROUP BY ROLLUP(wname, open_time, close_time);
------------------------------------------------------------------------------------------------------------------------------------------
           ---------------------------Ãëàâà 2. Òàáëèöà bacteriums: CONNECT BY, WITH (ðåêóðñèÿ) ------------------------------
------------------------------------------------------------------------------------------------------------------------------------------
DROP SEQUENCE bact_id;

CREATE SEQUENCE bact_id
INCREMENT BY 1
START WITH -1
MINVALUE -1
MAXVALUE 9999
NOCYCLE;

-----
DROP TABLE Bacteriums;

CREATE TABLE Bacteriums(
b_id INTEGER NOT NULL,
p_id INTEGER,
bname VARCHAR2(30),
weight INTEGER);

INSERT INTO Bacteriums VALUES(bact_id.nextval, NULL, 'Bacterium #'||bact_id.currval||'_adam', 7);


INSERT INTO Bacteriums
SELECT bact_id.nextval,
       dbms_random.value((SELECT MIN(b_id) FROM Bacteriums), bact_id.currval - 1), 
       'Bacterium #'||bact_id.currval,
       dbms_random.value(1,7)
FROM dual
CONNECT BY LEVEL <=10;

INSERT INTO Bacteriums VALUES(bact_id.nextval, NULL, 'Bacterium #'||bact_id.currval||'_null_weight', NULL);
INSERT INTO Bacteriums VALUES(bact_id.nextval, bact_id.currval - 1, 'Bacterium #'||bact_id.currval||'_null_weight_sun', NULL);
UPDATE Bacteriums SET bname = REPLACE(bname, 'sun', 'son'), weight = 1 WHERE b_id = 12;
--------------------------------------------------------
SELECT * FROM Bacteriums;


--TREE with CONNECT BY
--Îòîáðàçèòü:
--äåðåâî ïðîèñõîæäåíèÿ âñåõ áàêòåðèé,
--âåñ îòäåëüíî âçÿòîé áàêòåðèè, âåñ ñàìîé áàêòåðèè + âåñ âñåõ åå ïîòîìêîì,
--âåñ ñàìîé áàêòåðèè + âåñ âñåõ åå ïðåäêîâ,
--ïåðå÷èñëèòü ÷åðåç çàïÿòóþ ïðåäêîâ êëåòêè
SELECT LPAD('+——', 10*(LEVEL-1), ' ')||bname AS Tree, b_id AS "ID", p_id as  "PARENT",
       weight,
       (SELECT SUM(weight)
       FROM Bacteriums bb
       START WITH bb.b_id = b.b_id
       CONNECT BY PRIOR b_id = p_id) descen_sum,
       (SELECT SUM(weight)
       FROM Bacteriums bb
       START WITH bb.b_id = b.b_id
       CONNECT BY PRIOR p_id = b_id) prior_sum,
       --LEVEL lev,
       --CONNECT_BY_ISLEAF leaf,
       --CONNECT_BY_ROOT bname root,
       CASE
       WHEN (b_id = CONNECT_BY_ROOT b_id)
       THEN NULL
       ELSE SUBSTR(SYS_CONNECT_BY_PATH(PRIOR bname, ', '), 5)
       END life_path
FROM bacteriums b
START WITH p_id IS NULL
CONNECT BY PRIOR b_id = p_id
ORDER SIBLINGS BY bname;

--TREE with WITH RECURSIVE
--Îòîáðàçèòü:
--äåðåâî ïðîèñõîæäåíèÿ âñåõ áàêòåðèé,
--âåñ îòäåëüíî âçÿòîé áàêòåðèè, âåñ ñàìîé áàêòåðèè + âåñ âñåõ åå ïîòîìêîì,
--âåñ ñàìîé áàêòåðèè + âåñ âñåõ åå ïðåäêîâ,
--ïåðå÷èñëèòü ÷åðåç çàïÿòóþ ïðåäêîâ êëåòêè
WITH Rec (b_id, p_id, bname, lvl, weight, psum, tPath)
     AS (
        SELECT b_id, p_id, bname, 1 , weight, weight AS psum, bname AS tPath
        FROM Bacteriums WHERE p_id IS NULL
        UNION ALL
        SELECT b.b_id, b.p_id, b.bname, r.lvl + 1,
               b.weight, COALESCE(r.psum, 0) + COALESCE(b.weight, 0),
               r.tPath||CAST(', '||b.bname AS VARCHAR2(90)) AS tPath
        FROM Bacteriums b INNER JOIN Rec r ON b.p_id = r.b_id 
        )
        SEARCH DEPTH FIRST BY b_id SET ord
SELECT LPAD('+——', 10*(lvl-1), ' ')||bname AS Tree,-- lvl, ord,
       b_id AS "ID",
       p_id AS "PARENT",
       weight,
       (
       SELECT SUM(weight) --ñóììà ìàññ êëåòêè è åå ïîòîìêîâ
       FROM Bacteriums b
       WHERE b_id IN (
                     SELECT b_id --ñïèñîê id ñîñòîÿùåé èç id ñàìîé êëåòêè è åå ïîòîìêîâ
                     FROM Rec rr
                     WHERE rr.ord >= r.ord --ãäå ord íà õîäèòñÿ ìåæäó ñàìîé êëåòêîé(âêëþ÷èòåëüíî) è áëèçæàéøèì ÍÅ ïîòîìêîì(íå âêëþ÷èòåëüíî) èëè êîíöà òàáëèöû ïðè îòñóòñòâèè ÍÅ ïîòîìêà
                     AND rr.ord < COALESCE (
                                           (SELECT min(ord) FROM REC rrr WHERE rrr.ord > r.ord AND rrr.lvl <= r.lvl), --ïîèñê áëèçæàéøåãî ÍÅ ïîòîìêà èëè
                                           (SELECT MAX(ord) FROM Rec) + 1 --êîíöà òàáëèöû
                                           )
                     )
       ) AS descen_sum,
       psum AS prior_sum,
       REPLACE(REPLACE(tPath, ', '||bname), bname) AS life_path
FROM Rec r
ORDER BY ord;
------------------------------------------------------------------------------------------------------------------------------------------
                       -------------------------Ãëàâà 3. PIVOT, UNPIVOT è MERGE.-----------------------
------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------Òàáëöèöà ZOO---
DROP TABLE ZOO;

CREATE TABLE ZOO(
animal VARCHAR2(20) NOT NULL,
zoo_name VARCHAR(30) NOT NULL,
cnt INTEGER NOT NULL);


----------------Çàïîëíåíèå-----
INSERT INTO ZOO VALUES('Îáåçüÿíà',       'Öåíòðàëüíûé Çîîïàðê', 15);
INSERT INTO ZOO VALUES('Ñëîí',           'Öåíòðàëüíûé Çîîïàðê', 3);
INSERT INTO ZOO VALUES('Æèðàô',          'Öåíòðàëüíûé Çîîïàðê', 5);
INSERT INTO ZOO VALUES('Îð¸ë',           'Öåíòðàëüíûé Çîîïàðê', 2);
INSERT INTO ZOO VALUES('Íîñîðîã',        'Öåíòðàëüíûé Çîîïàðê', 3);
INSERT INTO ZOO VALUES('Ãèåíà',          'Öåíòðàëüíûé Çîîïàðê', 15);
INSERT INTO ZOO VALUES('Ëåâ',            'Öåíòðàëüíûé Çîîïàðê', 3);
INSERT INTO ZOO VALUES('Îáåçüÿíà',       'Çîîëýíä',             7);
INSERT INTO ZOO VALUES('Ñëîí',           'Çîîëýíä',             3);
INSERT INTO ZOO VALUES('Æèðàô',          'Çîîëýíä',             8);
INSERT INTO ZOO VALUES('Îð¸ë',           'Çîîëýíä',             12);
INSERT INTO ZOO VALUES('Íîñîðîã',        'Çîîëýíä',             9);
INSERT INTO ZOO VALUES('Ãèåíà',          'Çîîëýíä',             23);
INSERT INTO ZOO VALUES('Ëåâ',            'Çîîëýíä',             17);
INSERT INTO ZOO VALUES('Ëîñü',           'Çîîëýíä',             12);
INSERT INTO ZOO VALUES('Êàêàäó',         'Çîîëýíä',             23);
INSERT INTO ZOO VALUES('Ðûñü',           'Çîîëýíä',             19);
INSERT INTO ZOO VALUES('Îáåçüÿíà',       'Òèõàÿ ïîëÿíà',        5);
INSERT INTO ZOO VALUES('Æèðàô',          'Òèõàÿ ïîëÿíà',        2);
INSERT INTO ZOO VALUES('Íîñîðîã',        'Òèõàÿ ïîëÿíà',        1);
INSERT INTO ZOO VALUES('Ëîñü',           'Òèõàÿ ïîëÿíà',        3);

----------------------------
SELECT * FROM ZOO;

--1_1 (PIVOT) 
SELECT ANIMAL, COALESCE(zoo1_cnt, 0) "Öåíòðàëüíûé Çîîïàðê", COALESCE(zoo2_cnt, 0) "Çîîëýíä", COALESCE(zoo3_cnt, 0) "Òèõàÿ ïîëÿíà"
FROM ZOO
PIVOT(sum(cnt) cnt FOR zoo_name IN('Öåíòðàëüíûé Çîîïàðê' zoo1, 'Çîîëýíä' zoo2, 'Òèõàÿ ïîëÿíà' zoo3));

--1_2 (PIVOT)
SELECT zoo_name, Monkey_cnt Monkey, Elephant_cnt Elephant,
                 Giraffe_cnt Giraffe, Rhinoceros_cnt Rhinoceros,  Hyena_cnt Hyena,
                 Lion_cnt Lion, Cockatoo_cnt Cockatoo,  Elk_cnt Elk,  Eagle_cnt Eagle,  Lynx_cnt Lynx
FROM ZOO
PIVOT (SUM(cnt) AS cnt FOR animal IN ('Îáåçüÿíà' Monkey, 'Ñëîí' Elephant, 'Æèðàô' Giraffe,
                                     'Íîñîðîã' Rhinoceros, 'Ãèåíà' Hyena, 'Ëåâ' Lion,
                                     'Êàêàäó' Cockatoo, 'Ëîñü' Elk, 'Îð¸ë' Eagle, 'Ðûñü' Lynx));
                                     
----------MERGE----


SELECT * FROM Zoo;--Òàáëèöà äî èçìåíåíèÿ

--Ïîñòóïëåíèå æèâîòíûõ íà âðåìåííîå ïîëüçîâàíèå
MERGE INTO ZOO z
USING (SELECT 'Îð¸ë' animal, 'Öåíòðàëüíûé Çîîïàðê' zoo_name,  100 cnt FROM dual
       UNION ALL
       SELECT 'Îð¸ë',        'Çîîëýíä',                       200 FROM dual
       UNION ALL
       SELECT 'Ðûñü',        'Òèõàÿ ïîëÿíà',                  100 FROM dual) an
ON (z.zoo_name = an.zoo_name AND z.animal = an.animal)
WHEN MATCHED THEN
  UPDATE SET z.cnt = z.cnt + an.cnt
WHEN NOT MATCHED THEN
  INSERT(z.animal,  z.zoo_name,  z.cnt)
  VALUES(an.animal, an.zoo_name, an.cnt);
  
SELECT * FROM Zoo;--îòîáðàçèòü âðåìåííîå èçìåíåíèå

MERGE INTO ZOO z
USING (SELECT 'Îð¸ë' animal, 'Öåíòðàëüíûé Çîîïàðê' zoo_name,  100 cnt FROM dual
       UNION ALL
       SELECT 'Îð¸ë',        'Çîîëýíä',                       200 FROM dual
       UNION ALL
       SELECT 'Ðûñü',        'Òèõàÿ ïîëÿíà',                  100 FROM dual) an
ON (z.zoo_name = an.zoo_name AND z.animal = an.animal)
WHEN MATCHED THEN
  UPDATE SET z.cnt = z.cnt - an.cnt
  DELETE WHERE z.cnt = 0
WHEN NOT MATCHED THEN
  INSERT(z.animal,  z.zoo_name,  z.cnt)
  VALUES(an.animal, an.zoo_name, an.cnt);
  
SELECT * FROM Zoo;--îòîáðàçèòü êîíå÷íûé ðåçóëüòàò


--------------------------------Òàáëèöà Stock-----
DROP TABLE stocks;

CREATE TABLE Stocks(
city VARCHAR2(20) NOT NULL,
paint INTEGER NOT NULL,
board INTEGER NOT NULL,
toy INTEGER NOT NULL,
PC INTEGER NOT NULL
);


INSERT INTO Stocks VALUES('Ðîñòîâ', 100, 200, 300, 400);
INSERT INTO Stocks VALUES('Ìîñêâà', 500, 600, 700, 800);
---------------------------

SELECT * FROM Stocks;

--2 (UNPIVOT)
SELECT * FROM Stocks
UNPIVOT (cnt FOR type_ IN (paint, board, toy, pc));


------------------------------------------------------------------------------------------------------------------------------------------
                             -------------------------Ãëàâà 4. Áëîêèðîâêè.-----------------------
------------------------------------------------------------------------------------------------------------------------------------------

DROP SEQUENCE people_seq;

CREATE SEQUENCE people_seq
INCREMENT BY 1
START WITH 1
MAXVALUE 999999
CYCLE;

CREATE OR REPLACE FUNCTION get_people_id RETURN VARCHAR IS
BEGIN
   RETURN SUBSTR(CONCAT('00000', people_seq.nextval), -6);
END;

-----Ñîçäàíèå òàáëèöû---
DROP TABLE People;

CREATE TABLE People(
id_p VARCHAR2(6) NOT NULL,
NAME VARCHAR2(50),
age INTEGER,
PRIMARY KEY (id_p));

------çàïîëíåíèå PEOPLE-----
TRUNCATE TABLE People;

              --<<<<<<<<<<<<<<<<<<<<áëîêèðîâêè>>>>>>>>>>>>>>>>>>>--
                                                            | 1 | 2 | 3 | 4 | 5 | sel | ins | upd | del |
LOCK TABLE People IN ROW SHARE MODE NOWAIT;          -- <1> | 1 | 2 | 3 | 4 |   | sel | ins | upd | del |
LOCK TABLE People IN ROW EXCLUSIVE MODE NOWAIT;      -- <2> | 1 | 2 |   |   |   | sel | ins | upd | del |
LOCK TABLE People IN SHARE MODE NOWAIT;              -- <3> | 1 |   | 3 |   |   | sel |     |     |     |
LOCK TABLE People IN SHARE ROW EXCLUSIVE MODE NOWAIT;-- <4> | 1 |   |   |   |   | sel |     |     |     |
LOCK TABLE People IN EXCLUSIVE MODE NOWAIT;          -- <5> |   |   |   |   |   | sel |     |     |     |
              --<<<<<<<<<<<<<<<<<<<<áëîêèðîâêè>>>>>>>>>>>>>>>>>>>--
SELECT * FROM People;

INSERT INTO people
SELECT get_people_id, 'Oleg', dbms_random.value(1, 100)
FROM dual
CONNECT BY LEVEL <= 1;
COMMIT;
--------âûâîä òàáëèöû----
SELECT * FROM People;
SELECT max(id_p) max_id FROM People;

------------------------------------------------------------------------------------------------------------------------------------------
                           -------------------------Ãëàâà 5. Àíàëèòè÷åñêèå ôóíêöèè.-----------------------
------------------------------------------------------------------------------------------------------------------------------------------
--ïðèâåðû ñ ñàéòà:

Create table PersonA(Tbn number primary key, name varchar2(20), otd number, sal number); 
--Òàáåëüíûé íîìåð , èìÿ, îòäåë , çàðïëàòà

Insert into PersonA(Tbn,name,otd,sal) values(1, 'Àíÿ',10,9000);
Insert into PersonA(Tbn,name,otd,sal) values(2, 'Ñàøà',10,5500);
Insert into PersonA(Tbn,name,otd,sal) values(3, 'Òàíÿ',10,7000);
Insert into PersonA(Tbn,name,otd,sal) values(4, 'Âàíÿ',20,2300);
Insert into PersonA(Tbn,name,otd,sal) values(5, 'Îëåã',20,4300);
Insert into PersonA(Tbn,name,otd,sal) values(6, 'Êîëÿ',20,3900);
Insert into PersonA(Tbn,name,otd,sal) values(7, 'Òàíÿ',30,7000);
Insert into PersonA(Tbn,name,otd,sal) values(8, 'Ìàêñ',30,9000);
Insert into PersonA(Tbn,name,otd,sal) values(9, 'Òàíÿ',30,8500);
Insert into PersonA(Tbn,name,otd,sal) values(10,'Ìàêñ',30,9900);
Insert into PersonA(Tbn,name,otd,sal) values(11,'Îëåã',30,9900);
Insert into PersonA(Tbn,name,otd,sal) values(12,'Ìàêñ',30,9900);
Insert into PersonA(Tbn,name,otd,sal) values(13,'Ìàêñ',30,9900);
Insert into PersonA(Tbn,name,otd,sal) values(14,'Ìàêñ',30,9900);
Insert into PersonA(Tbn,name,otd,sal) values(15,'Ìàêñ',30,9900);
Insert into PersonA(Tbn,name,otd,sal) values(16,'Ìàêñ',30,7000);
Insert into PersonA(Tbn,name,otd,sal) values(17,'Òàíÿ',30,3500);


--Çàïðîñû ñïèñêà ëèäåðîâ
--Ïåðâûå òðè ñîòðóäíèêà ñ ñàìîé âûñîêîé çàðïëàòîé ïî îòäåëàì (ïàðòèøåí ïî îòäåëó)
select * 
from ( 
     select NAME, otd, sal, row_number() over (partition by otd order by sal desc) AS num 
     from personA
     ) 
where num<4;

--Áîëåå êîððåêòíî 
select * 
from ( 
     select NAME, otd, sal, rank() over (partition by otd order by sal desc) as num 
     from personA
     ) 
where num<4;

--Ïî íàèìåíîâàíèþ (ïàðòèøåí ïî îòäåëó) ñîðòèðîâêà ïî name
select * 
from ( 
     select NAME, otd, sal, row_number() over (partition by otd order by name) as num 
     from personA
     ) 
where num<4;

--Íàêîïèòåëüíûé èòîã ïî çàðïëàòå 
select NAME, otd, sal, sum(sal) over (partition by otd order by sal) as num 
from personA;

--Ñðåäíåå ïî çàðïëàòå â ðàìêàõ îòäåëà
select NAME, otd, sal,  avg(sal) over (partition by otd order by sal) as num 
from personA;

--Äåìîíñòðàöèÿ ðàáîòû lag, leed - ñîòðóäíèê , îòäåë , çàðïëàòà , ñîòðóäíèê ñ áîëåå áîëüøåé çàïëàòîé (maxsl),
--ñîòðóäíèê ñ ìåíåå ìåíüøåé çàïëàòîé ÷åì äàííûé(minsl) â ðàìêàõ îòäåëà
select NAME, otd, sal, 
       lead(NAME, 1) over (partition by otd order by sal) as maxsl,
       lag(name,1) over (partition by otd order by sal) as minsl
from personA;


--çàïðîñ äåìîíñòðèðóåò êîíñòðóêöèþ îêíà â ðàìêàõ îòäåëà , ñðåäíåå ïî çàðïëàòå,
--âû÷èñëÿåòñÿ, íå òîëüêî â ðàìêàõ îòäåëà , íî òàê æå è â ðàìêàõ îêíà èç 3õ ñòðîê
SELECT NAME, otd, sal, 
       AVG(sal) OVER(
                     partition by otd 
                     order by sal 
                     ROWS 
                     BETWEEN 1 PRECEDING 
                     AND
                     CURRENT ROW
                     ) as num 
from personA;


--ýòîò çàïðîñ äåìîíñòðèðóåò ïðèìåíåíèå àíàëèòè÷åñêèõ ôóíêöèé first_value last_value
select NAME, otd, sal,
       first_value(name) over (partition by otd) as maxsl,
       last_value(name) over (partition by otd) as minsl
from personA;

---------------------ìîè ïðèìåðû:
CREATE TABLE pers(
ID INTEGER PRIMARY KEY,
NAME VARCHAR2(20),
salary INTEGER,
dept VARCHAR(10)
);

INSERT INTO pers (id, NAME) VALUES(1,  'Êîëÿ');
INSERT INTO pers (id, NAME) VALUES(2,  'Àíòîí');
INSERT INTO pers (id, NAME) VALUES(3,  'Êèðèëë');
INSERT INTO pers (id, NAME) VALUES(4,  'Òàíÿ');
INSERT INTO pers (id, NAME) VALUES(5,  'Àíòîí');
INSERT INTO pers (id, NAME) VALUES(6,  'Êîëÿ');
INSERT INTO pers (id, NAME) VALUES(7,  'Êèðèëë');
INSERT INTO pers (id, NAME) VALUES(8,  'Ñàøà');
INSERT INTO pers (id, NAME) VALUES(9,  'Êèðèëë');
INSERT INTO pers (id, NAME) VALUES(10, 'Àíòîí');
INSERT INTO pers (id, NAME) VALUES(11, 'Êèðèëë');
INSERT INTO pers (id, NAME) VALUES(12, 'Àíÿ');
INSERT INTO pers (id, NAME) VALUES(13, 'Àíòîí');
INSERT INTO pers (id, NAME) VALUES(14, 'Àíòîí');
INSERT INTO pers (id, NAME) VALUES(15, 'Ñàøà');
INSERT INTO pers (id, NAME) VALUES(16, 'Àíÿ');
INSERT INTO pers (id, NAME) VALUES(17, 'Êîëÿ');
INSERT INTO pers (id, NAME) VALUES(18, 'Êèðèëë');
INSERT INTO pers (id, NAME) VALUES(19, 'Òàíÿ');
INSERT INTO pers (id, NAME) VALUES(20, 'Êèðèëë');
INSERT INTO pers (id, NAME) VALUES(21, 'Àíÿ');
INSERT INTO pers (id, NAME) VALUES(22, 'Òàíÿ');
INSERT INTO pers (id, NAME) VALUES(23, 'Àíòîí');
INSERT INTO pers (id, NAME) VALUES(24, 'Àíÿ');
INSERT INTO pers (id, NAME) VALUES(25, 'Êèðèëë');
INSERT INTO pers (id, NAME) VALUES(26, 'Àíÿ');
INSERT INTO pers (id, NAME) VALUES(27, 'Êîëÿ');
INSERT INTO pers (id, NAME) VALUES(28, 'Ñàøà');
INSERT INTO pers (id, NAME) VALUES(29, 'Àíÿ');
INSERT INTO pers (id, NAME) VALUES(30, 'Àíòîí');
INSERT INTO pers (id, NAME) VALUES(31, 'Êèðèëë');
INSERT INTO pers (id, NAME) VALUES(32, 'Àíÿ');
INSERT INTO pers (id, NAME) VALUES(33, 'Òàíÿ');
INSERT INTO pers (id, NAME) VALUES(34, 'Êèðèëë');
INSERT INTO pers (id, NAME) VALUES(35, 'Àíòîí');

UPDATE pers s SET 
dept = CASE (
             SELECT dptn 
             FROM  (
                   SELECT p.*, NTILE(3) OVER(ORDER BY ID) AS dptn
                   FROM pers p 
                   ) t 
             WHERE t.id = s.id
             )
       WHEN 1
         THEN 'Ðîñòîâ'
       WHEN 2
         THEN 'Òàãàíðîã'
       WHEN 3
         THEN 'Àçîâ'
       END,
salary = CAST(dbms_random.value(1,9) AS INTEGER)*1000;

SELECT * FROM Pers;

--Ïîñ÷èòàòü äëÿ ñðåäíþþ çàðïëàòó äëÿ òåêóùåé ïîçèöèè è äâóõ êðàéíèõ(ïî îäíîé ñëåâà è ñïðàâà) â ðàìêàõ îäíîãî îòäåëà
SELECT ID, NAME, dept, salary,
       AVG(salary) OVER(
                        PARTITION BY dept 
                        ORDER BY salary 
                        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
                       ) AS avg_salary
FROM pers;

--Ïîñ÷èòàòü êîððåëÿöèþ ìåæäó çàðïëàòîé è ðàçìåðîì ïðåìèè â êàæäîì îòäåëå
SELECT ID, NAME, dept, salary,
       cast(prize AS NUMERIC) AS prize,
       CAST(CORR(salary, prize) OVER(PARTITION BY dept ORDER BY id) AS NUMERIC(5,3)) AS correlation
FROM (
      SELECT p.*, salary * (dbms_random.value(1, 3)/10) AS prize
      FROM pers p
      ) t;
      
--ïîñ÷èòàòü ñóììó çàðïëàò äëÿ òåêóùåãî è äëÿ ïðåäûäóùèõ ó êîòîðûõ çï íå ÌÅÍÜØÅ 1000 îòíîñèòåëüíî òåêóùåé
--ïîñ÷èòàòü ñóììó çàðïëàò äëÿ òåêóùåãî è äëÿ ïðåäûäóùèõ ó êîòîðûõ çï íå ÁÎËÜØÅ 1000 îòíîñèòåëüíî òåêóùåé
SELECT ID, NAME, dept, salary,
       SUM(salary) OVER(PARTITION BY dept ORDER BY salary ASC RANGE 1000 PRECEDING ) AS summ_before,
       SUM(salary) OVER(PARTITION BY dept ORDER BY salary DESC RANGE 1000 PRECEDING) AS summ_after
FROM Pers p

--äëÿ êàæäîé ñòðîêè îòîáðàçèòü èìÿ ÷åÿ ç/ï ìåíüøå è áîëüøå ç/ï ó òåêóùåé ñòðîêè
--îòîáðàçèòü èìÿ öåëîâåêà ñ ñàìîé áîëüøîé/ìàëüêîé ç/ï â îòäåëå
SELECT ID, NAME, dept, salary
       LEAD(NAME) OVER(PARTITION BY dept ORDER BY salary) ,
       LAG(NAME) OVER(PARTITION BY dept ORDER BY salary)
FROM Pers;
ORDER BY dept, salary;

------------------------------------------------------------------------------------------------------------------------------------------
                           -------------------------Ãëàâà 6. Òàáëèöà ïåðåâîäîâ.-----------------------
------------------------------------------------------------------------------------------------------------------------------------------

DROP TABLE trans2;
CREATE TABLE trans2(
id_from INTEGER NOT NULL,
id_to INTEGER NOT NULL,
dat DATE NOT NULL,
val INTEGER NOT NULL);

LOCK TABLE Savalek.trans2 IN EXCLUSIVE MODE NOWAIT;

--Ñíà÷àëà íàõîäèì ID òàáëèöû.
select * from all_objects where object_name = 'TRANS';
--Çàòåì íàõîäèì ID ñåññèè, êîòîðàÿ áëîêèðóåò ýòó òàáëèöó.
select sid from v$lock where id1 = 188162 or id2 = 188556;
--Çàòåì íàõîäèì ñåðèéíûé íîìåð ñåññèè
select sid, serial# from v$session where sid = 134
--À ïîòîì ïðèáèâàåì ñåññèþ ê ÷åðòÿì. Ïàðàìåòð — sid || ',' || serial#
alter system kill session '134,9107' immediate

----Ãåíåðèðîâàíèå òàáëèöû
TRUNCATE TABLE trans2;
INSERT INTO trans2
SELECT dbms_random.value(1, 3), --ìíîæåñòâî id_from 
       dbms_random.value(1, 3), --ìíîæåñòâî id_to 
       SUBSTR(CONCAT('00', cast(dbms_random.value(1, 19) AS INTEGER)), -2)||'/'||  --äåíü
       SUBSTR(CONCAT('00', cast(dbms_random.value(5, 7) AS INTEGER)), -2)||'/'||   --ìåñÿö
       SUBSTR(CONCAT('0000', cast(dbms_random.value(2017, 2017) AS INTEGER)), -4), --ãîä
       cast(dbms_random.value(1, 5) AS INTEGER) * 10 --ñóììà ïåðåâîäîâ
FROM dual
CONNECT BY LEVEL<=100;
DELETE FROM trans2
WHERE id_from = id_to;


INSERT INTO trans2 VALUES(1, 4, '19/07/2017', 1000000);

DELETE FROM trans2 WHERE dat = '19/07/2017' AND val = 1000000;
SELECT * FROM trans2;

----Òàáëèöà äëÿ õðàíåíèÿ èíòåðâàëîâ
DROP TABLE interval_state_acc;
CREATE TABLE interval_state_acc
(
id_acc INTEGER NOT NULL,
int_beg DATE,
int_end DATE,
state_acc INTEGER NOT NULL,
state_change INTEGER
);

----Ïðîñ÷¸ò ïåðèîäîâ äëÿ âñåõ òðàíçàêöèé (Åäèíè÷íûé çàïðîñ)
TRUNCATE TABLE interval_state_acc;
INSERT INTO INTERVAL_STATE_ACC
  SELECT ID_ACC,
         LAG(DAT, 1) OVER(PARTITION BY ID_ACC ORDER BY DAT) AS INT_BEG,
         DAT AS INT_END,
         COALESCE(SUM(VAL)
                  OVER(PARTITION BY ID_ACC ORDER BY DAT ASC
                       ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
                  0) AS STATE_ACC,
         val AS state_change
    FROM (SELECT ID_ACC, DAT, SUM(VAL) VAL
            FROM (SELECT ID_ACC, DAT, SUM(VAL) VAL
                    FROM (SELECT ID_FROM AS ID_ACC, DAT, -VAL AS VAL
                            FROM trans2
                          UNION ALL
                          SELECT ID_TO AS ID_ACC, DAT, VAL AS VAL FROM trans2) TTT
                   GROUP BY ID_ACC, DAT
                  HAVING SUM(VAL) <> 0) TT
           GROUP BY GROUPING SETS((ID_ACC, DAT, VAL),(ID_ACC))) T;
         
SELECT * FROM INTERVAL_STATE_ACC;

----Îáíîâëåíèå áàçû interval_state_acc (×àñòî âûïîëíÿåìûé çàïðîñ)
DELETE FROM interval_state_acc
WHERE int_end > SYSDATE - INTERVAL '20' DAY
   OR int_beg > SYSDATE - INTERVAL '20' DAY;

INSERT INTO INTERVAL_STATE_ACC
  SELECT ID_ACC,
         COALESCE(LAG(DAT, 1) OVER(PARTITION BY ID_ACC ORDER BY DAT),
                  (SELECT ISA.INT_END
                     FROM INTERVAL_STATE_ACC ISA
                    WHERE ISA.ID_ACC = T.ID_ACC
                      AND ISA.INT_END =
                          (SELECT MAX(ISA2.INT_END)
                             FROM INTERVAL_STATE_ACC ISA2
                            WHERE ISA2.ID_ACC = ISA.ID_ACC))) AS INT_BEG,
         DAT AS INT_END,
         COALESCE(SUM(VAL)
                  OVER(PARTITION BY ID_ACC ORDER BY DAT ASC
                       ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
                  0) + COALESCE((SELECT ISA.STATE_ACC
                                   FROM INTERVAL_STATE_ACC ISA
                                  WHERE ISA.ID_ACC = T.ID_ACC
                                    AND ISA.INT_END =
                                        (SELECT MAX(ISA2.INT_END)
                                           FROM INTERVAL_STATE_ACC ISA2
                                          WHERE ISA2.ID_ACC = ISA.ID_ACC)) +
                                (SELECT ISA.STATE_CHANGE
                                   FROM INTERVAL_STATE_ACC ISA
                                  WHERE ISA.ID_ACC = T.ID_ACC
                                    AND ISA.INT_END =
                                        (SELECT MAX(ISA2.INT_END)
                                           FROM INTERVAL_STATE_ACC ISA2
                                          WHERE ISA2.ID_ACC = ISA.ID_ACC)),
                                0) AS STATE_ACC,
         VAL
    FROM (SELECT ID_ACC, DAT, SUM(VAL) VAL
            FROM (SELECT ID_ACC, DAT, SUM(VAL) AS VAL
                    FROM (SELECT TTTT.*,
                                 ROW_NUMBER() OVER(PARTITION BY ID_ACC ORDER BY DAT) AS ROW_NUM
                            FROM (SELECT ID_FROM AS ID_ACC, DAT, -VAL AS VAL
                                    FROM trans2
                                   WHERE DAT >= SYSDATE - INTERVAL '20' DAY
                                  UNION ALL
                                  SELECT ID_TO AS ID_ACC, DAT, VAL AS VAL
                                    FROM trans2
                                   WHERE DAT >= SYSDATE - INTERVAL '20' DAY) TTTT) TTT
                   GROUP BY ID_ACC, DAT
                  HAVING SUM(VAL) <> 0) TT
           GROUP BY GROUPING SETS((ID_ACC, DAT, VAL),(ID_ACC))) T;

SELECT * FROM INTERVAL_STATE_ACC;

----Ïðåäñòàâëåíèå äëÿ îòîáðàæåíèÿ ïåðèîäîâ
CREATE OR REPLACE VIEW interval_state_acc_view AS
SELECT id_acc as "id",
       COALESCE(CAST(int_beg AS VARCHAR(10)), 'Íà÷àëî Âðåì¸í') AS "Íà÷àëî ïåðèîäà",
       COALESCE(CAST(int_end AS VARCHAR(10)), 'Êîíåö Âðåì¸í') AS "Êîíåö ïåðèîäà", 
       state_acc AS "Ñîñòîÿíèå ñ÷åòà"
FROM interval_state_acc
ORDER BY id_acc, int_end;
  
SELECT * FROM interval_state_acc_view;
SELECT * FROM interval_state_acc ORDER BY id_acc, int_beg;

-----------------------------------------Âàðèàíò c MERGE-------------------------------------------
----Òàáëèöà äëÿ õðàíåíèÿ èíòåðâàëîâ (Merge)
DROP TABLE interval_state_acc_merge2;
CREATE TABLE interval_state_acc_merge2
(
int_num INTEGER NOT NULL,
id_acc INTEGER NOT NULL,
int_beg DATE,
int_end DATE,
state_acc INTEGER NOT NULL,
state_change INTEGER
);

----Ïðîñ÷¸ò ïåðèîäîâ äëÿ âñåõ òðàíçàêöèé (Åäèíè÷íûé çàïðîñ) (Merge)
TRUNCATE TABLE interval_state_acc_merge2;
INSERT INTO interval_state_acc_merge2
  SELECT row_number() OVER(PARTITION BY id_acc ORDER BY DAT) int_num,
         ID_ACC,
         LAG(DAT, 1) OVER(PARTITION BY ID_ACC ORDER BY DAT) AS INT_BEG,
         DAT AS INT_END,
         COALESCE(SUM(VAL)
                  OVER(PARTITION BY ID_ACC ORDER BY DAT ASC
                       ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
                  0) AS STATE_ACC,
         val AS state_change
    FROM (SELECT ID_ACC, DAT, SUM(VAL) VAL
            FROM (SELECT ID_ACC, DAT, SUM(VAL) VAL
                    FROM (SELECT ID_FROM AS ID_ACC, DAT, -VAL AS VAL
                            FROM trans2
                          UNION ALL
                          SELECT ID_TO AS ID_ACC, DAT, VAL AS VAL FROM trans2) TTT
                   GROUP BY ID_ACC, DAT
                  HAVING SUM(VAL) <> 0) TT
           GROUP BY GROUPING SETS((ID_ACC, DAT, VAL),(ID_ACC))) T;

SELECT * FROM interval_state_acc_merge2;
--Function (Merge) --(Íå ó÷àñòâóåò â ðàáîòå)
CREATE OR REPLACE FUNCTION GET_LAST_DATE(ID_ACC IN NUMBER) RETURN DATE IS
BEGIN
  RETURN(
       SELECT MAX(DATE)
       FROM interval_state_acc_merge2 ISA
       WHERE ISA.ID_ACC = ID_ACC
       AND INT_END <= SYSDATE - INTERVAL '20' DAY);
END GET_LAST_DATE;

SELECT GET_LAST_DATE(1) FROM dual;

---view
CREATE OR REPLACE VIEW new_isa AS  
SELECT O.*, MAX(int_num) OVER(PARTITION BY id_acc) AS int_max
FROM (
        SELECT ROW_NUMBER() OVER(PARTITION BY ID_ACC ORDER BY DAT) + COALESCE(((SELECT MAX(INT_NUM) FROM interval_state_acc_merge2 ISA
                                                                        WHERE ISA.ID_ACC = T.ID_ACC AND ISA.INT_END <= SYSDATE - INTERVAL '20' DAY)), 0) INT_NUM,
               ID_ACC,
               COALESCE(LAG(DAT, 1) OVER(PARTITION BY ID_ACC ORDER BY DAT),
                       (SELECT MAX(INT_END) FROM interval_state_acc_merge2 ISA
                        WHERE ISA.ID_ACC = T.ID_ACC  AND ISA.INT_END <= SYSDATE - INTERVAL '20' DAY)
                        ) AS INT_BEG,
               DAT AS INT_END,
               COALESCE(SUM(VAL) OVER(PARTITION BY ID_ACC ORDER BY DAT ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) + 
                        COALESCE((SELECT ISA.STATE_ACC + ISA.STATE_CHANGE
                                  FROM interval_state_acc_merge2 ISA
                                  WHERE ISA.ID_ACC = T.ID_ACC
                                  AND INT_END = (SELECT MAX(INT_END) FROM interval_state_acc_merge2 ISA
                                                 WHERE ISA.ID_ACC = T.ID_ACC AND ISA.INT_END <= SYSDATE - INTERVAL '20' DAY)),
                        0) AS STATE_ACC,
               VAL AS state_change
          FROM (SELECT ID_ACC, DAT, SUM(VAL) VAL
                  FROM (SELECT ID_ACC, DAT, SUM(VAL) AS VAL
                        FROM (SELECT TTTT.*, ROW_NUMBER() OVER(PARTITION BY ID_ACC ORDER BY DAT) AS ROW_NUM
                              FROM (SELECT ID_FROM AS ID_ACC, DAT, -VAL AS VAL
                                    FROM trans2
                                    WHERE DAT >= SYSDATE - INTERVAL '20' DAY
                                    UNION ALL
                                    SELECT ID_TO AS ID_ACC, DAT, VAL AS VAL
                                    FROM trans2
                                    WHERE DAT >= SYSDATE - INTERVAL '20' DAY
                                    ) TTTT
                             ) TTT
                        GROUP BY ID_ACC, DAT
                        HAVING SUM(VAL) <> 0) TT
                 GROUP BY GROUPING SETS((ID_ACC, DAT, VAL),(ID_ACC))) T
     ) O;


--merge
MERGE INTO interval_state_acc_merge2 isa
USING ( SELECT * FROM new_isa) n_isa 
ON ( isa.id_acc = n_isa.id_acc AND isa.int_num = n_isa.int_num)
  WHEN MATCHED THEN UPDATE
    SET isa.int_beg = n_isa.int_beg,
        isa.int_end = n_isa.int_end,
        isa.state_acc = n_isa.state_acc,
        isa.state_change = n_isa.state_change
        --WHERE isa.int_num = n_isa.int_num
    --DELETE WHERE isa.int_num > n_isa.int_max 
WHEN NOT MATCHED THEN INSERT (isa.int_num, isa.id_acc, isa.int_beg, isa.int_end, isa.state_acc, isa.state_change)
  VALUES(n_isa.int_num, n_isa.id_acc, n_isa.int_beg, n_isa.int_end, n_isa.state_acc, n_isa.state_change);

--âìåñòå ñ merge (÷èñòêà ñòàðûõ çàïèñåé è óäàëåíèå íå àêòóàëüíûõ àêêàóíòîâ)
DELETE FROM interval_state_acc_merge2 i
WHERE int_num > (SELECT MAX(int_num) FROM new_isa ii WHERE i.id_acc = ii.id_acc)
OR 0 = (SELECT COUNT(*) FROM new_isa ii WHERE i.id_acc = ii.id_acc);

--temp#################
SELECT * FROM interval_state_acc_merge2;
SELECT * FROM interval_state_acc_merge2_view;

INSERT INTO trans2 VALUES(1, 4, '19/07/2017', 1000000);

DELETE FROM trans2 WHERE val = 1000000;
COMMIT;
SELECT * FROM trans2;
--temp#################
----Ïðåäñòàâëåíèå äëÿ îòîáðàæåíèÿ ïåðèîäîâ (Merge)
CREATE OR REPLACE VIEW interval_state_acc_merge2_view AS
SELECT id_acc as "id",
       COALESCE(CAST(int_beg AS VARCHAR(10)), 'Íà÷àëî Âðåì¸í') AS "Íà÷àëî ïåðèîäà",
       COALESCE(CAST(int_end AS VARCHAR(10)), 'Êîíåö Âðåì¸í') AS "Êîíåö ïåðèîäà", 
       state_acc AS "Ñîñòîÿíèå ñ÷åòà"
FROM interval_state_acc_merge2
ORDER BY id_acc, int_end;
  
SELECT * FROM interval_state_acc_merge2_view;
SELECT * FROM interval_state_acc ORDER BY id_acc, int_beg;

------------------------------------------------------------------------------------------------------------------------------------------
                           -------------------------Ãëàâà 7. Òàðèôû è Òðàôèê. 100 äíåâíûé ïåðèîä.-----------------------
------------------------------------------------------------------------------------------------------------------------------------------
DROP TABLE Tariff;
CREATE TABLE Tariff
(
id_user NUMBER(10) NOT NULL,
tar_dat DATE NOT NULL,
lim NUMBER(20) CHECK(lim >= 0),
PRIMARY KEY(id_user, tar_dat)
);

DROP TABLE Traffic;
CREATE TABLE Traffic
(
id_user NUMBER(10) NOT NULL,
dat DATE NOT NULL,
amt NUMBER(20)
);

TRUNCATE TABLE Traffic;
TRUNCATE TABLE Tariff;
--user 1 (fair)   +
INSERT INTO traffic VALUES(1, to_date('01/01/2000', 'dd/mm/yyyy'), 100);
INSERT INTO traffic VALUES(1, to_date('01/02/2000', 'dd/mm/yyyy'), 600);
--user 2 (unfair) -
INSERT INTO tariff VALUES (2, to_date('01/01/2000', 'dd/mm/yyyy'), 1000);
INSERT INTO traffic VALUES(2, to_date('01/02/2000 16:00', 'dd/mm/yyyy HH24:MI'), 200);
INSERT INTO traffic VALUES(2, to_date('01/02/2000 16:02', 'dd/mm/yyyy HH24:MI'), 600);
INSERT INTO traffic VALUES(2, to_date('01/03/2000', 'dd/mm/yyyy'), 300);
--user 3 (fair)   +
INSERT INTO tariff VALUES (3, to_date('01/01/2000', 'dd/mm/yyyy'), 1000);
INSERT INTO traffic VALUES(3, to_date('01/02/2000', 'dd/mm/yyyy'), 200);
INSERT INTO traffic VALUES(3, to_date('01/06/2000', 'dd/mm/yyyy'), 200);
--user 4 (unfair) -
INSERT INTO tariff VALUES (4, to_date('01/01/2000', 'dd/mm/yyyy'), 1000);
INSERT INTO traffic VALUES(4, to_date('01/02/2000', 'dd/mm/yyyy'), 900);
INSERT INTO traffic VALUES(4, to_date('01/03/2000', 'dd/mm/yyyy'), 500);
INSERT INTO tariff VALUES (4, to_date('01/04/2000', 'dd/mm/yyyy'), 10000);
--user 5 (fair)   +
INSERT INTO tariff VALUES (5, to_date('01/01/2000', 'dd/mm/yyyy'), 1000);
INSERT INTO traffic VALUES(5, to_date('01/05/2000', 'dd/mm/yyyy'), 500);
INSERT INTO traffic VALUES(5, to_date('01/05/2001', 'dd/mm/yyyy'), 600);
--user 6 (unfair) -
INSERT INTO tariff VALUES (6, to_date('01/01/2000', 'dd/mm/yyyy'), 1000);
INSERT INTO traffic VALUES(6, to_date('01/02/2000', 'dd/mm/yyyy'), 900);
INSERT INTO traffic VALUES(6, to_date('01/04/2000', 'dd/mm/yyyy'), 50);
INSERT INTO tariff VALUES (6, to_date('01/05/2000', 'dd/mm/yyyy'), 900);
--user 7 (fair)   +
INSERT INTO traffic VALUES(7, to_date('01/01/2000', 'dd/mm/yyyy'), 2000);
INSERT INTO tariff VALUES (7, to_date('01/01/2001', 'dd/mm/yyyy'), 1000);
--user 8 (fair)   +
INSERT INTO tariff VALUES(8, to_date('01/01/2000', 'dd/mm/yyyy'), 2000);


-- äëÿ view:     
CREATE OR REPLACE VIEW list_ AS
SELECT row_number() OVER(PARTITION BY id_user ORDER BY dat) num_, t.*
FROM (
      SELECT id_user, 'Òàðèô' type_, lim count_, tar_dat dat
      FROM Tariff
      UNION
      SELECT id_user, 'Òðàôèê' type_, count_, dat
      FROM (
           SELECT id_user, sum(amt) count_, trunc(dat) dat
           FROM Traffic
           GROUP BY id_user, trunc(dat)
           ) tt
      UNION
      SELECT id_user, 'Òàðèô' type_, (999999) count_, to_date('01/01/1000', 'dd/mm/yyyy') dat -- 99..99 -íåïðåîäîëèìûé ëèìèò; 1000 ãîä ìåíüøèé ëþáîãî âîçìîæíîãî
      FROM (SELECT id_user FROM traffic) tt
     ) t;
------Âûâîä þçåðîâ:
WITH t_list AS
(SELECT id_user, count_, dat
FROM list_ l WHERE type_ = 'Òàðèô'
ORDER BY id_user, dat DESC)
SELECT id_user, type_, dat, sum_before_100, limit_before_100, CASE WHEN sum_before_100 <= limit_before_100 THEN '+' ELSE 'CAUGTH' END AS check1,
       sum_after_100, limit_after_100, CASE WHEN sum_after_100 <= limit_after_100 THEN '+' ELSE 'CAUGTH' END AS check2
FROM( SELECT l.*,
           SUM(CASE type_ WHEN 'Òðàôèê' THEN count_ ELSE 0 END) 
           OVER(PARTITION BY id_user ORDER BY dat  RANGE 100 PRECEDING) sum_before_100,
           (SELECT count_ FROM t_list t WHERE t.id_user = l.id_user AND t.dat <= l.dat AND ROWNUM = 1) limit_before_100,
           COALESCE(SUM(CASE type_ WHEN 'Òðàôèê' THEN count_ ELSE 0 END) 
           OVER(PARTITION BY id_user ORDER BY dat RANGE BETWEEN 1 FOLLOWING AND 101 FOLLOWING), 0) sum_after_100,
           (SELECT count_ FROM t_list t WHERE t.id_user = l.id_user AND t.dat <= l.dat + 100 AND ROWNUM = 1) limit_after_100
     FROM list_ l) temp
ORDER BY id_user, num_; 


------------------------------------------------------------------------------------------------------------------------------------------
                     -------------------------Ãëàâà 8. Òàðèôû è Òðàôèê. Ïåðèîä ïðèâÿçàííûé ê òàðèôó.-----------------------
------------------------------------------------------------------------------------------------------------------------------------------
DROP TABLE Tar;
CREATE TABLE Tar
(
id_user NUMBER(10) NOT NULL,
tar_dat DATE NOT NULL,
lim NUMBER(20) CHECK(lim >= 0),
interval_ NUMBER(7),
PRIMARY KEY(id_user, tar_dat)
);

DROP TABLE Traf;
CREATE TABLE Traf
(
id_user NUMBER(10) NOT NULL,
dat DATE NOT NULL,
amt NUMBER(20)
);

TRUNCATE TABLE Traf;
TRUNCATE TABLE Tar;
--user 1 (fair)   +
INSERT INTO traf VALUES(1, to_date('01/01/2000', 'dd/mm/yyyy'), 1200);
INSERT INTO traf VALUES(1, to_date('01/03/2000', 'dd/mm/yyyy'), 1800);
--user 2 (unfair) -
INSERT INTO traf VALUES(2, to_date('01/05/2000', 'dd/mm/yyyy'), 1000);
INSERT INTO tar VALUES (2, to_date('01/06/2000', 'dd/mm/yyyy'), 2500, 50);
INSERT INTO traf VALUES(2, to_date('01/07/2000', 'dd/mm/yyyy'), 2000);
--user 3 (fair)   +
INSERT INTO traf VALUES(3, to_date('01/02/2000', 'dd/mm/yyyy'), 6000);
INSERT INTO tar VALUES (3, to_date('01/05/2000', 'dd/mm/yyyy'), 10000, 300);
INSERT INTO traf VALUES(3, to_date('01/08/2000', 'dd/mm/yyyy'), 3000);
--user 4 (unfair)   -
INSERT INTO traf VALUES(4, to_date('01/01/1999', 'dd/mm/yyyy'), 3000);
INSERT INTO tar VALUES (4, to_date('01/06/2000', 'dd/mm/yyyy'), 1500, 100);
INSERT INTO traf VALUES(4, to_date('01/01/2002', 'dd/mm/yyyy'), 8000);
--user 5 (unfair) -
INSERT INTO tar VALUES (5, to_date('01/01/2000', 'dd/mm/yyyy'), 1000, 50);
INSERT INTO traf VALUES(5, to_date('01/02/2000', 'dd/mm/yyyy'), 800);
INSERT INTO tar VALUES (5, to_date('01/04/2000', 'dd/mm/yyyy'), 9000, 10);
INSERT INTO traf VALUES(5, to_date('01/09/2000', 'dd/mm/yyyy'), 400);
INSERT INTO tar VALUES (5, to_date('01/11/2000', 'dd/mm/yyyy'), 2000, 500);
INSERT INTO traf VALUES(5, to_date('01/01/2001', 'dd/mm/yyyy'), 900);
--user 6 (unfair) -
INSERT INTO tar VALUES (6, to_date('01/01/2000', 'dd/mm/yyyy'), 500, 100);
INSERT INTO traf VALUES(6, to_date('01/01/2010', 'dd/mm/yyyy'), 1000);
INSERT INTO tar VALUES (6, to_date('01/01/2015', 'dd/mm/yyyy'), 20, 100);
INSERT INTO traf VALUES(6, to_date('01/01/2016', 'dd/mm/yyyy'), 10);

--work
CREATE OR REPLACE VIEW check_view AS 
WITH tar_w AS
(SELECT * FROM tar ORDER BY tar_dat)
SELECT temp.*, CASE wHEN sum_ > lim THEN 'Ïîïàëñÿ' ELSE '+' END check_
FROM (SELECT t.id_user, t.tar_dat, t.interval_, t.lim, 
             SUM(amt) OVER(PARTITION BY t.id_user, t.tar_dat ORDER BY f.dat) sum_, f.amt,  f.dat
      FROM tar t, (SELECT id_user, TRUNC(dat) dat, sum(amt) amt
                   FROM traf 
                   GROUP BY id_user, TRUNC(dat)) f
      WHERE t.id_user = f.id_user AND ((f.dat >= t.tar_dat - t.interval_) AND 
                                       (f.dat <= t.tar_dat + t.interval_  
                                                OR t.tar_dat = (SELECT MAX(tar_dat) FROM traf tt 
                                                                WHERE tt.id_user = t.id_user)
                                                OR f.dat < (SELECT tt.tar_dat - tt.interval_ FROM tar_w tt 
                                                            WHERE tt.id_user = t.id_user AND t.tar_dat > tt.tar_dat AND ROWNUM = 1) ))
      ) temp
ORDER BY id_user, tar_dat;

--beatiful out
SELECT * FROM check_view
UNION ALL
SELECT null, null, null, null, null, null, null, 
       'Íàðóøèòåëè: '||listagg(id_user, ', ') WITHIN GROUP(ORDER BY id_user)
       FROM (SELECT DISTINCT id_user
             FROM check_view
             WHERE check_ = 'Ïîïàëñÿ') t;

------------------------------------------------------------------------------------------------------------------------------------------
                           -------------------------Ãëàâà (*?:;*"_¹2.&&&.-----------------------
------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE a
(
i INTEGER
);

CREATE TABLE b
(
i INTEGER
);

TRUNCATE TABLE a;
INSERT INTO a VALUES(1);
INSERT INTO a VALUES(2);
INSERT INTO a VALUES(3);
INSERT INTO a VALUES(4);
INSERT INTO a VALUES(7);
INSERT INTO a VALUES(8);

TRUNCATE TABLE b;
INSERT INTO b VALUES(1);
INSERT INTO b VALUES(1);
INSERT INTO b VALUES(2);
INSERT INTO b VALUES(3);
INSERT INTO b VALUES(8);
INSERT INTO b VALUES(10);

SELECT * FROM a;
SELECT * FROM b;

SELECT *
FROM a FULL JOIN b ON a.i = b.i;
------------------------------------------------------------------------------------------------------------------------------------------
                          -------------------------Ãëàâà @?"X_0. RECYCLE BIN.-----------------------
------------------------------------------------------------------------------------------------------------------------------------------
--CONNECT BY
--Ãåíåðàöèÿ n ïðîíóìåðîâàííûõ ñòðîê
SELECT ROWNUM-11 AS rn FROM dual
CONNECT BY LEVEL <=21;

--Ðàíäîì
-------
SELECT CAST(dbms_random.value(1,10) AS NUMERIC(2)) FROM dual;
------


--listagg
SELECT zoo_name, listagg(animal,', ') WITHIN GROUP(ORDER BY animal) animal_list
FROM Zoo
GROUP BY zoo_name;
--
SELECT DBMS_OUTPUT.PUT_LINE(add_ten)
FROM dual;


-------myJavaLibraryTest /begin------
--init 
CREATE OR REPLACE FUNCTION factorial(n IN NUMBER) RETURN NUMBER
AS LANGUAGE JAVA NAME 'SavalClass.fact(long) return long';

--test
SELECT factorial(LEVEL)
FROM dual
CONNECT BY LEVEL <= 30;

-------myJavaLibraryTest /start------
--JAVA----------------------------------------------
----------------------------------------------------------------------
DROP FUNCTION get_ten;

CREATE OR REPLACE FUNCTION add_one(n IN NUMBER) RETURN NUMBER
AS LANGUAGE JAVA NAME 'Adder.addOne (int) return int';

CREATE OR REPLACE FUNCTION mult_ten(n IN NUMBER) RETURN NUMBER
AS LANGUAGE JAVA NAME 'Adder.multTen (int) return int';

--JAVA TEST
SELECT add_one(10) AS a, mult_ten(5) AS b
FROM dual;
-
---------------------JAVA_SAVAL_MATH
--power  long
CREATE OR REPLACE FUNCTION sm_power(x IN NUMBER, st IN NUMBER) RETURN NUMBER
AS LANGUAGE JAVA NAME 'SavMath.power (int, int) return long';

SELECT LEVEL num_, sm_power(2, LEVEL)
FROM dual CONNECT BY LEVEL <= 100;
--power BigInteger 

--list files
CREATE OR REPLACE FUNCTION get_files_list(path_ IN VARCHAR2, max_deep IN NUMBER) RETURN VARCHAR2
AS LANGUAGE JAVA NAME 'SavMath.getFiles (java.lang.String, int) return String';

SELECT get_files_list FROM dual;

DECLARE
BEGIN
  dbms_output.put_line(get_files_list('c:\', 1));
END;

--ARRAY test
CREATE OR REPLACE TYPE fname_array IS VARRAY(3000) OF VARCHAR2(2000);
CREATE OR REPLACE FUNCTION get_array_files(arr IN fname_array) RETURN fname_array
AS LANGUAGE JAVA NAME '';

--httpsGet
CREATE OR REPLACE FUNCTION get_html(url_ IN VARCHAR2) RETURN VARCHAR2
AS LANGUAGE JAVA NAME 'HTTPGet.getHTML (java.lang.String) return String';

	
SET SERVEROUTPUT ON;
SET SERVEROUTPUT ON size '1000000';

BEGIN
  DBMS_OUTPUT.ENABLE(10000000);
  dbms_output.put_line(get_html('http://www.google.ru/'));
END;
--httpGet CLOB
CREATE OR REPLACE FUNCTION get_html_clob (url_ IN VARCHAR2) RETURN CLOB
AS LANGUAGE JAVA NAME 'HTTPGet.getHTML_clob (java.lang.String) return CLOB';

BEGIN
  dbms_output.ENABLE(1000000);
  dbms_output.put_line(get_html_clob('http://www.google.ru/'));
END;


DECLARE
clob_ CLOB;
vchar VARCHAR2(2000 CHAR);
i NUMBER(9);
BEGIN
  dbms_output.ENABLE(100000);
  clob_ := get_html_clob('http://www.google.ru/');
  i := 1;
  --FOR i IN 1..500
    LOOP
      vchar := SUBSTR(clob_, 1 + (i - 1)*100, 100);
      dbms_output.put_line(i||'<>'||(i - 1)*100||'<>'||i*100||'|'||vchar||'|');
      i := i + 1;
      EXIT wHEN i > 200 OR vchar IS NULL;
    END LOOP;
END;


SELECT get_html_clob('http://www.google.ru/') FROM DUAL;

--java example------------------------------------------------
DROP JAVA SOURCE "MyJavaProgInOracle";
DROP FUNCTION say_hello_from_java_to;

CREATE JAVA SOURCE NAMED "MyJavaProgInOracle" AS
public class MyJavaProgInOracle { 
       public static String sayHello (String toWhom) {
              return "Hello my dear friend, " + toWhom + "!";
       }
};

 
CREATE FUNCTION say_hello_from_java_to (to_whom IN VARCHAR2) RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'MyJavaProgInOracle.sayHello (java.lang.String)
      return java.lang.String';


SELECT say_hello_from_java_to('ALEX') FROM dual;

---RANK and DENSE_RANK and NTILE
SELECT s.*, RANK() OVER(ORDER BY wname) rank_
FROM staff s;

SELECT s.*, DENSE_RANK() OVER(ORDER BY wname DESC) rank_
FROM staff s;

SELECT s.*, NTILE(2) OVER(PARTITION BY wname ORDER BY salary) ntile_
FROM staff s;

--

SELECT * FROM staff
ORDER BY price DESC OFFSET @2*(@1-1) ROWS FETCH NEXT @2 ROWS ONLY;
--
SELECT * FROM staff;
--ROWID
SELECT staff.*, ROWID FROM staff;
--Ðàçìåð òàáëèöû
  SELECT COALESCE(CAST(ID_WORKER AS VARCHAR2(20)), 'Âñåãî áàéò:') AS ID_WORKER,
         SUM(VSIZE(ID_WORKER)) BT1,
         COALESCE(CAST(ID_SHOP AS VARCHAR2(20)), 'Âñåãî áàéò:') AS ID_SHOP,
         SUM(VSIZE(ID_SHOP)) BT2,
         COALESCE(CAST(SALARY AS VARCHAR2(20)), 'Âñåãî áàéò:') AS SALARY,
         SUM(VSIZE(SALARY)) BT3,
         COALESCE(CAST(WNAME AS VARCHAR2(20)), 'Âñåãî áàéò:') AS WNAME,
         SUM(VSIZE(WNAME)) BT4,
         COALESCE(CAST(AGE AS VARCHAR2(20)), 'Âñåãî áàéò:') AS AGE,
         SUM(VSIZE(AGE)) BT5,
         COALESCE(CAST(ROWID AS VARCHAR2(20)), 'Âñåãî áàéò:') AS ROWID_,
         SUM(VSIZE(ROWID)) BT6
    FROM STAFF
   GROUP BY GROUPING SETS((ID_WORKER, ID_SHOP, SALARY, WNAME, AGE, ROWID),());
--dump
SELECT id_worker, id_shop, salary, wname, age 
FROM staff;

SELECT dump(id_worker), dump(id_shop), dump(salary), dump(wname), dump(age) 
FROM staff;

----Ñòàðûé âàðèàíò òðàíçàêöèé
SELECT id_acc "id",
       COALESCE(CAST(LAG(dat, 1) OVER(PARTITION BY id_acc ORDER BY dat) AS VARCHAR(10)), 'Íà÷àëî Âðåì¸í') "Íà÷àëî ïåðèîäà", 
       COALESCE(CAST(dat AS VARCHAR(10)),'Êîíåö Âðåì¸í') "Êîíåö ïåðèîäà",
       --lag(true_summ,1) OVER(PARTITION BY id_acc ORDER BY dat) + val true_summ,
       coalesce(SUM(val) OVER(PARTITION BY id_acc ORDER BY dat ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS "Ñîñòîÿíèå ñ÷åòà"
       --,val 
FROM (SELECT id_acc, dat, sum(val) val
      FROM 
          (
             SELECT id_acc, dat, SUM(val) val
             FROM acc_val_list
             GROUP BY id_acc, dat
             HAVING SUM(val) <> 0
          ) tt
      GROUP BY GROUPING SETS((id_acc, dat, val),(id_acc))
     ) t;
--WHERE dat <> (SELECT MAX(dat) FROM trans WHERE t.id_acc = id_acc) OR dat IS NULL;
---------------------------
------Âûâîä þçåðîâ:
WITH w_list AS
(SELECT l.*, CASE type_ WHEN 'Òàðèô' THEN count_ END limit_before_100,
             CASE type_ WHEN 'Òàðèô' THEN count_ END limit_after_100
FROM list_ l
ORDER BY id_user, dat DESC),
w_list_2 AS 
(SELECT * FROM w_list ORDER BY id_user, dat)
SELECT --num_,
       id_user,
       type_,
       count_,
       dat,
       SUM(CASE type_ WHEN 'Òðàôèê' THEN count_ ELSE 0 END) 
       OVER(PARTITION BY id_user ORDER BY dat  RANGE 100 PRECEDING) sum_before_100,
       coalesce(limit_before_100, (SELECT count_ FROM w_list w WHERE w.type_ = 'Òàðèô' AND w.id_user = l.id_user 
                                                               AND w.dat < l.dat AND ROWNUM = 1 )) limit_before_100,
       coalesce(SUM(CASE type_ WHEN 'Òðàôèê' THEN count_ ELSE 0 END) 
       OVER(PARTITION BY id_user ORDER BY dat RANGE BETWEEN 1 FOLLOWING AND 101 FOLLOWING), 0) sum_after_100,
       (SELECT count_ FROM w_list_2 w WHERE w.type_ = 'Òàðèô' AND w.id_user = l.id_user 
                                                               AND w.dat >= (l.dat + 1) AND ROWNUM = 1) limit_after_100
FROM w_list l
ORDER BY id_user, l.num_;
---------------------------
------------

----------