Rapasoft.eu

--use poliklinika
--go

--1) Doplnte nasledujuci dopyt tak, aby vratil aj null hodnoty:
--select mesPrijem from pacienti
--	where mesPrijem <> 8500 

select mesPrijem from pacienti
	where mesPrijem <> 8500 or mesPrijem is null

--2) Modifikujte dopyt tak, aby vratil aj null hodnoty -10 riadkov
--select p1.mesPrijem from pacienti p1 
--	join pacienti p2
--	on p2.mesPrijem = p1.mesPrijem 

select p1.mesPrijem from pacienti p1 
	join pacienti p2
	on p2.idp = p1.idp 
--alebo:	
select p1.mesPrijem from pacienti p1 
cross join pacienti p2
where p2.idp = p1.idp


--3) doplnte (nie modifikujte) nasledujuci dopyt tak aby vratil aj null hodnoty - 10 riadkov:
--select p1.mesprijem from pacienti p1
--join pacienti p2 
--on p2.mesPrijem = p1.mesPrijem
select p1.mesprijem from pacienti p1
join pacienti p2 
on p2.mesPrijem = p1.mesPrijem
union all
select p1.mesprijem from pacienti p1
where p1.mesPrijem is null

--4a) akych pacientov (idp) mal lekar s id 3?
select idl Lekar,idp Pacient from navstevy where idL=3

--4b) akych pacientov nemal lekar s id 3
select idl Lekar,idp Pacient from navstevy
except select idl Lekar,idp Pacient from navstevy where idL=3

--5a) vratte id vsetkych pacientov, ktori maju prijem vyssi ako 10000
--alebo boli u lekara s id 2
select p.idp from pacienti p where p.mesPrijem > 10000
union all
select n.idp from navstevy n where n.idl = 2

--5b) Vratte id pacientov, ktori maju vyssi prijem ako 10000
-- a boli u lekara s id 2
select p.idp from pacienti p where p.mesPrijem > 10000
intersect
select n.idp from navstevy n where n.idl = 2

-- 6a) zistite mena pacientov, ktory uz navstivli lekara ota alebo imra
-- uvedte aj mena lekarov
--original:
--select p.krstne, l.krstne
--	from navstevy n 
--		join lekari l on l.idl = n.idl
--		join pacienti p on p.idp = n.idp
--	where l.krstne = 'oto' or l.krstne = 'imro'

select p.krstne, l.krstne
from navstevy n join lekari l on l.idl = n.idl
join pacienti p on p.idp = n.idp
where l.krstne = 'oto' or l.krstne = 'imro'

--riesenie bez OR pomocou UNION:
select p.krstne, l.krstne
	from navstevy n join lekari l on l.idl = n.idl
		join pacienti p on p.idp = n.idp
	where l.krstne = 'oto'
union all
select p.krstne, l.krstne
	from navstevy n join lekari l on l.idl = n.idl
		join pacienti p on p.idp = n.idp
	where l.krstne = 'imro'

--s usporiadanim podla mien lekraov, pacientov
--orig:
--select p.krstne, l.krstne
--	from navstevy n
--		join lekari l on l.idl = n.idl
--		join pacienti p on p.idp = n.idp
--	where l.krstne = 'oto' or l.krstne = 'imro'
--order by l.krstne, p.krstne

select p.krstne, l.krstne
	from navstevy n
		join lekari l on l.idl = n.idl
		join pacienti p on p.idp = n.idp
	where l.krstne = 'imro'
union all
select p.krstne, l.krstne
	from navstevy n
		join lekari l on l.idl = n.idl
		join pacienti p on p.idp = n.idp
	where l.krstne = 'oto'
order by l.krstne, p.krstne

--6b) vratte mena (iba) pacientov, ktory uz navstivili lekara ota a imra
select p.krstne
	from navstevy n
		join pacienti p on n.Idp = p.Idp
		join lekari l on n.idL = l.idl
		join navstevy n2 on p.idP = n2.idp
		join lekari l2 on l2.idL = n2.idL
	where l2.krstne = 'imro'
intersect
select p.krstne
	from navstevy n
		join pacienti p on n.Idp = p.Idp
		join lekari l on n.idL = l.idl
		join navstevy n2 on p.idP = n2.idp
		join lekari l2 on l2.idL = n2.idL
	where l.krstne = 'oto'

-- alebo ako vnoreny dopyt a vyuzitie in vo vonkajsom dopyte
select p2.krstne
	from navstevy n2 join lekari l2 on l2.idl = n2.idl
		join pacienti p2 on p2.idp = n2.idp
	where l2.krstne = 'oto'
		and p2.idp in (
			select p.idp
				from navstevy n 
					join lekari l on l.idl = n.idl
					join pacienti p on p.idp = n.idp
				where l.krstne = 'imro'
		)

-- 7a) vratte id a mena pacientov, ktory navstivili ota ale nie imra
select p.idp,p.krstne
	from navstevy n
		join pacienti p on n.Idp = p.Idp
		join lekari l on n.idL = l.idl
		join navstevy n2 on p.idP = n2.idp
		join lekari l2 on l2.idL = n2.idL
	where l2.krstne = 'oto'
except
select p.idp,p.krstne
	from navstevy n
		join pacienti p on n.Idp = p.Idp
		join lekari l on n.idL = l.idl
		join navstevy n2 on p.idP = n2.idp
		join lekari l2 on l2.idL = n2.idL
	where l.krstne = 'imro'
-- 7b) vratte iba id
select n.idp
	from navstevy n
		join lekari l on n.idL = l.idl
	where l.krstne = 'oto'
except
select n.idp
	from navstevy n
		join lekari l on n.idL = l.idl
    where l.krstne = 'imro'