changed 5 years ago  
Linked with GitHub jaanos/OPB/zapiski/2020/2020-03-12.md

Osnove podatkovnih baz - vaje 12.3.2020

Pogledi (VIEW)

Sintaksa

CREATE VIEW pogled AS
SELECT ...

Naloga 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.
CREATE VIEW traktor_premozenje AS
SELECT traktor.lastnik,
    count(*) * 10000 AS vrednost
   FROM traktor
  GROUP BY traktor.lastnik;
  • Sestavi pogled deli_premozenje(lastnik, vrednost), ki poda podobno informacijo za rezervne dele.
CREATE VIEW deli_premozenja AS
SELECT lastnik, count(*) * 10000 AS vrednosti 
FROM traktor
GROUP BY lastnik;
  • Z uporabo zgornjih dveh pogledov sestavi poizvedbo, ki za vsako osebo izpiše, koliko ima premoženja.
SELECT id, ime, coalesce(traktor_premozenje.vrednost, 0) + coalesce(deli_premozenje.vrednost, 0) AS premozenje FROM oseba
LEFT JOIN traktor_premozenje ON id = lastnik
FULL JOIN deli_premozenje USING (lastnik);


SELECT id, ime, coalesce(sum(vrednost),0) AS premozenje
FROM (
SELECT * FROM traktor_premozenje
UNION ALL
SELECT * FROM deli_premozenje
) AS t
RIGHT JOIN oseba ON id = lastnik
GROUP BY id, ime;
  • Sestavi isto poizvedbo še brez uporabe pogledov, z gnezdenimi SELECTi.
SELECT id, ime, coalesce(sum(vrednost), 0) AS premozenje
FROM (
  SELECT lastnik, count(*) * 10000 AS vrednost
  FROM traktor
  GROUP BY lastnik
  UNION ALL
  SELECT lastnik, sum(stevilo) * 100 AS vrednost
  FROM deli
  GROUP BY lastnik
) AS t
RIGHT JOIN oseba ON id = lastnik
GROUP BY id, ime;

Naloga 2

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

  • osebe, ki imajo otroka z rdečim traktorjem:
SELECT oseba.* FROM oseba
JOIN otroci ON oseba.id = stars
JOIN traktor ON lastnik = otrok AND barva = 'rdeca';
  • osebe, ki nimajo:
SELECT oseba.* FROM traktor
JOIN otroci ON lastnik = otrok AND barva = 'rdeca'
RIGHT JOIN oseba ON oseba.id = stars
WHERE traktor.id IS NULL;
  • boljši način, s podpoizvedbo:
SELECT * FROM oseba
WHERE NOT EXISTS (
    SELECT * FROM otroci
    JOIN traktor ON lastnik = otrok
    WHERE stars = oseba.id
    AND barva = 'rdeca'
);

Naloga 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) AS stevilo
FROM deli
WHERE znamka NOT IN (
    SELECT znamka FROM traktor
    WHERE lastnik = deli.lastnik
)
GROUP BY lastnik;

WITH

Sintaksa

WITH zacasna_tabela AS (
  SELECT ...
)
SELECT ...

Naloga 4

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

SELECT lastnik FROM traktor
GROUP BY lastnik
ORDER BY count(DISTINCT znamka) DESC
LIMIT 1;
  • če nas zanimajo vse take osebe, z WITH naredimo začasno tabelo
WITH razlicne_znamke AS (
    SELECT lastnik, count(DISTINCT znamka) AS stevilo
    FROM traktor
    GROUP BY lastnik
)
SELECT id, ime, stevilo FROM oseba
JOIN razlicne_znamke ON lastnik = id
WHERE stevilo >= ALL (
    SELECT stevilo FROM razlicne_znamke
);

Naloga 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 otrok FROM otroci
    WHERE stars = oseba.id
)
AND EXISTS (
    SELECT id FROM znamka
    WHERE id = ALL (
        SELECT znamka FROM otroci
        LEFT JOIN traktor
        ON otrok = lastnik AND znamka = znamka.id
        WHERE stars = oseba.id
    )
);
  • druga možnost:
SELECT * FROM oseba
WHERE EXISTS (
    SELECT otrok FROM otroci
    WHERE stars = oseba.id
)
AND EXISTS (
    SELECT id FROM znamka
    WHERE NOT EXISTS (
        SELECT otrok FROM otroci
        WHERE stars = oseba.id
        EXCEPT
        SELECT lastnik FROM traktor
        WHERE znamka = znamka.id
    )  
);

Naloga 6

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

i. rezervni deli so na razpolago lastniku

ii. staršu so na razpolago vsi rezervni deli, ki jih imajo v lasti njegovi otroci

iii. č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.

  • ideja: vedeti hočemo družine: otroke in starše
  • to konstruiramo:
SELECT oseba.id AS id, oseba.id AS clan
FROM oseba
UNION
SELECT otrok AS id, stars AS clan
FROM otroci;
  • za vsak id dobimo, kdo so njegovi družinski člani
  • iz tega lahko dobimo velikosti družin, zato si to shranimo
WITH druzine AS (
    SELECT oseba.id AS id, oseba.id AS clan
    FROM oseba
    UNION
    SELECT otrok AS id, stars AS clan
    FROM otroci
)
SELECT clan, sum(stevilo/velikost_druzine::real) AS razporozljivost
FROM druzine
JOIN (
    SELECT id, count(*) AS velikost_druzine
    FROM druzine
    GROUP BY id
) AS velikosti_druzin USING (id)
JOIN deli ON lastnik = id
GROUP BY clan;

Naloga 7

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

  • dobimo vse pare traktorjev istega lastnika:
SELECT lastnik FROM traktor AS t1
JOIN traktor AS t2 USING (lastnik);
  • celotna poizvedba:
SELECT DISTINCT lastnik FROM traktor AS t1
JOIN traktor AS t2 USING (lastnik)
WHERE t1.id <> t2.id;
  • to se v praksi ne dela, samo za demonstracijo, kako lahko isto naredimo na različne načine
Select a repo