{"id":11,"date":"2024-04-04T06:16:28","date_gmt":"2024-04-04T03:16:28","guid":{"rendered":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/alamparingud\/"},"modified":"2024-04-04T06:16:44","modified_gmt":"2024-04-04T03:16:44","slug":"alamparingud","status":"publish","type":"page","link":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/alamparingud\/","title":{"rendered":"Alamp\u00e4ringud"},"content":{"rendered":"

\n\t\u00a0\n<\/p>\n

\n\tAlamp\u00e4ringud on p\u00e4ringud, mida saab kirjutada teise p\u00e4ringu sisse, et anda esimesele p\u00e4ringule ette parameetrid. N\u00e4iteks soovime saada isikuid, kelle vanus on \u00fcle keskmise. Selleks on vaja leida eraldi, mis on keskmine vanus ja sorteerida isikute hulgast v\u00e4lja need, kelle vanus on suurem:\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\tSELECT<\/span><\/strong> * FROM<\/span><\/strong> \u201cIsik\u201d WHERE<\/span><\/strong> vanus > (SELECT AVG<\/strong> <\/span>(vanus) FROM<\/span><\/strong> \u201cIsik\u201d);\n<\/p>\n

\n\t\"6.png\"\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\tV\u00f5ime kontrollida, mis oli keskmine vanus.\n<\/p>\n

\n\tSELECT AVG<\/strong> <\/span>(vanus) as<\/span><\/strong> keskmine_vanus FROM<\/span><\/strong> \u201cIsik\u201d);\n<\/p>\n

\n\t\"keskminevanus\"\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\tSaame selle ka \u00fcmardada:\n<\/p>\n

\n\tSELECT <\/strong><\/span>ROUND (AVG<\/strong> <\/span>(vanus)) as<\/span><\/strong> keskmine_vanus FROM<\/span><\/strong> \u201cIsik\u201d);\n<\/p>\n

\n\t\"ymarkeskminevanus\"\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\tSaame kokku lugeda mingile tingimusele vastavate ridade arvu:\n<\/p>\n

\n\tSELECT COUNT <\/strong><\/span>(vanus) FROM<\/span><\/strong> \u201cIsik\u201d WHERE<\/span><\/strong>\u00a0perenimi = \u2018<\/span><\/strong>Luik\u2018<\/span><\/strong> ;\n<\/p>\n

\n\t\"count\"\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\tLihtsamad matemaatilised funktsioonid<\/span><\/b>\n<\/p>\n\n\n\n\n\n\n\n\n\n
\n\t\t\t\tCOUNT\n\t\t\t<\/td>\n\n\t\t\t\tridade arv\n\t\t\t<\/td>\n<\/tr>\n
\n\t\t\t\tMAX\n\t\t\t<\/td>\n\n\t\t\t\tsuurim v\u00e4\u00e4rtus\n\t\t\t<\/td>\n<\/tr>\n
\n\t\t\t\tMIN\n\t\t\t<\/td>\n\n\t\t\t\tv\u00e4ikseim v\u00e4\u00e4rtus\n\t\t\t<\/td>\n<\/tr>\n
\n\t\t\t\tAVG\n\t\t\t<\/td>\n\n\t\t\t\taritmeetiline keskmine\n\t\t\t<\/td>\n<\/tr>\n
\n\t\t\t\tSUM\n\t\t\t<\/td>\n\n\t\t\t\tsumma\n\t\t\t<\/td>\n<\/tr>\n
\n\t\t\t\tROUND\n\t\t\t<\/td>\n\n\t\t\t\t\u00fcmardab l\u00e4hima t\u00e4isarvuni. Nt. round(42.4) annab 42.\n\t\t\t<\/td>\n<\/tr>\n
\n\t\t\t\tROUND (v numeric, s int)\n\t\t\t<\/td>\n\n\t\t\t\t\u00fcmardab s arvu k\u00fcmnendkohani. Nt round(42.4382.2) annab 42.44\n\t\t\t<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\u00a0<\/p>\n

\n\tLihtsamad s\u00f5ne (teksti) funktsioonid <\/span><\/b>\n<\/p>\n\n\n\n\n\n\n\n\n\n
\n\t\t\t\tFunktsioon\n\t\t\t<\/th>\n\n\t\t\t\tTagastatakse\n\t\t\t<\/th>\n\n\t\t\t\tN\u00e4ide\n\t\t\t<\/th>\n\n\t\t\t\tTulemus\n\t\t\t<\/th>\n<\/tr>\n<\/thead>\n
\n\t\t\t\tstring || string\n\t\t\t<\/td>\n\n\t\t\t\t\u00a0\n\t\t\t<\/td>\n\n\t\t\t\t\u2019post\u2019 || \u2019greSQL\u2019\n\t\t\t<\/td>\n\n\t\t\t\tPostgreSQL\n\t\t\t<\/td>\n<\/tr>\n
\n\t\t\t\tstring || non-string or non-string || string\n\t\t\t<\/td>\n\n\t\t\t\t\u00a0\n\t\t\t<\/td>\n\n\t\t\t\t\u2018V\u00e4\u00e4rtus: \u2018 || 42\n\t\t\t<\/td>\n\n\t\t\t\tV\u00e4\u00e4rtus: 42\n\t\t\t<\/td>\n<\/tr>\n
\n\t\t\t\tchar_length (string) v\u00f5i character_length (string) or length (string)\n\t\t\t<\/td>\n\n\t\t\t\tt\u00e4hem\u00e4rkide arv s\u00f5nes\n\t\t\t<\/td>\n\n\t\t\t\tchar_length(\u2019Mart\u2019)\n\t\t\t<\/td>\n\n\t\t\t\t4\n\t\t\t<\/td>\n<\/tr>\n
\n\t\t\t\tlower (string)\n\t\t\t<\/td>\n\n\t\t\t\tkonverteerib v\u00e4iket\u00e4htedeks\n\t\t\t<\/td>\n\n\t\t\t\tlower(\u2019JAAN\u2019)\n\t\t\t<\/td>\n\n\t\t\t\tjaan\n\t\t\t<\/td>\n<\/tr>\n
\n\t\t\t\tupper (string)\n\t\t\t<\/td>\n\n\t\t\t\tkonverteerib suurt\u00e4htedeks\n\t\t\t<\/td>\n\n\t\t\t\tupper(\u2019jaan\u2019)\n\t\t\t<\/td>\n\n\t\t\t\tJAAN\n\t\t\t<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\u00a0<\/p>\n

\n\tV\u00f5rdlusoperaatorid PostgreSQLis<\/b>\n<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n\t\t\t\t=\n\t\t\t<\/td>\n\n\t\t\t\tv\u00f5rdne\n\t\t\t<\/td>\n<\/tr>\n
\n\t\t\t\t<> v\u00f5i !=\n\t\t\t<\/td>\n\n\t\t\t\tmittev\u00f5rdne\n\t\t\t<\/td>\n<\/tr>\n
\n\t\t\t\t>\n\t\t\t<\/td>\n\n\t\t\t\tsuurem kui\n\t\t\t<\/td>\n<\/tr>\n
\n\t\t\t\t<\n\t\t\t<\/td>\n\n\t\t\t\tv\u00e4iksem kui\n\t\t\t<\/td>\n<\/tr>\n
\n\t\t\t\t>=\n\t\t\t<\/td>\n\n\t\t\t\tsuurem v\u00f5i v\u00f5rdne\n\t\t\t<\/td>\n<\/tr>\n
\n\t\t\t\t<=\n\t\t\t<\/td>\n\n\t\t\t\tv\u00e4iksem v\u00f5i v\u00f5rdne\n\t\t\t<\/td>\n<\/tr>\n
\n\t\t\t\tNOT\n\t\t\t<\/td>\n\n\t\t\t\tp\u00f6\u00f6rab tingimuse vastupidiseks\n\t\t\t<\/td>\n<\/tr>\n
\n\t\t\t\tAND\n\t\t\t<\/td>\n\n\t\t\t\tsaab lisada mitu tingimust\n\t\t\t<\/td>\n<\/tr>\n
\n\t\t\t\tOR\n\t\t\t<\/td>\n\n\t\t\t\tsaab lisada mitu tingimust, millest v\u00e4hemalt \u00fchele peab vastama\n\t\t\t<\/td>\n<\/tr>\n
\n\t\t\t\tBETWEEN\n\t\t\t<\/td>\n\n\t\t\t\tvahemikus (min-max)\n\t\t\t<\/td>\n<\/tr>\n
\n\t\t\t\tLIKE\n\t\t\t<\/td>\n\n\t\t\t\tmidagi nagu\n\t\t\t<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\u00a0<\/p>\n

\n\tK\u00f5ige k\u00f5rgem vanus andmebaasis Isik:\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\tSELECT max<\/span><\/strong> (vanus) from<\/span><\/strong> \u201cIsik\u201d;\n<\/p>\n

\n\t\"12.png\"\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\tLeia k\u00f5ik isikud, kelle eesnimi algab A-ga. T\u00f5stutundlik.\n<\/p>\n

\n\tSELECT <\/span>* <\/span><\/strong>from<\/span><\/strong> \u201cIsik\u201d WHERE eesnimi LIKE \u2018<\/span><\/strong> A% \u2018<\/span><\/strong>;\n<\/p>\n

\n\t\"eesnimiAga\"\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\t\u00a0<\/p>\n","protected":false},"excerpt":{"rendered":"

\u00a0 Alamp\u00e4ringud on p\u00e4ringud, mida saab kirjutada teise p\u00e4ringu sisse, et anda esimesele p\u00e4ringule ette parameetrid. N\u00e4iteks soovime saada isikuid, kelle vanus on \u00fcle keskmise. Selleks on vaja leida eraldi, mis on keskmine vanus ja sorteerida isikute hulgast v\u00e4lja need, …<\/p>\n","protected":false},"author":231,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_acf_changed":false,"inline_featured_image":false,"footnotes":""},"class_list":["post-11","page","type-page","status-publish","hentry"],"acf":[],"_links":{"self":[{"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/pages\/11","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/users\/231"}],"replies":[{"embeddable":true,"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/comments?post=11"}],"version-history":[{"count":1,"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/pages\/11\/revisions"}],"predecessor-version":[{"id":140,"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/pages\/11\/revisions\/140"}],"wp:attachment":[{"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/media?parent=11"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}