5. Jazyk SQL

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

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;

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

• 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 …

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

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

Textová notace:

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]

• 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
  

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í.

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

Příklad 1

• 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