Osnove podatkovnih baz

Relacijski model

star več kot 50 let (E. F. Codd, 1970),
uporablja se v večini večjih poslovnih sistemov,
enostaven za razumevanje, pregleden,
omogoča zmogljive poizvedbe v standardiziranem jeziku SQL,
podpira učinkovite implementacije.

Relacijska algebra in SQL

Relacijska algebra je matematični opis operacij nad relacijami (tabelami).
Jezik SQL (Structured Query Language) je implementacija relacijske algebre v obliki poizvedovalnega jezika.
Operatorji so operacije, ki sprejmejo relacije (tabele) in vrnejo (nove) relacije (tabele).
Shema relacije = definicija relacije/tabele (imena + tipi).
- $\operatorname{shema}_R : \operatorname{stolpci}_R \to T$, kjer je $T$ množica tipov
- $R \subseteq \prod_{s \in \operatorname{stolpci}_R} \operatorname{shema}_R(s) = \lbrace r : \operatorname{stolpci}_R \to \bigcup T \mid \forall s \in \operatorname{stolpci}_R: \ r.s \in \operatorname{shema}_R(s) \rbrace$

Operatorji relacijske algebre

Operatorji so odvisni od shem relacij, nad katerimi jih izvajamo.

$\sigma_{\phi}(R) = \lbrace r \in R \mid \phi(r) \rbrace$ - izberi vrstice v relaciji $R$, ki ustrezajo pogoju $\phi$ (selekcija). Pogoj je logični izraz v odvisnosti od vrstice $r$. Shema vrnjene relacije je ista.
$\pi_{s_1, s_2, \ldots, s_n}(R) = \lbrace r \vert_{\lbrace s_1, s_2, \ldots, s_n \rbrace} \mid r \in R \rbrace$ - izberi stolpce z imeni $s_1, s_2, \ldots, s_n$ relacije $R$ in vrni novo tabelo s shemo, ki jo določajo definicije teh stolpcev (projekcija).
$\rho_{a/b}(R) = \lbrace r’: s \in \operatorname{stolpci}_R \oplus \lbrace a, b \rbrace \mapsto r.(s[b \mapsto a]) \mid r \in R \rbrace$ - spremeni ime stolpcu $a$ v $b$ (preimenovanje). Vrni enako tabelo (glede vrstic), le z drugo shemo.
$R_1 \cup R_2$, $R_1 \cap R_2$, $R_1 \setminus R_2$ - unija/presek/razlika vrstic, če imata relaciji $R_1$ in $R_2$ enaki shemi.
$R_1 \times R_2$ - kartezični produkt relacij (vsaka vrstica $R_1$ z vsako vrstico $R_2$). Shema rezultata je združitev shem obeh relacij.

</span>

`JOIN`

$R_1 \bowtie R_2 = \pi_{\operatorname{stolpci}!\normalsize_{R_1} \cup\, \operatorname{stolpci}!\normalsize_{R_2}}(\sigma_{\forall s \in \operatorname{stolpci}!\normalsize_{R_1} \cap\, \operatorname{stolpci}!\normalsize_{R_2}:\ R_1.s \, = \, R_2.s}(R_1 \times R_2))$

Vir: Wikipedia.

SQL

Structured Query Language.
Primeri iz tabel na SQLZOO:
Ogledali si bomo stavke INSERT, UPDATE, DELETE, SELECT.

`INSERT` - vstavljanje vrstic

INSERT - stavek za vstavljanje vrstic.
```
INSERT INTO ime_tabele
     VALUES (vrednost1, vrednost2, ..., vrednostn);
```
- Naštejemo vse vrednosti za vse stolpce, tudi če so NULL ali avtomatično generirane.
- Poznati moramo vrstni red stolpcev v shemi.

Naštejemo lahko le vrednosti za izbrane stolpce.

INSERT INTO ime_tabele (stolpec1, st2, ..., stn)
     VALUES (vrednost1, vrednost2, ... , vrednostn);

Ostali se nastavijo na privzeto vrednost (ali NULL, če ni določena).

`INSERT`

Za VALUES lahko naštejemo več vektorjev vrednosti (vrstic) in jih ločimo z vejico.
Vstavljamo lahko tudi rezultat stavka SELECT, če se ujema s shemo tabele.
```
INSERT INTO ime_tabele
     SELECT ...;
```

`UPDATE` - spreminjanje vrstic

Spreminjanje vrednosti v tabeli v vrsticah, ki zadoščajo pogojem ter stolpcih v teh vrsticah, ki jih želimo spremeniti.
```
UPDATE ime_tabele SET st1 = v1, st2 = v2, ...
 WHERE pogoj;
```
Če izpustimo WHERE, bomo spreminjali vse vrstice v tabeli!

