Cleverlance. SQL 1

SQL
Structured Query Language
vychází z angličtiny
Vlastnosti SQL:
Výkonnost
Snadná použitelnost
Komplexní funkčnost
STRANA

1.Dotaz
V nejjednodušším tvaru musí příkaz SELECT obsahovat:
Klauzuli SELECT, která specifikuje, které prvky (sloupce) se mají zobrazit (vybrat)
Klauzuli FROM, která určuje odkud, z které tabulky (tabulek) se data vybírají
SELECT * FROM zamestnanci;
STRANA

1.Dotaz
STRANA

Selekce
STRANA
SELECT *
FROM zamestnanci;

Formulace dotazu
SELECT * FROM zamestnanci WHERE pozice='Ucetni'
Vyber zaměstnance, kteří pracují na pozici účetní
Vyber účetní
Vyber ty záznamy z tabulky zamestnanci, které mají ve sloupci pozice hodnotu ‘Ucetni’
STRANA

Selekce
STRANA
SELECT *
FROM zamestnanci
WHERE pozice = 'Ucetni';

Selekce – porovnání s řetězcem
jmeno='Jan'
jmeno <> 'JAN'
jmeno <> 'Jan'
SELECT * FROM zamestnanci
WHERE pozice='Ucetni';
STRANA

Selekce – porovnání s číslem
SELECT *
FROM zamestnanci
WHERE oddeleni=3;
STRANA

