SQL Dotazy

Aby bylo na čem SQL dotazy zkoušet, je vhodné databázi naplnit daty. Pokud nemáte data skutečná, je potřeba si nějaká vygenerovat. Mnoho SQL klientů umí importovat CSV soubory do databáze. Je mnohem hezčí, když výsledky vašich SQL dotazů produkují alespoň trochu smysluplná data, a proto vám zde nabízíme něco dat na hraní.

• České svátky – Pokud chcete náhodná jména osob, můžete si je snadno vybrat z tabulky svátků. S příjmeními se netrapte.
• RÚIAN – Registr územní identifikace, adres a nemovitostí
  • Pokud chcete smysluplné adresy, zde najdete všechny u nás se vyskytující.
  • http://www.cuzk.cz/Uvod/Produkty-a-sluzby/RUIAN/RUIAN.aspx
  • http://data.cuzk.cz/kontroly-dat-isui/00-volebni-okrsky/2014-03-20_pouze_csv/
• Autíčka:
  • http://www.epa.gov/oms/tcldata.htm
• Meteorologická data GHCN:
  • ftp://ftp.ncdc.noaa.gov/pub/data/ghcn/
  • ftp://ftp.ncdc.noaa.gov/pub/data/ghcn/daily/by_year/

Vygenerování 50 náhodných čísel od 10 do 20:

SELECT x.*, ROUND(10 + 20 * RANDOM()) AS y
FROM generate_series(1, 50) AS x

Vygenerování 50 náhodných jmen z tabulky svátků (jména se mohou opakovat):

SELECT r.*, svatek.*
FROM
  (
    SELECT x, FLOOR(1 + 365 * RANDOM()) AS y
    FROM generate_series(1, 50) AS x
  ) AS r
LEFT JOIN svatky.svatek ON svatek.doy = r.y

• prorekvizitou pro další práci je odevzdaný ER-diagram
• při odevzdání - zadání SQL dotazů

• Abychom nemuseli ručně generovat pro každý záznam hodnotu primárního klíče, je užitečné založit sekvenci - prostředek, jak získat unikátní identifikátory 1,2,3,….

  CREATE SEQUENCE seq_tabulka;

• abychom nemuseli pokaždé explicitně nastavovat hodnotu primárního klíče v INSERTu, necháme jí defaultně nastavit. Aktuální hodnotu sekvence dostaneme pomocí funkce nextval().

 CREATE TABLE tabulka (
     id_tabulka int NOT NULL UNIQUE DEFAULT nextval('seq_tabulka'),
     ...,
   PRIMARY KEY (id_tabulka));

* v SQL kódu vygenerovaném DB Designerem (v módu MySQL) je nutné výče uvedené dopsat pro každou tabulku.

* dále, DB Designer ignoruje v módu MySQL referenční integrity, opět je nutné je dopsat přímo do vygenerované SQL kódu pomocí REFERENCES

 CREATE TABLE tabulka (
     id_tabulka int NOT NULL UNIQUE DEFAULT nextval('seq_tabulka'),
     ...,
     id_referencovana tabulka int REFERENCES referencovanatabulka
     ...,
   PRIMARY KEY (id_tabulka));

* pro primární klíče nepoužívejte volbu autoincrement, ale sekvence

• sekvence je možné buďto zadat klikacím způsobem nebo je v SQL klientu předpřipravit pomocí

CREATE SEQUENCE seq_tabulka;

• defaultní hodnotu primátního klíče nastavte na nextval('seq_tabulka')

* Pro úplnost - databázi lze vytvořit ručně, např. pomocí následujících dotazů

