Osnove podatkovnih baz

« nazaj

Osnove podatkovnih baz - vaje 11.3.2021

---

Pogledi (VIEW), gnezdene poizvedbe (subSELECTs), 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
• gnezdene poizvedbe (subqueries)
• UNION [ALL], INTERSECT [ALL], EXCEPT [ALL]
• EXISTS, ANY, ALL
• WITH

---

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 kaksen 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 SELECTi.

   CREATE VIEW traktor_premozenje AS
   SELECT lastnik, count(*) * 10000 AS vrednost
   FROM traktor
   GROUP BY lastnik;
      
   CREATE VIEW deli_premozenje AS
   SELECT lastnik, sum(stevilo) * 100 AS vrednost
   FROM deli
   GROUP BY lastnik;
      
   SELECT lastnik, sum(vrednost) AS premozenje
   FROM (
     SELECT * FROM traktor_premozenje
     UNION ALL
     SELECT * FROM deli_premozenje
   ) AS premozenje
   GROUP BY lastnik;
      
   SELECT lastnik, coalesce(traktor_premozenje.vrednost, 0) + coalesce(deli_premozenje.vrednost, 0) AS premozenje
   FROM traktor_premozenje
   FULL JOIN deli_premozenje
   USING (lastnik);
      
   SELECT lastnik, sum(vrednost) AS premozenje
   FROM (
     SELECT lastnik, 10000 AS vrednost FROM traktor
     UNION ALL
     SELECT lastnik, stevilo * 100 AS vrednost FROM deli
   ) AS premozenje
   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 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 oseba.* FROM oseba
   JOIN traktor ON oseba.id = lastnik
   GROUP BY oseba.id
   HAVING count(DISTINCT znamka) >= ALL (
     SELECT count(DISTINCT znamka) FROM traktor
     GROUP BY lastnik
   );
   

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 id IN (
     SELECT stars FROM otroci
   ) AND EXISTS (
     SELECT * FROM znamka
     WHERE NOT EXISTS (
       SELECT otrok FROM otroci
       WHERE stars = oseba.id
       EXCEPT
       SELECT lastnik FROM traktor
       WHERE znamka = znamka.id
     )
   );
   

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 id, id AS clan FROM oseba
     UNION
     SELECT otrok AS id, stars AS clan FROM otroci
   )
   SELECT clan, sum(stevilo/velikost::real) AS razpolaga
   FROM druzine
   JOIN (
     SELECT id, count(*) AS velikost
     FROM druzine
     GROUP BY id
   ) AS velikosti USING (id)
   JOIN deli ON lastnik = id
   GROUP BY clan;
   

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;
   

Ta stran je odprtokodna. Izboljšaj to stran.

Ta zapisek je dostopen tudi na HackMD.