Aritmetické operátory
*
/
+
-
% (MOD)
priorita operátorů se dá upravit pomocí závorek
• Vypište příjmení, jména a platy zaměstnanců včetně pěti procentní prémie. • Vypište název pracovní pozice a k ní připojte průměr maximálního a minimálního platu.
Chceme-li po databázi, aby nám vypočítala aritmetický výraz, musíme dodržet syntaxi. To znamená, že nesmí chybět část FROM. V takovýchto případech použijeme tabulku dual. • Pomocí tabulky dual vypočítejte hodnotu následujícího výrazu (33*((18-12)/3)+((24-2*6)/4)2)/32
Spojovací operátor: || • Spojovací operátor: || (platí pro Oracele, v jiných DBS lze nahradit funkcí CONCAT() viz později.) • spojuje – – – –
sloupce aritmetické výrazy konstanty řetězce znaků, čísel nebo dat
• vytváří řetězec • Vypište seznam, který bude obsahovat záznamy ve tvaru „číslo oddělení – název oddělení“.
Aliasy • umožňují libovolné pojmenování vypisovaných sloupců • nepovinné klíčové slovo AS • obsahuje-li alias – mezery – speciální znaky – chceme malá a velká písmena,
pak je nutné použít uvozovky „A lias“ • Spojte do jednoho sloupce jméno a příjmení zaměstnance a pojmenujte tento sloupec jména zaměstnanců.
NULL • • • • •
Hodnota, která je nedostupná nepřiřazená neznámá neaplikovatelná
DISTINCT • potlačuje duplicitní záznamy • musí následovat za SELECT • Vyberte čísla oddělení, ve kterých je alespoň jeden zaměstnanec. • Vyberte čísla všech vedoucích pracovníků.
Relační operátory • • • • • •
= > >= < <= <>, !=, ^=
rovno větší než větší nebo rovno menší než menší nebo rovno nerovno
• při práci se znaky, textovými řetězci a daty je potřeba použít apostrofy • Rozeznávají se malá a velké písmena (case Sensitive)
Další porovnávací operátory • • • • •
BETWEEN … AND … LIKE (%, _, \) IN IS NULL IS NOT NULL
• Vyberte všechny zaměstnance, kteří nastoupili mezi 2. 1. 2007 a 9. 7. 2008. • Vypište všechna příjmení zaměstnanců, jejichž příjmení má jako druhé písmeno o a poslední k. • Vypište všechny zaměstnance, kteří pracují na odděleních číslo (10,15,23) • Vypište jména a příjmení všech zaměstnanců, kteří nemají nadřízeného
Priorita operátorů Priorita 1 2 3 4 5 6 7 8
Operace Aritmetické, + - * / Zřetězení || Porovnání <, <=,…, <> IS (NOT) NULL, (NOT) IN, LIKE (NOT) BETWEEN NOT AND OR
Funkce V dbs rozlišujeme dva typy funkcí:
Víceřádkové funkce • • • • •
AVG COUNT MAX MIN SUM
Jednořádkové funkce
Znakové funkce • • • • • • • •
• •
•
LOWER – všechna písmena malá UPPER – všechna písmena velká INITCAP – první písmeno v každém slovu velké CONCAT – sřetězení SUBSTR('řetězec', číslo znaku kde začít počítáno od 1, kolik zanků) – podřetězec LENGTH – délka řetězce INSTR('řetězec', 'zvak') – najde pozici znaku v daném řetězci LPAD(řetězec, počet znaků, ’znak, který se bude doplňovat zleva’) – vypíše na daný počet znaků a zbytek doplní zleva požadovaným znakem RPAD – totéž jen zprava TRIM('H' FROM 'HelloWorld') --> elloWorld Odstraní všechny zadané znaky ze začátku nebo konce řetězce. REPLACE (string1, string_to_replace, [replacement_string]) – V prvním uvedeném řetězci nahradí podřetězce na drhém místě funkce řetězci na třetím místě
Data (čas) • SYSDATE – vrátí aktuální datum SELECT SYSDATE FROM DUAL; • Datum je uchováváno jako číslo pro ho lze odčítat, sčítat apod. • Vypočítejte, kolik máte dnes dnů MONTHS_BETWEEN ADD_MONTHS NEXT_DAY LAST_DAY ROUND TRUNC
Number of months between two dates Add calendar months to date Next day of the date specified Last day of the month Round date Truncate date
Předpokládejme, že SYSDATE = '25-JUL-95' Function ROUND (SYSDATE, 'MONTH') ROUND (SYSDATE, 'YEAR') TRUNC (SYSDATE, 'MONTH') TRUNC (SYSDATE, 'YEAR’)
Result 01-AUG-95 01-JAN-96 01-JUL-95 01-JAN-95
Převodní funkce: • Vycházejme ze čtyř základních typů: • VARCHAR2 – řetězce s proměnnou délkou • CHAR – řetězce s fixní délkou • NUMBER – typ pro čísla • DATE – typ pro data v DBS od Oracle se implicitně zobrazují DD-MON-YY (např. 19-JUN-04)
Datum -> text I • TO_CHAR (sloupec s datem, 'formát požadovaného zobrazení') YYYY YEAR MM MONTH MON DY DAY DD
Full year in numbers Year spelled out Two-digit value for month Full name of the month Three-letter abbreviation of the month Three-letter abbreviation of the day of the week Full name of the day of the week Numeric day of the month
HH24:MI:SS AM DD “of” MONTH DDspth Ddspth ddspth DDD or DD or D
15:45:32 PM 12 of October FOURTEENTH Fourteenth fourteenth Day of year, month or week
Datum -> text II Current Year Specified Date RR Format YY Format 1995 1995 2001 2001
27-OCT-95 27-OCT-17 27-OCT-17 27-OCT-95
1995 2017 2017 1995
1995 1917 2017 2095
If the specified two-digit year is: If two digits of the 0-49 current year are: 50-99
0-49 The return date is in the current century
50-99 The return date is in the century before the current one
The return date is in the century after the current one
The return date is in the current century
Číslo -> text TO_CHAR(číslo, 'formát výstupu') ELEMENT 9 0 $ L . , MI PR EEEE V B
DESCRIPTION Numeric position (# of 9‟s determine width) Display leading zeros Floating dollar sign Floating local currency symbol Decimal point in position specified Comma in position specified Minus signs to right (negative values) Parenthesize negative numbers Scientific notation ( must have four EEEE) Multiply by 10 n times (n= number of 9‟s after V) Display zero values as blank, not 0
EXAMPLE 999999 099999 $999999 L999999 999999.99 999,999 999999MI 999999PR 99.999EEEE 9999V99 B9999.99
RESULT 1234 001234 $1234 FF1234 1234.00 1,234 1234<1234> 1,23E+03 9999V99 1234.00
Vyhodnocení zanořených funkcí • Jednořádkové funkce je možné do sebe zanořovat. SELECT TO_CHAR( NEXT_DAY(ADD_MONTHS(datum_nastupu, 6), 'pátek'),'fmDay, Month DDth, YYYY') AS "Next Evaluation" FROM zamestnanci WHERE id_zamestnanci=100;
• Krok 1: k datu nástupu se přidá šest měsíců. • Krok 2: určí se datum prvního pátku, který bude následovat. • Krok 3: převede datum do textového řetězce požadovaného formátu
Funkce pro práci s hodnotou NULL NVL • Funkce NVL převede hodnotu null na známou hodnotu s daným datový typ, a to buď datum, řetězcem nebo číslo. Datové typy nulové hodnoty sloupce a nová hodnota musí být stejná. NVL2 • Tato funkce operuje se třemi parametry. Pokud je první hodnota není null, vrátí funkce hodnotu uvedenou jako první parametr, má-li první parametr hodnotu null, funkce vrátí hodnotu třetího parametru. NULLIF • Tato funkce porovnává dvě hodnoty. Pokud se rovnají, je vrácena hodnota NULL. Jestliže se nerovnají, je vrácena hodnota prvního parametru. COALESCE • Tato funkce je rozšířením funkce NVL. Její výhodou je, že je schopná zpracovat více atributů. Funkce prochází všechny uvedené parametry dokud nenalezne neNULLový a ten vypíše
https://trust.mendelu.cz/apex/ Workspace DBS_CVICENI Username student_xXX Password student_xXX (pokud jste si heslo už nezměnili)
Příklady 1. Vypište názvy všech zemí začínající na A a obsahující písmeno n. 2. Vypište adresu poboček, které mají orientační číslo dělitelné pěti beze zbytku. 3. Vypište unikátní názvy oddělení, které obsahují k nebo o
4. Vypište datum prodeje ve tvaru den v týdnu den v měsíci. měsíc slovy a poslední dvě číslice roku, kde byla celková tržba vyšší než 200 tisíc a zároveň menší než 350 tisíc. 5. Vypište název producentů, kteří nemají uvedený telefonní kontakt. 6. Vypište název a telefonní spojení producentů, kde kontaktní osoba se křestním jménem jmenuje Monika
7. Vypište datum všech prodejů a jejich celkovou cenu a počet prodaných, kde se produkt 'Produkt_SE 50' od firmy 'firma_39' prodal alespoň po osmi kusech. Celkovou cenu zarovnejte doprava a zaokrouhlete na stovky a vypište na 10 platných cifer a před jako první znak vypište dolar. Datum vypište podle příkladu: čtrnáctý led 09. Sloupce pojmenujte datum a cena.
8. Vypište názvy produktů a jejich pořizovací cenu, jejichž pořizovací cena je celé číslo.
9. Odkdy dokdy byly v oddělení potravin na pobočce Brno 2 nabízeny produkty dražší než 350. 10. Vypište maximální, minimální a průměrný plat všech zaměstnanců a celkové mzdové náklady (zaokrouhlený na dvě desetinná místa). Sloupce pojmenujte odpovídajícím způsobem. Mezi řády tisíců udělejte čárku 11. Vypište v kolika zemích se vyrábí nějaký produkt.
12. Vypište, kolik měsíců jste na světě. 13. Vypište jakým dnem (myšleno, pondělí, úterý, ...) končí aktuální měsíc. 14. Vypište datum prvního pátku roku 2015. 15. Vypište jméno a příjmení zaměstnanců a příjmení jejich vedoucích, jejichž plat je nad průměrný. Pokud nemají vedoucího vypište „bez nadřízeného“.
16. Ze jména a příjmení vytvořte login ve tvaru první písmeno křestního jména a prvních pět znaků příjmení vše malými písmeny. Loginy vytvořte tak, aby se v nich nevyskytovala diakritika. 17. Zjistěte, zda takto vytvořené loginy jsou pro daný soubor zaměstnanců unikátní. Výsledkem bud buď ano nebo ne.
18. Vypište název producenta a jeho telefonní číslo, pokud nemá uveden telefon, vypište email. Pokud nemá ani e-mail, vypište kontaktní osobu. Pokud nemá zaznamenánu ani tu, vypište: „Nemá uveden žádný kontakt.“ Tento sloupec pojmenujte kontakt. 19. Vypište jméno, příjmení, počet dnů v zaměstnání a název jejich pracovní pozice u zaměstnanců, kteří k 1. 1. 2008 byli již minimálně půl roku zaměstnáni a seřaďte je podle doby pracovního poměru od nejdéle pracujícího.
20. Vypište účet od prodej(e/ů), kter(ý/é) byl(y) provedeny 15. ledna 2009 na pobočce v Břeclavi a jejich celková cena byla mezi 10 a 12 tisíci. 21. Ověřte, zda celková cena prodeje odpovídá prodanému zboží v rámci daného prodeje. Zjistěte v kolik případech tomu tak není
