- Stahuj zápisky z přednášek a ostatní studijní materiály
- Zapisuj si jen kvalitní vyučující (obsáhlá databáze referencí)
- Nastav si své předměty a buď stále v obraze
- Zapoj se svojí aktivitou do soutěže o ceny
- Založ si svůj profil, aby tě tví spolužáci mohli najít
- Najdi své přátele podle místa kde bydlíš nebo školy kterou studuješ
- Diskutuj ve skupinách o tématech, které tě zajímají
Studijní materiály
Zjednodušená ukázka:
Stáhnout celý tento materiálsouhrnné funkce se musí objevit v seznamu group by.
Souhrnné funkce – klauzule having
Najděte jména všech poboček, jejichž průměrný zůstatek účtu je větší než $1,200.
select pobočka-jméno, avg (zůstatek)
from účet
group by pobočka-jméno
having avg (zůstatek) > 1200
Poznámka: predikáty v klauzuli having jsou po zformování skupin
Nulové hodnoty
N-tice můžou mít nulovou hodnotu, značenou null, ta znamená neznámou hodnotu nebo hodnotu, která neexistuje.
Výsledek jakékoliv aritmetické operace zahrnující null je null.
Všechna porovnávání zahrnující null vrací false. Precizněji:
Jakékoliv porovnání s null vrací unknown
(true or unknown) = true, (false or unknown) = unknown, (unknown or unknown) = unknown, (true and unknown) = unknown, (false and unknown) = false, (unknown and unknown) = unknown
Výsledek klauzule where je brán jako false, je-li unknown
„P is unknown“ se vyhodnotí jako true, jestliže predikát P je vyhodnocen jako unknown
Najděte všechna čísla půjček, které se objeví v relaci půjčka s nulovou hodnotou částka.
select půjčka-číslo
from půjčka
where částka is null
Součet všech částek půjček
select sum (částka)
from půjčka
Tyto výrazy ignorují nulové částky; výsledek je nula, jestliže zde není žádná nenulová částka.
Všechny souhrnné operace s výjimkou count(*) ignorují n-tice s nulovými hodnotami na souhrnných atributech.
Vnořené poddotazy (Nested subqueries)
SQL poskytuje mechanismus pro vnořování poddotazů.
Poddotaz je výraz select-from-where, který je vnořen do jiného dotazu.
Obvyklé použití poddotazů je provádění testů na členství v množině, porovnávání množin a kardinalitu množin.
Členství v množině
F in r ( ( t ( r (t = F)
0
(5 in
4
) = true
5
0
(5 in
4
) = false
6
0
(5 not in
4
) = true
6
Příklady dotazů
Najděte všechny zákazníky, kteří mají v bance účet i půjčku.
select distinct zákazník-jméno
from půjčovatel
where zákazník-jméno in (select zákazník-jméno
from vkladatel)
Najděte všechny zákazníky, kteří mají v bance půjčku, ale ne účet.
select distinct zákazník-jméno
from půjčovatel
where zákazník-jméno not in (select zákazník-jméno
from vkladatel)
Najděte všechny zákazníky, kteří mají účet i půjčku v pobočce Perryridge.
select distinct zákazník-jméno
from půjčovatel, půjčka
where půjčovatel.půjčka-číslo = půjčka.půjčka-číslo and
pobočka-jméno = „Perryridge“ and
(pobočka-jméno, zákazník-jméno) in
(select pobočka-jméno, zákazník-jméno
from vkladatel, účet
where vkladatel.číslo-účtu = účet.číslo-účtu)
Porovnávání množin
Najděte všechny pobočky, které mají větší aktiva než nějaká pobočka v Brooklynu.
select distinct T.pobočka-jméno
from pobočka as T, pobočka as S
where T.aktiva > S.aktiva and S.pobočka-město = „Brooklyn“
Klauzule some
F some r ( ( t (t ( r ( [F t])
kde může být: , (, =, (
0
(5 < some
5
) = true
6
(čteme: 5 je menší než nějaká n-tice z relace
(5 < some
0
) = false
5
(5 = some
0
) = true
5
(5 ( some
0
) = true (neboť 0 ( 5)
5
(= some) ( in
Ale: (( some) ( not in
Příklad dotazu
Najděte pobočky, které mají větší aktiva než nějaká pobočka v Brooklynu.
select pobočka-jméno
from pobočka
where aktiva > some
(select aktiva
from pobočka
where pobočka-město = „Brooklyn“)
Klauzule all
F all r ( ( t (t ( r ( [F t])
0
(5 < all
5
) = false
6
(5 < all
6
) = true
10
(5 = all
4
) = false
5
(5 ( all
4
) = true (neboť 5 ( 4 and 5 ( 6)
6
(( all) ( not in
Ale: (= all) ( in
Příklad dotazu
Najděte pobočky, které mají větší aktiva než všechny pobočky v Brooklynu.
select pobočka-jméno
from pobočka
where aktiva > all
(select aktiva
from pobočka
where pobočka-město = „Brooklyn“)
Test na prázdné relace
Konstrukce exists vrací hodnotu true, je-li poddotaz v argumentu neprázdný.
exists r ( r ( (
not exists r ( r = (
Příklad dotazu
Najděte všechny zákazníky, kteří mají účet ve všech pobočkách v Brooklynu.
select dictinct S.zákazník-jméno
from vkladatel as S
where not exists (
(select pobočka-jméno
from pobočka
where pobočka-město = „Brooklyn“)
except
(select R.pobočka-jméno
from vkladatel as T, účet as R
where T.číslo-účtu = R.číslo-účtu and
S.zákazník-jméno = T.zákazník-jméno))
Poznámka: X – Y = ( ( X ( Y
Test na nepřítomnost duplikátních n-tic
Konstrukce unique testuje, zda poddotaz má ve svém výsledku nějaké duplikátní n-tice.
Najděte všechny zákazníky, kteří mají pouze jeden účet v pobočce Perryridge.
select T.zákazník-jméno
from vkladatel as T
where unique (
select R.zákazník-jméno
from účet, vkladatel as R
where T.zákazník-jméno = R.zákazník-jméno and
R.číslo-účtu = účet.číslo-účtu and
účet.pobočka-jméno = „Perryridge“)
Příklad dotazu
Najděte všechny zákazníky, kteří mají alespoň dva účty v pobočce Perryridge.
select T.zákazník-jméno
from vkladatel as T
where not unique (
select R.zákazník-jméno
from účet, vkladatel as R
where T.zákazník-jméno = R.zákazník-jméno and
R.číslo-účtu = účet.číslo-účtu and
účet.pobočka-jméno = „Perryridge“)
Odvozené relace
Najděte průměrný zůstatek účtu v těch pobočkách, kde je průměrný zůstatek větší než $1200.
select pobočka-jméno, prům-zůstatek
from (select pobočka-jméno, avg (zůstatek)
from účet
group by pobočka-jméno)
as result (pobočka-jméno, prům-zůstatek)
where prům-zůstatek > 1200
Poznámka: nepotřebujeme použít klausuli having, jestliže v klauzuli from spočítáme dočasnou relaci result a její atributy můžeme použít přímo v klauzuli where.
Pohledy
Poskytují mechanismus, jak skrýt nějaká data před nějakými uživateli. Pro vytvoření pohledu použijeme příkaz:
create view v as
kde:
je jakýkoliv dovolený výraz
v reprezentuje jméno pohledu
Příklady dotazů
Pohled skládající se z poboček a jejich zákazníků
create view všichni-zákazníci as
(select pobočka-jméno, zákazník-jméno
from vkladatel, účet
where vkladatel.číslo-účtu = účet.číslo-účtu)
union
(select pobočka-jméno, zákazník-jméno
from půjčovatel, účet
where půjčovatel.půjčka-číslo = půjčka.půjčka-číslo)
Modifikace databáze – mazání
Smažte všechny záznamy o účtech z pobočky Perryridge
delete from účet
where pobočka-jméno = „Perryridge“
Smažte všechny účty z každé pobočky v Needhamu
delete from účet
where pobočka-jméno in (
select pobočka-jméno
from pobočka
where pobočka-město = „Needham“)
delete from vkladatel
where číslo-účtu in (
select číslo-účtu
from pobočka, účet
where pobočka-město = „Needham“
and pobočka.pobočka-jméno = účet.pobočka-jméno)
Smažte záznamy o všech účtech se zůstatkem pod průměrem banky
delete from účet
where zůstatek < (
select avg (zůstatek)
from účet)
Problém: když smažeme n-tice z vklad, průměrný zůstatek se změní
Řešení použité v SQL:
Nejprve spočítáme průměrný zůstatek a najdeme všechny n-tice ke smazání
Potom smažeme všechny n-tice nalezené výše
Modifikace databáze – vkládání
Vložení nové n-tice do relace účet
insert into účet
values („Perryridge“, A-9732, 1200)
nebo ekvivalentně
insert into účet (pobočka-jméno, zůstatek, číslo-účtu)
values („Perryridge“, 1200, A-9732)
Přidání nové n-tice do relace účet se zůstatkem nastaveným na nulu.
insert into účet
values („Perryridge“, A-9732, null)
Jako dárek poskytněte všem zákazníkům půjček v pobočce Perryridge $200 termínovaný účet. Nechť číslo půjčky slouží jako číslo účtu pro nový termínovaný účet.
insert into účet
select pobočka-jméno, půjčka-číslo, 200
from půjčka
where pobočka-jméno = „Perryridge“
insert into vkladatel
select zákazník-jméno, půjčka-číslo
fr
Vloženo: 24.04.2009
Velikost: 36,18 kB
Komentáře
Tento materiál neobsahuje žádné komentáře.
Copyright 2024 unium.cz