SQL

Připojení k databázi:

např podle podle zadání nulté úlohy.

• host: slon.felk.cvut.cz
• port: 5432
• username: izs20_SK
• databáze: izs20_SK

Další příklady: https://cw.fel.cvut.cz/wiki/courses/a4b33ds/cviceni-4-5

Cílem cvičení je ukázka použití SQL dotazu SELECT

Studnicí testovacích dat nám bude databáze navržená v minulém cvičení. Testovací databáze je přístupná přes konto 0. skupiny.

Vypište v lidské podobě seznam senzorů a veličin, které tyto senzory měří.==

• 1) začneme s tabulkou Senzor

 SELECT
    *
  FROM "senzor";

• 2) nyní k tabulce Senzor připojíme tabulku VelicinaSenzor. Minimální kardinatity jsou 1, tedy použijeme INNER JOIN. Vzhledem k tomu, že atribut cizího klíče id_senzor v tabulce VelicinaSenzor má stejné jméno jako atribut primárního klíče v tabulce Senzor, můžeme pro spojení tabulek použít verze s USING

 SELECT
    *
 FROM "senzor"
   JOIN "velicinasenzor" USING (id_senzor);

• 3) analogickým způsobem připojíme k těmto dvou tabulkám ještě tabulku Velicina

 SELECT
    *
 FROM "senzor"
   JOIN "velicinasenzor" USING (id_senzor)
   JOIN "velicina" USING (id_velicina);

• 4) nyní vybereme ty atributy, které poskytují člověku danou informaci. Vzhledem k tomu, že jak tabulka Senzor, tak tabulka Velicina mají atribut pojmenovaný nazev, je nutné jednotlivé atributy příslušející k daným tabulkám odlišit pomocí notace Tabulka.atribut

 SELECT
    "senzor".nazev,popis,
    "velicina".nazev,
    znacka || ' [' || CASE WHEN jednotka IS NULL THEN '-' ELSE jednotka END || ']'
 FROM "senzor"
   JOIN "velicinasenzor" USING (id_senzor)
   JOIN "velicina" USING (id_velicina);

Vypište průměrné denní hodnoty naměřené senzorem s id_senzor = 3 a veličinu id_veličina = 2.

* 1) Začneme tabulkou měření

 SELECT
    *
  FROM "mereni";

* 2) Pomocí WHEREové podmínky specifikujeme záznamy, které nás zajímají

 SELECT
    *
  FROM "mereni"
  WHERE id_senzor = 3 AND id_velicina = 2;

* 2b) (Volitelně, pokud bychom chtěli zobrazit průběh, můžeme záznamy seřadit sestupně podle času)

 SELECT
    *
  FROM "mereni"
  WHERE id_senzor = 3 AND id_velicina = 2
  ORDER BY cas ASC;

* 3) Nyní přistoupíme k agregaci záznamů. Chceme denní průměry, tedy musíme získat z času pouze datum - funkce date(). Připravíme si záznamy pomocí

 SELECT
    date(cas), hodnota
  FROM "mereni"
  WHERE id_senzor = 3 AND id_velicina = 2;

• 3b) a provedeme agregaci podle hodnoty atributu date(cas). Agregační funkcí - funkcí, které pole hodnot agreguje do jedné hodnoty - bude průměr - avg(). Agregaci provádíme pomocí GROUP BY. Nezapomeňte, že ve výčtu atributů nesmí být nic jiného než atributy, podle kterých se agreguje a agregační funkce.

 SELECT
    date(cas), avg(hodnota)
  FROM "mereni"
  WHERE id_senzor = 3 AND id_velicina = 2
  GROUP BY date(cas);

• 4) S výhodou použijeme řazení podle času z bodu 2b a máme použitelný výsledek.

 SELECT
    date(cas), avg(hodnota)
  FROM "mereni"
  WHERE id_senzor = 3 AND id_velicina = 2
  GROUP BY date(cas)
  ORDER BY date(cas) ASC;

• 1) SQL dotaz pro průměrné hodnoty veličiny pro daný senzor již máme, jen jej zobecníme pro všechny senzory měřící danou veličinu. Musíme podle toho upravit i hodnoty atributů, podle kterých budeme agregovat - dvojicice date(cas), id_senzor

 SELECT
    date(cas), id_senzor, avg(hodnota)
  FROM "mereni"
  WHERE id_velicina = 2
  GROUP BY date(cas), id_senzor
  ORDER BY date(cas) ASC, id_senzor;

• 2) Specifikujeme podmínku na hodnotu agregační funkce, budeme požadovat, aby průměrná hodnota veličiny avg(hodnota)>20. Podmínky na agregační funkce se specifikují pomocí klausule HAVING

 SELECT
    date(cas), id_senzor, avg(hodnota)
  FROM "mereni"
  WHERE id_velicina = 2
  GROUP BY date(cas), id_senzor
  HAVING avg(hodnota)>2.1
  ORDER BY date(cas) ASC, id_senzor;

Pozor, nezaměňujte WHERE podmínky na atributy před agregací s HAVING podmínkami na hodnoty výsledků agregačních funkcí!!!

• 3) Pokud bychom pozměnili zadání na požadavek, aby byly vypsány senzory s průměrnou hodnotou veličiny větší než je průměr přes všechny senzory (tedy různé agregace), je nutné použít tzv. vnořeného SELECTu - “předpočítat si hodnotu”. Průměr dané veličiny získáme pomocí

 SELECT
    avg(hodnota)
  FROM "mereni"
  WHERE id_velicina = 2;

• 3b) a tento mezivýsledek dosadíme do původního SELECTu (syntakticky do závorek)

 SELECT
    date(cas), id_senzor, avg(hodnota)
  FROM "mereni"
  WHERE id_velicina = 2
  GROUP BY date(cas), id_senzor
  HAVING avg(hodnota)> ( SELECT avg(hodnota) FROM "mereni" WHERE id_velicina = 2 )
  ORDER BY date(cas) ASC, id_senzor;

• 4) vnořeného SELECTu lze využít i v jiných častech, například namísto spojovaní tabulek

 SELECT
    date(cas), id_senzor, avg(hodnota) || (SELECT CASE
                                                     WHEN jednotka IS NULL THEN ''
                                                     ELSE jednotka END
                                                  FROM "velicina" WHERE "velicina".id_velicina = "mereni".id_velicina)
  FROM "mereni"
  WHERE id_velicina = 2
  GROUP BY date(cas), id_senzor, id_velicina
  HAVING avg(hodnota)> ( SELECT avg(hodnota) FROM "mereni" WHERE id_velicina = 2 )
  ORDER BY date(cas) ASC, id_senzor;

• Vkládaní záznamů - INSERT
• Úprava záznamů - UPDATE
• Mazání záznamů DELETE