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

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;