Keerulisemad päringud koos harjutustega

 

Järgneva materjaliga töötamiseks on vaja, et oleks lõpuni valmis tehtud andmebaas Randaja (vt Mitmetabelilise andmebaasi loomine), seal peavad olema kõik andmed, samuti lisatud välisvõtmed. Päringute tegemisel on abiks DB Designeris tehtud andmemudel, mille abil saab kiirelt ülevaate, millised väljad, millises tabelis on ja milliste väljade kaudu on tabelid omavahel seotud.

Kuidas korraga mitmest tabelist andmeid pärida?

Ühest tabelist saame kõik tabelis olevad andmed kätte nii (Isiku tabeli näitel):

SELECT * FROM "Isik";

Proovime samamoodi korraga kahest tabelist andmei pärida:

SELECT * FROM "Isik", "Leibkond";

Siis pannakse kahe tabeli veerud kõrvuti ja korratakse esimese tabeli sisu iga veeru korral teisest tabelist. See ei ole kõige mõttekam päring.  

Lisaks tabelite nimetamisele FROM lauses on vaja ka öelda, millised tabelite väljad peavad omavahel vastavuses olema. Seda saab teha WHERE tingimuslauses, kus filtreeritakse read, mis vastavad tingimustele. Siin ongi abi andmemudelist, kust saame meelde tuletada, et tabeli Leibkond tunnus leibkond_id on välisvõtmeks tabelis Isik, kus samu väärtusi sisaldava tunnuse nimeks on leibkond. Nende kaudu saamegi tabelid siduda.

SELECT * FROM "Isik", "Leibkond"

WHERE "Isik".leibkond = "Leibkond".leibkond_id;

Üle mitme tabeli päringuid tehes tuleb tabelid omavahel siduda öeldes, millised kahe tabeli väljad on vastavuses (sisaldavad samu väärtusi). Nn võtmeväljad, mille välisvõtmeid lisades ära sidusime.

Proovi need kaks eelmist päringut läbi. Viimase päringu tulemusena väljastatakse kõik andmed, mis on tabelites "Isik" ja "Leibkond", aga andmed on omavahel korrektselt seotud. Tuleb tähele panna vaid seda, et kui vara väärtus on meie andmetes antud leibkonna kohta summeeritult, siis selles suures tabelis kuvatakse leibkonna vara väärtust iga leibkonna liikme juures, seda aga tuleb tõlgendada kui selle leibkonna vara koguväärtust, kuhu isik kuulub, mitte isikliku varanduse väärtusena. 

Üldsüntaks:

SELECT tabel1.veerunimi1, tabel1.veerunimi2, tabel2.veerunimi3 FROM tabel1, tabel2

WHERE tabel1.veerunimi = tabel2.veerunimi;

WHERE lauses veerunimi tähistab omavahel välisvõtmetega seotud välju. Ühe tabeli puhul on tegu primaarvõtmega, teise tabeli puhul välisvõtmega.

SQL keeles on tabelite ühendamiseks olemas ka JOIN konstruktsioon. JOIN lausete abil saab erinevaid tabeleid kombineerida suhteliselt sarnaselt nagu FROM ... WHERE konstruktsiooniga. FROM konstruktsiooni nimetatakse ka vaikimisi JOIN (implicit JOIN), see on lihtsaim viis andmeid ühendada ja täiesti piisav ka palju suuremate tabelite puhul kui meie oma. On erinevaid arvamusi, kas FROM...WHERE laused on loetavamad või on JOIN laused loetavamad.

On ka rida väljendatud JOIN (explicit JOIN) variante, nagu CROSS JOIN, INNER JOIN (kui ei ole täpsustatud, tähendab JOIN INNER JOIN), LEFT OUTER JOIN, RIGHT OUTER JOIN,
FULL OUTER JOIN.

Üldsüntaks:

SELECT table1.column1, table2.column2...

FROM table1

JOIN table2

ON table1.common_filed = table2.common_field;

 

Läheme oma andmebaasiga edasi. Võtame nüüd andmebaasist välja iga leibkonna vara väärtuse koos leibkonnapea nimega.

SELECT eesnimi, perenimi, vara_vaartus FROM "Isik", "Leibkond" WHERE suhe_leibkonnapeaga= 'leibkonnapea'

AND "Leibkond".leibkond_id = "Isik".leibkond;

