USE OsobaVztah;

SELECT id, meno, priezvisko, pohlavie 
FROM osoba
WHERE pohlavie = 'z';

IF OBJECT_ID('OsobaZView', 'V') IS NOT NULL
	DROP VIEW OsobaZView;
	

CREATE OR ALTER VIEW OsobaZView
AS
select id, meno, priezvisko, dat_nar, pohlavie from osoba
where pohlavie = 'z';

select COUNT(*) from OsobaZView;

select * from Osoba;

select * from OsobaZView;

if OBJECT_ID('OsobaZ2View', 'V') is not null
	drop view OsobaZ2View;

create or alter view OsobaZ2View
AS
select id, meno, priezvisko from osoba
where pohlavie = 'z';

select COUNT(*) from OsobaZ2View;

select * from Osoba;

select * from OsobaZ2View;

SELECT * 
FROM Vztah v 
JOIN OsobaZView vo ON vo.id = v.id_ona;

SELECT * 
FROM Vztah v 
JOIN OsobaZ2View vo ON vo.id = v.id_ona;

UPDATE Osoba
SET pohlavie = 'z'
WHERE id = 25;

select * from Osoba;

select * from OsobaZView;

select * from OsobaZ2View;

CREATE OR ALTER VIEW priemerVyskyPodlaPohlaviaView
AS
SELECT pohlavie, AVG(vyska) priemer_vyska FROM Osoba
GROUP BY pohlavie;

select * from priemerVyskyPodlaPohlaviaView;

INSERT Osoba
VALUES (24, 'Janka', 'Hraskova', 
null, '2000-01-01', null, 'z', 
160, 55, null, null);

INSERT INTO OsobaZView
VALUES (25, 'Eva', 'Hneda', '2002-09-09','z');

/*
Aby bolo mozne vykonavat INSERT/UPDATE/DELETE nad VIEW, musi VIEW splnat nasledovne podmienky:
- Musi byt zalozeny na jednej tabulke – Ak VIEW obsahuje viacero tabuliek (JOIN), nie je priamo modifikovatelny.
- Nesmie obsahovat agregovane funkcie – Funkcie ako SUM(), AVG(), COUNT(), GROUP BY, HAVING znemoznuju modifikaciu.
- Musi obsahovat vsetky pozadovane NOT NULL stlpce – Ak ma tabulka stlpce s NOT NULL a VIEW ich nezahrna, INSERT nebude fungovat.
- Nesmie obsahovat DISTINCT, UNION, TOP, OFFSET/FETCH – Tieto konstrukcie znemoznuju upravy.
- Nesmie obsahovat vypoctove stlpce alebo vnutorne dopyty v SELECT liste.
*/
INSERT INTO OsobaZ2View
VALUES (26, 'Eva', 'Hneda');

select TOP(3) * from osoba
order by vyska desc
EXCEPT
select TOP(2) * from osoba
order by vyska desc

create view V3
as
select TOP(3) * from osoba
order by vyska desc;

create view V2
as
select TOP(2) * from osoba
order by vyska desc;

SELECT * FROM V3
EXCEPT
SELECT * FROM V2;