{"id":8621,"date":"2023-05-06T15:36:03","date_gmt":"2023-05-06T13:36:03","guid":{"rendered":"https:\/\/blue.pri.ee\/ttu\/?p=8621"},"modified":"2026-05-29T16:31:18","modified_gmt":"2026-05-29T14:31:18","slug":"pr2et14-sql","status":"publish","type":"post","link":"https:\/\/blue.pri.ee\/ttu\/laborid\/pr2et14-sql\/","title":{"rendered":"PR2ET14: SQL"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_85 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/blue.pri.ee\/ttu\/laborid\/pr2et14-sql\/#Praktikumi_materjal\" >Praktikumi materjal<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blue.pri.ee\/ttu\/laborid\/pr2et14-sql\/#Andmemudel\" >Andmemudel<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blue.pri.ee\/ttu\/laborid\/pr2et14-sql\/#Ulesanne_1_W14-1_Lisa_iseennast_andmebaasi\" >\u00dclesanne 1 [W14-1]: Lisa iseennast andmebaasi<\/a><ul class='ez-toc-list-level-5' ><li class='ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/blue.pri.ee\/ttu\/laborid\/pr2et14-sql\/#Nouded\" >N\u00f5uded<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/blue.pri.ee\/ttu\/laborid\/pr2et14-sql\/#Ulesanne_2_Andmete_parimine\" >\u00dclesanne 2: Andmete p\u00e4rimine<\/a><ul class='ez-toc-list-level-5' ><li class='ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/blue.pri.ee\/ttu\/laborid\/pr2et14-sql\/#Nouded-2\" >N\u00f5uded<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/blue.pri.ee\/ttu\/laborid\/pr2et14-sql\/#Ulesanne_2_hinnatav_osa_1_W14-2\" >\u00dclesanne 2 hinnatav osa 1 [W14-2]<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/blue.pri.ee\/ttu\/laborid\/pr2et14-sql\/#Ulesanne_2_hinnatav_osa_2_W14-3\" >\u00dclesanne 2 hinnatav osa 2 [W14-3]<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/blue.pri.ee\/ttu\/laborid\/pr2et14-sql\/#Ulesanne_2_hinnatav_osa_3_W14-4\" >\u00dclesanne 2 hinnatav osa 3 [W14-4]<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/blue.pri.ee\/ttu\/laborid\/pr2et14-sql\/#Lisaulesanne_1_W14-5_Enda_andmebaasi_lisamine_usaldusvaarsemalt\" >Lisa\u00fclesanne 1 [W14-5]: Enda andmebaasi lisamine (usaldusv\u00e4\u00e4rsemalt)<\/a><ul class='ez-toc-list-level-5' ><li class='ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/blue.pri.ee\/ttu\/laborid\/pr2et14-sql\/#Taustinfot\" >Taustinfot<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/blue.pri.ee\/ttu\/laborid\/pr2et14-sql\/#Nouded-3\" >N\u00f5uded<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-13\" href=\"https:\/\/blue.pri.ee\/ttu\/laborid\/pr2et14-sql\/#Lisaulesanne_2_W14-6_Olemasolevate_andmete_muutmine\" >Lisa\u00fclesanne 2 [W14-6] : Olemasolevate andmete muutmine<\/a><ul class='ez-toc-list-level-5' ><li class='ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-14\" href=\"https:\/\/blue.pri.ee\/ttu\/laborid\/pr2et14-sql\/#Nouded-4\" >N\u00f5uded<\/a><\/li><\/ul><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-15\" href=\"https:\/\/blue.pri.ee\/ttu\/laborid\/pr2et14-sql\/#Parast_seda_tundi_peaksid\" >P\u00e4rast seda tundi peaksid<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-16\" href=\"https:\/\/blue.pri.ee\/ttu\/laborid\/pr2et14-sql\/#Taiendav_materjal\" >T\u00e4iendav materjal<\/a><\/li><\/ul><\/nav><\/div>\n<h3><span class=\"ez-toc-section\" id=\"Praktikumi_materjal\"><\/span>Praktikumi materjal<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<ul>\n<li>Slaidid: <a href=\"https:\/\/blue.pri.ee\/ttu\/files\/iax0584\/slaidid-et\/2025\/14_sql.pdf\"><strong>SQL<\/strong><\/a><\/li>\n<li>N\u00e4idisandmebaas (slaididel olevad p\u00e4ringud): <strong><a href=\"https:\/\/blue.pri.ee\/ttu\/files\/iax0584\/aluskoodid\/week14-db\/cars.db\">cars.db<\/a><\/strong><\/li>\n<li>Andmebaasiga liidestamise n\u00e4ited: [<strong><a href=\"https:\/\/blue.pri.ee\/ttu\/files\/iax0584\/aluskoodid\/week14-db\/sql1.c\">sql1.c<\/a><\/strong>]\u00a0\u00a0 [<strong><a href=\"https:\/\/blue.pri.ee\/ttu\/files\/iax0584\/aluskoodid\/week14-db\/sql2.c\">sql2.c<\/a><\/strong>]\u00a0 [<strong><a href=\"https:\/\/blue.pri.ee\/ttu\/files\/iax0584\/aluskoodid\/week14-db\/sql3.c\">sql3.c<\/a><\/strong>]<\/li>\n<\/ul>\n<p><span style=\"font-size: 22px; font-weight: bold;\">Praktikumi \u00fclesanded<\/span><\/p>\n<p>Selle praktikumi k\u00e4igus liidestame me\u00a0 C-keelse programmi SQLite3 andmebaasiga kasutades <span class=\"lang:c highlight:0 decode:true crayon-inline\">libsqlite3-dev<\/span>\u00a0 teeki. Esimese \u00fclesande k\u00e4igus alustad andmete lisamisest olemasolevasse andmebaasi, millele j\u00e4rgneb andmete p\u00e4rimine teise \u00fclesande raames.<\/p>\n<p>Praktikumil on kaks lisa\u00fclesannet, millest esimeses tuleb muuta andmete lisamist t\u00f6\u00f6kindlamaks. Teises \u00fclesandes tuleb uuendada andmebaasis juba eksisteerivaid andmeid.<\/p>\n<p><strong>Lae alla tunnit\u00f6\u00f6ks kasutatav andmebaas: <a href=\"https:\/\/blue.pri.ee\/ttu\/files\/iax0584\/aluskoodid\/week14-db\/study_information.db\">study_information.db<\/a><\/strong><\/p>\n<h4><span class=\"ez-toc-section\" id=\"Andmemudel\"><\/span>Andmemudel<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p><a href=\"https:\/\/blue.pri.ee\/ttu\/wp-content\/uploads\/2023\/04\/14_sql_data_model.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-8605\" src=\"https:\/\/blue.pri.ee\/ttu\/wp-content\/uploads\/2023\/04\/14_sql_data_model.png\" alt=\"\" width=\"897\" height=\"465\" srcset=\"https:\/\/blue.pri.ee\/ttu\/wp-content\/uploads\/2023\/04\/14_sql_data_model.png 897w, https:\/\/blue.pri.ee\/ttu\/wp-content\/uploads\/2023\/04\/14_sql_data_model-300x156.png 300w, https:\/\/blue.pri.ee\/ttu\/wp-content\/uploads\/2023\/04\/14_sql_data_model-768x398.png 768w\" sizes=\"auto, (max-width: 897px) 100vw, 897px\" \/><\/a><\/p>\n<h4><span class=\"ez-toc-section\" id=\"Ulesanne_1_W14-1_Lisa_iseennast_andmebaasi\"><\/span><span style=\"font-size: 20px; font-weight: bold;\">\u00dclesanne 1 [W14-1]: Lisa iseennast andmebaasi<\/span><span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p><strong>NB! \u00dclesanne v\u00f5ib osutuda mitte tehtavaks kui andmebaasi fail asetseb P kettal t\u00e4nu Windows SMBle. Andmebaas j\u00e4\u00e4b lukustatuks ning muudatuste lisamine ei t\u00f6\u00f6ta! Lahenduseks on t\u00f6\u00f6 ajaks andmebaasifail hoiustada\u00a0 v\u00e4ljaspool P ketast, nt t\u00f6\u00f6laual ning hiljem sinna kopeerida.<\/strong><\/p>\n<p>Selle \u00fclesande raames harjutad INSERT p\u00e4ringuid andmete lisamiseks. Baas\u00fclesanne ei ole vajalik realiseerida C koodis. Kirjuta oma p\u00e4ringud ja jooksuta neid kasutades &#8220;Execute SQL&#8221; funktsiooni SQLite browser rakenduses.<\/p>\n<h5><span class=\"ez-toc-section\" id=\"Nouded\"><\/span>N\u00f5uded<span class=\"ez-toc-section-end\"><\/span><\/h5>\n<ul>\n<li>Kirjuta \u00fcles k\u00f5ik SQL p\u00e4ringud, mida sa jooksutad (nt tavalise tekstidokumendina)! N\u00e4ita jooksutatud p\u00e4ringuid ning n\u00e4ita oma andmebaasi p\u00e4rast p\u00e4ringute tegemist. Kokku peab olema 5 p\u00e4ringut!<\/li>\n<li><strong>Tabeli primaarv\u00f5tmeks<\/strong> olevaid ID v\u00e4\u00e4rtusi <span class=\"lang:c highlight:0 decode:true crayon-inline \">(students.id<\/span> , <span class=\"lang:c highlight:0 decode:true crayon-inline\">subjects.id<\/span> , <span class=\"lang:c highlight:0 decode:true crayon-inline\">declarations.id<\/span> ) ei tohi lisamisp\u00e4ringutesse sisse kirjutada! Need teeb andmebaas ise (id atribuut on andmebaasi disainis omadusega <em><strong>auto increment<\/strong><\/em> ning k\u00f5igi kolme tabeli id atribuudid omavad vastavat generaatorit)<\/li>\n<li>Teosta j\u00e4rgmised andmete lisamise p\u00e4ringud\n<ul>\n<li>Lisa andmebaasi enda kui tudengi andmed<\/li>\n<li>Lisa andmebaasi \u00f5ppeaine, mille oled sooritanud<\/li>\n<li>Lisa endale 3 aine deklaratsiooni. \u00dcks nendest peab olema ainele, mille just l\u00f5id, lisaks 2 tk olemasolevatele.<\/li>\n<\/ul>\n<\/li>\n<li>M\u00e4rkus: hinded ja isikukood ei pea olema reaalsed.<\/li>\n<\/ul>\n<p>Kui andmed lisatud, n\u00e4ita enda tehtud p\u00e4ringud ning n\u00e4ita oma andmebaasi SQLite Browser rakenduse kaudu.<\/p>\n<h4><span class=\"ez-toc-section\" id=\"Ulesanne_2_Andmete_parimine\"><\/span>\u00dclesanne 2: Andmete p\u00e4rimine<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>Selle \u00fclesande raames tuleb sul kirjutada p\u00e4ringuid, millega andmebaasis eksisteerivaid andmeid p\u00e4rida. K\u00f5ik p\u00e4ringud peavad olema kirjutatud C-keelse programmi sisse. Sulle on antud 3 koodin\u00e4idet selle lehe alguses materjalide all. Vali, milline neist on sulle k\u00f5ige arusadavam ning kasuta seda mallina enda p\u00e4ringute kirjutamisel.<\/p>\n<p>\u00dclesanne on jaotatud kolmeks eraldi hinnatavaks osaks.<\/p>\n<h5><span class=\"ez-toc-section\" id=\"Nouded-2\"><\/span>N\u00f5uded<span class=\"ez-toc-section-end\"><\/span><\/h5>\n<ul>\n<li>K\u00f5ik p\u00e4ringud vastavas \u00fclesande osas peavad olema sooritatud. Iga osa hinnatakse eraldi.<\/li>\n<li>Andmed peavad olema t\u00e4ielikult ette valmistatud andmebaasihalduri poolel &#8211; st k\u00f5ik arvutused (nt summa, keskmine, max jne) peavad olema kirjutatud SQL p\u00e4ringu sisse.\u00a0<strong>Arvutusi C koodis ei tohi teha!<\/strong><\/li>\n<li>K\u00f5ik olulised andmev\u00e4ljad tuleb v\u00e4ljastada. <strong>\u00c4ra v\u00e4ljasta identifikaatori v\u00e4\u00e4rtusi<\/strong> (nt subjects.id, declarations.id)<\/li>\n<li>P\u00e4ringuid v\u00f5id jooksutada l\u00e4bi men\u00fc\u00fc valikuliselt v\u00f5i \u00fches rakenduses \u00fcksteise j\u00e4rel. Sinu otsustada.<\/li>\n<li>K\u00f5ik ressursid peavad olema programmi l\u00f5puks vabastatud &#8211; kontrolli lekete puudumist valgrindiga.<\/li>\n<\/ul>\n<h5><span class=\"ez-toc-section\" id=\"Ulesanne_2_hinnatav_osa_1_W14-2\"><\/span>\u00dclesanne 2 hinnatav osa 1 [W14-2]<span class=\"ez-toc-section-end\"><\/span><\/h5>\n<p>Esimeses osas teeme lihtsaid SELECT p\u00e4ringuid<\/p>\n<p><strong>P\u00e4ring 1:<\/strong> Leia k\u00f5ik \u00f5ppeained, mis on v\u00e4hem kui 6 EAPd<\/p>\n<p><strong>P\u00e4ring 2: <\/strong>Leia k\u00f5ik \u00f5ppeaineid, milles on eksam. J\u00e4rjesta tulemused EAPde arvu alusel v\u00e4himast suurimani.<\/p>\n<p><strong>N\u00e4idisv\u00e4ljund<\/strong><\/p>\n<pre class=\"theme:cisco-router toolbar:2 nums:false lang:c highlight:0 decode:true \">risto@risto-lt:~\/git_prj\/iax058x-instructor-resources\/pr2\/wk14_sql$ .\/task_p1\r\nT2 P1 Q1: Subjects less than 6 ECTS\r\n\r\n id     code   credits                  name_et                 \r\n5     HHF3080    3       Filosoofia                               \r\n7     HPP0300    3       Insenerieetika                           \r\n8     ITX0130    2       Elements of AI - Tehisintellekti baaskursus \r\n9     ITC8120    3       Kurivara                                 \r\n11    IDU0230    5       Andmebaasid II                           \r\n\r\n\r\nT2 P1 Q2: Subjects having an exam, ordered by ETS\r\n\r\n\r\n id     code                   name_et                 credits      Assessment     \r\n9     ITC8120    Kurivara                                 3        Examination          \r\n11    IDU0230    Andmebaasid II                           5        Examination          \r\n2     IAX0010    Diskreetne matemaatika                   6        Exam                 \r\n3     IAX0043    Arvutid                                  6        Exam                 \r\n4     IAS0090    Algoritmid ja andmestruktuurid           6        Exam                 \r\n6     YMX0231    Matemaatiline anal\u00fc\u00fcs I                  6        Exam                 \r\n10    ITI0206    Andmebaasid I                            6        Examination<\/pre>\n<h5><span class=\"ez-toc-section\" id=\"Ulesanne_2_hinnatav_osa_2_W14-3\"><\/span>\u00dclesanne 2 hinnatav osa 2 [W14-3]<span class=\"ez-toc-section-end\"><\/span><\/h5>\n<p>Teises osas harjutame veidi keerukamaid SELECT p\u00e4ringuid, kus tuleb kasutada JOIN m\u00e4rks\u00f5na \u00fche- v\u00f5i kahekordselt, et liita mitme tabeli vahelisi andmeid kasutades nende PK-FK v\u00f5tmepaare.<\/p>\n<p><strong>P\u00e4ring 1:\u00a0<\/strong>Leia \u00f5ppuri &#8216;Marko&#8217; k\u00f5ik hinded<\/p>\n<p><strong>P\u00e4ring 2:<\/strong> Leia k\u00f5ik \u00f5ppeaineid, mis sa oled sooritanud. N\u00e4ita neid koos hinnetega, alustades k\u00f5rgeimast hindest.<\/p>\n<p><strong>N\u00e4idisv\u00e4ljund<\/strong><\/p>\n<p>NB! \u00dclesande lahendus n\u00f5uab enda \u00fclesandeid. N\u00e4ite huvides on kasutatud \u00fchte olemasolevat tudengit tabelist.<\/p>\n<pre class=\"theme:cisco-router toolbar:2 nums:false lang:c highlight:0 decode:true\">risto@risto-lt:~\/git_prj\/iax058x-instructor-resources\/pr2\/wk14_sql$ .\/task_p2\r\nT2 P2 Q1: All grades of student 'Marko'\r\n\r\n    eid        fname      lname   subject_id  grade  \r\n39012110145  Marko       Mets        2           4        \r\n39012110145  Marko       Mets        5           0        \r\n39012110145  Marko       Mets        6           3        \r\n39012110145  Marko       Mets        7           5        \r\n39012110145  Marko       Mets        9           3        \r\n39111190001  Marko       Mets        3           5        \r\n39111190001  Marko       Mets        4           4        \r\n\r\n\r\nT2 P2 Q2: List your grades (replaced for demonstration purposes with one Marko Mets)\r\n\r\n    eid        fname      lname                  name_et                 grade  \r\n39111190001  Marko       Mets        Arvutid                               5         \r\n39111190001  Marko       Mets        Algoritmid ja andmestruktuurid        4<\/pre>\n<h5><span class=\"ez-toc-section\" id=\"Ulesanne_2_hinnatav_osa_3_W14-4\"><\/span>\u00dclesanne 2 hinnatav osa 3 [W14-4]<span class=\"ez-toc-section-end\"><\/span><\/h5>\n<p>Kolmandas osas harjutame andmete koondamist tunnuste alusel ja tulemuste arvutamist oma SELECT p\u00e4ringutes. Meil on vaja nii JOIN kui GROUP BY m\u00e4rks\u00f5nu ning m\u00f5ningaid arvutamise funktsioone.<\/p>\n<p><strong>P\u00e4ring: <\/strong>Leia iga tudengi poolt teenitud EAPde arv ning kaalutud keskmine hinne.<\/p>\n<p><strong>N\u00e4idisv\u00e4ljund<\/strong><\/p>\n<pre class=\"theme:cisco-router toolbar:2 nums:false lang:c highlight:0 decode:true\">risto@risto-lt:~\/git_prj\/iax058x-instructor-resources\/pr2\/wk14_sql$ .\/task_p3\r\nT2 P3: Total ECTS and weighted average of all students\r\n\r\n    eid        fname      lname     ECTS   Average grade  \r\n30610072188  Andres      Seeder      21       4.14             \r\n39012110145  Marko       Mets        21       3.14             \r\n39111190001  Marko       Mets        12       4.5              \r\n39404101291  Taavi       Kuusk       35       4.49             \r\n49011162694  Sirje       Vakra       28       4.0              \r\n49212043256  Anneli      Ojamaa      36       0.83             \r\n49311121214  Tiina       M\u00e4nd        12       5.0              \r\n55506211816  Marko       Rebane      0        -                \r\n60102039403  Siim        Kivi        30       1.2              \r\n60210031677  Anneli      Oja         17       2.65             \r\n68604152911  Doris       Raud        12       5.0<\/pre>\n<h4><span class=\"ez-toc-section\" id=\"Lisaulesanne_1_W14-5_Enda_andmebaasi_lisamine_usaldusvaarsemalt\"><\/span>Lisa\u00fclesanne 1 [W14-5]: Enda andmebaasi lisamine (usaldusv\u00e4\u00e4rsemalt)<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>Esimese baas\u00fclesande raames ei olnud sul otseselt piiranguid, kuidas ennast andmebaasi lisama peaks. V\u00f5isid vaadata mis ID v\u00e4\u00e4rtused tulid ning neid siis jooksvalt kasutada. Reaalses elus kasutatava andmebaasi puhul p\u00e4ris nii m\u00f5istlik teha ei oleks.<\/p>\n<h5><span class=\"ez-toc-section\" id=\"Taustinfot\"><\/span>Taustinfot<span class=\"ez-toc-section-end\"><\/span><\/h5>\n<p>Paremaks lahenduseks pakun v\u00e4lja 2 v\u00f5imalust. Kummagi puhul pole tegu ideaalse lahendusega, kuid saame sammu v\u00f5rra paremuse poole.<\/p>\n<p><strong>V\u00f5imalus 1a:<\/strong> SQL toetab alamp\u00e4ringuid &#8211; me saame \u00fche p\u00e4ringu sisse peita teise p\u00e4ringu ja selle tulemust siis kasutada esimeses p\u00e4ringus. N\u00e4iteks me saame INSERT p\u00e4ringu sisse kirjutada SELECT p\u00e4ringu.<\/p>\n<p>Oletame, et tahame oma n\u00e4idisandmebaasi lisada isikule Marko Mets veel \u00fchte autot. Selleks saaksime jooksutada j\u00e4rgmise p\u00e4ringu.<\/p>\n<pre class=\"toolbar:2 lang:tsql decode:true\">INSERT INTO cars (owner_eid, car_reg_num, tax)\r\nVALUES (\r\n\t(SELECT eid FROM person WHERE fname = 'Marko' AND lname = 'Mets'),\r\n\t'BOSS3',\r\n\t1000)<\/pre>\n<p>Antud n\u00e4ide pole veatu &#8211; n\u00e4iteks kui meil oleks 2 isikut kelle nimi on Marko Mets, siis l\u00e4heks see katki. Sellest parem versioon on 1b, mille v\u00f5id esitada labori\u00fclesandena.<\/p>\n<p><strong>V\u00f5imalus 1b: <\/strong>Sarnaselt eelmise v\u00f5imalusega saad teha sarnase p\u00e4ringu eesti isikukoodi p\u00f5hjal. Tegu ei ole garanteeritult eksisteeriva v\u00e4\u00e4rtuse ega ei pruugi ka unikaalsust garanteerida kui v\u00e4lisriigi isikukoodid sekka tuua aga on parem kui eelmine.<\/p>\n<p><strong>V\u00f5imalus 2:<\/strong> Parem viis sellele l\u00e4heneda oleks teha mitu p\u00e4ringut. Esiteks lisaksime end tudengina andmebaasi. Seej\u00e4rel p\u00e4riksime andmebaasilt, mis meie ID v\u00e4\u00e4rtuseks sai, kasutades\u00a0 <span class=\"lang:tsql decode:true crayon-inline \">SELECT last_insert_rowid();<\/span>\u00a0 p\u00e4ringut. See annab meile tagasi automaatselt genereeritud ID v\u00e4\u00e4rtuse. Seej\u00e4rel saaksime juba kasutada seda ID v\u00e4\u00e4rtust j\u00e4rgnevate andmete lisamiseks<\/p>\n<p>Ka see pole veatu ega ideaalne. N\u00e4iteks, kui meie kahe p\u00e4ringu vahel lisatakse m\u00f5ne teise klientrakenduse poolt uus \u00f5ppur, siis saaksime tagasi vale koodi (<em>race condition). <\/em>Seda lahendatakse tegelikus maailmas mehhanismiga\u00a0<em>TRANSACTION<\/em>, mis paneb baasi lukku p\u00e4ringute ajaks teistele &#8211; ehk siis k\u00f5ik v\u00f5i mitte midagi meetod. K\u00fcll aga kuna meil on kohalik failip\u00f5hine andmebaas, siis me saame sellest t\u00e4na m\u00f6\u00f6da vaadata.<\/p>\n<h5><span class=\"ez-toc-section\" id=\"Nouded-3\"><\/span>N\u00f5uded<span class=\"ez-toc-section-end\"><\/span><\/h5>\n<ul>\n<li>Kirjuta enda andmebaasi lisamise p\u00e4ringud C keelse rakenduse sisse.<\/li>\n<li>Kui lisamise koodi jooksutatakse mitu korda, siis tuleb veenduda, et duplikaate ei tekiks (kontrolli kasutades m\u00f5nda teadatuntud unikaalset v\u00e4\u00e4rtust &#8211; nt kas sinu eID juba eksisteerib).<\/li>\n<li>Pead kasutama automaatselt genereeritud ID v\u00e4\u00e4rtusi. \u00dchtegi ID v\u00e4\u00e4rtust ei tohi koodi j\u00e4igalt sisse kodeerida.\n<ul>\n<li>Kasuta \u00fchte eelnevalt v\u00e4lja pakutud meetoditest.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h4><span class=\"ez-toc-section\" id=\"Lisaulesanne_2_W14-6_Olemasolevate_andmete_muutmine\"><\/span>Lisa\u00fclesanne 2 [W14-6] : Olemasolevate andmete muutmine<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>Andmete muutmiseks andmebaasis saame kasutada UPDATE p\u00e4ringuid. Selliste p\u00e4ringute jaoks peame esmalt tuvastama, millist rida me uuendada soovime ja seej\u00e4rel saame kirjutada sisse uuendatud v\u00e4\u00e4rtused. Oluline on, et tuvastaksime ainult selle v\u00f5i need read, mida me peame uuendama. \u00dclesande raames pead genereerima k\u00f5igile tudengitele Uni-ID tunnused.<\/p>\n<h5><span class=\"ez-toc-section\" id=\"Nouded-4\"><\/span>N\u00f5uded<span class=\"ez-toc-section-end\"><\/span><\/h5>\n<ul>\n<li>Genereeri k\u00f5igile tudengitele Uni-ID tunnused<\/li>\n<li>Uni-IDd tuleb lisada andmebaasi kasutades UPDATE lauset. T\u00e4ita tuleb uni_id atribuut.<\/li>\n<li>Kood peab t\u00f6\u00f6tama \u00fcksk\u00f5ik kui paljude tudengite puhul<\/li>\n<li>Kui genereeritav Uni-ID juba eksisteerib, pead tegema konfliktilahendust. Selle jaoks peaksid kirjutama vastava koodi C keeles.<\/li>\n<\/ul>\n<h3><span class=\"ez-toc-section\" id=\"Parast_seda_tundi_peaksid\"><\/span>P\u00e4rast seda tundi peaksid<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<ul>\n<li>Teadma, kuidas andmebaas on defineeritud<\/li>\n<li>Teadma p\u00f5hilisi andmemudeleid \u2013 lameandmebaas ja relatsiooniline mudel<\/li>\n<li>M\u00f5istma seoseid tabelite vahel<\/li>\n<li>Teadma andmebaasihalduss\u00fcsteemi (DBMS) eesm\u00e4rki\n<ul>\n<li>m\u00f5istma, mida andmebaasihalduss\u00fcsteemid teevad<\/li>\n<li>m\u00f5istma andmebaasi ja andmebaasihalduss\u00fcsteemi erinevust<\/li>\n<li>Ttadma m\u00f5ningaid laialdaselt kasutatavaid andmebaasihalduss\u00fcsteeme<\/li>\n<\/ul>\n<\/li>\n<li>M\u00f5istma SQL-keele p\u00f5hit\u00f5desid\n<ul>\n<li>deklaratiivne keel<\/li>\n<li>s\u00fcntaks<\/li>\n<\/ul>\n<\/li>\n<li>Oskama kirjutada lihtsaid SQL-p\u00e4ringuid j\u00e4rgmisteks toiminguteks:\n<ul>\n<li>andmete p\u00e4rimine \u00fchest tabelist<\/li>\n<li>andmete \u00fchendamine ja p\u00e4rimine kahest v\u00f5i enamast tabelist<\/li>\n<li>andmete lisamine tabelisse<\/li>\n<\/ul>\n<\/li>\n<li>Teadma veebip\u00f5histe ja lokaalse andmebaasi eeliseid ja puudusi<\/li>\n<li>Oskama liidestada C programmi SQLite3 andmebaasiga kasutades libsql3 teeki<\/li>\n<li>Teadma, et veebip\u00f5histe SQL-andmebaasidega on v\u00f5imalik liidestuda vastava andmebaasit\u00fc\u00fcbi teekide abil<\/li>\n<\/ul>\n<h3><span class=\"ez-toc-section\" id=\"Taiendav_materjal\"><\/span>T\u00e4iendav materjal<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<ul>\n<li>SQL reference<br \/>\n<strong><a href=\"https:\/\/www.w3schools.com\/sql\/\">https:\/\/www.w3schools.com\/sql\/<\/a><\/strong><\/li>\n<li>SQL tutorial<br \/>\n<strong><a href=\"https:\/\/www.tutorialspoint.com\/sql\/index.htm\">https:\/\/www.tutorialspoint.com\/sql\/index.htm<\/a><\/strong><\/li>\n<li>SQLite documentation<br \/>\n<strong><a href=\"https:\/\/www.sqlite.org\/docs.html\">https:\/\/www.sqlite.org\/docs.html<\/a><\/strong><\/li>\n<li>SQLite C library documentation<br \/>\n<strong><a href=\"https:\/\/www.sqlite.org\/c3ref\/intro.html\">https:\/\/www.sqlite.org\/c3ref\/intro.html<\/a><\/strong><\/li>\n<li>SQLite C tutorial<br \/>\n<strong><a href=\"https:\/\/zetcode.com\/db\/sqlitec\/\">https:\/\/zetcode.com\/db\/sqlitec\/<\/a><\/strong><\/li>\n<li>SQLite in C\/C++<br \/>\n<strong><a href=\"https:\/\/www.tutorialspoint.com\/sqlite\/sqlite_c_cpp.htm\">https:\/\/www.tutorialspoint.com\/sqlite\/sqlite_c_cpp.htm<\/a><\/strong><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Praktikumi materjal Slaidid: SQL N\u00e4idisandmebaas (slaididel olevad p\u00e4ringud): cars.db Andmebaasiga liidestamise n\u00e4ited: [sql1.c]\u00a0\u00a0 [sql2.c]\u00a0 [sql3.c] Praktikumi \u00fclesanded Selle praktikumi k\u00e4igus liidestame me\u00a0 C-keelse programmi SQLite3 andmebaasiga kasutades libsqlite3-dev\u00a0 teeki. Esimese \u00fclesande k\u00e4igus alustad andmete lisamisest olemasolevasse andmebaasi, millele j\u00e4rgneb andmete p\u00e4rimine teise \u00fclesande raames. Praktikumil on kaks lisa\u00fclesannet, millest esimeses tuleb muuta andmete lisamist t\u00f6\u00f6kindlamaks. &hellip; <a href=\"https:\/\/blue.pri.ee\/ttu\/laborid\/pr2et14-sql\/\" class=\"more-link\">Loe edasi <span class=\"screen-reader-text\">PR2ET14: SQL<\/span> <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[97,176],"tags":[],"class_list":["post-8621","post","type-post","status-publish","format-standard","hentry","category-laborid","category-pr2-et"],"_links":{"self":[{"href":"https:\/\/blue.pri.ee\/ttu\/wp-json\/wp\/v2\/posts\/8621","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blue.pri.ee\/ttu\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blue.pri.ee\/ttu\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blue.pri.ee\/ttu\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blue.pri.ee\/ttu\/wp-json\/wp\/v2\/comments?post=8621"}],"version-history":[{"count":16,"href":"https:\/\/blue.pri.ee\/ttu\/wp-json\/wp\/v2\/posts\/8621\/revisions"}],"predecessor-version":[{"id":11425,"href":"https:\/\/blue.pri.ee\/ttu\/wp-json\/wp\/v2\/posts\/8621\/revisions\/11425"}],"wp:attachment":[{"href":"https:\/\/blue.pri.ee\/ttu\/wp-json\/wp\/v2\/media?parent=8621"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blue.pri.ee\/ttu\/wp-json\/wp\/v2\/categories?post=8621"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blue.pri.ee\/ttu\/wp-json\/wp\/v2\/tags?post=8621"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}