Kui me selles päringus ei märgiks ära, et suhe_leibkonnapeaga = ’leibkonnapea’, saaksime tulemuseks taas kõik leibkonna liikmed ja kõigi nende juures kuvataks leibkonna vara väärtust.

 

 

Ülesanne 1ÜLESANNE 1

Kirjuta SQL päring, mis leiab ainult perepea Henrik Kartofeli vara väärtuse ja kuvab ka tema eesnime ja perenime. Siin ja järgmiste ülesannete juures on ära toodud tabel, mille PostgreSQL väljastab õige SQL lause abil. Veergude järjekord ei pea lahenduses sama olema.  

 

 

Väljarändajate keskmine vanus

Leiame väljarändajate keskmise vanuse. Tabelis "Isik" on väljad vanus ja sünniaeg. Erinevatest allikatest andmeid lisades peab arvestama, et need võivad olla erineval kujul, näiteks võivad inimeste kohta olla sisestatud sünniajad või vanused, nagu praegusel juhul ongi.  Andmebaasi disainima asudes peab otsustama, kas andmed ühtlustada juba sisestamise käigus, siis tulnuks kõigi puhul, kellel oli teada sünniaeg, mitte vanus, arvutada vanus ja lisada see andmebaasi ja piirduda ainult ühe, vanuse lahtriga. Samas on sünniaeg täpsem ja sellest infost oleks kahju loobuda, kui see on meil olemas ja võiksime selle hõlpsalt lisada. Seepärast on mõistlik teha alguses kaks erinevat välja.

Aga kui tahame leida keskmist vanust või leibkonna keskmist vanust, siis oleks meil jälle vaja, et see info oleks esitatud ühetaoliselt.

Saame luua oma andmebaasis uue välja ja seal andmed ühtlustada, alles jäävad ka mõlemad algsed väljad. Nii on lihtsam sisestada ja olenevalt andmetest, võib olla lihtsam pärast kasutada samu andmeid uute küsimuste küsimiseks, kui andmed ei ole vastavalt esialgsetele küsimustele liigselt normaliseeritud.

Selleks, et saaksime leida väljarändajate keskmise vanuse, peame kõigepealt arvutama vanused ka nende isikute jaoks, kelle kohta on andmebaasis olemas sünniaeg.

Vaatame kõigepealt olemasolevad andmed üle, toome tabelitest "Isik" ja "Leibkond" kokku väljad, millega edasi toimetama peame ja sorteerime tabeli algusesse väljad, kus on teada synniaeg, aga pole vanust.

SELECT perenimi, vanus, synniaeg, ymberkirjutus FROM "Isik", "Leibkond"

WHERE "Isik".leibkond = "Leibkond".leibkond_id

ORDER BY synniaeg;

 

 

Siit on hästi näha, et küsimus on teisisõnu leida, kui vanad olid väljarändajad ümberkirjutamise ajal. Tabelis "Leibkond" on väli ymberkirjutus, sellest tuleks lahutada tabelis "Isik" asuval väljal synniaeg olev info. Kuna meie andmetes ei ole kirjas, mis kuus on ümberkirjutamine toimunud, siis võtame vanuse arvutamiseks sünniajast välja ainult aasta ja teeme lahutustehte. Saadud vanuseid läheb vaja keskmise vanuse arvutamiseks, seepärast teeme uue välja arvutatud_vanused ja paneme arvutatava info sinna.

Teeme uue välja:

ALTER TABLE "Isik" ADD COLUMN arvutatud_vanused numeric;

