---
tags: vaje, opb, view, select, gnezdenje
hackmd: https://hackmd.io/Bpt5AAqLRUOeS2Qou0e27w
---
# Osnove podatkovnih baz - vaje 10.3.2022

---

## Pogledi (`VIEW`), gnezdene poizvedbe (sub`SELECT`s), zahtevnejši `SELECT` stavki

Še naprej delamo na tabelah s traktorji.

Če v nalogi ni čisto točno določeno, kaj je treba izpisati (npr. ime osebe ali ID osebe), v glavnem velja, da se izogni tovrstnim tehnikalijam, reši lažjo varianto in raje porabi čas za še druge naloge.

Naloge poleg določil `JOIN` in `GROUP BY` večinoma zahtevajo še uporabo kakšnega od naslednjih pristopov/ukazov/določil:

* [`CREATE VIEW`](http://www.postgresql.org/docs/current/static/sql-createview.html)
* gnezdene poizvedbe (subqueries)
* [`UNION [ALL]`, `INTERSECT [ALL]`, `EXCEPT [ALL]`](http://www.postgresql.org/docs/current/static/queries-union.html)
* [`EXISTS`, `ANY`, `ALL`](http://www.postgresql.org/docs/current/static/functions-subquery.html)
* [`WITH`](http://www.postgresql.org/docs/current/static/queries-with.html)

---

CREATE VIEW pogled AS
SELECT ...

---

### Naloge

1. Vsak traktor je vreden 10000 dolarjev, vsak rezervni del pa 100.

* Sestavi pogled `traktor_premozenje` s stolpcema `lastnik` in `vrednost`, ki za vsako osebo, ki ima v lasti kakšen traktor, pove, koliko so vredni njegovi traktorji.
* Sestavi pogled `deli_premozenje(lastnik, vrednost)`, ki poda podobno informacijo za rezervne dele.
* Z uporabo zgornjih dveh pogledov sestavi poizvedbo, ki za vsako osebo izpiše, koliko ima premoženja.
* Sestavi isto poizvedbo še brez uporabe pogledov, z gnezdenimi `SELECT`i.

   CREATE VIEW traktor_premozenje AS
   SELECT lastnik, 10000 * count(*) AS vrednost
   FROM traktor
   GROUP BY lastnik;
   
   CREATE VIEW deli_premozenje AS
   SELECT lastnik, 100 * sum(stevilo) AS vrednost
   FROM deli
   GROUP BY lastnik;
   
   SELECT lastnik, coalesce(traktor_premozenje.vrednost, 0) + coalesce(deli_premozenje.vrednost, 0) AS vrednost
   FROM traktor_premozenje FULL JOIN deli_premozenje USING (lastnik);
   
   SELECT lastnik, sum(vrednost) AS vrednost FROM (
       SELECT lastnik, vrednost FROM deli_premozenje
       UNION ALL
       SELECT lastnik, vrednost FROM traktor_premozenje
   ) AS t
   GROUP BY lastnik;
   
   SELECT lastnik, sum(vrednost) AS vrednost FROM (
       SELECT lastnik, 100 * stevilo AS vrednost
       FROM deli
       UNION ALL
       SELECT lastnik, 10000 AS vrednost
       FROM traktor
   ) AS t
   GROUP BY lastnik;

2. Izpiši ljudi, ki nimajo nobenega otroka z rdečim traktorjem.

   SELECT * FROM oseba
   WHERE NOT EXISTS (
       SELECT * FROM otroci
       JOIN traktor ON lastnik = otrok
       WHERE barva = 'rdeca' AND stars = oseba.id
   );

3. Za vsako osebo izpiši, koliko ima takšnih rezervnih delov, ki jih ne more uporabiti na nobenem od svojih traktorjev. Oseb, za katere bi bila ta vrednost 0, ni treba izpisovati.

   SELECT lastnik, sum(stevilo) FROM deli
   WHERE znamka NOT IN (
       SELECT znamka FROM traktor
       WHERE lastnik = deli.lastnik
   )
   GROUP BY lastnik;

4. Izpiši ime osebe, ki ima v lasti največ različnih znamk traktorjev.

   SELECT lastnik FROM traktor
   GROUP BY lastnik
   HAVING count(DISTINCT znamka) >= ALL (
       SELECT count(DISTINCT znamka) FROM traktor
       GROUP BY lastnik
   );
   
   WITH razlicne_znamke AS (
       SELECT lastnik, count(DISTINCT znamka) AS znamke
       FROM traktor
       GROUP BY lastnik
   )
   SELECT lastnik FROM razlicne_znamke
   WHERE znamke >= ALL (
       SELECT znamke FROM razlicne_znamke
   );

5. Izpiši osebe, katerih otroci so vsi lastniki traktorja iste znamke (t.j., obstaja znamka traktorja, ki jo imajo vsi otroci te osebe). Oseb brez otrok ne izpisuj.

   SELECT * FROM oseba
   WHERE EXISTS (
       SELECT * FROM znamka
       WHERE NOT EXISTS (
           SELECT otrok FROM otroci
           WHERE stars = oseba.id
           EXCEPT
           SELECT lastnik FROM traktor
           WHERE znamka = znamka.id
       )
   )
   AND id IN (
       SELECT stars FROM otroci
   );

6. Za vsako osebo izpiši, koliko rezervnih delov ima na razpolago. "Imeti na razpolago" ni isto kot "imeti v lasti"; za potrebe te naloge veljajo naslednja pravila:

* rezervni deli so na razpolago lastniku
* staršu so na razpolago vsi rezervni deli, ki jih imajo v lasti njegovi otroci
* če ima nek del na razpolago *N* ljudi, vsakemu priznavamo le 1/*N* razpoložljivosti. Če ima nekdo npr. v lasti 3 rezervne dele, v bazi pa nastopa tudi njegov oče (ki sam ni lastnik nobenega rezervnega dela), potem naj poizvedba tako za sina kot za očeta izpiše, da imata na razpolago 1.5 dela.

   WITH druzine AS (
       SELECT stars AS id, otrok AS clan FROM otroci
       UNION
       SELECT id, id AS clan FROM oseba
   )
   SELECT id, sum(stevilo / velikost::real) AS razpolozljivost FROM druzine
   JOIN deli ON lastnik = clan
   JOIN (
       SELECT clan, count(*) AS velikost
       FROM druzine
       GROUP BY clan
   ) AS v USING (clan);
   GROUP BY id;

7. Izpiši vse osebe, ki imajo v lasti vsaj dva traktorja. Prepovedana je uporaba `GROUP BY` in funkcije `count`.

   SELECT DISTINCT lastnik FROM traktor AS t1
   JOIN traktor AS t2 USING (lastnik)
   WHERE t1.id <> t2.id;