`DELETE` - brisanje vrstic

Brisanje vrstic, ki zadoščajo pogoju.
```
DELETE FROM ime_tabele
 WHERE pogoj;
```
Če izpustimo WHERE, bomo izbrisali vse vrstice v tabeli!

`SELECT` - projekcija

Izberi stolpca population in name iz tabele world:
```
SELECT population, name FROM world;
```

Izberi stolpca name in population ter ju preimenuj v drzava in st_prebivalcev.

SELECT name AS drzava, population AS st_prebivalcev
  FROM world;

Izberi stolpec name in ga preimenuj v Ime države.
```
SELECT name AS "Ime države" FROM world;
```
- To v splošnem ni dobra praksa (šumniki v imenih, zgodovinsko, …)

`SELECT`

Katera različna področja nastopajo v tabeli nobel?
```
SELECT DISTINCT subject FROM nobel;
```
Katere različne celine nastopajo v tabeli world?
```
SELECT DISTINCT continent FROM world;
```

`SELECT ... WHERE` - selekcija

Vse vrstice v tabeli world, ki pripadajo državam v Evropi.
```
SELECT * FROM world WHERE continent = 'Europe';
```
V pogoju lahko uporabljamo sledeče operatorje:
- =, <>, !=, <, <=, >, >=
- IS NULL, IS NOT NULL
- e BETWEEN a AND b
- e NOT BETWEEN a AND b
- e IN (v1, v2, ...)
- e NOT IN (v1, v2, ...)
Pogoje lahko sestavljamo z logičnimi vezniki : NOT, AND, OR, XOR

Izrazi v `SELECT`

Za vsako državo v tabeli world izračunaj razmerje med prebivalstvom in površino.

SELECT name, population / area
  FROM world
 WHERE continent = 'Europe'
   AND area > 0; -- da se izognemo deljenju z 0

Še z ustreznim preimenovanjem.

SELECT name AS ime_drzave,
       population / area AS gostota_prebivalstva
  FROM world
 WHERE continent = 'Europe'
   AND area > 0;

Izrazi v `SELECT`

Dodajmo še pogoj, da je število prebivalcev večje od 2 milijonov.

SELECT name AS "ime države",
       ROUND(population / area, 2)
       AS "gostota prebivalstva"
  FROM world
 WHERE continent = 'Europe' AND population > 2000000;

Funkcije, ki jih lahko uporabljamo.

SELECT name, ROUND(population/1000000, 2) AS milijoni
  FROM world
 WHERE continent IN ('Asia', 'Europe')
   AND name LIKE 'C%';

`ORDER BY` - urejanje podatkov

Urejanje po stolpcu population v tabeli world:

SELECT name, population
  FROM world
 ORDER BY population;

Urejanje po 2. stolpcu:

SELECT name, population
  FROM world
 ORDER BY 2;

V padajočem vrstnem redu:

SELECT name, population
  FROM world
 ORDER BY population DESC;

`ORDER BY` - urejanje podatkov

Urejanje po izračunanem stolpcu:

SELECT name
  FROM world
 WHERE continent = 'Europe'
   AND area > 0
 ORDER BY population/area;

Urejanje po continent in potem še po name:

SELECT continent, name
  FROM world
 ORDER BY continent, name;

Podpoizvedbe

Uporabimo rezultat poizvedbe za izračun pogojev v drugi poizvedbi.

Katere države na svetu imajo manj prebivalcev kot Slovenija? Podatki iz tabele world.

SELECT name FROM world
 WHERE population <= (
         SELECT population FROM world
          WHERE name = 'Slovenia');

Katere države na svetu imajo več ali enako prebivalcev kot Kanada in manj ali enako kot Alžirija?

SELECT name FROM world
 WHERE population BETWEEN (
         SELECT population FROM world
          WHERE name = 'Canada'
       ) AND (
         SELECT population FROM world
          WHERE name = 'Algeria');

</small>

Podpoizvedbe

Podpoizvedbe morajo imeti ustrezno število stolpcev in vrstic glede na uporabljene operatorje.

V katerih letih je bila podeljena Nobelova nagrada za fiziko, ne pa tudi za kemijo? Podatki iz tabele nobel.

SELECT DISTINCT yr
  FROM nobel
 WHERE subject = 'physics'
   AND yr NOT IN (
         SELECT yr FROM nobel
          WHERE subject = 'chemistry'
       );

Združevalne funkcije

Povprečno število prebivalcev na državo v Evropi. Podatki iz tabele world.

