owned this note
owned this note
Published
Linked with GitHub
---
tags: vaje, opb, pogledi, podpoizvedbe
hackmd: https://hackmd.io/ZTzx2iLBQ9ymFCN0F0w5mg
---
# Osnove podatkovnih baz - vaje 12.3.2020
---
## Pogledi (`VIEW`)
### Sintaksa
```sql
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.
```sql
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.
```sql
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.
```sql
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 `SELECT`i.
```sql
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:
```sql
SELECT oseba.* FROM oseba
JOIN otroci ON oseba.id = stars
JOIN traktor ON lastnik = otrok AND barva = 'rdeca';
```
* osebe, ki nimajo:
```sql
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:
```sql
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.
```sql
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
```sql
WITH zacasna_tabela AS (
SELECT ...
)
SELECT ...
```
---
### Naloga 4
Izpiši ime osebe, ki ima v lasti največ različnih znamk traktorjev.
```sql
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
```sql
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.
----
```sql
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:
```sql
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:
```sql
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
```sql
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:
```sql
SELECT lastnik FROM traktor AS t1
JOIN traktor AS t2 USING (lastnik);
```
----
* celotna poizvedba:
```sql
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
Sign in with Wallet
