Gymnázium a Střední odborná škola, Rokycany, Mládežníků 1115 Číslo projektu:
CZ.1.07/1.5.00/34.0410
Číslo šablony:
V/2 - inovace směřující k rozvoji odborných kompetencí
Název materiálu:
Pracovní listy – Databáze
Ročník:
3. ročník (IT)
Identifikace materiálu:
BEL_52_DAT_PL7
Jméno autora:
Miloslav Bělský
Předmět:
Databáze
Tématický celek:
Výběrový dotaz
Anotace:
Obsahem tohoto pracovního listu je využití funkcí SQL – funkce pro práci s datem, čísly a konverzní funkce
Datum:
22. 10. 2013
7-1
Databáze Pracovní list č. 7
Funkce – numerické, datové, konverzní Cíl Naučit se využívat víceřádkové a jednořádkové funkce v rámci výběrového dotazu. Uvědomit si, jak funkce pracují a jaké mají možnosti.
Výklad Jednořádkové funkce – numerické Mezi tyto funkce patří ROUND, TRUNC a MOD.
ROUND(číslo, přesnost) – funkce zaokrouhlí číslo na celé číslo. V případě uvedení přesnosti zaokrouhlí na příslušný počet desetinných míst. Záporná přesnost zaokrouhluje na desítky(-1), stovky (-2) atd.
TRUNC(číslo) – tato funkce pracuje stejně jako ROUND, ale číslo pouze ořízne, nezaokrouhluje.
MOD(dělenec, dělitel) – funkce vrací zbytek po celočíselném dělení.
Jaký bude rozdíl ve výsledku: ROUND(2.78, 1) a TRUNC(2.78, 1)? ____________________________________________________________________ Jednořádkové funkce – datové Mezi tyto funkce patří SYSDATE, MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND a TRUNC. Výsledkem všech funkcí je datum. Pozor na to, jak je nastavené prostředí databáze a s jakým formátem data pracuje!
SYSDATE – funkce vrací systémový čas v lokalizovaném formátu. Tzn. v ČR to je DD.MM.RRRR atd.
MONTHS_BETWEEN(datum1, datum2) kalendářních měsíců mezi dvěma daty.
ADD_MONTHS(datum, počet_měsíců) – funkce přidá počet_měsíců k zadanému datu.
NEXT_DAY(datum, 'pátek') – funkce vrátí datum nejbližšího páteku po zadaném datu.
–
tato funkce vrátí počet
7-2
LAST_DAY(datum) – funkce vrátí datum posledního dne v měsíci.
ROUND/TRUNC(datum, 'formát') – funkce zaokrouhlí/ořízne datum podle zadaného formátu ('MONTH', 'YEAR', …).
První den v měsíci, který nastane za půl roku, bude mít zaměstnanec pracovní hodnocení. Jaké funkce budete potřebovat? ____________________________________________________________________ Jednořádkové funkce – konverzní (převod mezi datovými typy) Databáze rozeznávají různé datové typy. Mezi nimi dokáží provádět tzv. implicitní konverzi. Vždy je ale jistější zadat konverzi explicitně, abychom měli jistotu. Převádět můžeme: datum na text, text na datum, číslo na text a text na číslo.
TO_CHAR(datum, 'formát') – funkce převede datum na text v zadaném formátu. Lze používat různé zástupné znaky: YYYY – plný rok, MM – měsíc na dvě číslice, DAY – název dne v týdnu; MON, MONTH, DD, HH24, MI, SS atd.
TO_CHAR(číslo, 'formát') – funkce převede číslo na text v zadaném formátu. Opět lze využívat různé zástupné znaky: 9 – cifra, 0 – zobrazí 0, L – měna, MI – mínus vpravo, atd.
TO_NUMBER(text, 'formát') - funkce převede text na číslo v zadaném formátu (nepovinný), pokud je to možné.
TO_DATE(text, 'formát') - funkce převede text na datum podle zadaného formátu. Text musí odpovídat pořadí hodnot ve formátu (stejné formátovací znaky jako u TO_CHAR).
Jednořádkové funkce – práce s NULL hodnotami V případě, že v tabulce narazíme na hodnoty NULL, můžeme chtít zobrazit místo této hodnoty smysluplnější informaci. Např. není-li u zaměstnance zadán plat, tak vypíšeme 'zaměstnanec pracuje zadarmo '. K dispozici máme funkce NVL, NVL2, NULLIF a COALESCE.
NVL(sloupec, náhrada) – v případě hodnoty NULL ve sloupci zobrazí náhradu.
NVL2(sloupec, hodnota1, hodnota2) – v případě, že ve sloupci není NULL, vloží hodnotu1, jinak vloží hodnotu2. NULLIF (výraz1, výraz2) – porovná výrazy, pokud jsou stejné, vrací NULL, jinak vrací první výraz. COALESCE(v1, v2, …, vN) – vrací první první výraz, který není NULL.
7-3
Příklady k procvičení Vytvářejte dotazy podle následujícího zadání. Příklady označené *** jsou velmi obtížné. Pod každým úkolem je prostor na poznámky: 1. Vypište příjmení a roční plat zaměstnance. Roční plat zaokrouhlete na desetitisíce pomocí funkcí ROUND a TRUNC. Porovnejte. (EMPLOYEES)
2. Určete výsledek dělení 500 : 13 ve tvaru celočíselný výsledek a zbytek. Např: 5 : 2 je 2 zbytek 1.
3. Určete plat každého zaměstnance za 1 minutu a zaokrouhlete na dvě desetinná místa. Vypište a seřaďte zaměstnance podle tohoto údaje vzestupně (EMPLOYEES)
4. Vypište najednou datum 15.7.2012 zaokrouhlené na rok a poté na měsíc pomocí funkce TRUNC a ROUND. Porovnejte výsledky.
5. Vypište dnešní datum podle následujícího formátu: '12:52 Pátek, 22. února 2013'.
6. Určete kolik let, kolik měsíců a kolik dní je zaměstnán libovolný zaměstnanec. (EMPLOYEES).
7. Vypište zaměstnance, kteří jsou zaměstnáni déle, než 20 let. Vypište také jejich plat navýšený o 10%.(EMPLOYEES)
8. Určete, jaké bude datum od příštího pátku za 14 dní.
7-4
9. ***Zaměstnanec má pracovní hodnocení jednou ročně vždy ve výročí nástupu do zaměstnání. Určete datum příštího pracovního hodnocení libovolného zaměstnance. (EMPLOYEES)
10. Dva měsíce před datem události je potřeba sjednat schůzku s klientem. Schůzky se plánují vždy na pondělí. Vypište název události, datum události a datum schůzky tak, aby bylo vidět, který je to den. (D_EVENTS).
11. Vypište platy zaměstnanců ve tvaru $10,500. (EMPLOYEES)
12. Zaměstnanec podal výpověď k dnešnímu dni. Určete, kdy skončí jeho pracovní poměr, jestliže výpovědní doba jsou dva měsíce od začátku příštího měsíce. Např. dnes je 20.3.2012, pracovní poměr skončí 31.5.2012.
13. Vypište všechna oddělení a číslo jejich vedoucího. V případě, že oddělení vedoucího nemá, vypište 'nemá vedoucího'. (DEPARTMENTS)
14. Vypište jméno, příjmení zaměstnance a jeho plat, navýšený o provizi. V případě, že provizi nedostává, tak jen plat. (EMPLOYEES)
15. Vypište všechna oddělení a v případě, že má přiděleného vedoucího, vypište 'Má vedoucího'. Jinak vypište 'Nemá vedoucího'. (DEPARTMENTS)
7-5
