Osnove podatkovnih baz - vaje 25.2.2021

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

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)
  -- smiseln glavni ključ: (lastnik, znamka, tip)
);

SELECT [DISTINCT] st1, st2, count(*), sum(stolpec)
FROM tabele
WHERE pogoj
GROUP BY st1, st2, ...
ORDER BY st1 [ASC | DESC], st2 [ASC | DESC], ...
LIMIT stevilo [OFFSET stevilo];

Naloga 2: Določili ORDER BY, LIMIT

Pogosto želimo prikazati rezultate poizvedbe v določenem vrstnem redu. V ta namen uporabimo določilo ORDER BY (glej dokumentacijo za ORDER BY za Postgresql in članek ORDER BY (SQL) 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:

1. Izpiši najmlajšega komitenta banke.

   ​​​SELECT oseba.* FROM oseba
   ​​​JOIN racun ON emso = lastnik
   ​​​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

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 za PostgreSQL in članek GROUP BY (SQL) 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.

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 racun, sum(znesek) AS stanje FROM transakcija
   ​​​GROUP BY racun
   ​​​ORDER BY stanje DESC;