# 5. Jazyk SQL

## Oracle a NLS

- typ DATE
- české řazení
- vestavěné funkce
- jak vyřešit potřebu datového typu SERIAL, když jej Oracle nenabízí

    SET echo ON
    -- v Oracle můžete nastavit řadu NLS
    --- National Language Settings parametrů
    SELECT * FROM nls_session_parameters;

    SELECT * FROM NLS_DATABASE_PARAMETERS;

## datový typ date je prakticky datetime

    CREATE TABLE test (a date);

    SELECT sysdate FROM dual;

    INSERT INTO test VALUES ('19-NOV-12');
    SELECT * FROM test;
    INSERT INTO test VALUES ('19-NOVEMBER-12');
    SELECT * FROM test;
    INSERT INTO test VALUES ('19.11.12');
    SELECT * FROM test;

    INSERT INTO test VALUES ('19.11.2012');
    SELECT * FROM test;

    INSERT INTO test VALUES (to_date('19.11.2012','DD.MM.YYYY'));
    SELECT * FROM test;

    ALTER session SET nls_date_format='DD.MM.YYYY';

    INSERT INTO test VALUES ('19.11.2012');
    SELECT * FROM test;

    ALTER session SET nls_date_format='DD.MM.YYYY HH:MI:SS';

    SELECT * FROM test;

### České řazení

    CREATE TABLE test2(a varchar(30));

    INSERT INTO test2 VALUES ('žofie');
    INSERT INTO test2 VALUES ('adam');
    INSERT INTO test2 VALUES ('čeněk');
    INSERT INTO test2 VALUES ('karel');
    INSERT INTO test2 VALUES ('chrudoš');

    SELECT * FROM test2 ORDER BY a;

    ALTER session SET nls_sort=xczech;

    SELECT * FROM test2 ORDER BY a;

Implicitní nastavení NLS u klienta SQL Developer se dá provést v
Preferences ⇒ Database ⇒ NLS

## úprava prostředí SQL*Plus

- set NLS_DATE_LANGUAGE, NLS_DATE_FORMAT a NLS_SORT lze aplikovat i v rámci skriptu pro SQL*Plus
- soubor login.sql v adresáři, ze kterého spouštíte SQL*Plus může obsahovat taková nastavení, provede se pak automaticky při přihlášení
- protože sqlplus neumí historii příkazů, je užitečné zabalit jeho volání do utility rlwrap
- na úrovni SQL*Plus máme sadu užitečných príkazů show (show all), set, define, describe …

## Dallší triky pro práci s Oraclem a SQL

- Jak si v SQLDeveloperu pomoci s výrobou testovacích dat?
- „Scriptování v SQL“

    SELECT 'drop table '||table_name||' cascade constraints;' AS prikaz
    FROM user_tables;

## Dotazy v SQL

Zapište zde uvedené dotazy v jazyku SQL.
Dotazy jsou zatím uvedeny v

- v přirozeném jazyku
- a u některých jako výraz v relační algebře
- přidejte zápis v SQL

## Mějme toto relační schéma datového úložiště

Textová notace:

## Dotazy řešitelné i pomocí relační algebry

klepnutí na číslo dotazu vyvolá zobrazení možného řešení

1. Jména zelených lodí, typy modrých lodí.
* Co, když se dvě zelené lodi jmenují stejně, potřebujeme je v seznamu vícekrát?

    LOD(BARVA = 'zelená')[JMENOL]

    LOD(BARVA = 'modrá')[LTYP]

- Poskytují zde uvedený výraz v RA a náš SQL dotaz stejný výsledek?

2. Jména zákazníků, kteří si rezervovali (alespoň jeden) modrý parník.

    {ZAKAZNIK * REZERVACE * LOD}(LTYP = 'parník' and BARVA='modrý')[JMENOZ]

- Co když je odpověď prázdná, můžeme vědět, že jsme hotovi?
- Jsou zápisy v RA a SQL ekvivalentní?
- Jak se v SQL provádí operace přejmenování?

3. Seznam typů lodí, které byly na plavbě s průvodcem.

    varianta 1:
    {LOD [LODID=ID_LOD] PLAVBA_S_PRUVODCEM }[LTYP]
    varianta 2:
    {LOD <LODID=ID_LOD] PLAVBA_S_PRUVODCEM }[LTYP]

4. Lodě (všechny atributy), které pokryly nějaké pravidelné spoje a byly také na některé plavbě s průvodcem.

    varianta 1:
    (LOD * POKRYTI)[LODID,JMENOL,LTYP,BARVA,POCET_MIST]
       ∩
    (LOD[LODID=ID_LOD] PLAVBA_S_PRUVODCEM)[LODID,JMENOL,LTYP,BARVA,POCET_MIST]

    varianta 2:
      (LOD <* POKRYTI) ∩ (LOD <LODID=ID_LOD] PLAVBA_S_PRUVODCEM)

    (LOD <LOD.LODID=POKRYTI.LODID] POKRYTI) ∩ (LOD <LODID=ID_LOD] PLAVBA_S_PRUVODCEM)

