Rapasoft.eu

-- h1) sumarny poplatok v jednotlivych mesiacoch
select month(den) mes, sum(poplatok) popl 
from navstevy group by month(den) order by mes
--cez vnoreny dopyt nanutime group by mesiac (nejde)
--select month(den) as mes,sum(poplatok) popl
--from (select month(den) as mes, poplatok from navstevy)as T group by mes

-- 2a) pocet poplatkov v jednotlivych mesiacoch
select month(den) mesiac, count(poplatok) pocetPopl
from navstevy group by month(den) order by mesiac

-- 2b_a) priemerny poplatok v jednotlivych mesiacoch
select month(den) mesiac, avg(cast(poplatok as dec(5,2))) priemPoplatok 
from navstevy group by month(den) order by mesiac
--bez avg()
select month(den) mesiac, sum(cast(poplatok as decimal))/count(poplatok)
priemPoplatok from navstevy group by month(den) order by mesiac

--3) minimalny a maximalny poplatok jednotlivych pacientov, uvedte aj id
select idP, min(poplatok) mini, max(poplatok) maxi 
	from navstevy 
	group by idP
--aj meno:
select p.idP, p.krstne, min(poplatok) mini, max(poplatok) maxi 
	from navstevy n
		join pacienti p on p.idp = n.idp
	group by p.idP, p.krstne;

-- h 4b) id a krstne pacienta, ktory zaplatil najvacsi poplatok
select p.idp, p.krstne, n.maxi from pacienti p
join (select n2.idp, n2.poplatok maxi from navstevy n2 where n2.poplatok = 
(select max(n1.poplatok) from navstevy n1)) n
on n.idp = p.idp

-- 5a) id (a krstne) pacienta, ktory v auguste zaplatil najvacsi poplatok
select top(1) p.idp, krstne, max(poplatok) from navstevy n
	join pacienti p on p.idp = n.idp
	where month(den) = 8
group by p.idp, krstne
order by max(poplatok) desc;
--bez joinu
select n2.idp, n2.poplatok maxi from navstevy n2
where n2.poplatok = (select max(n1.poplatok) from navstevy n1
where month(n1.den) = 8)
and month(n2.den) = 8

-- 6) pacienti, ktori zarabaju viac ako 100-nasobok toho co zaplatili
select p.idp , p.krstne from pacienti p
	join navstevy n on p.idp = n.idp
where p.mesPrijem >= (100*n.poplatok)

-- 7) Lekari, ktori liecili pacienta s mes.prijmom vyssim ako 30 000
select * 
	from lekari l
		join navstevy n on n.idl = l.idl
		join pacienti p on p.idp = n.idp
	where p.mesPrijem >= (30000)

--8) pocet pacientov u jednotlivych lekarov (specializacia)
select l.spec,count(*) 
	from lekari l
		join navstevy n on n.idl = l.idl
	group by l.spec

--9)pocet pacientov u dvoch specialistov ocny a interny
select l.spec,count(*) 
	from lekari l
		join navstevy n on n.idl = l.idl
	where l.spec = 'ocny' or l.spec = 'interny'
	group by l.spec