CREATE VIEW all_table AS
SELECT * FROM implementation
NATURAL JOIN  disciplines
NATURAL JOIN  groups
NATURAL JOIN  navantazh
NATURAL JOIN  specialnist
NATURAL JOIN  themes
order by id_impl;
--SELECT * FROM all_table;


CREATE or REPLACE VIEW laboratorn_view AS
SELECT id_impl, name_n, name_g, name_d, name_t, kurs FROM implementation
INNER JOIN groups      on implementation.id_g = groups.id_g
INNER JOIN themes      on implementation.id_t = themes.id_t
INNER JOIN disciplines on implementation.id_d = disciplines.id_d
INNER JOIN navantazh   on implementation.id_n = navantazh.id_n
WHERE navantazh.id_n = 1;

--SELECT * FROM laboratorn_view;

id_impl name_n  name_g  name_d  name_t  kurs
1001    Лабораторне заняття   ВНГ-12-1 Будівництво та проектування споруд   Вступне заняття   3
1005    Лабораторне заняття   ВНГ-12-1 Будівництво та проектування споруд   Вступ до сопромату  3
1007    Лабораторне заняття   А-13-1 Архітектура нафтових вишок  Архітектура, як мистецтво 2
1015    Лабораторне заняття   ПБС-14-1 Вступ до фаху    Вступне заняття   1
1017    Лабораторне заняття   ПБС-14-1 Вступ до фаху    Пожежна система   1
1022    Лабораторне заняття   ВНГ-11-1 Проектування споруд   Сучасні споруди   4

CREATE OR REPLACE VIEW last_view("Назва дисципліни","Назва групи","Назва навантаження","Назва спеціальності","Назва теми","Курс","Дата","Пара")
AS SELECT disciplines.name_d, groups.name_g, navantazh.name_n, specialnist.name_s, themes.name_t,implementation.kurs, implementation.date_s,implementation.n_par
FROM implementation
INNER JOIN specialnist on implementation.id_s = specialnist.id_s
INNER JOIN groups      on implementation.id_g = groups.id_g
INNER JOIN themes      on implementation.id_t = themes.id_t
INNER JOIN disciplines on implementation.id_d = disciplines.id_d
INNER JOIN navantazh   on implementation.id_n = nav;ejantazh.id_n;

--SELECT * FROM last_view;

CREATE OR REPLACE RULE rule_last_view_INSERT AS ON INSERT TO last_view DO INSTEAD(
INSERT INTO groups(id_g, name_g) VALUES(New.id_g, New.name_g);
INSERT INTO navantazh(id_n, name_n) VALUES(New.id_n, New.name_n);
INSERT INTO specialnist(id_s, name_s) VALUES(New.id_s, New.name_s);
INSERT INTO disciplines(id_d, name_d) VALUES(New.id_d, New.name_d);
INSERT INTO themes(id_t, name_t)          VALUES(New.id_t, New.name_t);
INSERT INTO implementation(id_impl, id_d, id_s, kurs, id_n, date_s, n_par,id_g, id_t, hours)
VALUES(New.id_impl, New.id_d, New.id_s, New.kurs, New.id_n, New.date_s, New.n_par,New.id_g, New.id_t, New.hours)
);

CREATE OR REPLACE RULE rule_last_view_UPDATE AS ON UPDATE TO last_view DO INSTEAD(
UPDATE groups SET id_g = New.id_g,name_g = New.name_g WHERE id_g = OLD.id_g;
UPDATE navantazh SET id_n = New.id_n,name_n = New.name_n WHERE id_n = OLD.id_n;
UPDATE specialnist SET id_s = New.id_s,name_s = New.name_s WHERE id_s = OLD.id_s;
UPDATE disciplines SET id_d = New.id_d,name_d = New.name_d WHERE id_d = OLD.id_d;
UPDATE themes SET id_t = New.id_t,name_t = New.name_t WHERE id_t = OLD.id_t;
UPDATE implementation SET id_impl = New.id_impl, id_d = New.id_d, id_s = New.id_s, kurs = New.id_s, id_n = New.id_n, date_s = New.date_s, n_par = New.n_par,id_g = New.id_g, id_t = New.id_t, hours = New.hours WHERE id_impl = Old.id_impl
);

CREATE RECURSIVE VIEW nums_1_100 (n) AS
    VALUES (1)
UNION ALL
    SELECT n+1 FROM nums_1_100 WHERE n < 100;
n
1
2
3
4
5
6
7
8
9
10
11


