SQL - Pokročilá práce s daty

[Samnick]

Pro hlubší či složitější práci s daty si bohužel nevystačíme pouze s SQL dotazem SELECT, některé data potřebují být při zobrazení různě spojována, nahrazována a nebo přidávány virtuální sloupce. Jedná se pouze o zobrazení, takže níže uvedené dotazy nemění žádné data, ale pouze vytváří zobrazovaný pohled na data.

 

V tomto příkladu budeme používat SQL klíčové slova JOIN, UNION, CASE a dále si ukážeme Subquery.

 

Vytvoříme si nové data:

První tabulka:

CREATE TABLE Zakaznici (
  id INT PRIMARY KEY,
  jmeno VARCHAR(50) NOT NULL,
  prijmeni VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  adresa VARCHAR(200) NOT NULL,
  mesto VARCHAR(50) NOT NULL,
  stat VARCHAR(50) NOT NULL,
  psc VARCHAR(10) NOT NULL
);

INSERT INTO Zakaznici (id, jmeno, prijmeni, email, adresa, mesto, stat, psc)
VALUES
  (1, 'Jan', 'Novák', 'jan.novak@email.com', 'Hlavní 1', 'Praha', 'Česká republika', '100 00'),
  (2, 'Petr', 'Svoboda', 'petr.svoboda@email.com', 'Náměstí 2', 'Brno', 'Česká republika', '602 00'),
  (3, 'Kateřina', 'Veselá', 'katerina.vesela@email.com', 'Hrad 3', 'Olomouc', 'Česká republika', '779 00'),
  (4, 'Miroslav', 'Kučera', 'miroslav.kucera@email.com', 'Dlouhá 4', 'Liberec', 'Česká republika', '460 01'),
  (5, 'Lucie', 'Novotná', 'lucie.novotna@email.com', 'Hradčany 5', 'Praha', 'Česká republika', '110 00');

 

Druhá tabulka:

CREATE TABLE Objednavky (
  id INT PRIMARY KEY,
  zakaznik_id INT NOT NULL,
  produkt VARCHAR(100) NOT NULL,
  mnozstvi INT NOT NULL,
  cena DECIMAL(10, 2) NOT NULL,
  datum DATE NOT NULL,
  FOREIGN KEY (zakaznik_id) REFERENCES Zakaznici(id)
);

INSERT INTO Objednavky (id, zakaznik_id, produkt, mnozstvi, cena, datum)
VALUES
  (1, 1, 'Kávovar', 1, 2499.00, '2022-01-15'),
  (2, 1, 'Mixér', 2, 1799.00, '2022-01-18'),
  (3, 2, 'Myčka', 1, 8999.00, '2022-01-22'),
  (4, 3, 'Trouba', 1, 5999.00, '2022-01-25'),
  (5, 4, 'Klimatizace', 1, 12999.00, '2022-02-01'),
  (6, 5, 'Prádelní stroj', 1, 8499.00, '2022-02-05'),
  (7, 2, 'Lednice', 1, 7999.00, '2022-02-07'),
  (8, 4, 'Tablet', 2, 5699.00, '2022-02-11');

 

JOIN

Spojení dvou tabulek pomocí sloupce, který mají obě nebo více tabulek společný.

Ukázka:

SELECT Objednavky.id, Zakaznici.jmeno, Zakaznici.prijmeni, Objednavky.produkt, Objednavky.mnozstvi, Objednavky.cena
FROM Objednavky
JOIN Zakaznici ON Objednavky.zakaznik_id = Zakaznici.id;

Výstup dat:

Výsledkem je tabulka obsahující sloupce id, jmeno, prijmeni, produkt, mnozstvi a cena spojená z tabulek Objednavky a Zakaznici.

 

UNION

spojení výsledků dvou a více dotazů do jednoho výstupu.

Ukázka:

SELECT produkt, mnozstvi, cena, zakaznik_id
FROM Objednavky
WHERE zakaznik_id = 1
UNION
SELECT produkt, mnozstvi, cena, zakaznik_id
FROM Objednavky
WHERE zakaznik_id = 3;

V tomto případě dotaz kombinuje výsledky dvou dotazů pomocí UNION. První SELECT vrací všechny řádky z tabulky Objednavky, kde má zákazník id 1. Druhý SELECT vrací všechny řádky z tabulky Objednavky, kde má zákazník id 3. UNION pak spojí výsledky obou dotazů do jedné tabulky a odstraní duplicity. Výsledkem dotazu bude tabulka obsahující řádky pro produkty, které si objednali zákazníci s id 1 nebo id 3.

 

CASE

použití podmínky pro generování nového sloupce za určitých podmínek dle specifikovaných podmínek. 

Ukázka:

SELECT produkt, mnozstvi, cena,
  CASE
    WHEN cena < 5000 THEN 'levný'
    WHEN cena >= 5000 AND cena < 10000 THEN 'střední'
    ELSE 'drahý'
  END AS cena_kategorie
FROM Objednavky;

Výsledkem bude tabulka obsahující sloupce produkt, mnozstvi, cena a cena_kategorie. Poslední sloupec je vytvořen pomocí podmínky CASE, která rozděluje produkty do tří kategorií podle jejich cen.

 

 

SubQUERY

V některých případech pro zobrazení dat potřebujeme první část dat nějak vyfiltrovat, na toto jsou vhodné subquery, jak je jasné z názvu, tak to znamená že je jeden příkaz vnořený do druhého. Takže pro použití výsledku jednoho dotazu jako vstup do druhého.

 

Ukázka:

SELECT jmeno, prijmeni
FROM Zakaznici
WHERE id IN (
  SELECT zakaznik_id
  FROM Objednavky
  WHERE produkt = 'Klimatizace'
);

Výsledkem bude tabulka obsahující sloupce jmeno a prijmeni pro zákazníky, kteří si objednali klimatizaci. Vnitřní SELECT vrací seznam ID zákazníků, kteří si objednali klimatizaci, a vnější SELECT používá tento seznam jako podmínku pro výběr zákazníků z tabulky Zakaznici.