Datab ze standardu SQL, d¡l 13.
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!   
Jarom¡r Kukal