--Lab_view_BD
CREATE VIEW all_table AS
SELECT * FROM implementation
NATURAL JOIN  disciplines
NATURAL JOIN  groups
NATURAL JOIN  navantazh
NATURAL JOIN  specialnist
NATURAL JOIN  themes
order by id_impl;
--SELECT * FROM all_table;

CREATE or REPLACE VIEW laboratorn_view AS
SELECT id_impl, name_n, name_g, name_d, name_t, kurs FROM implementation
INNER JOIN groups      on implementation.id_g = groups.id_g
INNER JOIN themes      on implementation.id_t = themes.id_t
INNER JOIN disciplines on implementation.id_d = disciplines.id_d
INNER JOIN navantazh   on implementation.id_n = navantazh.id_n
WHERE navantazh.id_n = 1;
--SELECT * FROM laboratorn_view;

CREATE VIEW groups_view AS SELECT groups.id_g, groups.name_g FROM groups;

--UPDATE groups_view SET name_g = 'PI-14-1' WHERE id_g = 2;

CREATE RULE groups_view_rule AS ON UPDATE TO groups_view DO
INSTEAD UPDATE groups SET name_g = NEW.name_g
WHERE id_g = NEW.id_g
CREATE RULE delete_groups_view AS ON DELETE TO groups_view DO
INSTEAD DELETE FROM groups where name_g = OLD.name_g;
CREATE VIEW groups_view AS SELECT groups.id_g, groups.name_g FROM groups;

Висновок: в даній лабораторній роботі було виконано 20 представлень.

------------------------------------------------------------------------------------
CREATE VIEW themes_view AS SELECT * FROM themes;
SELECT * FROM themes;
--скрін
--ПРАВИЛО НА INSERT
CREATE OR REPLACE RULE for_themes_view_insert AS ON INSERT TO themes_view DO INSTEAD
INSERT INTO themes VALUES(NEW.id_t, NEW.name_t);

INSERT INTO themes VALUES(13,"New theme");
--скрін
--Правило на UPDATE
CREATE OR REPLACE RULE for_themes_view_update AS ON UPDATE TO themes_view DO INSTEAD
UPDATE  themes SET name_t = NEW.name_t WHERE id_t = NEW.id_t;

UPDATE themes SET name_t = "n" WHERE id_t = 1;
--Правило на DELETE
CREATE OR REPLACE RULE for_themes_view_delete AS ON DELETE TO themes_view DO INSTEAD
DELETE FROM  themes  WHERE id_t = OLD.id_t;
--------------------------------------------------------------------------------------
CREATE VIEW first_kurs AS SELECT
 implementation.id_impl,
 disciplines.name_d,
 groups.name_g,
 navantazh.name_n,
 specialnist.name_s,
 themes.name_t,
 implementation.kurs,
 implementation.date_s,
 implementation.n_par
FROM implementation
INNER JOIN specialnist on implementation.id_s = specialnist.id_s
INNER JOIN groups      on implementation.id_g = groups.id_g
INNER JOIN themes      on implementation.id_t = themes.id_t
INNER JOIN disciplines on implementation.id_d = disciplines.id_d
INNER JOIN navantazh   on implementation.id_n = navantazh.id_n
WHERE implementation.kurs = 1;
;

Правило на INSERT
CREATE OR REPLACE RULE for_first_kurs_view_insert AS ON INSERT TO first_kurs DO INSTEAD
INSERT INTO first_kurs VALUES
(NEW.name_d, NEW.name_g,NEW.name_n,NEW.name_s,NEW.name_t,NEW.kurs, NEW.date_s, NEW.n_par);



SELECT * FROM first_kurs




00000000000000000000
CREATE VIEW disciplines_view AS SELECT * FROM disciplines;
SELECT * FROM disciplines;
--скрін
--ПРАВИЛО НА INSERT
CREATE OR REPLACE RULE for_disciplines_view_insert AS ON INSERT TO disciplines_view DO INSTEAD
INSERT INTO disciplines VALUES(NEW.id_d, NEW.name_d);

INSERT INTO disciplines VALUES(13,"New theme");
--скрін
--Правило на UPDATE
CREATE OR REPLACE RULE for_disciplines_view_update AS ON UPDATE TO disciplines_view DO INSTEAD
UPDATE  disciplines SET name_d = NEW.name_d WHERE id_d = NEW.id_d;

UPDATE disciplines SET name_d = "n" WHERE id_d = 1;
--Правило на DELETE
CREATE OR REPLACE RULE for_disciplines_view_delete AS ON DELETE TO disciplines_view DO INSTEAD
DELETE FROM  disciplines  WHERE id_d = OLD.id_d;
----------------------------------------------------------------------------------------