Skip to content

2022-03-03.md

Latest commit

 

History

History
140 lines (105 loc) · 4.8 KB

2022-03-03.md

File metadata and controls

140 lines (105 loc) · 4.8 KB
tags vaje, opb, join, group, having
hackmd https://hackmd.io/PHND5qqnQtG3CUW9-L5EbA

Osnove podatkovnih baz - vaje 3.3.2022

JOIN, GROUP BY, HAVING

SELECT [DISTINCT] stolpci
FROM tabela1
[LEFT | RIGHT | FULL] JOIN tabela2 ON pogoj
WHERE pogoj
GROUP BY stolpci
HAVING pogoj
ORDER BY stolpci
LIMIT število

JOIN

  1. Izpiši imena oseb brez traktorjev.

    SELECT oseba.* FROM oseba
LEFT JOIN traktor ON oseba.id = lastnik
WHERE traktor.id IS NULL;

  2. Izpiši vse veljavne pare (ime starša, ime otroka).

    SELECT stars.ime AS ime_starsa, otrok.ime AS ime_otroka
FROM oseba AS stars
JOIN otroci ON stars.id = stars
JOIN oseba AS otrok ON otrok.id = otrok;

  3. Izpiši starše, ki so svojim otrokom kupili traktor, še preden so ti dopolnili 10 let.

    Namig: cas1+'10 years'::INTERVAL vrne timestamp, ki opisuje trenutek 10 let po času cas1. Timestampe lahko med seboj primerjaš z operatorji <, >, <=, >=.

    SELECT DISTINCT stars.*
FROM oseba AS stars
JOIN otroci ON stars.id = stars
JOIN oseba AS otrok ON otrok.id = otrok
JOIN traktor ON lastnik = otrok.id
WHERE nakup < otrok.rojstvo + '10 years'::INTERVAL;

  4. Izpiši vse pare (ime osebe, ime starega starša). Za osebe, ki nimajo starih staršev, izpiši par (ime osebe, NULL).

    SELECT otrok.ime AS ime_otroka, staristars.ime AS ime_starega_starsa
FROM oseba AS staristars
JOIN otroci AS o1 ON staristars.id = o1.stars
JOIN otroci AS o2 ON o1.otrok = o2.stars
RIGHT JOIN oseba AS otrok ON o2.otrok = otrok.id;

GROUP BY, HAVING

Še o funkciji count():

Funkcija count() zna šteti na tri različne načine:

  • count(*) prešteje vse vrstice, tudi takšne z vrednostmi NULL
  • count(bla) prešteje vrstice, ki v stolpcu bla nimajo NULL
  • count(DISTINCT bla) prešteje število različnih vrednosti v stolpcu bla, ki so različne od NULL

  1. Za vsako osebo izpiši, koliko otrok ima. Osebe lahko izpišeš kar z IDjem. Oseb, ki nimajo otrok, ne izpisuj.

    SELECT stars, count(*) FROM otroci
GROUP BY stars;

  2. Za vsako znamko traktorjev izpiši število takih traktorjev v bazi. Pazi na znamko Edelstahl. Znamke izpiši z imenom.

    SELECT ime, count(traktor.id) FROM znamka
LEFT JOIN traktor ON znamka.id = znamka
GROUP BY ime;

  3. Za vsako neprevidno osebo izpiši, koliko rezervnih delov ima v lasti. Neprevidna je oseba, ki ima v lasti največ en rezervni del. Ne pozabi na osebe, ki nimajo nobenega rezervnega dela. Uporabi funkcijo coalesce, ki morebitno vrednost NULL zamenja s podano konstanto (recimo 0).

    SELECT oseba.id, oseba.ime, coalesce(sum(stevilo), 0) AS skupno_stevilo
FROM oseba
LEFT JOIN deli ON oseba.id = lastnik
GROUP BY oseba.id
HAVING coalesce(sum(stevilo), 0) <= 1;

  4. Izpiši ime osebe, ki ima največ vozniških izkušenj. Predpostavljamo, da je vsak lastnik vozil vsakega od svojih traktorjev od dneva nakupa do danes povprečno 15 minut na dan. Količine vozniških izkušenj (števila ur) ni treba izpisovati.

    SELECT ime, sum(now() - nakup) / 96 AS izkusnje FROM oseba
JOIN traktor ON lastnik = oseba.id
GROUP BY oseba.id
ORDER BY izkusnje DESC
LIMIT 1;

SELECT ime FROM oseba
JOIN traktor ON lastnik = oseba.id
GROUP BY oseba.id
ORDER BY sum(now() - nakup) DESC
LIMIT 1;

  5. Vaščani se odločijo rezervne dele zložiti v skupni fond, iz katerega bo potem vsak po potrebi jemal, ko se mu pokvari traktor. Za vsak tip rezervnega dela izpiši, koliko traktorjev lahko preskrbijo z delom tega tipa. (Štejemo samo prvo okvaro: če imamo en sam volan za Mercedese in 8 Mercedesov, pa nič drugih traktorjev, lahko z volanom preskrbimo 8 traktorjev, ne enega.)

    Namig: Uporabi count z določilom DISTINCT.

    SELECT tip, count(DISTINCT traktor.id)
FROM deli JOIN traktor USING (znamka)
GROUP BY tip;

  6. V obliki dan. mesec. (npr. 19. 7.) izpiši vse datume, na katere imata rojstni dan vsaj dve osebi. Mesec iz datuma dobiš s funkcijo extract(month FROM datumska_vrednost), podobno tudi za dan (glej funkcije za časovne vrednosti). Spomni se tudi operatorja || za stikanje nizov (t.j., "seštevanje" nizov).

    SELECT extract(day FROM rojstvo) || '. ' || extract(month FROM rojstvo) || '.' AS rojstni_dan
FROM oseba
GROUP BY rojstni_dan
HAVING count(*) >= 2;