SELECT AVG(population)
  FROM world
 WHERE continent = 'Europe';

Največje število prebivalcev v afriški državi.

SELECT MAX(population)
  FROM world
 WHERE continent = 'Africa';

Združevalne funkcije

Najmanjša površina države na svetu.
```
SELECT MIN(area) FROM world;
```

Vsota bruto domačih proizvodov evropskih držav.

SELECT SUM(gdp) FROM world
 WHERE continent = 'Europe';

Funkcije za združevanje ignorirajo vrednosti NULL!

`COUNT`

Koliko je vrstic v tabeli world?
```
SELECT COUNT(*) FROM world;
```
Koliko je vrstic v tabeli world, ki imajo gdp različen od NULL?
```
SELECT COUNT(gdp) FROM world;
```

Koliko je različnih celin?

SELECT COUNT(DISTINCT continent) FROM world;

Kolikokrat se pojavi beseda Asia v celini?

SELECT COUNT(*) FROM world
 WHERE continent LIKE '%Asia%';

Primeri

Kako pa je z uporabo združevalnih funkcij v pogoju? Podatki iz tabele world.

SELECT name, population
  FROM world
 WHERE continent = 'Africa'
   AND population = MAX(population);

Funkcij za združevanje ne moremo uporabljati z WHERE.

SELECT name, population
  FROM world
 WHERE continent = 'Africa'
   AND population = (
         SELECT MAX(population) FROM world
          WHERE continent = 'Africa'
       );

Primeri

Poišči imena tistih držav, ki imajo bruto družbeni proizvod večji od vseh evropskih držav. Podatki iz tabele world.

SELECT name FROM world
 WHERE gdp > (
         SELECT MAX(gdp) FROM world
          WHERE continent = 'Europe'
       );

SELECT name FROM world
 WHERE gdp > ALL (
         SELECT gdp FROM world
          WHERE continent = 'Europe'
            AND gdp IS NOT NULL
       );

Za primerjavo z NULL moramo vedno uporabiti IS NULL ali IS NOT NULL, nikoli = ali <>.

Primeri

V tabeli world poiščimo države z maksimalnim številom prebivalstva v svoji celini.

SELECT continent, name, population FROM world t1
 WHERE population >= ALL (
         SELECT population FROM world t2
          WHERE t1.continent = t2.continent
            AND population > 0
       );

Podpoizvedbo si predstavljamo, kot da je pri filtriranju posamezne vrstice parametrizirana s t1.continent.

`GROUP BY`

GROUP BY razdeli tabelo na skupine, definirane z istimi vrednostmi navedenih stolpcev.
Vsaka skupina vrne kot rezultat eno vrstico. Zato morajo biti morebitni ostali stolpci, navedeni pri SELECT, agregirani s kako od združevalnih funkcij.
```
SELECT continent, MAX(population) FROM world
 GROUP BY continent;
```
Če uporabimo združevalne funkcije, smemo pri SELECT zunaj njih uporabljati samo stolpce, ki so navedeni pri GROUP BY!

`HAVING`

Po posameznih regijah preštej tiste države, kjer je število prebivalcev več kot 200 milijonov. Podatki iz tabele world.
```
SELECT continent, COUNT(*) AS kolikoDrzav FROM world
 WHERE population > 200000000
 GROUP BY continent;
```
- Najprej smo izbrali ustrezne vrstice in jih nato pošteli.
Katere celine imajo vsaj 500 milijonov prebivalcev?

Pozor: WHERE nam tu ne more pomagati!

SELECT continent, SUM(population) FROM world
 GROUP BY continent
HAVING SUM(population) >= 500000000;

`HAVING`

HAVING je dejansko WHERE nad vrsticami, ki predstavljajo skupine, dobljene z združevanjem.

Akumulirani stolpci, uporabljeni v pogoju, niso nujno v rezultatu.

SELECT continent FROM world
 GROUP BY continent
HAVING SUM(population) >= 500000000;

Primeri

Podatki iz tabele nobel.

Izpiši tista leta po letu 1970, ko je Nobelovo nagrado iz fizike (Physics) dobil le en posameznik.

SELECT yr FROM nobel
 WHERE subject = 'Physics' AND yr > 1970
 GROUP BY yr
HAVING COUNT(yr) = 1;

Kateri posamezniki so dobili Nobelovo nagrado na dveh ali več področjih?

SELECT winner FROM nobel
 GROUP BY winner
HAVING COUNT(DISTINCT subject) > 1;

Primeri

Podatki iz tabele nobel.

Prikaži tista leta in področja, kjer so bile v istem letu podeljene 3 nagrade ali več. Upoštevaj le leta po letu 2000.

