====== SQL ======

Připojení k databázi:

např podle podle [[00|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]]


===== Dotazování na data v SQL - SELECT =====

Cílem cvičení je ukázka použití SQL dotazu [[ http://www.postgresql.org/docs/8.4/interactive/sql-select.html | 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.

{{courses:a5m33izs:cviceni:senzorovasit.png?800|Model senzorové sítě}}

==== SQL - SELECT ====

=== Seznam senzorů a veličin ===

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


=== Průměrné denní hodnoty měření ===

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í **WHERE**ové 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;
  

 === Průměrné hodnoty veličiny větší než limit ===

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

===== Další datové operace v SQL - SELECT =====

   * Vkládaní záznamů - [[http://www.postgresql.org/docs/8.4/static/sql-insert.html| INSERT ]]
   * Úprava záznamů - [[http://www.postgresql.org/docs/8.4/static/sql-update.html| UPDATE ]]
   * Mazání záznamů [[http://www.postgresql.org/docs/8.4/static/sql-delete.html| DELETE ]]