====== SQL dotazy - SELECT ======

==== Připojení k databázi ====

V této hodině budeme pracovat s demonstrační databází **Foodmart**. Ta obsahuje data z datového skladu popisujícího prodeje jídla a doprovodné informace. Zde budeme pracovat s tabulkou zaměstnanci (table employee) a tabulkou sklady (table store). Vztah mezi těmito tabulkami je 1:N.

K databázi se připojíme pomocí programu {{http://www.pgadmin.org|Pgadmin3}}.

Vytvoříme připojení k serveru
 
{{courses:a4b33ds:foodmart.png|Foodmart connection}}
  
Postupným rozklikáváním hierarchie nalezneme databázi Foodmart, kterou máme právo prohledávat. Následně se přepneme do okna SQL, kde můžeme zkoušet připravené dotazy.

Na následujícím obrázku je nakresleno schéma databáze pro lepší orientaci v tabulkách databáze.

{{courses:a4b33ds:foodmarter.png|FoodMart Databaze}}
  
==== Jednoduché dotazy ====

Pro výběr dat slouží v SQL příkaz SELECT, jehož obecné schéma je: 

**SELECT** sloupce 

**FROM** tabulky 

**WHERE** podmínky 

GROUP BY spojit podle... 

HAVING podmínky na skupiny 

**ORDER BY** seřadit podle...   

V této části se seznámíme s tučně označenými klauzulemi příkazu SELECT, tj. FROM, WHERE, ORDER BY. Příkazy budeme přímo zkoušet na databázi Foodmart. Uvedené příklady berte jako ukázky příkazů, vymyslete ke každému příkazu jiný podobný. 

Vypracování je možné získat označením textu za slovem SELECT, napsáno bíle.

  - Vypište celou tabulku zaměstnanců (skladu). 
    * SELECT <fc #FFFFFF>* FROM employee</fc>
  - Vypište pouze sloupce first_name, last_name, birth_date z tabulky zaměstnanců. 
    * SELECT <fc #FFFFFF>first_name, last_name, birth_date FROM employee</fc>
  - Vypište jména pozic zaměstnanců (sloupec position_title) tak, aby se žádnéjméno neopakovalo.
    * SELECT <fc #FFFFFF>distinct position_title FROM employee</fc>
  - Vypište přijmení zaměstnanců podle abecedy od a do z
    * SELECT <fc #FFFFFF>distinct last_name FROM employee order by last_name asc</fc>
  - Vypište přijmení zaměstnanců podle abecedy od z do a
    * SELECT <fc #FFFFFF>distinct last_name FROM employee order by last_name desc</fc>
  - Vypište jména a přijmení zaměstnanců a seřaďte je podle abecedy nejprve podle přijmení a pak podle jména
    * SELECT <fc #FFFFFF>first_name, last_name FROM employee order by last_name, first_name
</fc>
  - Vypište pouze zaměstnance s platem větším než 40.000
    * SELECT <fc #FFFFFF>* from employee where salary>40000</fc>
  - Vyzkoušejte postupně další podmínky (=, >, <, >=, <=)
  - Vypište pouze zaměstnance s platem mezi 10.000 a 20.000
    * SELECT <fc #FFFFFF>* from employee where salary between 10000 and 20000</fc>
  - Vyzkoušejte i složené podmínky s operátory AND, OR, NOT 
  - Vypište pouze zaměstnance jejichž přijmení začíná na S
    * SELECT <fc #FFFFFF>* from employee where last_name like 'S%'</fc>
  - Vypište pouze zaměstnance jejichž jméno končí na S a srovnejte je podle abecedy
    * SELECT <fc #FFFFFF>* from employee where first_name like '%s' order by last_name</fc>
  - Spojte tabulku zaměstnanců (employee) a tabulku skladů (store) a vypište všechny údaje.
    * SELECT <fc #FFFFFF>* from store as s, employee as e where s.store_id=e.store_id</fc>
  - Vypište všechny zaměstnance, kteří pracují v obchodu Store 1.
    * SELECT <fc #FFFFFF>* from store as s, employee as e where s.store_id=e.store_id and store_name='Store 1'</fc> 
  - Spočítejte kolik takových zaměstnanců v databázi existuje.
    * SELECT <fc #FFFFFF>count(*) from store as s, employee as e where s.store_id=e.store_id and store_name='Store 1'</fc> 

==== Spojování tabulek pomocí JOIN ====
V předchozím cvičení je tabulka zaměstnanců (employee) a tabulku skladů (store) spojena pomocí podmínky v klazuli WHERE, což je nejjednodušší varianta spojení tabulek. 

SELECT * from store, employee where store.store_id=employee.store_id

můžeme přepsat pomocí **INNER JOIN**

SELECT * from employee INNER JOIN store ON store.store_id=employee.store_id

Představme si, že existuje zaměstnanec, kterému zatím nebylo přiděleno pracoviště (nemá vyplněno store_id). Takový zaměstnanec nesplňuje podmínku pro spojení tabulek a proto vypadává z výsledné tabulky. To může být nežádoucí například v případě, že chceme vypsat všechny zaměstnance a jejich pracoviště (pokud zaměstnanec nemá pracoviště, chceme ho mít v seznamu s nevyplněným pracovištěm). Pro tento účel existují varianty JOIN -
**LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL JOIN**.

Příklady:

Vypíše všechny zaměstnance i ty, pro které neexistuje vazba do tabulky store.\\
SELECT * from employee LEFT OUTER JOIN store ON store.store_id=employee.store_id
  
Vypíše pouze zaměstnance, pro které existuje vazba do tabulky store, ale doplní i ty řádky z tabulky store, pro které neexistuje vazba do tabulky employee.\\
SELECT * from employee RIGHT OUTER JOIN store ON store.store_id=employee.store_id
 
Vypíše všechny zaměstnance a všechny řádky z tabulky store bez ohledu na vazbu mezi tabulkami.\\
SELECT * from employee FULL JOIN store ON store.store_id=employee.store_id       

==== Dotazy s klauzulemi GROUP BY, HAVING ====

Pro výběr dat slouží v SQL příkaz SELECT: 

**SELECT** sloupce 

FROM tabulky 

WHERE podmínky 

**GROUP BY** spojit podle... 

**HAVING** podmínky na skupiny 

ORDER BY seřadit podle...   

V této části se zaměříme na klauzule GROUP BY a HAVING. Doposud jsme pracovali s jednotlivými zaměstnanci. Často potřebujeme pracovat ne pouze s jednotlivými zaměstnanci, ale se skupinami zaměstnanců. Například v našem příkladě nás mohou zajímat skupiny zaměstnanců podle obchodu, ve kterém pracují (tabulka store). Vzhledem k tomu, že pracujeme se skupinou (množinou), nás budou zajímat tzv. agregované hodnoty - počet COUNT, průměr AVG, minimum MIN, maximum MAX, součet SUM pro zvolené skupiny. Například nás nezajímá věk jednoho konkrétního zaměstnance, ale zajímáme se o charakteristiku celé skupiny zaměstnanců, tedy o průměrný věk, nebo o nejnižší věk apod.

Vypracování je možné opět získat označením textu za slovem SELECT, napsáno bíle.

  - Zjistěte počty zaměstnanců podle jejich profese (position_title).
    * SELECT <fc #FFFFFF>position_title, count(*) FROM employee GROUP BY position_title ORDER BY position_title</fc>
  - Zjistěte průměrné platy zaměstnanců podle profese, seřaďte podle průměrného platu vzestupně.
    * SELECT <fc #FFFFFF>position_title, avg(salary) as avg FROM employee GROUP BY position_title ORDER BY avg</fc>
  - Zjistěte průměrné platy zaměstnanců - mužů podle profese, seřaďte podle průměrného platu sestupně.
    * SELECT <fc #FFFFFF>position_title, avg(salary) as avg FROM employee WHERE gender='M' GROUP BY position_title ORDER BY avg desc</fc>
  - Zjistěte pro každý obchod (store) počet zaměstnanců, seřaďte podle jména obchodu.
    * SELECT <fc #FFFFFF>store_name, count(*) FROM store as s, employee as e WHERE s.store_id=e.store_id GROUP BY store_name ORDER BY store_name</fc>
  - Zjistěte jména obchodů s více jak 50 zaměstnanci.
    * SELECT <fc #FFFFFF>store_name, count(*) as count FROM store as s, employee as e WHERE s.store_id=e.store_id GROUP BY store_name HAVING count(*)>50</fc>
  - Zjistěte jména obchodů, kde pracuje více než 40 žen.
    * SELECT <fc #FFFFFF>store_name, count(*) as count FROM store as s, employee as e WHERE s.store_id=e.store_id AND gender='F' GROUP BY store_name HAVING count(*)>40</fc>

==== Vnořené dotazy ====

V některých případech potřebujeme nejprve zjistit z databáze nějakou hodnotu, kterou následně použijeme v dotazu. Například chceme najít všechny zaměstnance obchodů s vyšším než průměrným platem. Nejprve musíme z databáze zjistit velikost průměrného platu: SELECT avg(salary) from employee. Výsledek následně použijeme pro hledání všech zaměstnanců, kteří mají plat vyšší než průměrný: SELECT * from employee where salary>(SELECT avg(salary) from employee)

  - Zjistěte počet zaměstnanců, kteří měli nižší než je průměrný věk při nástupu do zaměstnání. Pomůcka: věk při nástupu do zaměstnání je (date_part('year',hire_date)-date_part('year',birth_date)).
    * SELECT <fc #FFFFFF> count(*) FROM employee WHERE (date_part('year',hire_date)-date_part('year',birth_date))<(SELECT avg(date_part('year',hire_date)-date_part('year',birth_date)) FROM employee)</fc>



====== Studentské databáze ======

== Změna hesla ==

  ALTER USER jmeno WITH PASSWORD 'nove_heslo'

==== Zkušební databáze ====

SQL dotaz pro vytvoření databáze:

   CREATE SEQUENCE seq_sleva;
   CREATE TABLE sleva (
        id_sleva        int             NOT NULL UNIQUE DEFAULT nextval('seq_sleva'),
        nazev           varchar(64)     NOT NULL UNIQUE,
        sleva           numeric(2)      NOT NULL CHECK (sleva BETWEEN 0 AND 100),
        PRIMARY KEY (id_sleva));
  GRANT USAGE ON SEQUENCE seq_sleva TO student_db12_00;
  GRANT ALL PRIVILEGES ON TABLE sleva TO student_db12_00;

   CREATE SEQUENCE seq_zbozi;
   CREATE TABLE zbozi (
    id_zbozi            int             NOT NULL UNIQUE DEFAULT nextval('seq_zbozi'),
    nazev               varchar(128)    NOT NULL UNIQUE,
    popis               text,
    id_sleva            int             REFERENCES sleva ON UPDATE CASCADE ON DELETE SET NULL,
    cena                numeric(2)      CHECK (cena >0),
    PRIMARY KEY (id_zbozi));
   GRANT USAGE ON SEQUENCE seq_zbozi TO student_db12_00;
   GRANT ALL PRIVILEGES ON TABLE zbozi TO student_db12_00;

  CREATE SEQUENCE seq_faktura;
  CREATE TABLE faktura (
    id_faktura          int             NOT NULL UNIQUE DEFAULT nextval('seq_faktura'),
    odberatel           varchar(128)    NOT NULL,
    prodavac            varchar(64)     NOT NULL DEFAULT (user),
    PRIMARY KEY (id_faktura));
  GRANT USAGE ON SEQUENCE seq_faktura TO student_db12_00;
  GRANT ALL PRIVILEGES ON TABLE faktura TO student_db12_00;


  CREATE TABLE fakturazbozi (
    id_faktura          int             NOT NULL REFERENCES faktura ON UPDATE CASCADE ON DELETE CASCADE,
    id_zbozi            int             NOT NULL REFERENCES zbozi ON UPDATE CASCADE ON DELETE CASCADE,
    id_sleva            int             REFERENCES sleva ON UPDATE CASCADE ON DELETE SET NULL,
    cena                numeric         NOT NULL,
    sleva               numeric(2),
    ks                  numeric         NOT NULL CHECK(ks>0),
    PRIMARY KEY (id_faktura,id_zbozi));
  GRANT ALL PRIVILEGES ON TABLE fakturazbozi TO student_db12_00;


Vkládání dat
  insert into sleva(nazev,sleva) VALUES ('AKCE: dnes 20%',20);
  insert into sleva(nazev,sleva) VALUES ('TOTALNÍ VÝPRODEJ: 80%',80);


  INSERT INTO zbozi(nazev,popis,id_sleva) VALUES ('ponožky modré','Krásné modré ponožky, co musíte mít',NULL);
  INSERT INTO zbozi(nazev,popis,id_sleva) VALUES ('ponožky rudé','Kdo by nechěl tyto krásně rudé ponožky',1);
  INSERT INTO zbozi(nazev,popis,id_sleva) VALUES ('ponožky děravé','druhá jakost, velká sleva',3);

  INSERT INTO faktura (odberatel) VALUES ('Pepa Novák, Technická 8, Praha 6');
  INSERT INTO faktura (odberatel) VALUES ('Pepina Nováková, Technická 18, Praha 6');
  INSERT INTO faktura (odberatel) VALUES ('Pepina Nováková, Technická 18, Praha 6');

  INSERT INTO fakturazbozi (id_faktura,ks,id_zbozi,id_sleva,cena,sleva)
        SELECT 1,10,id_zbozi,id_sleva,cena,sleva 
            FROM zbozi LEFT JOIN sleva USING (id_sleva)
            WHERE id_zbozi = 13;
  INSERT INTO fakturazbozi (id_faktura,ks,id_zbozi,id_sleva,cena,sleva)
        SELECT 1,20,id_zbozi,id_sleva,cena,sleva 
            FROM zbozi LEFT JOIN sleva USING (id_sleva)
            WHERE id_zbozi = 14;
  INSERT INTO fakturazbozi (id_faktura,ks,id_zbozi,id_sleva,cena,sleva)
        SELECT 2,10,id_zbozi,id_sleva,cena,sleva 
            FROM zbozi LEFT JOIN sleva USING (id_sleva)
            WHERE id_zbozi = 13;
  INSERT INTO fakturazbozi (id_faktura,ks,id_zbozi,id_sleva,cena,sleva)
        SELECT 2,30,id_zbozi,id_sleva,cena,sleva 
            FROM zbozi LEFT JOIN sleva USING (id_sleva)
            WHERE id_zbozi = 15;
            

  INSERT INTO fakturazbozi (id_faktura,ks,id_zbozi,id_sleva,cena,sleva)
        SELECT 3,100,id_zbozi,id_sleva,cena,sleva 
            FROM zbozi LEFT JOIN sleva USING (id_sleva)