SELECT yr, subject FROM nobel
 WHERE yr > 2000
 GROUP BY yr, subject
HAVING COUNT(*) >= 3
 ORDER BY yr;

`JOIN`

Podatki iz tabel movie, actor in casting.

V katerih filmih je igral John Wayne?

SELECT title FROM movie
  JOIN casting ON movie.id = movieid
  JOIN actor   ON actorid = actor.id
 WHERE actor.name = 'John Wayne';

Z enim ali več JOIN združimo potrebne tabele in na ta način posredno izvajamo sklicevanje med tabelami.

Kdo je poleg Jamesa Deana še igral v filmu Giant?

SELECT name FROM movie
  JOIN casting ON movie.id = movieid
  JOIN actor   ON actor.id = actorid
 WHERE title = 'Giant' AND name <> 'James Dean';

Primer

Izpiši tiste igralce, ki so bili glavni igralci (ord = 1) v vsaj 10 filmih.

SELECT actor.name FROM actor
  JOIN casting ON actorid = id
 WHERE ord = 1
 GROUP BY actorid
HAVING COUNT(id) >= 10;

Pozor: ali lahko v SELECT izberemo stolpec, ki ne nastopa v GROUP BY?

Glede na to da vemo, da se za nek actorid pojavi lahko samo eno ime actor.name, lahko naredimo takole:

SELECT actor.name FROM actor
  JOIN casting ON actorid = id
 WHERE ord = 1
 GROUP BY actorid, actor.name
HAVING COUNT(id) >= 10;

Primer

Zanima nas še, v koliko filmih so bili ti igralci glavni igralci.

SELECT actor.name, COUNT(actor.name) AS filmi
  FROM actor JOIN casting ON actorid = id
 WHERE ord = 1
 GROUP BY actorid, actor.name
HAVING COUNT(id) >= 10
 ORDER BY filmi DESC;

Primer

Kateri igralci so igrali v več kot enem filmu, ki ima v naslovu love?

SELECT name, COUNT(*) FROM movie
  JOIN casting ON movie.id = movieid
  JOIN actor ON actor.id = actorid
 WHERE title LIKE '%love%'
 GROUP BY name
HAVING COUNT(*) > 1;

Primer

Zanimajo nas naslovi in glavni igralci vseh tistih filmov, kjer je igral Al Pacino in ni bil v glavni vlogi.

SELECT movie.title, actor.name FROM movie
  JOIN casting ON movie.id = movieid
  JOIN actor ON actor.id = actorid
 WHERE casting.ord = 1
   AND movie.id IN (
         SELECT movieid FROM casting
           JOIN actor ON actor.id = actorid
          WHERE actor.name = 'Al Pacino'
       )
   AND actor.name <> 'Al Pacino';

`LEFT JOIN`

Za katere filme nimamo seznama igralcev?

SELECT id, title FROM movie
  LEFT JOIN casting ON movieid = movie.id
 WHERE movieid IS NULL;

Izpišimo druge igralce v filmih Wallace & Gromit.

SELECT title, name FROM movie
  LEFT JOIN casting ON movieid = movie.id AND ord = 2
  LEFT JOIN actor ON actor.id = actorid
 WHERE title LIKE 'Wallace & Gromit: %';

Ta stran je odprtokodna. Izboljšaj to stran.

Osnove podatkovnih baz

Relacijski model

Relacijska algebra in SQL

Operatorji relacijske algebre

JOIN

SQL

INSERT - vstavljanje vrstic

INSERT

UPDATE - spreminjanje vrstic

DELETE - brisanje vrstic

SELECT - projekcija

SELECT

SELECT ... WHERE - selekcija

Izrazi v SELECT

Izrazi v SELECT

ORDER BY - urejanje podatkov

ORDER BY - urejanje podatkov

Podpoizvedbe

Podpoizvedbe

Združevalne funkcije

Združevalne funkcije

COUNT

Primeri

Primeri

Primeri

GROUP BY

HAVING

HAVING

Primeri

Primeri

JOIN

Primer

Primer

Primer

Primer

LEFT JOIN

`JOIN`

`INSERT` - vstavljanje vrstic

`INSERT`

`UPDATE` - spreminjanje vrstic

`DELETE` - brisanje vrstic

`SELECT` - projekcija

`SELECT`

`SELECT ... WHERE` - selekcija

Izrazi v `SELECT`

Izrazi v `SELECT`

`ORDER BY` - urejanje podatkov

`ORDER BY` - urejanje podatkov

`COUNT`

`GROUP BY`

`HAVING`

`HAVING`

`JOIN`

`LEFT JOIN`