Některá řešení některých příkladů

-- 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;