====== Některá řešení některých příkladů ======
<code sql>

-- Seznam všech specializací lékařů
select distinct spe
from lek
order by spe;

-- Jména ortopedů.
select jml
from lek
where spe = 'orthoped';

--Jména a adresy pacientů narozených před rokem 1920.

-- preklopim datum (dnp) na char  (funkce to_char())
-- a potom jeste char na cislo (funkce to_number() 
select jmp, adp, dnp
from pac
where to_number(to_char(dnp,'yyyy'))  < 1920;

-- obracene: z retezce udelame datum
select jmp, adp, dnp
from pac
where dnp < to_date('01.01.1920','mm.dd.yyyy');

-- dle standardu SQL lze k podobnym transformacim 
-- pouzit take funkci CAST


-- IDL lékařů, které navštívil pacient Scott.
select distinct idl
from nav join pac using(idp)
where jmp = 'SCOTT';


--Jména internistů, které 
--navštívil pacient Scott.

select jml
from lek join nav using (idl) join pac using(idp)
where jmp='SCOTT' and SPE='internist';

select jml
 from lek join nav using (idl)
  where spe = 'internist' and idp in
    (select idp from pac where jmp='SCOTT');
    
--Jména a adresy pacientů, kteří navštívili doktora Jonese.

select distinct jmp, adp
from pac join nav using (idp) join lek using (idl)
where jml= 'Jones';
    
-- Jména a adresy pacientů, kteří navštívili pouze doktora Jonese.
select distinct jmp, adp
from pac join nav using (idp) join lek using (idl)
where jml= 'Jones'
minus
select distinct jmp, adp
from pac join nav using (idp) join lek using (idl)
where jml <> 'Jones';

select distinct jmp, adp
 from pac join nav using (idp) 
 where 
  idp in (select idp from nav join lek using (idl) 
          where jml = 'Jones')
  and 
  idp not in (select idp from nav join lek using (idl) 
               where jml <> 'Jones');
               
-- Dvojice jmen pacientů sdílejících stejnou adresu.
select p1.jmp, p2.jmp
from pac p1 join pac p2 on (p1.adp=p2.adp)
where p1.idp < p2.idp;
 
-- Lékaři, kteří měli pacienta.
select distinct l.*
from lek l join nav n on (l.idl=n.idl);

select * 
from lek
where idl in (select idl from nav);

select * 
from lek
where exists (select 'X' from nav where lek.idl=nav.idl);


-- Lékaři, kteří dosud neměli pacienta.
select * 
from lek
where idl not in (select idl from nav);

select * 
from lek
where not exists (select 'X' from nav where lek.idl=nav.idl);

-- Která specializace dosud nepracovala?
select distinct SPE from lek
minus
select distinct SPE 
from lek
where idl in (select idl from nav);

--Spočtěte celkou sumu, kterou utratili 
--všichni pacienti dohromady za svoje návštěvy.

select sum(cen) from nav;

--Jaká je maximální cena za návštěvu?

select max(cen) from nav;

--Pacienti, kteří za některou svou návštěvu zaplatili maximální cenu.

select p.* 
from pac p join nav n on (p.idp=n.idp)
where cen = (select max(cen) from nav);

-- 3 pacienti, kteri zaplatili nejvic.

--Pro každého pacienta určete průměrnou cenu jeho nástěv. 
--Výstup seřaďte sestupne podle počtu návštěv.

select idp, jmp, 
  round(avg(coalesce(cen,0)),2) as prum_cena, 
  count(*) as pocet_navstev 
from nav join pac using (idp)
group by idp, jmp
order by pocet_navstev desc;


--Jako 20, ale zahrňte i pacienty, kteří nebyli na žádné návštěvě.

-- mezivyklad: vnejsi spojeni:
select * from pac left join nav using(idp);

select idp, jmp, 
  round(avg(coalesce(cen,0)),2) as prum_cena, 
  count(idl) as pocet_navstev 
from nav right outer join pac using (idp)
group by idp, jmp
order by pocet_navstev desc;


--Jako 20, ale zobrazte pouze pacienty, kteří byli 
--alespoň na ctyrech návštěvách.

select idp, jmp, 
  round(avg(coalesce(cen,0)),2) as prum_cena, 
  count(idl) as pocet_navstev 
from nav join pac using (idp)
group by idp, jmp
having count(*) >= 4
order by pocet_navstev desc;

</code>