Selekce – porovnání s datumem
Oracle:
TO_DATE(‘2004-03-17','yyyy-mm-dd')
MSSQL:
convert(datetime,‘2004-03-17',120)
SELECT *
FROM zamestnanci
WHERE datum_nastupu='01.01.2003';
STRANA

Operátory porovnání
Operátor
=
>
>=
<
<=
<> !=
Význam
Rovná se
Větší než
Větší nebo roven
Menší než
Menší nebo roven
Nerovná se
STRANA

Projekce
STRANA
SELECT jmeno, prijmeni
FROM zamestnanci;

Projekce
Projekce omezuje výsledek dotazu pouze na sloupce, které jsou vyjmenovány za klíčovým slovem SELECT
SELECT jmeno, prijmeni
FROM zamestnanci;
STRANA

Příklad: Selekce a projekce
STRANA
SELECT jmeno, prijmeni
FROM zamestnanci
WHERE pozice = 'Ucetni';

Cvičení
Vypište jméno a příjmení všech skladníků.
STRANA
SELECT jmeno, prijmeni
FROM zamestnanci
WHERE pozice = 'Skladnik';

Vypište jména zaměstnanců, kteří nastoupili před rokem 2003.
STRANA
SELECT jmeno, prijmeni
FROM zamestnanci
WHERE datum_nastupu < '01.01.2003';

Vypište jména a pozice zaměstnanců, kteří mají základní plat nižší než 10000.
STRANA
SELECT jmeno, prijmeni, pozice
FROM zamestnanci
WHERE zakladni_plat < 10000;

Vypište zaměstnance oddělení číslo 3
STRANA
SELECT jmeno, prijmeni
FROM zamestnanci
WHERE oddeleni = 3;

Kdo nastoupil do firmy 1.8.2004?
STRANA
SELECT jmeno, prijmeni
FROM zamestnanci
WHERE datum_nastupu = '01.08.2004';

Jaké je křestní jméno zaměstnance Větvičky?
STRANA
SELECT jmeno
FROM zamestnanci
WHERE prijmeni= 'Vetvicka';

Cvičení
Kdy nastoupil zaměstnanec s číslem 27?
STRANA
SELECT datum_nastupu
FROM zamestnanci
WHERE zamestnanec_id = 27;

DISTINCT
STRANA
SELECT DISTINCT pozice
FROM zamestnanci;

DISTINCT
Duplicity se z výstupu odstraňují pomocí klíčového slova DISTINCT
klíčové slovo DISTINCT se píše za klíčové slovo SELECT a před seznam sloupců v klauzuli SELECT
SELECT DISTINCT pozice
FROM zamestnanci;
STRANA

Výraz
Operátor
+
-
*
/
Popis
Sčítání
Odčítání
Násobení
Dělení
SELECT jmeno, prijmeni, 12*zakladni_plat
FROM zamestnanci;
STRANA

Priorita operátorů
Násobení a dělení má přednost před sčítáním a odečítáním
Operátory se stejnou prioritou se provádějí zleva doprava
Použití závorek mění pořadí provádění a zjednodušují výrazy
*
/
+
_
STRANA

alias, AS
STRANA
SELECT jmeno, prijmeni, 12 * zakladni plat as "Rocni PLAT"
FROM zamestnanci;

alias, AS
Alias umožňuje přejmenovat sloupec výsledku dotazu
Klíčové slovo AS lze vynechat
SELECT jmeno, prijmeni, 12*zakladni_plat AS
"Rocni plat"
FROM zamestnanci;
STRANA

SELECT - shrnutí
SELECT *|{[DISTINCT] sloupec|výraz [alias],...}
FROM tabulka
[WHERE podmínka(y)]
STRANA

AND
klauzule WHERE může více podmínek, které musí být splněny současně
Vyberte zaměstnance, kteří nastoupili roce 2003 a dříve a pracují jako účetní
SELECT * FROM zamestnanci
WHERE datum_nastupu<='31-12-2003' AND pozice = 'Ucetni';
STRANA

OR
Spojkou OR jsou spojeny podmínky, z nichž alespoň jedna musí být splněna
Příklad
Vyberte všechny cukráře a cukrářky
SELECT * FROM zamestnanci
WHERE pozice='Cukrar' OR pozice='Cukrarka';
STRANA

BETWEEN AND
umožňuje vybrat takové záznamy, pro které je hodnota v uvedeném sloupci v určitém rozmezí (včetně hraničních hodnot)
Možno použít pro:
datum
číslo
textový řetězec
… WHERE sloupec BETWEEN dolni_mez AND horni_mez
STRANA

BETWEEN AND
Příklad:
Vypište zaměstnance kteří nastoupili v roce 2003
SELECT *
FROM zamestnanci
WHERE datum_nastupu BETWEEN '01-01-2003' AND '31-12-2003';
STRANA

IN
Porovnává hodnotu se seznamem
Příklad:
Vyberte všechny cukráře a cukrářky
SELECT *
FROM zamestnanci
WHERE pozice IN ('Cukrar','Cukrarka');
STRANA

LIKE
Porovnává řetězec s uvedenou maskou
Zástupné znaky:
_ (podtržítko) nahrazuje právě jeden znak
% nahrazuje libovolný počet znaků (nula a více znaků)
Maska '_a%' odpovídá slovům:
Pavel _ nahrazuje P, % nahrazuje 'vel'
maminka _ nahrazuje m, % nahrazuje ‘minka'
STRANA

LIKE
Příklad:
Kteří zaměstnanci mají příjmení, které začíná na písmeno N:
SELECT jmeno, prijmeni
FROM zamestnanci
WHERE prijmeni LIKE 'N%';
STRANA

Masky – cvičení
Které z vyjmenovaných slov odpovídají masce?
LIKE 'N%'
‘novak' ,'Novak', 'N‘, ‘Hanák‚
LIKE '_a%‘
‘pa’, ‘papa’, ‘ahoj’, ’pavel’
LIKE ‘%les%’
‘Lesnik’, ‘lesnik’, ‘prales’, ’polesny’
STRANA

IS NULL
NULL je speciální hodnota pro hodnoty, které nejsou vyplněny
NULL se nemůže porovnávat pomocí = nebo <>
Pro porovnání je třeba použít IS NULL nebo IS NOT NULL
Příklad:
Který zaměstnanec nemá nadřízeného?
SELECT jmeno, prijmeni, pozice
FROM zamestnanci
WHERE nadrizeny_id IS NULL;
STRANA

NOT
Negace následující podmínky
Příklad:
Vypiš jména všech zaměstnanců kromě účetních
SELECT *
FROM zamestnanci
WHERE NOT pozice= 'Ucetni';
STRANA

Priority operatoru
Pořadí vyhodnocení Operátor
1 Aritmetické operace ( * / + - )
2 Operátor zřetězení ( || )
3 Porovnání
4 IS [NOT] NULL, LIKE, [NOT] IN
5 [NOT] BETWEEN
6 NOT logická podmínka
7 AND logická podmínka
8 OR logická podmínka
STRANA

Cvičení
Vypište zaměstnance, kteří pracují na pozici ‘Reditel’ nebo jsou na pozici ‘Ucetni’ s platem větším než 15000
STRANA
SELECT jmeno, prijmeni, pozice, zakladni_plat
FROM zamestnanci
WHERE pozice = 'Reditel' OR pozice = 'Ucetni' AND zakladni_plat > 15000;

Vypište zaměstnance :
kteří mají na druhém místě v křestním jméně písmeno ‘a’
kteří nemají na druhém místě v křestním jméně písmeno ‘a’
jejich příjmení končí písmenem ‘a’
kteří mají kdekoliv ve jméně ‘i’
kteří pracují na pozici ‘Ucetni’ nebo ‘Referent’
kteří nemají nadřízené
STRANA

ORDER BY
Třídění výsledku dotazu podle jednoho nebo více sloupců
Vzestupné třídění ASC (defaultní)
Sestupné třídění DESC
SELECT prijmeni, jmeno
FROM zamestnanci
ORDER BY prijmeni, jmeno ASC;
STRANA

Agregační funkce
AVG průměr
COUNT počet
MAX maximum
MIN minimum
SUM součet
STRANA

COUNT
SELECT COUNT(*)
FROM zamestnanci;
SELECT COUNT(*)
FROM zamestnanci
WHERE datum_nastupu BETWEEN '01.01.2003' AND '31.12.2003';
STRANA
Kolik má tabulka zamestnanci záznamů?
Kolik zaměstnanců nastoupilo v průběhu roku 2003?

AVG
SELECT AVG(zakladni_plat)
FROM zamestnanci;
STRANA
Jaký je průměrný základni plat v tabulce zamestnanci?

MAX a MIN
Kolik je maximální plat?
Od kdy pracuje ve firmě zaměstnanec, který je pracuje ve firmě pracuje nejdéle?
SELECT MAX(zakladni_plat)
FROM zamestnanci;
SELECT MIN(datum_nastupu)
FROM zamestnanci;
STRANA

Operátor zřetězení
Umožňuje spojit libovolné řetězce
|| (dvě svislé čáry)
SELECT 'Tabulka zamestnanci ma ' || count(*) ||' zaznamu' AS "Vysledek dotazu"
FROM zamestnanci;
STRANA

GROUP BY
GROUP BY umožňuje seskupit řádky a získávat souhrnné informace za jednotlivé skupiny
Užití společně s agregačními funkcemi
Všechny sloupce, které jsou v seznamu sloupců SELECT, které nejsou v agregačních funkcích, musí být uvedeny v klauzuli GROUP BY
STRANA

GROUP BY
Asistentka
Cukrar
Cukrarka
STRANA

GROUP BY
Příklad:
Kolik lidí pracuje na jednotlivých pozicích?
SELECT pozice, COUNT(*)
FROM zamestnanci GROUP BY pozice;
STRANA

GROUP BY
STRANA
SELECT pozice, COUNT(*)
FROM zamestnanci
GROUP BY pozice;

HAVING
Podmínky, které se vztahují ke skupině nemůžeme psát do klauzule WHERE
Vlastnosti skupiny píšeme do klauzule HAVING
Můžeme používat agregační funkce, které nemusí být uvedeny v klauzuli SELECT
STRANA

HAVING
Příklad:
Vypište pozice a počet zaměstnanců u pozic, které mají průměrný plat alespoň 15000
SELECT pozice, COUNT(*)
FROM zamestnanci
GROUP BY pozice
HAVING AVG(zakladni_plat)>=15000;
STRANA

Pořadí klíčových slov v dotazu
SELECT sloupce, výrazy
FROM tabulka
[WHERE podmínky]
[GROUP BY výraz pro seskupení]
[HAVING podmínky pro skupiny]
[ORDER BY sloupce]
STRANA

Cvičení (pokračování)
Kolik je průměrný základní plat?
Kolik ve firmě pracuje účetních?
Kolik je průměrný plat uklízeček?
Vypište průměrný plat podle oddělení
Vypište průměrný plat na odděleních, které mají více než jednoho zaměstnance.
Napište seznam zaměstnanců setříděný podle toho, jak dlouho ve firmě pracují.
Jaký je průměrný plat zaměstnanců, kteří nastoupili před rokem 2000?
STRANA

Cvičení (pokračování)
Kolik je průměrný základní plat?
STRANA
SELECT AVG(zakladni_plat)
FROM zamestnanci;

Cvičení (pokračování)
Kolik ve firmě pracuje účetních?
STRANA
SELECT COUNT(*)
FROM zamestnanci
WHERE pozice = 'Ucetni';

Cvičení (pokračování)
Kolik je průměrný plat uklízeček?
STRANA
SELECT AVG(zakladni_plat)
FROM zamestnanci
WHERE pozice = 'Uklizecka';

Cvičení (pokračování)
Vypište průměrný plat podle oddělení
STRANA
SELECT oddeleni, AVG(zakladni_plat)
FROM zamestnanci
GROUP BY oddeleni;

Cvičení (pokračování)
Vypište průměrný plat na odděleních, které mají více než jednoho zaměstnance.
STRANA
SELECT oddeleni, AVG(zakladni_plat)
FROM zamestnanci
GROUP BY oddeleni
HAVING COUNT(*) > 1;

Cvičení (pokračování)
Napište seznam zaměstnanců setříděný podle toho, jak dlouho ve firmě pracují.
STRANA
SELECT jmeno, prijmeni, datum_nastupu
FROM zamestnanci
ORDER BY datum_nastupu;