Jist╪ jste si minule vτimli, ºe problematika zahnízd╪n∞ch dotazà není ºádná legrace - v╪nujeme se jí proto i v dalτím pokraƒování naτeho seriálu.
Mnoºiny a práce s více tabulkami
Chceme-li u kaºdého holiƒe mít poznámku, zda je nadpràm╪rn╪ chytr∞ nebo nadpràm╪rn╪ chytr∞ holiƒ, píτeme:
SELECT RC, JMENO, IQ>(SELECT AVG(IQ) FROM CLOVEK) CHYTRY, IQ>(SELECT AVG(IQ) FROM CLOVEK WHERE PROFESE="HOLIC") CHYTRY_HOLIC FROM CLOVEK WHERE PROFESE="HOLIC" ORDER BY JMENO,RC;
Zajímají-li nás okresy, kde je pràm╪rn∞ plat uƒitele v╪tτí neº pràm╪rn∞ plat kv╪tiná²ky v Hradci Králové, staƒí psát:
SELECT OKRES FROM CLOVEK WHERE PROFESE="UCITEL" GROUP BY OKRES HAVING AVG(PLAT)>(SELECT AVG(PLAT) FROM CLOVEK WHERE PROFESE="KVETINARKA" AND OKRES="HK") ORDER BY OKRES;
Trochu více mnoºin
Z p²edchozích dílà víme, ºe jedna hodnota màºe b∞t porovnávána s celou mnoºinou hodnot s pouºitím operátoru IN nebo NOT IN. První operátor odpovídá na otázku, zda hodnota je prvkem mnoºiny hodnot. NOT IN naopak testuje nep²ítomnost hodnoty v mnoºin╪. Pak je logick∞ v∞raz
ROK IN (1415,1620,1621,1848,1948, 1968)
roven YES, pokud jde o smutn∞ letopoƒet. Podobn╪ v∞raz
KRESTNI NOT IN ("LUBOMIR","VASIL","ALOIS")
respektuje p²ání rodiƒky p²ed porodem.
Pro usnadn╪ní práce s mnoºinami slouºí dalτí operátory, které konvertují mnoºinu na zástupnou hodnotu vhodnou pro porovnání s jinou hodnotou. Nejprve probereme operátor ANY, kter∞ se spokojí s existencí t²eba jen jediného prvku mnoºiny splσujícího podmínku p²ed ANY. Ve stejném v∞znamu màºeme pouºít i operátor SOME. Sloºené operátory =ANY, >ANY, <ANY, >=ANY, <=ANY, <>ANY, LIKE ANY znamenají totéº co =SOME, >SOME, <SOME, >=SOME, <=SOME, <>SOME, LIKE SOME. Nap²íklad operátor >ANY je vhodn∞ k poloºení otázky, zda existuje takov∞ prvek mnoºiny, jehoº hodnota je menτí neº hodnota v∞razu p²ed operátorem. Pak pravdivost následujícího v∞razu jeτt╪ nezaruƒuje zbohatnutí:
PRACHY >ANY(5000, 321000, 500)
Znamená to pouze testování, zda máme více neº p╪tikilo, coº v praxi neznamená tolik. Rovn╪º chudoba není dána pravdivostí v∞razu:
PRACHY <ANY(5000000, 2000, 500)
Vτichni, kdo mají mén╪ neº p╪t melounà, si nep²ipadají jako ºebráci. Operátor =ANY je rovnocenn∞ obyƒejnému IN. Pouºití LIKE ANY je siln∞m nástrojem pro obecné vyhledávání v textech. Máme i siln╪jτí operátor ALL, kter∞ trvá na souƒasném spln╪ní podmínky p²ed ALL pro vτechny prvky mnoºiny uvedené za ALL. Sloºené operátory =ALL, >ALL, <ALL, >=ALL, <=ALL, <>ALL, LIKE ALL jsou moºn∞mi konstrukcemi. Tak operátor >ALL je vhodn∞ k poloºení otázky, zda vτechny prvky mnoºiny mají menτí hodnotu, neº je hodnota v∞razu p²ed operátorem. Pak pravdivost v∞razu jiº vede k optimismu:
PRACHY >ALL(50, 73254710, 800)
Pevn╪ v╪²ím, ºe ƒtená²i ƒlánku p²ipadá, ºe hodnota následujícího v∞razu v jeho p²ípad╪ není ani NULL, ani YES:
IQ <ALL(147, 110, 160, 105, 45)
Operátor <>ALL je rovnocenn∞ obyƒejnému NOT IN. Pokud je mnoºina definována v∞ƒtem hodnot, je to pro aplikace málo. Naτt╪stí si snadno màºeme vytvo²it mnoºinu zahnízd╪n∞m dotazem vhodného typu.
Jednosloupcová tabulka
Obsahuje-li tabulka práv╪ jeden sloupec, màºe b∞t pouºita místo mnoºiny hodnot. Hodnoty v jednotliv∞ch ²ádcích tabulky p²edstavují prvky mnoºiny, takºe jde p²ímo o ráj pro operátory IN, NOT IN, ALL, SOME a ANY. Pokud tabulka vytvo²ená poddotazem obsahuje pouze jeden ²ádek, je také jeτt╪ mnoºinou o jednom prvku. Opakují-li se v tabulce stejné hodnoty, práci s mnoºinami to nevadí, jenom SQL server se více nad²e a my se déle naƒekáme na odpov╪╘. Pokud p²i popisu projekce, která jednosloupcovou tabulku vytvá²í, pouºijeme p²ed v∞razem slovo DISTINCT, zamezíme tím vzniku duplicitních hodnot. Tak màºeme v∞razn╪ zmenτit poƒet prvkà v mnoºin╪ a odpov╪╘ na poddotaz i na dotaz bude velmi rychlá.
Pokud v tabulce neexistuje ani jeden ²ádek, jde o prázdnou mnoºinu, která je povolena. Operace s prázdn∞mi mnoºinami typicky vracejí hodnotu NO - s v∞jimkou NOT IN, kter∞ vrací YES. Ud╪lejme dalτí krok sm╪rem k propasti zvané SQL a zeptejme se jinak na seznam nejmladτích králíkà. Nejsou to náhodou ti, jejichº v╪k je menτí nebo roven v╪ku jakéhokoli králíka?
SELECT ID, JMENO FROM SAMEC WHERE VEK<=ALL(SELECT DISTINCT VEK FROM SAMEC) ORDER BY JMENO, ID;
Pak uº je pouhá hraƒka zeptat se na seznam nejvypasen╪jτích nejv∞τe dvoulet∞ch králíkà:
SELECT ID, JMENO, VEK FROM SAMEC WHERE VEK<=2 AND HMOTNOST >=ALL(SELECT DISTINCT HMOTNOST FROM SAMEC WHERE VEK<=2) ORDER BY VEK, JMENO, ID;
N╪kte²í vaτi kamarádi mají moºná stejná p²íjmení jako slavné osobnosti, p²estoºe sami slavní nejsou:
SELECT RC, PRIJMENI, JMENO FROM CLOVEK WHERE PRIJMENI IN (SELECT DISTINCT PRIJMENI FROM CLOVEK WHERE SLAVNY) AND NOT SLAVNY ORDER BY PRIJMENI, JMENO, RC;
Kdo nemá rád IN, ptá se jinak:
SELECT RC, PRIJMENI, JMENO FROM CLOVEK WHERE PRIJMENI =ANY(SELECT DISTINCT PRIJMENI FROM CLOVEK WHERE SLAVNY) AND NOT SLAVNY ORDER BY PRIJMENI, JMENO, RC;
Jiní vaτi kamarádi mají stejné k²estní jméno jako vaτi nep²átelé. Proto na mejdan pozvete jenom ty, kte²í vám nebudou p²ipomínat nevy²ízené úƒty:
SELECT RC, PRIJMENI, JMENO FROM CLOVEK WHERE JMENO NOT IN (SELECT DISTINCT JMENO FROM CLOVEK WHERE NEPRITEL) AND KAMARAD ORDER BY PRIJMENI, JMENO, RC;
Kdo nemá rád NOT IN, màºe postupovat i jinak:
SELECT RC, PRIJMENI, JMENO FROM CLOVEK WHERE JMENO <>ALL(SELECT DISTINCT JMENO FROM CLOVEK WHERE NEPRITEL) AND KAMARAD ORDER BY PRIJMENI, JMENO, RC;
Mnoºina màºe b∞t ku prosp╪chu nejen za WHERE, ale i p²i popisu projekce nebo za HAVING. Chceme-li u lidí mít poznámku, zda jsou podez²elí, staƒí v╪d╪t, ºe podez²el∞ je kaºd∞ zloƒinec, kaºd∞ se zloƒineck∞m p²íjmením nebo obyvatel m╪sta, kde bydlí nejmén╪ 10 zloƒincà:
SELECT RC, PRIJMENI, JMENO FROM CLOVEK, ZLOCINEC OR PRIJMENI =ANY(SELECT DISTINCT PRIJMENI FROM CLOVEK WHERE ZLOCINEC) OR PSC IN(SELECT PSC FROM CLOVEK WHERE ZLOCINEC GROUP BY PSC HAVING COUNT(PSC)>=10) PODEZIRAN ORDER BY PRIJMENI, JMENO, RC;
Seznam okresà s nejv╪tτím poƒtem trvale bydlících zloƒincà by se také hodil. V takovém seznamu bude patrn╪ jeden okres, ale není vylouƒeno, ºe tam bude více okresà. Trochu si zaagregujeme v hlavním dotazu i v poddotazu, aby se nám poda²ilo p²ejít od jednotliv∞ch zloƒincà ke zloƒineck∞m okresàm:
SELECT OKRES FROM CLOVEK WHERE ZLOCINEC GROUP BY OKRES HAVING COUNT(RCISLO)>=ALL(SELECT COUNT(RCISLO) FROM CLOVEK WHERE ZLOCINEC GROUP BY OKRES) ORDER BY OKRES;
Poloºme si otázku, na co by se mohla hodit tabulka o neomezeném poƒtu ²ádkà a sloupcà uvnit² zahnízd╪ného dotazu.
Existence ²ádkà tabulky
V obecné tabulce s více sloupci je uloºena sm╪s hodnot ràzného typu, které t╪ºko budeme hromadn╪ porovnávat s hodnotou jednoho typu. Navíc to není k niƒemu. Jediné, co je zajímavé testovat na tabulce o neurƒeném poƒtu sloupcà, je, zda obsahuje alespoσ jeden ²ádek. To se hodí spíτe pro ²ízení v∞poƒtà neº pro zahníz╘ování dotazà. Operátor EXISTS urƒí, zda existuje alespoσ jeden ²ádek tabulky vygenerované poddotazem. Operátor NOT EXISTS má pouze opaƒn∞ v∞znam. Za operátorem se nachází zahnízd╪ná tabulka a p²ed ním není hodnota. Pokud bych cht╪l poslat vτem nepodvodníkàm seznam vτech podvodníkà, pak p²ílohu dopisu stvo²ím snadno. Staƒí napsat p²íkaz:
SELECT RC, PRIJMENI, JMENO, BYDLISTE, PSC FROM CLOVEK WHERE PODVODNIK ORDER BY PRIJMENI, JMENO, RC;
Jak ale vygenerovat samolepky na obálky? Takhle to d╪lají zaƒáteƒníci nebo pesimisté:
SELECT PRIJMENI, JMENO, BYDLISTE, PSC FROM CLOVEK WHERE NOT PODVODNIK ORDER BY PSC, PRIJMENI, JMENO;
Profesionální optimisté τet²ící papír do tiskárny rad╪ji píτí:
SELECT PRIJMENI, JMENO, BYDLISTE, PSC FROM CLOVEK WHERE EXISTS (SELECT * FROM CLOVEK WHERE PODVODNIK ) AND NOT PODVODNIK ORDER BY PSC, PRIJMENI, JMENO;
Nebudu p²eci psát milionàm nepodvodníkà o tom, ºe seznam podvodníkà je prázdn∞. Jist╪ by to τlo napsat i bez EXISTS:
SELECT PRIJMENI, JMENO, BYDLISTE, PSC FROM CLOVEK WHERE (SELECT COUNT(RCISLO) FROM CLOVEK WHERE PODVODNIK )>0 AND NOT PODVODNIK ORDER BY PSC, PRIJMENI, JMENO;
Vidíte, ºe cesty k zvládnutí rozmanit∞ch dotazà do jedné tabulky jsou velmi ƒlenité a strukturovanost z nich jenom ƒiτí. N╪kte²í ƒtená²i si moºná kladou otázku, proƒ jsem v úvodu seriálu rozsekával problém na n╪kolik tabulek a proƒ stále jeτt╪ tajím, jak to vτechno spojit do jednoho celku ku vτeobecnému uºitku. Necht╪l jsem vás zatím p²íliτ rozptylovat. Poƒínaje následující malou kapitolou uº se nebudu vícetabulkov∞m dotazàm vyh∞bat.
Práce se dv╪ma tabulkami
Ono velké tajemství zní: poddotaz màºe sm╪²ovat i do jiné zdrojové tabulky. Nejprve si novou moºnost vychutnáme na dvojicích tabulek v 5NF obrn╪n∞ch doménovou a entitní integritou, které mají k sob╪ relaci N:1. Co kdyby nás zajímali lidé, kte²í nemají ani jeden úƒet a je jim víc neº 18? Chceme jim poslat skv╪lou nabídku od naτí nové banky GOLD TRANSILVANIA Ltd:
SELECT PRIJMENI, JMENO, BYDLISTE, PSC FROM CLOVEK WHERE VEK>=18 AND RCISLO NOT IN(SELECT DISTINCT RC FROM UCET)ORDER BY PSC, PRIJMENI, JMENO;
Zajímav∞ je téº dotaz do rejst²íku trestà a registru firem:
SELECT RC, PRIJMENI, JMENO FROM REJSTRIK WHERE TRESTAN AND RC=ANY(SELECT DISTINCT RC FROM REG_FIREM) ORDER BY PRIJMENI, JMENO, RC;
Sn╪hurka τílela z mil∞ch a závisl∞ch trpaslíkà. V tabulce trpaslíkà se dob²e loví pomocí pod²ízené tabulky vlastností, která obsahuje ƒíslo trpaslíka a název vlastnosti jako sloºen∞ klíƒ:
SELECT JMENO FROM TRPASLIK WHERE CISLO IN(SELECT DISTINCT CISLO FROM VLASTNOST WHERE NAZEV="MILY") AND CISLO IN(SELECT DISTINCT CISLO FROM VLASTNOST WHERE NAZEV="ZAVISLY")ORDER BY JMENO;
Práce s více tabulkami
Z více tabulek nemusíte mít obavy. Pokud je databázov∞ systém dob²e navrºen, jde vτechno hladce. Vzpomeσme si na první díl seriálu a na ƒíselník receptà, ƒíselník surovin a na spojovací entitu DAVKA, která obsahovala dva klíƒové sloupce CIR a CIS pro orientaci v ƒíselnících a jeden neklíƒov∞ sloupec pro mnoºství suroviny v rámci receptu. Mám te╘ zrovna chu£ na KARI ko²ení a nechce se mi je jíst samotné. Poddotazem budu muset zjistit ƒíslo suroviny, kterého se zmocní nad²ízen∞ poddotaz, a stanoví ƒísla receptà, které pot²ebují KARI, a koneƒn╪ hlavní dotaz vypíτe názvy receptà s KARI podle abecedy. Takºe pozor na hnízdo v hnízd╪:
SELECT CIR, NAZEV FROM RECEPT WHERE CIR=ANY(SELECT CIR FROM DAVKA WHERE CIS=ANY(SELECT CIS FROM SUROVINA WHERE NAZEV ="KARI")) ORDER BY NAZEV, CIR;
N╪které suroviny nejsou k niƒemu a chci si je uv╪domit:
SELECT CIS, NAZEV FROM SUROVINA WHERE CIS NOT IN (SELECT DISTINCT CIS FROM DAVKA) ORDER BY NAZEV, CIS;
Chci zaloºit GOULASH PUB, s.r.o., a zajímá m╪, z ƒeho se vlastn╪ guláτ d╪lá. Musím se prohrabat recepty, které p²ipomínají guláτ, a opsat si jejich ƒísla. Pak se zano²ím do dávek a najdu ƒísla potenciálních surovin. Pak teprv budu moci abecedn╪ vypsat suroviny do guláτà:
SELECT CIS, NAZEV FROM SUROVINA WHERE CIS IN (SELECT DISTINCT CIS FROM DAVKA WHERE CIR IN (SELECT CIR FROM RECEPT WHERE NAZEV LIKE "%GULAS%")) ORDER BY NAZEV, CIS;
Dobrou chu£ - i na spojování tabulek pomocí joinà v dalτím dílu!
