Osnove podatkovnih baz - vaje 11.3.2021

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

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

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

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

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 11.3.2021

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

Naloge

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