---
tags: vaje, opb, create, group, order
hackmd: https://hackmd.io/i2JTLX9bR7GYY5NUZLwPtw
---
# Osnove podatkovnih baz - vaje 24.2.2022

---

## `CREATE TABLE` (ključi, omejitve), `ORDER BY`, `GROUP BY`

CREATE TABLE tabela (
    st1 tip [PRIMARY KEY] [UNIQUE] [NOT NULL] [CHECK (pogoj)] [DEFAULT vrednost] [REFERENCES tabela(stolpec)],
    st2 tip ...,
    ...,
    [PRIMARY KEY (st1, st2, ...),]
    [UNIQUE (st1, st2, ...),]
    [CHECK (pogoj),]
    [FOREIGN KEY (st1, st2, ...) REFERENCES tabela(st1, st2, ...)]
);

---

### Naloga 1: `CREATE TABLE`

Naredi sledeče tabele, v katerih boš hranil podatke o osebah, njihovih traktorjih in rezervnih delih za traktorje:

oseba(id, ime, rojstvo)
otroci(stars, otrok)
znamka(id, ime)
traktor(id, lastnik, znamka, barva, nakup)
deli(lastnik, znamka, tip, stevilo)

* Tabela `oseba` opisuje posamezne osebe.
* Tabela `otroci` vsebuje IDje vseh staršev in njihovih otrok.
* Tabela `znamka` našteva znamke traktorjev. ID znamke naj bo serijska številka, ki se določi avtomatično (pri vpisu vrstice nam je ni treba posebej navajati).
* Tabela `traktor` opisuje posamezne traktorje. Barva traktorja je lahko neznana (`NULL`). Če čas nakupa pri vstavljanju v bazo ni podan, privzemi, da je bil traktor kupljen v trenutku vnosa vrstice v bazo. Uporabi funkcijo `now()`, ki vrne trenutni čas. ID traktorja naj bo serijska številka, ki se določi avtomatično.
* Tabela `deli` opisuje nadomestne dele. Posamezna vrstica opisuje enega ali več (stolpec `stevilo`) nadomestnih delov določenega tipa (npr. volan) za traktor določene znamke. Če število delov pri vnosu v tabelo ni podano, privzemi, da je del en sam.

