{"id":958,"date":"2023-06-08T12:17:34","date_gmt":"2023-06-08T12:17:34","guid":{"rendered":"https:\/\/tarkvara.thkit.ee\/veeb\/?page_id=958"},"modified":"2023-06-08T12:20:00","modified_gmt":"2023-06-08T12:20:00","slug":"alamparingud","status":"publish","type":"page","link":"https:\/\/tarkvara.thkit.ee\/veeb\/alamparingud\/","title":{"rendered":"Alamp\u00e4ringud"},"content":{"rendered":"\n<p>Kui \u00fcksik p\u00e4ring kipub liialt keerukaks minema v\u00f5i ei paista m\u00f5nd tulemust olema lootustki tavalise p\u00e4ringuga v\u00e4lja arvutada, siis v\u00f5ib aidata alamp\u00e4ring. Nii nagu avaldiste kirjutamisel v\u00f5ib igasugu v\u00e4\u00e4rtused asendada funktsioonidega, nii saab SQL-p\u00e4ringute puhul olemasolevad kohad asendada alamp\u00e4ringutega. Kusjuures tasub eristada kolme v\u00f5imalust.<\/p>\n\n\n\n<p>\u00dchel juhul antakse alamp\u00e4ringu vastuseks terve tabel (nt. SELECT * FROM lapsed). Sel juhul saab alamp\u00e4ringu panna kohale, kus muidu oli tabeli nimi.<\/p>\n\n\n\n<p>Teisel juhul v\u00e4ljastab alamp\u00e4ring \u00fche veerut\u00e4ie andmeid. Sel juhul v\u00f5ib kontrollida, kas uuritav rida vastab v\u00e4hemasti \u00fchele p\u00e4ringus v\u00e4ljastatud v\u00e4\u00e4rtustest. Kontrollimiseks k\u00e4sklus IN, millest edaspidi.<\/p>\n\n\n\n<p>Kolmas ja loodetavasti k\u00f5ige lihtsam v\u00f5imalus on, kus alamp\u00e4ring v\u00e4ljastab vaid \u00fche arvu. Sel juhul saab p\u00e4ringu panna selle v\u00e4\u00e4rtuse kohale. Alamp\u00e4ring kirjutatakse alati sulgudesse.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><a><\/a><a><\/a> Tabeli asendaja<\/h4>\n\n\n\n<p>Siin tehti v\u00f5imalikult lihtne n\u00e4ide, kus p\u00e4ringu tulemusena loodud tabelist k\u00fcsitakse eraldi v\u00e4\u00e4rtused v\u00e4lja. SELECT * FROM lapsed annab tulpadeks id, eesnimi, pikkus ja synniaasta. T\u00e4iend &#8220;as tabel1&#8221; \u00fctleb, et selle alamp\u00e4ringu tulemust saab edaspidises p\u00e4ringus kasutada nime all tabel1. Ning praegu lihtsalt k\u00fcsitaksegi sealt soovitud tulbad v\u00e4lja.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSELECT tabel1.eesnimi, tabel1.pikkus \nFROM (SELECT * FROM lapsed) as tabel1\n<\/pre><\/div>\n\n\n<p>Selline vahetabelist edasi k\u00fcsimine v\u00f5ib aga toimuda ka tunduvalt keerulisemate p\u00e4ringute puhul, kus \u00fche p\u00e4ringu tulemusena saadakse tabelikujuline vastus kokku ning seda asutakse j\u00e4rgmise p\u00e4ringuga edasi t\u00f6\u00f6tlema.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"> V\u00e4\u00e4rtuse asendaja<\/h4>\n\n\n\n<p>Agregaatfunktsiooni v\u00f5i ka \u00fche konkreetse rea lahtri k\u00fcsimise peale saab SQL-p\u00e4ringu panna v\u00e4ljastama vaid \u00fcht v\u00e4\u00e4rtust. Seda \u00fcksikut v\u00e4\u00e4rtust v\u00f5ib taas edaspidises p\u00e4ringus kasutada. Siin leitakse k\u00f5igepealt alamp\u00e4ringuga laste keskmine pikkus. Edasi v\u00e4ljastatakse k\u00f5ikide laste andmed, kelle pikkus \u00fcletab keskmist.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSELECT nimi, pikkus FROM laps_tbl\nWHERE pikkus&gt;(SELECT AVG(pikkus) FROM laps_tbl)\n<\/pre><\/div>\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/06\/image-31.png\" alt=\"\" class=\"wp-image-963\" width=\"413\" height=\"310\" srcset=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/06\/image-31.png 413w, https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/06\/image-31-300x225.png 300w\" sizes=\"auto, (max-width: 413px) 100vw, 413px\" \/><\/figure>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nKontrolliks saab alamp\u00e4ringu v\u00e4\u00e4rtust ka eraldi vaadata. N\u00e4eme, et laste keskmine pikkus tabelis on 159 sentimeetrit.\n<\/pre><\/div>\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSELECT AVG(pikkus) FROM laps_tbl\n<\/pre><\/div>\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"329\" height=\"273\" src=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/06\/image-32.png\" alt=\"\" class=\"wp-image-967\" srcset=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/06\/image-32.png 329w, https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/06\/image-32-300x249.png 300w\" sizes=\"auto, (max-width: 329px) 100vw, 329px\" \/><\/figure>\n\n\n\n<p>V\u00e4ljaarvutatud v\u00e4\u00e4rtust v\u00f5ib ka avaldise sees tarvitada. Siin leitakse iga lapse pikkuse erinevus keskmisest pikkusest.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSELECT nimi, pikkus, \n       pikkus - (SELECT AVG(pikkus) FROM laps_tbl) as erinevus\nFROM laps_tbl\n<\/pre><\/div>\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"515\" height=\"375\" src=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/06\/image-33.png\" alt=\"\" class=\"wp-image-970\" srcset=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/06\/image-33.png 515w, https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/06\/image-33-300x218.png 300w\" sizes=\"auto, (max-width: 515px) 100vw, 515px\" \/><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>Kui \u00fcksik p\u00e4ring kipub liialt keerukaks minema v\u00f5i ei paista m\u00f5nd tulemust olema lootustki tavalise p\u00e4ringuga v\u00e4lja arvutada, siis v\u00f5ib aidata alamp\u00e4ring. Nii nagu avaldiste kirjutamisel v\u00f5ib igasugu v\u00e4\u00e4rtused asendada funktsioonidega, nii saab SQL-p\u00e4ringute puhul olemasolevad kohad asendada alamp\u00e4ringutega. Kusjuures tasub eristada kolme v\u00f5imalust. \u00dchel juhul antakse alamp\u00e4ringu vastuseks terve tabel (nt. SELECT * FROM &hellip; <a href=\"https:\/\/tarkvara.thkit.ee\/veeb\/alamparingud\/\" class=\"more-link\">Loe edasi <span class=\"screen-reader-text\">Alamp\u00e4ringud<\/span> <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-958","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/pages\/958","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/comments?post=958"}],"version-history":[{"count":10,"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/pages\/958\/revisions"}],"predecessor-version":[{"id":971,"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/pages\/958\/revisions\/971"}],"wp:attachment":[{"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/media?parent=958"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}