use OsobaVztah;


select id, meno, priezvisko, pohlavie from osoba
where pohlavie = 'z';

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

alter view VOsobaZ
AS
select id, meno, priezvisko, dat_nar, pohlavie from osoba
where pohlavie = 'z';

select COUNT(*) from VOsobaZ;


select * from Osoba;

select * from VOsobaZ;

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

create view VPriemerVyskyPohlavi
AS
SELECT pohlavie, AVG(vyska) priemer_vyska FROM Osoba
GROUP BY pohlavie;

select * from VPriemerVyskyPohlavi;

insert into VOsobaZ
VALUES (25, 'Eva', 'Hneda', '2002-09-09','z');

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

select * 
from Vztah v 
JOIN VOsobaZ vo ON vo.id = v.id_ona
;

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;

WITH 
W3(meno, priezvisko)
AS
(
select TOP(3) meno, priezvisko 
from osoba
order by vyska desc
),
W2(meno, priezvisko)
AS
(
select TOP(2) meno, priezvisko 
from osoba
order by vyska desc
)
SELECT * FROM W3
EXCEPT
SELECT * FROM W2;

WITH W2(meno, priezvisko)
AS
(
select TOP(2) meno, priezvisko 
from osoba
order by vyska desc
)
SELECT * FROM W2;


WITH 
W3(meno, priezvisko)
AS
(
select TOP(3) meno, priezvisko 
from osoba
order by vyska desc
)
SELECT * FROM W3
EXCEPT
SELECT meno, priezvisko FROM V2;