Nüüd võtame Leibkonna tabelist ümberkirjutusaja, lahutame sellest sünniaja (võtame synniaja väljalt ainult aasta käsuga SELECT date_part('year', synniaeg) ja paneme leitud vanused uuele vastloodud väljale arvutatud_vanused

UPDATE "Isik"

SET arvutatud_vanused = ymberkirjutus - (SELECT date_part ('year', synniaeg))

FROM "Leibkond"

WHERE "Isik".leibkond="Leibkond".leibkond_id;

Kontrollime, kas sai nii nagu tahtsime.

SELECT perenimi, eesnimi, vanus, synniaeg, ymberkirjutus, arvutatud_vanused FROM "Isik", "Leibkond"

WHERE "Isik".leibkond="Leibkond".leibkond_id

ORDER BY arvutatud_vanused;

 

 

Terviklikkuse jaoks kopeerime väljale arvutatud_vanused ka ülejäänud vanused väljalt vanused, et seda veergu siis  kasutada keskmise vanuse arutamiseks.

UPDATE "Isik"

SET arvutatud_vanused= vanus

FROM "Leibkond"

WHERE arvutatud_vanused is null AND "Isik".leibkond = "Leibkond".leibkond_id;

Ära seda praegu järele proovi, aga kui midagi läheb valesti, saab veergu, nt veergu arvutatud_vanused kustutada nii:

ALTER TABLE "Isik" DROP COLUMN arvutatud_vanused;

 

Kontrollime, kas kõigil isikutel on väljal arvutatud_vanused vanus olemas ja kui ei ole, siis miks see nii olla võib, ehk pärime andmebaasist kõik asjasse puutuvad väljad tabelitest Isik ja Leibkond ja inimese nime, kellel vanust pole:

SELECT perenimi, eesnimi, vanus, synniaeg, ymberkirjutus, arvutatud_vanused FROM "Isik", "Leibkond"

WHERE arvutatud_vanused is null AND "Isik".leibkond = "Leibkond".leibkond_id

 

Selgub, et Jevdokia Tilanil pole märgitud ei vanust ega synniaega ja seepärast ei ole tal ka vanust lahtris arvutatud_vanused.

Tahame allikast järele vaadata, kas tal ikka tõesti ei olnud vanust ega sünniaega või on jäänud see lihtsalt välja märkimata. Otsime viite, see tähendab, et meil läheb vaja lisaks tabelitele Isik ja Leibkond andmeid veel tabelitest Leidumine ja Arhiiviallikas:

SELECT perenimi, eesnimi, nimetus, viide, lehed FROM "Isik", "Leibkond", "Leidumine", "Arhiiviallikas"

WHERE perenimi = 'Tilan' AND eesnimi = 'Jevdokia'

AND "Isik".leibkond = "Leibkond".leibkond_id

AND "Leibkond".leibkond_id = "Leidumine".leibkond

AND "Leidumine".allikas = "Arhiiviallikas".allikas_id;

Selgituseks: me ei kuva siin andmeid tabelist Leibkond, aga päringu peab kirjutama selle kaudu, sest tabelid Isik ja Arhiiviallikas on omavahel seotud tabelite Leidumine ja Leibkond kaudu. Ühtlasi tasub siin meelde jätta, et kui on tabelid A, B ja C, siis kui välisvõtmega on seotud A ja B ja B ja C, siis on seotud ka A ja C. Mudelit tehes ei pea seega kõiki tabeleid omavahel vahetult siduma.

 

 

Saime viite ja kontrollime üle, säiliku EAA.68.1.124 lehelt 3 selgub, et vanus on tegelikult täitsa olemas, Jevdokia on 35-aastane.

Lisame selle info nii väljale vanus kui arvutatud_vanused.

UPDATE "Isik"

SET vanus=35

WHERE perenimi = ’TilanAND eesnimi = ’Jevdokia’;

Samamoodi saab ka andmeid parandada, kui seda on vaja, eelmine väärtus kirjutatakse üle. Meil ongi vaja parandada Jaan Jõggi suhe_leibkonnapeaga, seal on kirjaviga.

UPDATE "Isik"

SET suhe_leibkonnapeaga = 'leibkonnapea'

WHERE perenimi = 'Jõggi' AND eesnimi = 'Jaan';

 

ÜLESANNE 2

Kirjuta SQL lause, mis lisaks Jevdokia vanuse ka veergu arvutatud_vanused, sest selle põhjal arvutame keskmise vanuse.

 

Jõuame nüüd tagasi keskmise vanuse arvutamise juurde.

 

ÜLESANNE 3

Kirjuta SQL lause, mis arvutaks kõigi väljarändajate keskmise vanuse. 

 

 

 

ÜLESANNE 4

Kirjuta SQL lause, mis ümardaks eelmise tulemuse täisarvuks.

  Nüüd tahaksime leida ka iga leibkonna liikmete keskmise vanuse. Selleks saab kasutada mingi tunnuse asemel grupeerimise lauset.

 

GROUP by laused

GROUP BY lauset saab kasutada pärast FROM või WHERE osa. GROUP BY saab toimuda kas SQL lausele või mõnele veerule. Ei kasutata alati agregeeriva funktsiooniga võib ka lihtsalt kasutada esitamaks kõiki ridu ühekordselt, sarnane DISTINCT lausega.

Nt kui tahame teada, milliseid vara väärtusi üldse esineb, siis

SELECT vara_vaartus FROM "Leibkond"

GROUP by vara_vaartus;

 

 

Aga rohkem on GROUP BY lausest kasu kui kasutada koos agregeerivate matemaatiliste funktsioonidega (Vt lihtsamate SQL päringute materjali).

 

 

ÜLESANNE 5

Kirjuta SQL lause, mis leiab leibkondade suurused ja reastab leibkonnad suuruse järgi. Vihje: teame, et tabeli Isik veerus leibkond sisaldub iga leibkonna id ja saame leida kui mitmel inimesel on sama leibkonna id ja sorteerime suuremast väiksema suunas.

 

 

Üldistatult süntaks:

SELECT veerg1, veerg2, agregeeriv funktsioon(veerg3)

FROM tabel

GROUP BY veerg1, veerg2;

Koos GROUP BY lausega kasutatakse sageli HAVING lauset, et filtreerida grupeeritud ridu, mis ei vasta tingimusele. WHERE lausest eristab HAVING lauset see, et WHERE seab tingimused üksikutele ridadele enne GROUP BY-d.

Leiame leibkonnad, mis on suuremad kui viis liiget. Oluline on järjekord, sorteerimiskäsk peab olema kõige viimane.

 

SELECT perenimi, COUNT (leibkond)

FROM "Isik"

GROUP by perenimi, leibkond

HAVING COUNT (leibkond) > 5

ORDER BY COUNT (leibkond) DESC;

 

 

HAVING lauset saab kasutada ka ilma GROUP by lauseta.

 

ÜLESANNE 6

Kirjuta SQL käsk, mis leiab ümardatud keskmise vanus igas leibkonnas, ja kuvab keskmise vanuse koos leibkonna perenimega (meie andmetes leibkonnaliikmete perenimed kattuvad leibkonnapea nimega):

 

 

Vihje: veeru nimi "keskmine vanus" on saadud veeru aliast kasutades. PostgreSQL lubab kuvatavates tulemustes veeru nimetusi muuta, kasutades jutumärke võib alias olla ka kaheosaline.

ÜLDSÜNTAKS veeru ümbernimetamiseks ehk aliase kasutamiseks:

SELECT veerg1, veerg2, veerg3 AS alias

FROM tabel

WHERE [tingimused];

Aliast saab kasutada ka agregeerivate funktsioonidega.

SELECT veerg1, veerg2, agregeeriv funktsioon(veerg3) AS uus veeru nimi

FROM tabel

GROUP BY veerg1, veerg2;

  

Elukohad

Leiame kõigi isikute elukohad. See on taas päring üle kolme tabeli, sest tabelid Isik ja Elukoht on seotud läbi Leibkonna tabeli.

SELECT perenimi, eesnimi, nimi FROM "Isik", "Elukoht", "Leibkond"

WHERE "Isik".leibkond="Leibkond".leibkond_id

AND "Leibkond".elukoht="Elukoht".elukoht_id;

 

Aga tegelikult tahaksime pigem teada, kui palju ühest vallast inimesi välja rändas sorteeritult alates vallast, kust oli kõige rohkem väljarändajaid. See on GROUP BY päring üle rohkem kui ühe tabeli.

SELECT nimi as elukoht, COUNT (leibkond) as inimesi

FROM "Isik", "Elukoht", "Leibkond"

WHERE "Isik".leibkond="Leibkond".leibkond_id

AND "Leibkond".elukoht="Elukoht".elukoht_id

GROUP BY elukoht, nimi

ORDER BY COUNT (leibkond) DESC;

Oluline on silmas pidada, et WHERE, kus märgitakse, millised tabelid tuleb omavahel siduda, oleks enne GROUP BY osa.

 

 

 

 

 

 

 

 

 

Selles päringus võib tunduda, et tabelit Isik ei ole vaja siduda. See on siiski vajalik sest tabelist Leibkond ei saa me kätte leibkonda kuuluvate inimeste arvu. Kui tahaksime ühest või teisest vallast väljarännanud leibkondade arvu, piisaks ainult kahe tabeli sidumisest. Siis tuleks tulemus selline:

 

 

 

 

 

 

 

 

 

 

 

 

ÜLESANNE 7

Kirjuta SQL päring, millega saab eelmise tabeli. Veeru nime leibkondi saamiseks on kasutatud aliast (AS), samuti veeru nime elukoht saamiseks, sest tabelis Elukoht on valla nimi veerus nimi. Pane tähele, et Leibkond tabelis on leibkonna tunnus väljal leibkond_id.

 

Saab veel küsida, kuhu rännati?

ÜLESANNE 8

Eespoolsete näidete varal kirjuta SQL päring, mis kuvab iga inimese perenime, eesnime, elukoha ja sihtkoha kubermangu, maakonna, kyla, asunduse, valla ja linna. Siduda tuleks neli tabelit. Tulemuses on elukoha päise nimeks kasutamiseks kasutatud aliast, sest välja nimeks on Elukoht tabelis nimi.

 

 

 

 

Leiame leibkonna keskmise suuruse (isikute keskmine arv leibkonnas) väljarände sihtkubermangude kaupa. Andmeid on andmebaasis vähe, seepärast ei ole tulemus väga informatiivne.

SELECT kubermang, ROUND (avg (arv))

FROM (SELECT leibkond, COUNT (*) as arv FROM "Isik" GROUP BY leibkond) as a, "Sihtkoht", "Isik", "Leibkond"

WHERE "Leibkond".leibkond_id=a.leibkond 

AND "Sihtkoht".sihtkoht_Id="Leibkond".sihtkoht

GROUP BY kubermang;

 

 

Selgituseks:

alampäring

SELECT leibkond, COUNT (*) as arv FROM "Isik"

                 GROUP BY leibkond;

tagastab tulemuseks tabeli, kus on Isiku tabelist leibkonna id ja iga leibkonna suurus. PostgreSQL tahab, et alampäringul oleks FROM lauses alias (FROM (SELECT ...) [AS] ALIAS), et alampäringu tulemust saaks kasutada alampäringut ümbritsevas päringus, vt ümbritseva päringu SELECT lauset ja aliase arv kasutamist seal.

Selle näite ümbritsevas päringus arvutatakse ümardatud leibkondade keskmine suurus iga kubermangu kohta.  Ufaasse läks ainult Kartofelite 14 liikmeline leibkond. Tobolskisse aga läksid perekond Vähi (5), Uibo (7) ja Roop (3). Nende leibkondade keskmine suurus on 5.

 

Veel saab küsida, kui palju väljarännanuid millisest maakonnast oli.

SELECT "Maakond".nimi as "Kust maakonnast", COUNT (perenimi) as väljarändajaid FROM "Isik", "Leibkond", "Kihelkond", "Maakond", "Elukoht"

WHERE "Isik".leibkond="Leibkond".leibkond_id AND

"Leibkond".elukoht="Elukoht".elukoht_id AND

"Elukoht".kihelkond="Kihelkond".kihelkond_id AND

"Kihelkond".maakond_id="Maakond".maakond_id

GROUP BY "Kust maakonnast";

 

 

Milliseid asundusi on mainitud?

 

SELECT DISTINCT asundus from "Sihtkoht";

 

ÜLESANNE 9

Kirjuta SQL lause, mis annaks tulemuseks, kes mis vallast (alias elukoht enne) läksid linna elama ja kuhu?

 

 

Vihje: Siin peab üks WHERE lause tingimuse olema linn is NOT NULL. Kui seda ei pane, kuvatakse kõik isikud, ka need, kes linna ei läinud, sest linna väli on nende puhul olemas, lihtsalt tühi. PostgreSQLis esindab NULL puuduvat väärtust. Puuduv väärtus võib põhjustada probleeme, sest võrreldes puuduvat teadmata väärtust teiste väärtustega on tulemus samuti teadmata.

 

Mis järjekorras leibkonnad välja rändasid? Järjesta leibkonnad (perenimed) väljarändamisaasta järgi alates varaseimast.

SELECT perenimi, ymberkirjutus FROM "Isik", "Leibkond"

WHERE "Isik".leibkond="Leibkond".leibkond_id

GROUP BY perenimi, ymberkirjutus

ORDER BY ymberkirjutus ASC;

 

 

 

ÜLESANNE 10

Kirjuta SQL lause, mille tulemusest saab teada, kuhu kubermangu läksid kõige jõukamad leibkonnad. Päring peab vastuseks andma leibkonna (perenimi), vara väärtuse sorteerituna langevas joones (vara_vaartus) ja iga leibkonna sihtkohaks olnud kubermangu.

 

 

Leiame ka igasse kubermangu rännanud leibkondade vara keskmise väärtuse.

SELECT kubermang, AVG (vara_vaartus) FROM "Leibkond", "Sihtkoht"

WHERE "Leibkond".sihtkoht="Sihtkoht".sihtkoht_id

GROUP BY kubermang;