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

CREATE VIEW pogled AS
SELECT ...

Naloge

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

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;

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
);

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;

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
);

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
);

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;
```

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;

Syntax	Example	Reference
# Header	Header	基本排版
- Unordered List	Unordered List
1. Ordered List	Ordered List
- [ ] Todo List	Todo List
> Blockquote	Blockquote
Bold font	Bold font
Italics font	Italics font
~~Strikethrough~~	~~Strikethrough~~
19^th^	19^th
H~2~O	H₂O
++Inserted text++	Inserted text
==Marked text==	Marked text
[link text](https:// "title")	Link
![image alt](https:// "title")	Image
`Code`	`Code`	在筆記中貼入程式碼
```javascript var i = 0; ```	`var i = 0;`	在筆記中貼入程式碼
:smile:		Emoji list
{%youtube youtube_id %}	Externals
$L^aT_eX$	L^aT_eX
:::info This is a alert area. :::	This is a alert area.

Osnove podatkovnih baz - vaje 10.3.2022

Pogledi (VIEW), gnezdene poizvedbe (subSELECTs), zahtevnejši SELECT stavki

Naloge

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