• (ruční způsob má tu výhodu, že je vše vidět na jednom místě a nic není skryto za klikání myši)

 CREATE SEQUENCE seq_mistnost;
 CREATE TABLE mistnost (
      id_mistnost     int             NOT NULL DEFAULT nextval('seq_mistnost'),
      nazev           varchar(32)     NOT NULL UNIQUE,
      PRIMARY KEY (id_mistnost));
 CREATE SEQUENCE seq_senzor;
 CREATE TABLE senzor (
      id_senzor       int             NOT NULL UNIQUE DEFAULT nextval('seq_senzor'),
      nazev           varchar(64)     NOT NULL UNIQUE,
      popis           text,
      id_mistnost     int             REFERENCES mistnost ON UPDATE CASCADE ON DELETE SET NULL,
      PRIMARY KEY (id_senzor));
 CREATE SEQUENCE seq_velicina;
 CREATE TABLE velicina (
      id_velicina     int             NOT NULL UNIQUE DEFAULT nextval('seq_velicina'),
      nazev           varchar(64)     NOT NULL UNIQUE,
      znacka          varchar(10)     NOT NULL,
      jednotka        varchar(10),
      PRIMARY KEY (id_velicina));
 CREATE TABLE velicinasenzor (
      id_velicina     int             NOT NULL REFERENCES velicina ON UPDATE CASCADE ON DELETE CASCADE,
      id_senzor       int             NOT NULL REFERENCES senzor   ON UPDATE CASCADE ON DELETE CASCADE,
      PRIMARY KEY (id_velicina,id_senzor));
 CREATE TABLE mereni (
      id_velicina     int             NOT NULL REFERENCES velicina ON UPDATE CASCADE ON DELETE CASCADE,
      id_senzor       int             NOT NULL REFERENCES senzor  ON UPDATE CASCADE ON DELETE CASCADE,
      cas             timestamp       NOT NULL DEFAULT now(),
      hodnota         float           NOT NULL,
      PRIMARY KEY (id_velicina,id_senzor,cas));
 CREATE SEQUENCE seq_podminka;
 CREATE TABLE podminka (
      id_podminka     int             NOT NULL UNIQUE DEFAULT nextval('seq_podminka'),
      id_velicina     int             NOT NULL REFERENCES velicina ON UPDATE CASCADE ON DELETE CASCADE,
      id_senzor       int             NOT NULL REFERENCES senzor   ON UPDATE CASCADE ON DELETE CASCADE,
      nazev           varchar(64)     NOT NULL UNIQUE,
      poznamka        text,
      prahovahodnota  float           NOT NULL,
      typ             char            CHECK (typ IN ('>','<')),
      PRIMARY KEY (id_podminka));
 CREATE SEQUENCE seq_akce;
 CREATE TABLE akce (
      id_akce         int             NOT NULL UNIQUE DEFAULT nextval('seq_akce'),
      nazev           varchar(64)     NOT NULL UNIQUE,
      PRIMARY KEY (id_akce));
 CREATE SEQUENCE seq_pravidlo;
 CREATE TABLE pravidlo (
      id_pravidlo     int             NOT NULL UNIQUE DEFAULT nextval('seq_pravidlo'),
      nazev           varchar(64)     NOT NULL UNIQUE,
      poznamka        text,
      id_akce         int             NOT NULL REFERENCES akce ON DELETE CASCADE ON UPDATE CASCADE,
      PRIMARY KEY (id_pravidlo));      
 CREATE TABLE pravidlopodminka (
      id_pravidlo     int             NOT NULL REFERENCES pravidlo ON DELETE CASCADE ON UPDATE CASCADE,
      id_podminka     int             NOT NULL REFERENCES podminka ON DELETE CASCADE ON UPDATE CASCADE,
      PRIMARY KEY (id_pravidlo, id_podminka));

 INSERT INTO senzor (nazev,popis) VALUES ('S1','Meterologický senzor na střeše');
 INSERT INTO senzor (nazev,popis) VALUES ('M1T','Teploměr v místnosti M1');
 INSERT INTO velicina (nazev,znacka,jednotka) VALUES ('teplota','t','C');
 INSERT INTO velicina (nazev,znacka,jednotka) VALUES ('vítr','v','m/s');
 INSERT INTO mistnost(nazev) VALUES ('M1');