{"id":37,"date":"2021-03-04T07:29:02","date_gmt":"2021-03-04T07:29:02","guid":{"rendered":"https:\/\/web.htk.tlu.ee\/digitaru\/lisamoodulid\/?post_type=chapter&#038;p=37"},"modified":"2021-05-07T13:52:49","modified_gmt":"2021-05-07T13:52:49","slug":"lihtsad-paringud","status":"publish","type":"chapter","link":"https:\/\/web.htk.tlu.ee\/digitaru\/lisamoodulid\/chapter\/lihtsad-paringud\/","title":{"raw":"Lihtsad p\u00e4ringud","rendered":"Lihtsad p\u00e4ringud"},"content":{"raw":"Peatselt hakkame uurima ja jooksutama oma esimesi p\u00e4ringuid. Definitsiooni kohaselt on p\u00e4ring informatsiooni leidmise korraldus. Kujult sarnanevad need muude juba \u00f5pitud SQL-i lausetega.\r\n\r\nEnne seda lisame aga n\u00e4idisandmebaasi tabelitesse veidi rohkem andmeid. Selleks avame taaskord konsooli, mille abil jooksutasime eelmises peat\u00fckis n\u00e4iteks tabelite loomise lauseid.\r\n\r\nTuleta vajadusel <a href=\"https:\/\/web.htk.tlu.ee\/digitaru\/lisamoodulid\/chapter\/tarkvara-installimine-ja-naidisandmebaasi-loomine\/\">eelmise peat\u00fcki<\/a> abil meelde, kuidas konsooli avamine ja lausete jooksutamine k\u00e4is.\r\n\r\nJooksuta n\u00fc\u00fcd j\u00e4rgnevaid lauseid, et t\u00e4ita tabelit Kursused andmetega:\r\n<ul>\r\n \t<li><code><span style=\"color: #970000\"><strong>INSERT INTO<\/strong><\/span> kursused (nimi, maht_tundides)<\/code>\r\n<code><span style=\"color: #970000\"><strong>VALUES<\/strong><\/span> (<span style=\"color: #008000\">'Programmeerimine'<\/span>, <span style=\"color: #0000ff\">10<\/span>)<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\r\n \t<li><code><span style=\"color: #970000\"><strong>INSERT INTO<\/strong><\/span> kursused (nimi, maht_tundides)<\/code>\r\n<code><span style=\"color: #970000\"><strong>VALUES<\/strong><\/span> (<span style=\"color: #008000\">'Tarkvaraarendus'<\/span>, <span style=\"color: #0000ff\">30<\/span>)<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\r\n \t<li><code><span style=\"color: #970000\"><strong>INSERT INTO<\/strong><\/span> kursused (nimi, maht_tundides)<\/code>\r\n<code><span style=\"color: #970000\"><strong>VALUES<\/strong><\/span> (<span style=\"color: #008000\">'Programmeerimine II'<\/span>, <span style=\"color: #0000ff\">10<\/span>)<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\r\n \t<li><code><span style=\"color: #970000\"><strong>INSERT INTO<\/strong><\/span> kursused (id, nimi, maht_tundides)<\/code>\r\n<code><span style=\"color: #970000\"><strong>VALUES<\/strong><\/span> (3, <span style=\"color: #008000\">'Primaarv\u00f5tmete meeldetuletus'<\/span>, <span style=\"color: #0000ff\">5<\/span>)<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\r\n<\/ul>\r\nKas lausete jooksutamise k\u00e4igus esines vigasid? Kui jah, siis proovi lause ja <em>Statistics<\/em> vaates esitatud veateate p\u00f5hjal m\u00f5ista, mis l\u00e4ks valesti. Paranda seej\u00e4rel vigane lause ning jooksuta parandatud versiooni uuesti.\r\n\r\nSeej\u00e4rel jooksuta j\u00e4rgnevaid lauseid, et t\u00e4ita ka tabelit Oppimine andmetega:\r\n<ul>\r\n \t<li><code><span style=\"color: #970000\"><strong>INSERT INTO<\/strong><\/span> oppimine (opilase_id, kursuse_id)<\/code>\r\n<code><span style=\"color: #970000\"><strong>VALUES<\/strong><\/span> (<span style=\"color: #0000ff\">1<\/span>, <span style=\"color: #0000ff\">1<\/span>)<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\r\n \t<li><code><span style=\"color: #970000\"><strong>INSERT INTO<\/strong><\/span> oppimine (opilase_id, kursuse_id)<\/code>\r\n<code><span style=\"color: #970000\"><strong>VALUES<\/strong><\/span> (<span style=\"color: #0000ff\">1<\/span>, <span style=\"color: #0000ff\">2<\/span>)<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\r\n \t<li><code><span style=\"color: #970000\"><strong>INSERT INTO<\/strong><\/span> oppimine (opilase_id, kursuse_id)<\/code>\r\n<code><span style=\"color: #970000\"><strong>VALUES<\/strong><\/span> (<span style=\"color: #0000ff\">1<\/span>, <span style=\"color: #0000ff\">3<\/span>)<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\r\n \t<li><code><span style=\"color: #970000\"><strong>INSERT INTO<\/strong><\/span> oppimine (opilase_id, kursuse_id)<\/code>\r\n<code><span style=\"color: #970000\"><strong>VALUES<\/strong><\/span> (<span style=\"color: #0000ff\">2<\/span>, <span style=\"color: #0000ff\">3<\/span>)<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\r\n<\/ul>\r\nKontrolli siinkohal tabeleid Kursused ja Oppimine ning vaata \u00fcle, kas andmeid said edukalt sisestatud.\r\n\r\nN\u00fc\u00fcd jooksutamegi oma esimese p\u00e4ringu. P\u00e4ringute jooksutamine k\u00e4ib t\u00e4pselt samamoodi nagu muude SQL-i lausete jooksutamine, mida me siiani teinud oleme.\r\n\r\nEsimene p\u00e4ringulause on j\u00e4rgmine:\r\n<ul>\r\n \t<li><code><span style=\"color: #970000\"><strong>SELECT<\/strong><\/span> * <span style=\"color: #970000\"><strong>FROM<\/strong><\/span> kursused<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\r\n<\/ul>\r\nP\u00e4rast p\u00e4ringu edukat jooksutamist peaks DBeaveri vaade v\u00e4lja n\u00e4gema sarnane. Pane t\u00e4hele, et konsooli vaate (pealkirjaga <em>&lt;postgres&gt; Console<\/em>) all on n\u00e4ha k\u00f5iki andmeridu, mis vastavad jooksutatud p\u00e4ringule. Teisis\u00f5nu on seal n\u00e4ha p\u00e4ringu tulemust.\r\n\r\n<img class=\"alignnone size-full wp-image-73\" src=\"https:\/\/web.htk.tlu.ee\/digitaru\/lisamoodulid\/wp-content\/uploads\/sites\/21\/2021\/03\/4-1.png\" alt=\"DBeaver'i vaade p\u00e4rast p\u00e4ringu jooksutamist\" width=\"1024\" height=\"768\" \/>\r\n\r\n[h5p id=\"2\"]\r\n\r\nKui soovid meelde tuletada m\u00f5isteid nagu olemi eksemplar ja tunnus, vaata \u00fcle vastav <a href=\"https:\/\/web.htk.tlu.ee\/digitaru\/lisamoodulid\/chapter\/andmebaaside-loomise-protsess\/\">peat\u00fckk<\/a> eelmisest moodulist.\r\n\r\nJooksutame n\u00fc\u00fcd uut, teistsugust p\u00e4ringut:\r\n<ul>\r\n \t<li><code><span style=\"color: #970000\"><strong>SELECT<\/strong><\/span> nimi <span style=\"color: #970000\"><strong>FROM<\/strong><\/span> kursused<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\r\n<\/ul>\r\nP\u00e4rast p\u00e4ringu jooksutamist n\u00e4eme p\u00e4ringu tulemuses, et iga andmerea kohta on n\u00e4ha vaid veeru <code>nimi<\/code> v\u00e4\u00e4rtus ning et veerge <code>id<\/code> ja <code>maht_tundides<\/code> enam ei kuvata.\r\n\r\nSeega n\u00e4eme, et m\u00e4rks\u00f5nade <code><span style=\"color: #970000\"><strong>SELECT<\/strong><\/span><\/code> ja <code><span style=\"color: #970000\"><strong>FROM<\/strong><\/span><\/code> vahel saame t\u00e4psustada nende tunnuste nimesid, mida soovime p\u00e4rida. Kui soovime p\u00e4rida mitut tunnust, eraldame need omavahel komadega.\r\n\r\nN\u00e4iteks, kui soovime n\u00e4ha \u00f5pilaste tunnuseid <code>eesnimi<\/code> ja <code>perekonnanimi<\/code>, kuid mitte tunnust <code>id<\/code>, siis saame seda teha jooksutades j\u00e4rgnevat p\u00e4ringut:\r\n<ul>\r\n \t<li><code><span style=\"color: #970000\"><strong>SELECT<\/strong><\/span> eesnimi, perekonnanimi <span style=\"color: #970000\"><strong>FROM<\/strong><\/span> \u00f5pilased<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\r\n<\/ul>\r\nKatseta k\u00f5iki siiani \u00f5pitud p\u00e4ringut\u00fc\u00fcpe ka teiste tabelite peal ning m\u00f5tiskle, mida nende p\u00e4ringute tulemustest v\u00e4lja lugeda saab.\r\n\r\n\u00d5pime n\u00fc\u00fcd veel \u00fche uue p\u00e4rimise viisi \u00e4ra. Jooksutame selleks k\u00f5igepealt j\u00e4rgnevat p\u00e4ringut:\r\n<ul>\r\n \t<li><code><span style=\"color: #970000\"><strong>SELECT<\/strong><\/span> opilase_id <span style=\"color: #970000\"><strong>FROM<\/strong><\/span> \u00f5ppimine<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\r\n<\/ul>\r\nP\u00e4ringu tulemusest saame v\u00e4lja lugeda seda, mitmel kursusel iga \u00f5pilane \u00f5pib. Seda selle j\u00e4rgi, mitu korda mingi \u00f5pilase ID tulemuses esineb. Kui andmed on sisestatud nii nagu \u00fclaltoodud n\u00e4idetes, peaks \u00f5pilase ID v\u00e4\u00e4rtusega 1 esinema 3 korda. See t\u00e4hendabki seda, et see \u00f5pilane \u00f5pib parasjagu 3 kursusel.\r\n\r\nJooksutame n\u00fc\u00fcd uut m\u00e4rks\u00f5na <code><span style=\"color: #970000\"><strong>DISTINCT<\/strong><\/span><\/code> sisaldavat p\u00e4ringut:\r\n<ul>\r\n \t<li><code><span style=\"color: #970000\"><strong>SELECT DISTINCT<\/strong><\/span> opilase_id <span style=\"color: #970000\"><strong>FROM<\/strong><\/span> \u00f5ppimine<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\r\n<\/ul>\r\nKas m\u00e4rkad erinevust v\u00f5rreldes eelneva p\u00e4ringu tulemusega? M\u00f5tiskle, mida me uue p\u00e4ringu tulemuses n\u00e4eme.\r\n\r\nJooksutage n\u00fc\u00fcd j\u00e4rgmist kahte v\u00e4lja toodud p\u00e4ringut ja v\u00f5rrelge ka nende tulemusi:\r\n<ul>\r\n \t<li><code><span style=\"color: #970000\"><strong>SELECT<\/strong><\/span> id, opilase_id <span style=\"color: #970000\"><strong>FROM<\/strong><\/span> \u00f5ppimine<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\r\n \t<li><code><span style=\"color: #970000\"><strong>SELECT DISTINCT<\/strong><\/span> id, opilase_id <span style=\"color: #970000\"><strong>FROM<\/strong><\/span> \u00f5ppimine<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\r\n<\/ul>\r\nPeale andmeridade j\u00e4rjestuse ei tohiks kahe viimase p\u00e4ringu tulemuste vahel erinevusi olla. M\u00f5tiskle, miks andmeridade arv seekord ei erinenud.\r\n\r\n[h5p id=\"3\"]\r\n\r\nEelnevalt tutvustatud p\u00e4ringu <code><span style=\"color: #970000\"><strong>SELECT DISTINCT<\/strong><\/span> opilase_id <span style=\"color: #970000\"><strong>FROM<\/strong><\/span> \u00f5ppimine<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code> tulemusest saime teada, mitu \u00f5pilast parasjagu \u00fcldse \u00f5pivad. Kuigi seda v\u00f5is lugeda v\u00e4lja ka algsest p\u00e4ringust, kus m\u00e4rks\u00f5na <code><span style=\"color: #970000\"><strong>DISTINCT<\/strong><\/span><\/code> ei kasutatud, tegi <code><span style=\"color: #970000\"><strong>DISTINCT<\/strong><\/span><\/code> kasutamine meile selle lihtsamaks, kuna korduvaid v\u00e4\u00e4rtuseid enam ei kuvatud.\r\n\r\nTeise p\u00e4ringupaari puhul oli p\u00e4ringu tulemuses ka tunnus <code>id<\/code>, mist\u00f5ttu ei saanud p\u00e4ringu tulemuses olnud andmeread korduda. Seda seet\u00f5ttu on tunnus <code>id<\/code> on primaarv\u00f5ti ehk selle v\u00e4\u00e4rtus on tabelis Oppimine igal real alati erinev. Seega ei muutnud m\u00e4rks\u00f5na <strong><code><span style=\"color: #970000\">DISTINCT<\/span><\/code><\/strong> p\u00e4ringusse lisamine andmeridade arvu.\r\n\r\nTasub teada, et <strong><code><span style=\"color: #970000\">DISTINCT<\/span><\/code><\/strong> kasutamine teeb p\u00e4ringu jooksutamise m\u00e4rksa aeglasemaks, sest sisemiselt tehakse v\u00f5rdlusi, millega leitakse kattuvaid ridu. Kuigi niiv\u00f5rd lihtsate p\u00e4ringute puhul ei ole ajakulu m\u00e4rgatav, tuleks seda siiski vaid vajadusel kasutada.\r\n\r\n[h5p id=\"4\"]\r\n\r\nErinevaid p\u00e4ringuid, sh ise koostatuid, v\u00f5ib alati julgelt katsetada ning ei tasu karta, et p\u00e4ringute jooksutamise t\u00f5ttu midagi katki l\u00e4heb. Isegi kui p\u00e4ring on vigane, ei muuda see andmebaasi struktuuri ega seal hoiustatavaid andmeid.\r\n\r\nN\u00fc\u00fcd oleme \u00f5ppinud lihtsas n\u00e4idisandmebaasis lihtsaid p\u00e4ringuid jooksutama ning saame andmeid suurest Netflixi teemalisest andmebaasist p\u00e4rima hakata. Samuti saame \u00f5ppida oma p\u00e4ringuid piirama selleks, et p\u00e4ringu tulemuses ei kuvataks \u00fcleliigset infot.","rendered":"<p>Peatselt hakkame uurima ja jooksutama oma esimesi p\u00e4ringuid. Definitsiooni kohaselt on p\u00e4ring informatsiooni leidmise korraldus. Kujult sarnanevad need muude juba \u00f5pitud SQL-i lausetega.<\/p>\n<p>Enne seda lisame aga n\u00e4idisandmebaasi tabelitesse veidi rohkem andmeid. Selleks avame taaskord konsooli, mille abil jooksutasime eelmises peat\u00fckis n\u00e4iteks tabelite loomise lauseid.<\/p>\n<p>Tuleta vajadusel <a href=\"https:\/\/web.htk.tlu.ee\/digitaru\/lisamoodulid\/chapter\/tarkvara-installimine-ja-naidisandmebaasi-loomine\/\">eelmise peat\u00fcki<\/a> abil meelde, kuidas konsooli avamine ja lausete jooksutamine k\u00e4is.<\/p>\n<p>Jooksuta n\u00fc\u00fcd j\u00e4rgnevaid lauseid, et t\u00e4ita tabelit Kursused andmetega:<\/p>\n<ul>\n<li><code><span style=\"color: #970000\"><strong>INSERT INTO<\/strong><\/span> kursused (nimi, maht_tundides)<\/code><br \/>\n<code><span style=\"color: #970000\"><strong>VALUES<\/strong><\/span> (<span style=\"color: #008000\">'Programmeerimine'<\/span>, <span style=\"color: #0000ff\">10<\/span>)<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\n<li><code><span style=\"color: #970000\"><strong>INSERT INTO<\/strong><\/span> kursused (nimi, maht_tundides)<\/code><br \/>\n<code><span style=\"color: #970000\"><strong>VALUES<\/strong><\/span> (<span style=\"color: #008000\">'Tarkvaraarendus'<\/span>, <span style=\"color: #0000ff\">30<\/span>)<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\n<li><code><span style=\"color: #970000\"><strong>INSERT INTO<\/strong><\/span> kursused (nimi, maht_tundides)<\/code><br \/>\n<code><span style=\"color: #970000\"><strong>VALUES<\/strong><\/span> (<span style=\"color: #008000\">'Programmeerimine II'<\/span>, <span style=\"color: #0000ff\">10<\/span>)<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\n<li><code><span style=\"color: #970000\"><strong>INSERT INTO<\/strong><\/span> kursused (id, nimi, maht_tundides)<\/code><br \/>\n<code><span style=\"color: #970000\"><strong>VALUES<\/strong><\/span> (3, <span style=\"color: #008000\">'Primaarv\u00f5tmete meeldetuletus'<\/span>, <span style=\"color: #0000ff\">5<\/span>)<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\n<\/ul>\n<p>Kas lausete jooksutamise k\u00e4igus esines vigasid? Kui jah, siis proovi lause ja <em>Statistics<\/em> vaates esitatud veateate p\u00f5hjal m\u00f5ista, mis l\u00e4ks valesti. Paranda seej\u00e4rel vigane lause ning jooksuta parandatud versiooni uuesti.<\/p>\n<p>Seej\u00e4rel jooksuta j\u00e4rgnevaid lauseid, et t\u00e4ita ka tabelit Oppimine andmetega:<\/p>\n<ul>\n<li><code><span style=\"color: #970000\"><strong>INSERT INTO<\/strong><\/span> oppimine (opilase_id, kursuse_id)<\/code><br \/>\n<code><span style=\"color: #970000\"><strong>VALUES<\/strong><\/span> (<span style=\"color: #0000ff\">1<\/span>, <span style=\"color: #0000ff\">1<\/span>)<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\n<li><code><span style=\"color: #970000\"><strong>INSERT INTO<\/strong><\/span> oppimine (opilase_id, kursuse_id)<\/code><br \/>\n<code><span style=\"color: #970000\"><strong>VALUES<\/strong><\/span> (<span style=\"color: #0000ff\">1<\/span>, <span style=\"color: #0000ff\">2<\/span>)<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\n<li><code><span style=\"color: #970000\"><strong>INSERT INTO<\/strong><\/span> oppimine (opilase_id, kursuse_id)<\/code><br \/>\n<code><span style=\"color: #970000\"><strong>VALUES<\/strong><\/span> (<span style=\"color: #0000ff\">1<\/span>, <span style=\"color: #0000ff\">3<\/span>)<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\n<li><code><span style=\"color: #970000\"><strong>INSERT INTO<\/strong><\/span> oppimine (opilase_id, kursuse_id)<\/code><br \/>\n<code><span style=\"color: #970000\"><strong>VALUES<\/strong><\/span> (<span style=\"color: #0000ff\">2<\/span>, <span style=\"color: #0000ff\">3<\/span>)<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\n<\/ul>\n<p>Kontrolli siinkohal tabeleid Kursused ja Oppimine ning vaata \u00fcle, kas andmeid said edukalt sisestatud.<\/p>\n<p>N\u00fc\u00fcd jooksutamegi oma esimese p\u00e4ringu. P\u00e4ringute jooksutamine k\u00e4ib t\u00e4pselt samamoodi nagu muude SQL-i lausete jooksutamine, mida me siiani teinud oleme.<\/p>\n<p>Esimene p\u00e4ringulause on j\u00e4rgmine:<\/p>\n<ul>\n<li><code><span style=\"color: #970000\"><strong>SELECT<\/strong><\/span> * <span style=\"color: #970000\"><strong>FROM<\/strong><\/span> kursused<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\n<\/ul>\n<p>P\u00e4rast p\u00e4ringu edukat jooksutamist peaks DBeaveri vaade v\u00e4lja n\u00e4gema sarnane. Pane t\u00e4hele, et konsooli vaate (pealkirjaga <em>&lt;postgres&gt; Console<\/em>) all on n\u00e4ha k\u00f5iki andmeridu, mis vastavad jooksutatud p\u00e4ringule. Teisis\u00f5nu on seal n\u00e4ha p\u00e4ringu tulemust.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-73\" src=\"https:\/\/web.htk.tlu.ee\/digitaru\/lisamoodulid\/wp-content\/uploads\/sites\/21\/2021\/03\/4-1.png\" alt=\"DBeaver'i vaade p\u00e4rast p\u00e4ringu jooksutamist\" width=\"1024\" height=\"768\" srcset=\"https:\/\/web.htk.tlu.ee\/digitaru\/lisamoodulid\/wp-content\/uploads\/sites\/21\/2021\/03\/4-1.png 1024w, https:\/\/web.htk.tlu.ee\/digitaru\/lisamoodulid\/wp-content\/uploads\/sites\/21\/2021\/03\/4-1-300x225.png 300w, https:\/\/web.htk.tlu.ee\/digitaru\/lisamoodulid\/wp-content\/uploads\/sites\/21\/2021\/03\/4-1-768x576.png 768w, https:\/\/web.htk.tlu.ee\/digitaru\/lisamoodulid\/wp-content\/uploads\/sites\/21\/2021\/03\/4-1-65x49.png 65w, https:\/\/web.htk.tlu.ee\/digitaru\/lisamoodulid\/wp-content\/uploads\/sites\/21\/2021\/03\/4-1-225x169.png 225w, https:\/\/web.htk.tlu.ee\/digitaru\/lisamoodulid\/wp-content\/uploads\/sites\/21\/2021\/03\/4-1-350x263.png 350w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/p>\n<div id=\"h5p-2\">\n<div class=\"h5p-iframe-wrapper\"><iframe id=\"h5p-iframe-2\" class=\"h5p-iframe\" data-content-id=\"2\" style=\"height:1px\" src=\"about:blank\" frameBorder=\"0\" scrolling=\"no\" title=\"AP_13_kontrollk\u00fcsimus_1\"><\/iframe><\/div>\n<\/div>\n<p>Kui soovid meelde tuletada m\u00f5isteid nagu olemi eksemplar ja tunnus, vaata \u00fcle vastav <a href=\"https:\/\/web.htk.tlu.ee\/digitaru\/lisamoodulid\/chapter\/andmebaaside-loomise-protsess\/\">peat\u00fckk<\/a> eelmisest moodulist.<\/p>\n<p>Jooksutame n\u00fc\u00fcd uut, teistsugust p\u00e4ringut:<\/p>\n<ul>\n<li><code><span style=\"color: #970000\"><strong>SELECT<\/strong><\/span> nimi <span style=\"color: #970000\"><strong>FROM<\/strong><\/span> kursused<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\n<\/ul>\n<p>P\u00e4rast p\u00e4ringu jooksutamist n\u00e4eme p\u00e4ringu tulemuses, et iga andmerea kohta on n\u00e4ha vaid veeru <code>nimi<\/code> v\u00e4\u00e4rtus ning et veerge <code>id<\/code> ja <code>maht_tundides<\/code> enam ei kuvata.<\/p>\n<p>Seega n\u00e4eme, et m\u00e4rks\u00f5nade <code><span style=\"color: #970000\"><strong>SELECT<\/strong><\/span><\/code> ja <code><span style=\"color: #970000\"><strong>FROM<\/strong><\/span><\/code> vahel saame t\u00e4psustada nende tunnuste nimesid, mida soovime p\u00e4rida. Kui soovime p\u00e4rida mitut tunnust, eraldame need omavahel komadega.<\/p>\n<p>N\u00e4iteks, kui soovime n\u00e4ha \u00f5pilaste tunnuseid <code>eesnimi<\/code> ja <code>perekonnanimi<\/code>, kuid mitte tunnust <code>id<\/code>, siis saame seda teha jooksutades j\u00e4rgnevat p\u00e4ringut:<\/p>\n<ul>\n<li><code><span style=\"color: #970000\"><strong>SELECT<\/strong><\/span> eesnimi, perekonnanimi <span style=\"color: #970000\"><strong>FROM<\/strong><\/span> \u00f5pilased<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\n<\/ul>\n<p>Katseta k\u00f5iki siiani \u00f5pitud p\u00e4ringut\u00fc\u00fcpe ka teiste tabelite peal ning m\u00f5tiskle, mida nende p\u00e4ringute tulemustest v\u00e4lja lugeda saab.<\/p>\n<p>\u00d5pime n\u00fc\u00fcd veel \u00fche uue p\u00e4rimise viisi \u00e4ra. Jooksutame selleks k\u00f5igepealt j\u00e4rgnevat p\u00e4ringut:<\/p>\n<ul>\n<li><code><span style=\"color: #970000\"><strong>SELECT<\/strong><\/span> opilase_id <span style=\"color: #970000\"><strong>FROM<\/strong><\/span> \u00f5ppimine<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\n<\/ul>\n<p>P\u00e4ringu tulemusest saame v\u00e4lja lugeda seda, mitmel kursusel iga \u00f5pilane \u00f5pib. Seda selle j\u00e4rgi, mitu korda mingi \u00f5pilase ID tulemuses esineb. Kui andmed on sisestatud nii nagu \u00fclaltoodud n\u00e4idetes, peaks \u00f5pilase ID v\u00e4\u00e4rtusega 1 esinema 3 korda. See t\u00e4hendabki seda, et see \u00f5pilane \u00f5pib parasjagu 3 kursusel.<\/p>\n<p>Jooksutame n\u00fc\u00fcd uut m\u00e4rks\u00f5na <code><span style=\"color: #970000\"><strong>DISTINCT<\/strong><\/span><\/code> sisaldavat p\u00e4ringut:<\/p>\n<ul>\n<li><code><span style=\"color: #970000\"><strong>SELECT DISTINCT<\/strong><\/span> opilase_id <span style=\"color: #970000\"><strong>FROM<\/strong><\/span> \u00f5ppimine<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\n<\/ul>\n<p>Kas m\u00e4rkad erinevust v\u00f5rreldes eelneva p\u00e4ringu tulemusega? M\u00f5tiskle, mida me uue p\u00e4ringu tulemuses n\u00e4eme.<\/p>\n<p>Jooksutage n\u00fc\u00fcd j\u00e4rgmist kahte v\u00e4lja toodud p\u00e4ringut ja v\u00f5rrelge ka nende tulemusi:<\/p>\n<ul>\n<li><code><span style=\"color: #970000\"><strong>SELECT<\/strong><\/span> id, opilase_id <span style=\"color: #970000\"><strong>FROM<\/strong><\/span> \u00f5ppimine<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\n<li><code><span style=\"color: #970000\"><strong>SELECT DISTINCT<\/strong><\/span> id, opilase_id <span style=\"color: #970000\"><strong>FROM<\/strong><\/span> \u00f5ppimine<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code><\/li>\n<\/ul>\n<p>Peale andmeridade j\u00e4rjestuse ei tohiks kahe viimase p\u00e4ringu tulemuste vahel erinevusi olla. M\u00f5tiskle, miks andmeridade arv seekord ei erinenud.<\/p>\n<div id=\"h5p-3\">\n<div class=\"h5p-iframe-wrapper\"><iframe id=\"h5p-iframe-3\" class=\"h5p-iframe\" data-content-id=\"3\" style=\"height:1px\" src=\"about:blank\" frameBorder=\"0\" scrolling=\"no\" title=\"AP_13_kontrollk\u00fcsimus_2\"><\/iframe><\/div>\n<\/div>\n<p>Eelnevalt tutvustatud p\u00e4ringu <code><span style=\"color: #970000\"><strong>SELECT DISTINCT<\/strong><\/span> opilase_id <span style=\"color: #970000\"><strong>FROM<\/strong><\/span> \u00f5ppimine<span style=\"color: #ff0000\"><strong>;<\/strong><\/span><\/code> tulemusest saime teada, mitu \u00f5pilast parasjagu \u00fcldse \u00f5pivad. Kuigi seda v\u00f5is lugeda v\u00e4lja ka algsest p\u00e4ringust, kus m\u00e4rks\u00f5na <code><span style=\"color: #970000\"><strong>DISTINCT<\/strong><\/span><\/code> ei kasutatud, tegi <code><span style=\"color: #970000\"><strong>DISTINCT<\/strong><\/span><\/code> kasutamine meile selle lihtsamaks, kuna korduvaid v\u00e4\u00e4rtuseid enam ei kuvatud.<\/p>\n<p>Teise p\u00e4ringupaari puhul oli p\u00e4ringu tulemuses ka tunnus <code>id<\/code>, mist\u00f5ttu ei saanud p\u00e4ringu tulemuses olnud andmeread korduda. Seda seet\u00f5ttu on tunnus <code>id<\/code> on primaarv\u00f5ti ehk selle v\u00e4\u00e4rtus on tabelis Oppimine igal real alati erinev. Seega ei muutnud m\u00e4rks\u00f5na <strong><code><span style=\"color: #970000\">DISTINCT<\/span><\/code><\/strong> p\u00e4ringusse lisamine andmeridade arvu.<\/p>\n<p>Tasub teada, et <strong><code><span style=\"color: #970000\">DISTINCT<\/span><\/code><\/strong> kasutamine teeb p\u00e4ringu jooksutamise m\u00e4rksa aeglasemaks, sest sisemiselt tehakse v\u00f5rdlusi, millega leitakse kattuvaid ridu. Kuigi niiv\u00f5rd lihtsate p\u00e4ringute puhul ei ole ajakulu m\u00e4rgatav, tuleks seda siiski vaid vajadusel kasutada.<\/p>\n<div id=\"h5p-4\">\n<div class=\"h5p-iframe-wrapper\"><iframe id=\"h5p-iframe-4\" class=\"h5p-iframe\" data-content-id=\"4\" style=\"height:1px\" src=\"about:blank\" frameBorder=\"0\" scrolling=\"no\" title=\"AP_13_kontrollk\u00fcsimus_3\"><\/iframe><\/div>\n<\/div>\n<p>Erinevaid p\u00e4ringuid, sh ise koostatuid, v\u00f5ib alati julgelt katsetada ning ei tasu karta, et p\u00e4ringute jooksutamise t\u00f5ttu midagi katki l\u00e4heb. Isegi kui p\u00e4ring on vigane, ei muuda see andmebaasi struktuuri ega seal hoiustatavaid andmeid.<\/p>\n<p>N\u00fc\u00fcd oleme \u00f5ppinud lihtsas n\u00e4idisandmebaasis lihtsaid p\u00e4ringuid jooksutama ning saame andmeid suurest Netflixi teemalisest andmebaasist p\u00e4rima hakata. Samuti saame \u00f5ppida oma p\u00e4ringuid piirama selleks, et p\u00e4ringu tulemuses ei kuvataks \u00fcleliigset infot.<\/p>\n","protected":false},"author":32,"menu_order":3,"template":"","meta":{"pb_show_title":"on","pb_short_title":"","pb_subtitle":"","pb_authors":[],"pb_section_license":""},"chapter-type":[],"contributor":[],"license":[],"class_list":["post-37","chapter","type-chapter","status-publish","hentry"],"part":31,"_links":{"self":[{"href":"https:\/\/web.htk.tlu.ee\/digitaru\/lisamoodulid\/wp-json\/pressbooks\/v2\/chapters\/37","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/web.htk.tlu.ee\/digitaru\/lisamoodulid\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/web.htk.tlu.ee\/digitaru\/lisamoodulid\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/web.htk.tlu.ee\/digitaru\/lisamoodulid\/wp-json\/wp\/v2\/users\/32"}],"version-history":[{"count":22,"href":"https:\/\/web.htk.tlu.ee\/digitaru\/lisamoodulid\/wp-json\/pressbooks\/v2\/chapters\/37\/revisions"}],"predecessor-version":[{"id":1401,"href":"https:\/\/web.htk.tlu.ee\/digitaru\/lisamoodulid\/wp-json\/pressbooks\/v2\/chapters\/37\/revisions\/1401"}],"part":[{"href":"https:\/\/web.htk.tlu.ee\/digitaru\/lisamoodulid\/wp-json\/pressbooks\/v2\/parts\/31"}],"metadata":[{"href":"https:\/\/web.htk.tlu.ee\/digitaru\/lisamoodulid\/wp-json\/pressbooks\/v2\/chapters\/37\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/web.htk.tlu.ee\/digitaru\/lisamoodulid\/wp-json\/wp\/v2\/media?parent=37"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/web.htk.tlu.ee\/digitaru\/lisamoodulid\/wp-json\/pressbooks\/v2\/chapter-type?post=37"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/web.htk.tlu.ee\/digitaru\/lisamoodulid\/wp-json\/wp\/v2\/contributor?post=37"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/web.htk.tlu.ee\/digitaru\/lisamoodulid\/wp-json\/wp\/v2\/license?post=37"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}