Rapasoft.eu

use upjs;

-- student je v druhej prednaske

-- 1a) Zistite pocet riadkov/zanzmaov v tabulke student

select count(*) from student;

-- 1b) Kolko datumov narodeni je zadanych?

select count(datum_narodenia) from student;

-- 1c) A kolko je nezadanych datumov?

select count(*)-count(datum_narodenia) from student;

-- 2) Zistite pocet muzov a zien / vratte pohlavie a pocet

select pohlavie, count(*) pocet from student group by pohlavie;

-- 3a) zistite vsetky rozne roky v ktorych sa studenti narodili! Vratte iba roky

select year(datum_narodenia) from student group by year(datum_narodenia);
--alebo
select distinct year(datum_narodenia) from student;

-- 3b) to iste co pred tym ale null nevratte

select distinct year(datum_narodenia) as RokNar from student where year(datum_narodenia) is not null;

-- 3c) V predchadzajucom priklade vratte aj pohlavie

select distinct pohlavie,year(datum_narodenia) as RokNar from student where year(datum_narodenia) is not null;
--prida sa jeden riadok s duplikatom

-- 3d) zistite pocty vsetkych roznych rokov v ktorych sa studenti narodili. Vratte roky, pocty a pohlavie
-- zoradene podla rokov

select year(datum_narodenia),count(datum_narodenia),pohlavie 
from student where year(datum_narodenia) is not null
group by year(datum_narodenia),pohlavie order by year(datum_narodenia);

-- 3e) to iste len bez pohlavia

select year(datum_narodenia),count(datum_narodenia)--,pohlavie 
from student where year(datum_narodenia) is not null
group by year(datum_narodenia)/*,pohlavie*/ order by year(datum_narodenia);

-- 4a) vypiste rok narodenia zien a ich vek

select year(datum_narodenia),year(getdate())-year(datum_narodenia) from student where pohlavie='z';

-- 4b)+4c) zistite pocet a priemerny vek muzov a zien, vratte pohlavie, pocet a priemerny vek
-- aby to ukazovalo nezaokruhlene

select pohlavie,count(pohlavie) as pocet,avg( cast( year(getdate())-year(datum_narodenia) as float ) )
as avg from student group by pohlavie;
--alebo
select pohlavie,count(pohlavie) as pocet,avg( convert( float,year(getdate())-year(datum_narodenia) ) )
as avg from student group by pohlavie;

-- 5a) Zistite pocet studentov v jednotlivych rocnikoch, vratte rocnik a pocet.

select rocnik,count(*) pocet from student group by rocnik;

-- 5b) Vratte aj priemerny vek v rocnikoch

select rocnik,count(*) pocet,
avg( convert( float,year(getdate())-year(datum_narodenia) ) ) as priemernyVek
 from student group by rocnik;

-- 5c) vratte iba tie rocniky, ktorych priemerny vek je >25

select rocnik,count(*) pocet,
avg( convert( float,year(getdate())-year(datum_narodenia) ) ) as priemernyVek
 from student  group by rocnik having avg( convert( float,year(getdate())-year(datum_narodenia) ) )>25;