---
tags: vaje, opb, join, group, having
hackmd: https://hackmd.io/N-QMh3j1Tv-zLWfI8iB7JQ
---
# Osnove podatkovnih baz - vaje 4.3.2021

---

## `JOIN`, `GROUP BY`, `HAVING`

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

---

### `JOIN`

1. Izpiši imena oseb brez traktorjev.

   SELECT ime FROM oseba
   LEFT JOIN traktor ON lastnik = oseba.id
   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 = otroci.stars
   JOIN oseba AS otrok ON otrok.id = otroci.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 = otroci.stars
   JOIN oseba AS otrok ON otrok.id = otroci.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 vnuk.ime AS vnuk, stari_stars.ime AS stari_stars
   FROM oseba AS stari_stars
   JOIN otroci AS o1 ON o1.stars = stari_stars.id
   JOIN otroci AS o2 ON o1.otrok = o2.stars
   RIGHT JOIN oseba AS vnuk ON o2.otrok = vnuk.id;

---

### `GROUP BY`, `HAVING`

**Še o funkciji `count()`:**

Funkcija `count()` zna šteti na [tri različne načine](http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES):

* `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(*) AS stevilo_otrok 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) AS stevilo_traktorjev
   FROM znamka LEFT JOIN traktor ON znamka = znamka.id
   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`](http://www.postgresql.org/docs/current/static/functions-conditional.html), ki morebitno vrednost `NULL` zamenja s podano konstanto (recimo 0).

   SELECT oseba.id, coalesce(sum(stevilo), 0) AS stevilo_delov
   FROM oseba LEFT JOIN deli ON lastnik = oseba.id
   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)/96) 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) AS stevilo
   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](http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT)). 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(*) > 1;