Andmebaasid 2021/22 kevad

Alampäringud

Alampäring on päringu esitamine teise päringu sees. Neid kasutatakse andmaks andmeid seda päringut ümbritsevale päringule. Alampäringud (sisemised päringud) võivad tulemuseks anda nii üksiku väärtuse kui ka kirjete loendi. Alampäringud võib kasutada päringus erinevatel viisidel ja erinevates kohtades.

Alampäringute kasutamisel tuleb järgida mõningaid juhiseid:

• Alampäring peab olema ümarsulgudes.
• Alampäring tuleb paigutada võrdlusoperaatorist paremale.
• Alampäringutes ei saa kasutada järjestamist kui pole tegemist SELECT TOP'iga. Järjestamist kasutada peamises SELECT lauses.
• Kui alampäring tagastab välisele päringule (põhipäringule) nullväärtuse, ei tagasta väline päring ühtegi rida kui seda alampäringut kasutatakse WHERE osa võrdluses.

Näiteks me tahame teada, milliste riikide pindala on väiksem kui Eesti pindala. Koostame päringu, millega saame teada Eesti pindala:

SELECT area 
FROM world
WHERE name='Estonia'

ja nüüd paneme selle võrratuse sisse põhipäringusse (alampäring on halli taustaga):

SELECT name 
FROM world
WHERE area <
    (SELECT area 
     FROM world
     WHERE name='Estonia')

Ühes päringus võib olla ka mitu alampäringut. Näiteks tahame teada, milliste riikide rahvaarvud on väiksemad kui Jaapanis, kuid suuremad kui Kanada. Ühe päringuga saame teada Jaapani rahvaarvu ja teisega Kanada oma ning paneme mõlemad päringulausesse:

SELECT name, population 
FROM world 
WHERE population < 
    (SELECT population 
     FROM world
     WHERE name='Japan') 
AND population > 
    (SELECT population 
     FROM world 
     WHERE name='Canada')

SELECT TOP n

Vaatame nüüd oma male andmebaasi andmeid. Tahame leida aga nimeliselt kõik isikud, kes kuuluvad klubisse, mille mängijad on saanud mustadega mängides keskmiselt kõige enam punkte. Oskame juba leida kui palju on klubiliikmed saanud punkte keskmiselt mustadega mängides klubide kaupa. Selle ülesande täitmiseks õpime veel ära päringu piiramise SELECT TOP n, kus n võib olla mis tahes täisarv.

Me ei saanud kasutada koos agregeeritud funktsiooniga (näiteks MAX, AVG jms) veerunime päringulauses ilma GROUP BY kasutamata. Samas kui me ei taha näiteks kõikide klubide liikmeid, vaid ainult suurima liikmete arvuga klubi, siis kirjutame:

SELECT TOP 1 nimi, COUNT(*)
FROM klubid KEY JOIN isikud
GROUP BY nimi
ORDER BY 2 DESC;

Siin päringulauses peab kindlasti olema järjestamine, et saaks suurimat või vähimat. Kui oleks soovinud näiteks kolme kõige väiksema liikmete arvuga klubi, siis oleks kirjutanud:

SELECT TOP 3 nimi, COUNT(*)
FROM klubid KEY JOIN isikud
GROUP BY nimi
ORDER BY 2 ASC;

Selle teadmisega leiame kõigepealt klubi id, mlle mängijad on saanud mustadega mängides keskmiselt kõige enam punkte

SELECT TOP 1 klubid.id
FROM partiid, Isikud, klubid 
WHERE partiid.must=Isikud.id AND isikud.klubi=klubid.id
GROUP BY klubid.id
ORDER BY AVG(musta_tulemus/2.0) DESC;

Jagame 2.0-ga, et saada sellist tulemust, kus võit annaks 1 ja viik 0.5 punkti ning kindlasti peab olema jagaja 2.0, mitte 2, et vastus poleks täisarv (kahe täisarvu jagatis on täisarv SQLis). Nagu näete, siis ei pea olema tunnust, mille alusel järjestatakse SELECT lause osas. Päringu, millega leidsime selle klubi id paneme nüüd põhipäringu sisse:

SELECT eesnimi, perenimi FROM isikud 
WHERE klubi=
    (SELECT TOP 1 klubid.id
     FROM partiid, Isikud, klubid 
     WHERE partiid.must=Isikud.id AND isikud.klubi=klubid.id
     GROUP BY klubid.id
     ORDER BY AVG(musta_tulemus/2.0) DESC);

SELECT TOP n START AT m, kus ka m on täisarv, saame alustada ka mitte päris alugusest või lõpust, vaid näiteks alates kolmandast või eelviimasest.

EXIST operaator

Alampäringut saab koostada ka kasutades EXIST operaatorit, mis testib alampäringu kirje olemasolu.

SELECT */veeruavaldised
FROM <tabelid>
WHERE EXISTS
    (SELECT veeruavaldised 
     FROM <tabelid> 
     WHERE piirangud);

Näiteks soovime turniiride andmeid, mil mängiti partii, mille üheks mängijaks (kas mustadega või valgetega mängija) on isik, kelle id on 72:

SELECT * 
FROM turniirid
WHERE EXISTS 
    (SELECT * 
     FROM partiid 
     WHERE partiid.turniir = turniirid.id AND (valge = 72 OR must = 72));