\n\tKuidas korraga mitmest tabelist andmeid p\u00e4rida?<\/b>\n<\/p>\n
\n\t\u00dchest tabelist saame k\u00f5ik tabelis olevad andmed k\u00e4tte nii (Isiku tabeli n\u00e4itel):\n<\/p>\n
\n\tSELECT<\/span><\/strong> * FROM<\/strong><\/span> \u201cIsik\u201d;\n<\/p>\n \n\tProovime samamoodi korraga kahest tabelist andmei p\u00e4rida:\n<\/p>\n \n\tSELECT <\/strong><\/span>* FROM <\/strong><\/span>\u201cIsik\u201d, \u201cLeibkond\u201d;\n<\/p>\n \n\tSiis pannakse kahe tabeli veerud k\u00f5rvuti ja korratakse esimese tabeli sisu iga veeru korral teisest tabelist. See ei ole k\u00f5ige m\u00f5ttekam p\u00e4ring. \u00a0\n<\/p>\n \n\tLisaks tabelite nimetamisele FROM lauses on vaja ka \u00f6elda, millised tabelite v\u00e4ljad 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\u00e4lisv\u00f5tmeks tabelis Isik, kus samu v\u00e4\u00e4rtusi sisaldava tunnuse nimeks on leibkond. Nende kaudu saamegi tabelid siduda.\n<\/p>\n \n\tSELECT <\/strong><\/span>* FROM<\/strong><\/span> \u201cIsik\u201d, \u201cLeibkond\u201d\n<\/p>\n \n\tWHERE<\/strong><\/span> \u201cIsik\u201d.leibkond = \u201cLeibkond\u201d.leibkond_id;\n<\/p>\n \n\t\u00dcle mitme tabeli p\u00e4ringuid tehes tuleb tabelid omavahel siduda \u00f6eldes, millised kahe tabeli v\u00e4ljad on vastavuses (sisaldavad samu v\u00e4\u00e4rtusi). Nn v\u00f5tmev\u00e4ljad, mille v\u00e4lisv\u00f5tmeid lisades \u00e4ra sidusime.\n<\/p>\n \n\tProovi need kaks eelmist p\u00e4ringut l\u00e4bi. Viimase p\u00e4ringu tulemusena v\u00e4ljastatakse k\u00f5ik andmed, mis on tabelites \u201cIsik\u201d ja \u201cLeibkond\u201d, aga andmed on omavahel korrektselt seotud. Tuleb t\u00e4hele panna vaid seda, et kui vara v\u00e4\u00e4rtus on meie andmetes antud leibkonna kohta summeeritult, siis selles suures tabelis kuvatakse leibkonna vara v\u00e4\u00e4rtust iga leibkonna liikme juures, seda aga tuleb t\u00f5lgendada kui selle leibkonna vara koguv\u00e4\u00e4rtust, kuhu isik kuulub, mitte isikliku varanduse v\u00e4\u00e4rtusena.\u00a0\n<\/p>\n \n\t\u00dclds\u00fcntaks:\n<\/p>\n \n\tSELECT<\/strong><\/span> tabel1.veerunimi1, tabel1.veerunimi2, tabel2.veerunimi3 FROM <\/strong><\/span>tabel1, tabel2\n<\/p>\n \n\tWHERE<\/strong><\/span> tabel1.veerunimi = tabel2.veerunimi;\n<\/p>\n \n\tWHERE lauses veerunimi t\u00e4histab omavahel v\u00e4lisv\u00f5tmetega seotud v\u00e4lju. \u00dche tabeli puhul on tegu primaarv\u00f5tmega, teise tabeli puhul v\u00e4lisv\u00f5tmega.\n<\/p>\n \n\tSQL keeles on tabelite \u00fchendamiseks olemas ka JOIN konstruktsioon. JOIN lausete abil saab erinevaid tabeleid kombineerida suhteliselt sarnaselt nagu FROM \u2026 WHERE konstruktsiooniga. FROM konstruktsiooni nimetatakse ka vaikimisi JOIN (implicit JOIN), see on lihtsaim viis andmeid \u00fchendada ja t\u00e4iesti piisav ka palju suuremate tabelite puhul kui meie oma. On erinevaid arvamusi, kas FROM\u2026WHERE laused on loetavamad v\u00f5i on JOIN laused loetavamad. <\/i>\n<\/p>\n \n\tOn ka rida v\u00e4ljendatud JOIN (explicit JOIN) variante, nagu CROSS JOIN, INNER JOIN (kui ei ole t\u00e4psustatud, t\u00e4hendab JOIN INNER JOIN), LEFT OUTER JOIN, RIGHT OUTER JOIN, \n\t\u00dclds\u00fcntaks:<\/em>\n<\/p>\n \n\tSELECT<\/strong><\/span> table1.column1, table2.column2\u2026<\/em>\n<\/p>\n \n\tFROM<\/strong><\/span> table1<\/em>\n<\/p>\n \n\tJOIN<\/strong><\/span> table2<\/em>\n<\/p>\n \n\tON<\/strong><\/span> table1.common_filed = table2.common_field;<\/em>\n<\/p>\n \n\t\u00a0\n<\/p>\n \n\tL\u00e4heme oma andmebaasiga edasi. V\u00f5tame n\u00fc\u00fcd andmebaasist v\u00e4lja iga leibkonna vara v\u00e4\u00e4rtuse koos leibkonnapea nimega.\n<\/p>\n \n\tSELECT<\/strong><\/span> eesnimi, perenimi, vara_vaartus FROM<\/strong><\/span> \u201cIsik\u201d, \u201cLeibkond\u201d WHERE<\/strong><\/span> suhe_leibkonnapeaga= \u2018leibkonnapea\u2019\n<\/p>\n \n\tAND <\/span><\/strong>\u201cLeibkond\u201d.leibkond_id = \u201cIsik\u201d.leibkond;\n<\/p>\n \n\tKui me selles p\u00e4ringus ei m\u00e4rgiks \u00e4ra, et suhe_leibkonnapeaga = \u2019leibkonnapea\u2019, saaksime tulemuseks taas k\u00f5ik leibkonna liikmed ja k\u00f5igi nende juures kuvataks leibkonna vara v\u00e4\u00e4rtust.\n<\/p>\n \n\t\u00a0\n<\/p>\n \n\t \n\t\u00a0\n<\/p>\n \n\t \n\tKirjuta SQL p\u00e4ring, mis leiab ainult perepea Henrik Kartofeli vara v\u00e4\u00e4rtuse ja kuvab ka tema eesnime ja perenime. Siin ja j\u00e4rgmiste \u00fclesannete juures on \u00e4ra toodud tabel, mille PostgreSQL v\u00e4ljastab \u00f5ige SQL lause abil. Veergude j\u00e4rjekord ei pea lahenduses sama olema.<\/i> \u00a0\n<\/p>\n \n\t\u00a0\n<\/p>\n \n\t \n\t\u00a0\n<\/p>\n \n\tV\u00e4ljar\u00e4ndajate keskmine vanus<\/b>\n<\/p>\n \n\tLeiame v\u00e4ljar\u00e4ndajate keskmise vanuse. Tabelis \u201cIsik\u201d on v\u00e4ljad vanus ja s\u00fcnniaeg. Erinevatest allikatest andmeid lisades peab arvestama, et need v\u00f5ivad olla erineval kujul, n\u00e4iteks v\u00f5ivad inimeste kohta olla sisestatud s\u00fcnniajad v\u00f5i vanused, nagu praegusel juhul ongi.\u00a0 Andmebaasi disainima asudes peab otsustama, kas andmed \u00fchtlustada juba sisestamise k\u00e4igus, siis tulnuks k\u00f5igi puhul, kellel oli teada s\u00fcnniaeg, mitte vanus, arvutada vanus ja lisada see andmebaasi ja piirduda ainult \u00fche, vanuse lahtriga. Samas on s\u00fcnniaeg t\u00e4psem ja sellest infost oleks kahju loobuda, kui see on meil olemas ja v\u00f5iksime selle h\u00f5lpsalt lisada. Seep\u00e4rast on m\u00f5istlik teha alguses kaks erinevat v\u00e4lja.\n<\/p>\n \n\tAga kui tahame leida keskmist vanust v\u00f5i leibkonna keskmist vanust, siis oleks meil j\u00e4lle vaja, et see info oleks esitatud \u00fchetaoliselt.\n<\/p>\n \n\tSaame luua oma andmebaasis uue v\u00e4lja ja seal andmed \u00fchtlustada, alles j\u00e4\u00e4vad ka m\u00f5lemad algsed v\u00e4ljad. Nii on lihtsam sisestada ja olenevalt andmetest, v\u00f5ib olla lihtsam p\u00e4rast kasutada samu andmeid uute k\u00fcsimuste k\u00fcsimiseks, kui andmed ei ole vastavalt esialgsetele k\u00fcsimustele liigselt normaliseeritud.\n<\/p>\n \n\tSelleks, et saaksime leida v\u00e4ljar\u00e4ndajate keskmise vanuse, peame k\u00f5igepealt arvutama vanused ka nende isikute jaoks, kelle kohta on andmebaasis olemas s\u00fcnniaeg.\n<\/p>\n \n\tVaatame k\u00f5igepealt olemasolevad andmed \u00fcle, toome tabelitest \u201cIsik\u201d ja \u201cLeibkond\u201d kokku v\u00e4ljad, millega edasi toimetama peame ja sorteerime tabeli algusesse v\u00e4ljad, kus on teada synniaeg, aga pole vanust.\n<\/p>\n \n\tSELECT<\/strong><\/span> perenimi, vanus, synniaeg, ymberkirjutus FROM<\/strong><\/span> \u201cIsik\u201d, \u201cLeibkond\u201d\n<\/p>\n \n\tWHERE<\/strong><\/span> \u201cIsik\u201d.leibkond = \u201cLeibkond\u201d.leibkond_id\n<\/p>\n \n\tORDER BY<\/strong> <\/span>synniaeg;\n<\/p>\n \n\t\u00a0\n<\/p>\n \n\t \n\t\u00a0\n<\/p>\n \n\tSiit on h\u00e4sti n\u00e4ha, et k\u00fcsimus on teisis\u00f5nu leida, kui vanad olid v\u00e4ljar\u00e4ndajad \u00fcmberkirjutamise ajal. Tabelis \u201cLeibkond\u201d on v\u00e4li ymberkirjutus, sellest tuleks lahutada tabelis \u201cIsik\u201d asuval v\u00e4ljal synniaeg olev info. Kuna meie andmetes ei ole kirjas, mis kuus on \u00fcmberkirjutamine toimunud, siis v\u00f5tame vanuse arvutamiseks s\u00fcnniajast v\u00e4lja ainult aasta ja teeme lahutustehte. Saadud vanuseid l\u00e4heb vaja keskmise vanuse arvutamiseks, seep\u00e4rast teeme uue v\u00e4lja arvutatud_vanused ja paneme arvutatava info sinna.\n<\/p>\n \n\tTeeme uue v\u00e4lja:\n<\/p>\n \n\tALTER TABLE<\/span><\/strong> \u201cIsik\u201d ADD COLUMN<\/strong><\/span> arvutatud_vanused numeric;\n<\/p>\n \n\tN\u00fc\u00fcd v\u00f5tame Leibkonna tabelist \u00fcmberkirjutusaja, lahutame sellest s\u00fcnniaja (v\u00f5tame synniaja v\u00e4ljalt ainult aasta k\u00e4suga SELECT<\/strong><\/span> date_part(\u2018year\u2019, synniaeg) ja paneme leitud vanused uuele vastloodud v\u00e4ljale arvutatud_vanused\n<\/p>\n \n\tUPDATE<\/span><\/strong> \u201cIsik\u201d\n<\/p>\n \n\tSET<\/span><\/strong> arvutatud_vanused = ymberkirjutus \u2013 (SELECT<\/span><\/strong> date_part (\u2018year\u2019, synniaeg))\n<\/p>\n \n\tFROM<\/span><\/strong> \u201cLeibkond\u201d\n<\/p>\n \n\tWHERE<\/span><\/strong> \u201cIsik\u201d.leibkond=\u201dLeibkond\u201d.leibkond_id;\n<\/p>\n \n\tKontrollime, kas sai nii nagu tahtsime.\n<\/p>\n \n\tSELECT<\/span><\/strong> perenimi, eesnimi, vanus, synniaeg, ymberkirjutus, arvutatud_vanused FROM \u201cIsik\u201d, \u201cLeibkond\u201d\n<\/p>\n \n\tWHERE<\/span><\/strong> \u201cIsik\u201d.leibkond=\u201dLeibkond\u201d.leibkond_id\n<\/p>\n \n\tORDER BY <\/span><\/strong>arvutatud_vanused;\n<\/p>\n \n\t\u00a0\n<\/p>\n \n\t \n\t\u00a0\n<\/p>\n \n\tTerviklikkuse jaoks kopeerime v\u00e4ljale arvutatud_vanused ka \u00fclej\u00e4\u00e4nud vanused v\u00e4ljalt vanused, et seda veergu siis\u00a0 kasutada keskmise vanuse arutamiseks.\n<\/p>\n \n\tUPDATE <\/span><\/strong>\u201cIsik\u201d\n<\/p>\n \n\tSET <\/span><\/strong> arvutatud_vanused= vanus\n<\/p>\n \n\tFROM <\/span><\/strong>\u201cLeibkond\u201d\n<\/p>\n \n\tWHERE<\/span><\/strong> arvutatud_vanused <\/strong>is null AND<\/strong><\/span> \u201cIsik\u201d.leibkond = \u201cLeibkond\u201d.leibkond_id;\n<\/p>\n \n\t\u00c4ra seda praegu j\u00e4rele proovi, aga kui midagi l\u00e4heb valesti, saab veergu, nt veergu arvutatud_vanused kustutada nii:\n<\/p>\n \n\tALTER TABLE<\/span><\/strong> \u201cIsik\u201d DROP COLUMN <\/span><\/strong> arvutatud_vanused;\n<\/p>\n \n\t\u00a0\n<\/p>\n \n\tKontrollime, kas k\u00f5igil isikutel on v\u00e4ljal arvutatud_vanused vanus olemas ja kui ei ole, siis miks see nii olla v\u00f5ib, ehk p\u00e4rime andmebaasist k\u00f5ik asjasse puutuvad v\u00e4ljad tabelitest Isik ja Leibkond ja inimese nime, kellel vanust pole:\n<\/p>\n \n\tSELECT<\/span><\/strong> <\/span><\/strong>perenimi, eesnimi, vanus, synniaeg, ymberkirjutus, arvutatud_vanused FROM<\/span><\/strong> \u201cIsik\u201d, \u201cLeibkond\u201d\n<\/p>\n \n\tWHERE<\/strong> <\/span>arvutatud_vanused is null AND<\/strong><\/span> \u201cIsik\u201d.leibkond = \u201cLeibkond\u201d.leibkond_id\n<\/p>\n \n\t\u00a0\n<\/p>\n \n\t \n\tSelgub, et Jevdokia Tilanil pole m\u00e4rgitud ei vanust ega synniaega ja seep\u00e4rast ei ole tal ka vanust lahtris arvutatud_vanused.\n<\/p>\n \n\tTahame allikast j\u00e4rele vaadata, kas tal ikka t\u00f5esti ei olnud vanust ega s\u00fcnniaega v\u00f5i on j\u00e4\u00e4nud see lihtsalt v\u00e4lja m\u00e4rkimata. Otsime viite, see t\u00e4hendab, et meil l\u00e4heb vaja lisaks tabelitele Isik ja Leibkond andmeid veel tabelitest Leidumine ja Arhiiviallikas:\n<\/p>\n \n\tSELECT <\/span><\/strong><\/strong><\/strong><\/strong><\/strong>perenimi, eesnimi, nimetus, viide, lehed FROM <\/span><\/strong><\/span><\/strong><\/strong><\/strong><\/strong><\/strong>\u201cIsik\u201d, \u201cLeibkond\u201d, \u201cLeidumine\u201d, \u201cArhiiviallikas\u201d <\/span><\/strong><\/span><\/strong><\/strong><\/strong><\/strong><\/strong>\n<\/p>\n \n\tWHERE <\/span><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong>perenimi = \u2018Tilan\u2019 AND <\/span><\/strong><\/span><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong>eesnimi = \u2018Jevdokia\u2019\n<\/p>\n \n\tAND <\/span><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong>\u201cIsik\u201d.leibkond = \u201cLeibkond\u201d.leibkond_id\n<\/p>\n \n\tAND <\/span><\/strong>\u201cLeibkond\u201d.leibkond_id = \u201cLeidumine\u201d.leibkond\n<\/p>\n \n\tAND <\/span><\/strong>\u201cLeidumine\u201d.allikas = \u201cArhiiviallikas\u201d.allikas_id;\n<\/p>\n \n\tSelgituseks: me ei kuva siin andmeid tabelist Leibkond, aga p\u00e4ringu peab kirjutama selle kaudu, sest tabelid Isik ja Arhiiviallikas on omavahel seotud tabelite Leidumine ja Leibkond kaudu. \u00dchtlasi tasub siin meelde j\u00e4tta, et kui on tabelid A, B ja C, siis kui v\u00e4lisv\u00f5tmega on seotud A ja B ja B ja C, siis on seotud ka A ja C. Mudelit tehes ei pea seega k\u00f5iki tabeleid omavahel vahetult siduma.\n<\/p>\n \n\t\u00a0\n<\/p>\n \n\t \n\t\u00a0 <\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong>\n<\/p>\n \n\tSaime viite ja kontrollime \u00fcle, s\u00e4iliku EAA.68.1.124 lehelt 3 selgub, et vanus on tegelikult t\u00e4itsa olemas, Jevdokia on 35-aastane.\n<\/p>\n \n\tLisame selle info nii v\u00e4ljale vanus kui arvutatud_vanused.\n<\/p>\n \n\tUPDATE<\/strong> <\/span>\u201cIsik\u201d\n<\/p>\n \n\tSET<\/strong> <\/span>vanus=35\n<\/p>\n \n\tWHERE<\/strong> <\/span>perenimi = \u2019Tilan\u2019 AND<\/strong> <\/span><\/span>eesnimi = \u2019Jevdokia\u2019;\n<\/p>\n \n\tSamamoodi saab ka andmeid parandada, kui seda on vaja, eelmine v\u00e4\u00e4rtus kirjutatakse \u00fcle. Meil ongi vaja parandada Jaan J\u00f5ggi suhe_leibkonnapeaga, seal on kirjaviga.\n<\/p>\n \n\tUPDATE<\/strong> <\/span>\u201cIsik\u201d\n<\/p>\n \n\tSET <\/span><\/strong>suhe_leibkonnapeaga = \u2018leibkonnapea\u2019\n<\/p>\n \n\tWHERE<\/strong> <\/span>perenimi = \u2018J\u00f5ggi\u2019 AND<\/strong> <\/span><\/span>eesnimi = \u2018Jaan\u2019;\n<\/p>\n \n\t
FULL OUTER JOIN.<\/i>\n<\/p>\n![\"keerulisem1.png\"](\"https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/randaj_mudel_uuem.png\" "\\"keerulisem1.png\\"")
\n<\/p>\n\u00dc<\/span>LESANNE 1<\/span><\/strong>\n<\/p>\n\n<\/p>\n\n<\/p>\n\n<\/p>\n<\/strong> <\/strong><\/strong><\/strong><\/strong>\n<\/p>\n <\/strong>\n<\/p>\n