5. Lodě (všechny atributy), které pokryly nějaký pravidelný spoj anebo byly na plavbě s průvodcem.

    varianta 1:
       (LOD * POKRYTI)[LODID,JMENOL,LTYP,BARVA]
        ∪
       (LOD [LODID = ID_LOD] PLAVBA_S_PRUVODCEM)[LODID,JMENOL,LTYP,BARVA]

    varianta 2:
       (LOD <* POKRYTI)   ∪ (LOD <* PLAVBA_S_PRUVODCEM[id_lod -> LODID])

6. Lodě (všechny atributy), které nebyly rezervovány

    varianta 1:
       LOD !<* REZERVACE

    varianta 2:
       LOD[barva,jmenol,lodid,ltyp,pocet_mist]  ∖ (LOD <* REZERVACE)[barva,jmenol,lodid,ltyp,pocet_mist]

7. Typy lodí, které pokrývají pouze pravidelné spoje

       { LOD <* POKRYTI
        \
        LOD <LODID=ID_LOD] PLAVBA_S_PRUVODCEM
        \
        LOD <* REZERVACE
       }[LTYP];

- Budeme spokojeni s odladěností, jestliže dotaz nevybere (možná díky aktuálním datům) nic?

8. Vyberte zákazníky (ZID), kteří si rezervovali každou zelenou lod

       REZERVACE[ZID,LODID] ÷ LOD(BARVA='zelená')[LODID]

Rozepsání algoritmu dělení pomocí dvojité negace:

       T1:= LOD(BARVA='zelená')[LODID] x ZAKAZNIK[ZID] ... všechny možné rezervace
       T2:= LOD(BARVA='zelená')*> REZERVACE[LODID,ZID] ... uskutečněné rezervace zelených lodí
       T3:= {T1\T2}[ZID]                               ... zákazníci, kteří si nerezervovali některou zelenou loď
       T4:= T1[ZID]                                    ... zákazníci, kteří rezervovali některou zelenou loď
       T5:= T4 !<* T3                                  ... zákazníci, pro které neplatí, že si nerezervovali některou zelenou loď

9. Dvojice zákazníků (zid,jmenoz,prijmeni), kteří bydlí na stejné adrese

       ( ZAKAZNIK [ ADRESA  ->  ADR1,ZID ->  ZID1,JMENOZ -> JMENO1,PRIJMENI -> PRIJMENI1 ]
          [ADR1 = ADR2  ∧  ZID1 < ZID2]
         ZAKAZNIK [ ADRESA  -> ADR2, ZID ->  ZID2, JMENOZ -> JMENO2, PRIJMENI -> PRIJMENI2 ]
       ) [ ZID1, JMENO1, PRIJMENI1, ZID2, JMENO2, PRIJMENI2]

## Další dotazy na relační dělení

- Lodě, které pokryly všechny spoje na lince 1
- Lodě, které pokryly všechny linky, jedoucí ze stanice Hůrka (STRT)
- Linky, na kterých jezdí všechny typy lodí
- Námořníci, kteří dělali průvodce na všech parnících(typ)
- Lodě, na kterých dělali průvodce všichni námořníci, starší 44 let

## Dotazy, bez řešení pomocí relační algebry, například proto, že v RA jsou neřešitelné

klepnutí na číslo dotazu vyvolá zobrazení možného řešení

10. Průměrný věk lodníků, zaokrouhlete na dvě desetinná místa.

11. Různé agregace, jako „Seznam typů lodí, u každého uveďte, kolik lodí daného typu je.

12. Námořníci, kteří byli na plavbě s průvodcem méně, než 4x

13. Námořníci, kteří pokryli méně než 3 různé pravidelné linky (včetně těch, kteří nepokryli žádnou)

14. Pro každého lodníka vypište počet jeho plaveb s průvodcem

15. Jména Námořníků mladších 40 let, kteří mají za sebou alespoň 3 plavby s průvodcem na lodi typu škuner. Výstup řaďte sestupně dle jmen námořníků.

16. Námořníkům, kteří mají za sebou alespoň 5 pokrytí zvedněte plat o 15%

17. Přidejte k tabulce NAMORNIK sloupec pocet_plaveb a proveďte jednorázový dopočet.

18. Zákazníci, kteří si rezervovali každý parník s počtem míst většim než 150.

19. Vytvořte pohled se seznamem spojů, u nichž budou podrobnosti jejich linek.