Preden narediš tabele, si dobro oglej datoteko [`kmetija_insert.sql`](https://ucilnica.fmf.uni-lj.si/pluginfile.php/16308/mod_page/intro/kmetija_insert.sql), s katero boš tabele pozneje napolnil; na podlagi te datoteke tudi določi smiselne podatkovne tipe, primarne in tuje ključe ter omejitve. Tabele naredi tako, da se bodo ob vstavljanju prožile napake natanko na tistih mestih, kjer je to označeno v `kmetija_insert.sql`.

Če se pri ustvarjanju tabel zmotiš, jih lahko bodisi uničiš z ukazom `DROP TABLE` in začneš znova, bodisi poskusiš popraviti z ukazom [`ALTER TABLE`](http://www.postgresql.org/docs/current/static/sql-altertable.html).

CREATE TABLE oseba (
    id INTEGER PRIMARY KEY,
    ime TEXT NOT NULL,
    rojstvo DATE NOT NULL
);

CREATE TABLE otroci (
    stars INTEGER REFERENCES oseba(id),
    otrok INTEGER REFERENCES oseba(id),
    PRIMARY KEY (stars, otrok),
    CHECK (stars <> otrok)
);

CREATE TABLE znamka (
    id SERIAL PRIMARY KEY,
    ime TEXT NOT NULL UNIQUE
);

CREATE TABLE traktor (
    id SERIAL PRIMARY KEY, 
    lastnik INTEGER NOT NULL REFERENCES oseba(id), 
    znamka INTEGER NOT NULL REFERENCES znamka(id) , 
    barva TEXT, 
    nakup DATE NOT NULL DEFAULT now() CHECK (nakup <= now())
);

CREATE TABLE deli (
    lastnik INTEGER NOT NULL REFERENCES oseba(id), 
    znamka INTEGER NOT NULL REFERENCES znamka(id), 
    tip TEXT NOT NULL, 
    stevilo INTEGER NOT NULL DEFAULT 1 CHECK (stevilo > 0)
    -- PRIMARY KEY (lastnik, znamka, tip)
);

---

### Naloga 2: Določili `ORDER BY`, `LIMIT`

SELECT [DISTINCT] st1, st2, ... FROM tabela
WHERE pogoj
ORDER BY st1 [ASC | DESC], st2 [ASC | DESC]
LIMIT stevilo [OFFSET stevilo];

Pogosto želimo prikazati rezultate poizvedbe v določenem vrstnem redu. V ta namen uporabimo določilo `ORDER BY` (glej [dokumentacijo za `ORDER BY`](http://www.postgresql.org/docs/current/interactive/sql-select.html#SQL-ORDERBY) za Postgresql in [članek `ORDER BY` (SQL)](http://wiki.fmf.uni-lj.si/wiki/ORDER_BY_%28SQL%29) iz MaFiRa wiki). V podatkovni zbirki `banka` napravi naslednje poizvedbe:

1. seznam vseh krajev, urejen po imenu kraja (ali baza pravilno uredi šumnike?)

   SELECT kraj FROM kraj
   ORDER BY kraj;

2. seznam vseh oseb, urejen v padajočem vrstnem redu po datumu rojstva

   SELECT * FROM oseba
   ORDER BY rojstvo DESC;

V praksi imamo podatkov dostikrat zelo veliko in zato ni smiselno, da bi izpisovali vse. Takrat uporabimo [`LIMIT`](http://www.postgresql.org/docs/current/static/queries-limit.html):

1. Izpiši najmlajšega komitenta banke.

   SELECT oseba.* FROM oseba
   JOIN racun ON racun.lastnik = oseba.emso
   ORDER BY rojstvo DESC
   LIMIT 1;

2. Izpiši tiste tri transakcije, pri katerih se je obrnilo največ denarja.

   SELECT * FROM transakcija
   ORDER BY abs(znesek) DESC
   LIMIT 3;

### Naloga 3: Določilo `GROUP BY`

SELECT count(*) FROM tabela
WHERE pogoj
GROUP BY st1, st2, ...;

Z določilom `GROUP BY` rezultate poizvedbe združimo v posamezne skupine, vsako skupino pa združimo v eno vrstico z agregacijsko funkcijo, kot je `sum` ali `count` (glej [dokumentacijo za `GROUP BY`](http://www.postgresql.org/docs/current/interactive/sql-select.html#SQL-GROUPBY) za PostgreSQL in [članek `GROUP BY` (SQL)](http://wiki.fmf.uni-lj.si/wiki/GROUP_BY_%28SQL%29) iz MaFiRa wiki). V podatkovni zbirki `banka` napravi naslednje poizvedbe:

1. število oseb, ki živijo v vsakem posameznem kraju. Izpiši dva stolpca: poštna številka in število naročnikov, ki živijo v kraju s to poštno številko.

   SELECT posta, count(*) AS stevilo FROM oseba
   GROUP BY posta;

2. trenutno stanje na računih oseb (brez obresti). Izpiši dva stolpca: številka računa in trenutno stanje.

   SELECT racun, sum(znesek) FROM transakcija
   GROUP BY racun;

3. trenutno stanje na računih oseb (brez obresti). Izpiši štiri stolpce: številka računa, ime, priimek lastnika in trenutno stanje.

   SELECT oseba.ime, oseba.priimek, racun, sum(znesek) AS stanje
   FROM transakcija
   JOIN racun ON racun.stevilka = transakcija.racun
   JOIN oseba ON racun.lastnik = oseba.emso
   GROUP BY oseba.ime, oseba.priimek, racun.lastnik, transakcija.racun;

Določili `ORDER BY` in `GROUP BY` lahko uporabimo hkrati:

1. izpiši trenutno stanje na računih naročnikov (brez obresti). Izpiši štiri stolpce: številka računa, ime, priimek, trenutno stanje. Izpis naj bo urejen po trenutnem stanju v padajočem vrstnem redu.

   SELECT oseba.ime, oseba.priimek, racun, sum(znesek) AS stanje
   FROM transakcija
   JOIN racun ON racun.stevilka = transakcija.racun
   JOIN oseba ON racun.lastnik = oseba.emso
   GROUP BY oseba.ime, oseba.priimek, racun.lastnik, transakcija.racun
   ORDER BY sum(znesek) DESC;