Rapasoft.eu

use poliklinika;
-- treba stiahnut poliklinika.sql z janik.7u.cz

-- 2a) Zistite id pacientov, ktori uz boli u kardiologa (teda u lekara s id 1)
select idP from navstevy where idL = 1;

-- 2b) Zistite ID pacientov a pocet ich navstev u lekara s ID 1
SELECT idP,count(*) from navstevy where idL = 1 group by idP;

-- 3a) Zistite ID pacientov, ktori uz kedysi navstivili lekara so specializaciou ocny
select N.idp from navstevy N join Lekari L on L.idL = N.idl where L.spec = 'Ocny';
--alebo mozeme menit poradia (s pouzitim aliasov)
select N.idp from Lekari L join navstevy N on L.idL = N.idl where L.spec = 'Ocny';

-- 3b) ukazat, ze preco je zle...
select N.idp from Navstevy N, Lekari L where L.spec = 'Ocny' --zle chyba podm. AND N.idl = N.idp

-- 3c) zistite aj pocty navstev
select N.idp,count(*) pocet 
	from navstevy N 
		join Lekari L on L.idL = N.idl 
	where L.spec = 'Ocny' 
group by N.idP;

-- 4a) Zistite ID pacientov, ktori uz kedysi navstivili zubneho lekara.
--     Okrem ID uvedte aj datum navstevy.
select n.idp,n.den from navstevy n join Lekari l on l.idL = n.idl where l.spec = 'Zubny';

-- 4b) zistite iba pocet navstev u zubneho lekara.
select count(*) pocet from navstevy n join lekari l on l.idL = n.idl where l.spec = 'zubny';
-- alebo
select count(*) pocet from (select n.idP, n.den from navstevy N join Lekari L on L.idL = N.idL where L.spec = 'Zubny') as T

-- 4c) Navrhnite riesenie na ulohu 4b) s pouzitim SUM vo vonkajsom dopyte (pozri 3c)
select sum(pocet) pocetPomocouSum 
	from (select N.idp,count(*) pocet 
			from navstevy N 
				join Lekari L on L.idL = N.idl 
			where L.spec = 'zubny' group by N.idP) 
	as t; 

-- 4d) Zistite mena pacientov, ktory uz navstivili zubneho lekara. Vratte aj meno lekara (vychadzat zo 4a).
select p.krstne as pacient, l.krstne as lekar
	from pacienti p
		join navstevy n on n.idP = p.idP
		join lekari l on n.idL = l.idL
	where l.spec = 'Zubny';
-- alebo
select p.krstne as pacient, l.krstne as lekar
	from navstevy n
		join pacienti p on n.idP = p.idP
		join lekari l on n.idL = l.idL
	where l.spec = 'Zubny';

-- 5a) zistite mena pacientov, ktory uz navstivili lekara Ota alebo Imra.
--	   uvedte aj mena lekarov a vysledok zoradte podla mien lekarov, pacientov.
select p.krstne,l.krstne
	from navstevy n
		join pacienti p on n.Idp = p.Idp
		join lekari l on n.idL = l.idl
	where l.krstne = 'oto' or l.krstne = 'imro'
order by l.krstne, p.krstne;

-- 5b) teraz takych co boli aj u ota aj u imra // neprehladne riesenie
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' and l2.krstne = 'imro'
order by l.krstne, p.krstne;
--alebo skuste vychadzat z (ako vnoreny DP + vyuzite IN vo vonkajsom):
--bude ID tych, ktori navstivili Imra medzi tymi, ktori navstivili Ota?