20. Kteří lodníci sloužili na jednotlivých lodich?

21. Seznam rezervací včetně lodí, které nebyly rezervovány a zákazníků, kteří si nic nerezervovali.

22. Seznam námořníků. U každého uveďte, kolika plaveb s průvodcem se účastnil.

23. Rezervuj zákazníkům číslo 6 a 7 všechny zelené lodě.

24. Smaž zákazníkům číslo 6 a 7 všechny rezervace zelených lodí.

25. Vyber zákazníky, pro které počet jimi rezervovaných různých zelených lodí se rovná celkovému počtu všech zelených lodí.

## Odkazy

- SQL skript pro vytvoření datového úložiště a naplnění testovacími daty

## Tabulka pokrytí kategorií rozmanitosti dotazů

Příklad 1

A | 2, 3 | pozitivní dotaz nad spojením alespoň dvou tabulek (Seznam kateder - id, název, jejichž učitelé učil/učili v předmětech, které garantuje katedra KKKK), („vyber seznam se jmény a adresami pacientů, kteří byli vyšetřováni Mudr. Čermákem“ )
B | 6 | negativní dotaz nad spojením alespoň dvou tabulek (seznam semestrů - id, název, ve kterých NEzkoušel nikdo z katedry Zubních trhaček), („seznam se jmény a adresami pacientů, kteří nenavštívili lékaře Mudr. Čermáka“ ).
C | 7 | Vyber ty, kteří mají vztah POUZE k …(vyber seznam kateder učitelů, kteří učí/učili POUZE v letních semestrech), (# „seznam se jmény a adresami pacientů, kteří navštívili pouze Mudr. Čermáka ~ navštívili doktora Čermáka a nenavštívili žádného jiného lékaře)
D1 | 8 | Vyber ty, kteří/které jsou ve vztahu se všemi - dotaz s univerzální kvantifikací (seznam učitelů - id, jmeno, prijmeni, titul, kteří přednášeli ve VŠECH semestrech počínaje zimním semestrem 2001/2002 až do letního semestru 2007/2008 včetně),(„seznam se jmény a adresami lékařů, které navštívíli VŠICHNI pacienti, kteří někdy navštívili Mudr. Čermáka“)
D2 | 8 | kontrola výsledku dotazu z kategorie D1, například jestliže dotaz {seznam učitelů, kteří přednášeli ve VŠECH semestrech počínaje zimním semestrem 2001/2002 až do letního semestru 2007/2008 včetně} vybere učitele „123 Michal Valenta“, tak kontrolní dotaz bude {{Seznam semestrů ze zadaného obodobí, ve kterých přednášel Michal Valenta} \ {Seznam všech semestrů ze zadaného období}} a ten musí vrátit prázdnou množinu
F1 | 3 | spojení - JOIN ON
F2 | 2 | spojení - NATURAL JOIN,l JOIN USING
F3 | 8 | spojení - CROSS JOIN
F4 | 11 | jednostranné vnější polospojení - LEFT , RIGHT OUTER JOIN
F5 | 21 | plné (vnější) spojení - FULL (OUTER) JOIN
G1 | 8 | vnořený dotaz v klauzuli WHERE
G2 | 18 | vnořený dotaz v klauzuli FROM
G3 | 22 | vnořený dotaz v klauzuli SELECT
G4 | 18 3 | vztažený vnořený dotaz (EXISTS, NOT EXISTS)
H1 | 5 | množinové sjednocení - UNION
H2 | 6 | množinový rozdíl - MINUS (v Oracle)
H3 | 5 | množinový průnik - INTERSECT
I1 | 11 | agregační funkce (count, sum. min, max, avg)
I2 | 11 | agregační funkce nad seskupenými řádky - GROUP BY (HAVING)
J | 8 | stejný dotaz ve třech různých formulacích SQL
K | 15 | všechny klauzule - SELECT FROM WHERE GROUP BY HAVING ORDER BY
L | 19 | pohled VIEW
M | 19 | dotaz nad pohledem
N | 23 | Příkaz pro vložení množiny řádků - INSERT bez klauzule VALUES, např. „Rezervuj zákazníkovi č. 6 v různých časech všechny zelené lodě“
O | 16 | UPDATE s vnořeným SELECT příkazem
P | 24 | DELETE s vnořeným SELECT příkazem

## Další dotazy na relační dělení

- Lodě, které pokryly všechny spoje na lince 1
- Lodě, které pokryly všechny linky, jedoucí z Vranova (STRT)
- Linky, na kterých jezdí všechny typy lodí
- Námořníci, kteří dělali průvodce na všech škunerech (typ)
- Lodě, na kterých dělali průvodce všichni námořníci, starší 45 let