Podatkovne baze 1

Podatkovne baze 1 - seminar

`SELECT` - primer

`Ime`	`Priimek`	`Starost`	`Kraj`
Janez	Slovenec	42	Kranj
Ivan	Slovenec	44	Celje
Peter	Slovenec	33	Kranj
Miha	Slovenski	12	Celje

SELECT DISTINCT Kraj FROM t;               -- 2 vrstici
SELECT DISTINCT Priimek, Kraj FROM t;      -- 3 vrstice
SELECT DISTINCT Ime, Priimek, Kraj FROM t; -- 4 vrstice
SELECT Kraj FROM t;                        -- 4 vrstice
SELECT Priimek, Kraj FROM t;               -- 4 vrstice
SELECT Ime, Priimek, Kraj FROM t;          -- 4 vrstice

`SELECT` - dodatki

Vrnjeni stolpci so poljubni izrazi!

SELECT naslov, ROUND(cena * 1.10, 2) FROM knjige 
 WHERE cena * (1 + davek/100.0) > 100.00;

Dodatki
- AS: pre(po)imenujemo izhodni stolpec
- ORDER BY: uredimo vrstice
- DISTINCT: v končnem rezultatu upoštevamo le različne vrstice

Enostavna oblika stavka `SELECT`

SELECT * FROM world;

SELECT name, population FROM world;

SELECT name AS "Ime države" FROM world;

SELECT name AS Ime FROM world
 ORDER BY population DESC;

SELECT DISTINCT continent FROM world;

SELECT 2 FROM world WHERE continent = 'Europe';

SELECT population/area FROM world
 WHERE continent IN ('Europe', 'Asia', 'Africa') AND
       area BETWEEN 100000 AND 1000000;

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

Poizvedbe s podpoizvedbami

Izpiši imena evropskih držav, ki imajo manj prebivalcev kot Slovenija.

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

Ali bomo dobili tudi Slovenijo?

Poizvedbe s podpoizvedbami (2)

Izpiši imena držav, ki imajo število prebivalcev med številom prebivalcev Alžirije in Kanade.

Po korakih:

SELECT population FROM world
 WHERE name = 'Canada';

Dobimo rezultat 35427524.

SELECT population FROM world
 WHERE name = 'Algeria';

Dobimo rezultat 38700000.

SELECT name FROM world
 WHERE population BETWEEN 35427524 AND 38700000;

Poizvedbe s podpoizvedbami (3)

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

`WITH` (2)

Navedemo lahko tudi več podpoizvedb in se nanje sklicujemo kot na tabele:

WITH gospodarji_prstanov AS (
  SELECT * FROM film
   WHERE naslov LIKE 'Gospodar prstanov%'
),
leta_gospodarjev AS (
  SELECT leto FROM gospodarji_prstanov
),
ocene_gospodarjev AS (
  SELECT ocena FROM gospodarji_prstanov
)
SELECT * FROM film
 WHERE leto IN leta_gospodarjev AND 
       ocena IN ocene_gospodarjev;

Velike države

Poišči tiste države (s pripadajočimi celinami), ki imajo več kot 3x toliko prebivalcev, kot jih imajo ostale države na tej celini.
Ideja: poiskati maksimalno število prebivalcev med tistimi državami na isti celini (potrebni bosta dve kopiji tabele), ki nimajo istega imena kot ta država.

SELECT name, continent FROM world AS t1
 WHERE population > (
         SELECT 3 * MAX(population) FROM world AS t2
          WHERE t1.continent = t2.continent AND 
                t1.name <> t2.name
       );

`HAVING`

Tabela: nobel(yr, subject, winner)

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;

`HAVING` (2)

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

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

`winner`	`COUNT(subject)`
Frederick Sanger	2
John Bardeen	2
International Committee of the Red Cross	3
Linus Pauling	2
Marie Curie	2

Je to v redu?

Preverimo

Kako bi dobili še ostale podatke zanje?

SELECT * FROM nobel
 WHERE winner IN (
         SELECT winner FROM nobel
          GROUP BY winner
         HAVING COUNT(subject) > 1
       );

Dejansko smo dobili tiste, ki so dobili Nobelovo nagrado več kot enkrat (ne glede na področje).

Popravimo

Šteti želimo različna področja.

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

`winner`	`podrocja`
Linus Pauling	2
Marie Curie	2

`yr`	`subject`	`winner`
1962	Peace	Linus Pauling
1954	Chemistry	Linus Pauling
1911	Chemistry	Marie Curie
1903	Physics	Marie Curie

Glavni igralci

Zanimajo nas naslovi in glavni igralci vseh tistih filmov, kjer Al Pacino ni bil v glavni vlogi.
Potrebujemo podatke iz vseh treh tabel!
Zanimajo nas le tiste vrstice, kjer imamo glavnega igralca …
- WHERE ord = 1
in to ni Al Pacino!
- AND name <> 'Al Pacino'

Mora pa Al Pacino biti med igralci.

AND movie.id IN (
  -- seznam ID-jev filmov, kjer je igral Al Pacino
)

Seznam filmov z Alom Pacinom

Potrebujemo tabeli casting in actor.
Od filmov potrebujemo samo ID - ta se nahaja v casting!

Zanimajo nas samo vrstice, kjer je igralec Al Pacino.

SELECT movieid FROM casting
  JOIN actor ON actor.id = actorid
 WHERE actor.name = 'Al Pacino';

Rešitev

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

Vrstni red izvajanja

SELECT [DISTINCT]                       -- 8
    stolpci, združevalne_funkcije       -- 7
  FROM leva_tabela                      -- 1
[LEFT] JOIN                             -- 3
       desna_tabela ON pogoji_stikanja  -- 2
 WHERE vsebinski_pogoji                 -- 4
 GROUP BY ključi_skupin                 -- 5
HAVING pogoji_skupin                    -- 6
 ORDER BY ključi_urejanja               -- 9
 LIMIT število;                         -- 10

Primer

Imamo tabeli narocnik in narocilo.

`narocnik_id`	`ime`	`priimek`
1	Damjan	Kmetec
2	Aljaž	Kmetec
3	Špela	Prezelj
4	Metka	Kmetec

`narocilo_id`	`narocnik_id`	`kolicina`
1	1	2
2	1	1
3	3	1
4	4	4

Želimo dobiti število naročil vsakega naročnika s priimkom Kmetec, ki je naročil največ tri kose, urejeno naraščajoče po številu naročil.

`narocnik_id`	`ime`	`priimek`	`stevilo`	`skupna_kolicina`
2	Aljaž	Kmetec	0	0
1	Damjan	Kmetec	2	3

Poizvedba

SELECT narocnik_id, ime, priimek,
         COUNT(narocilo_id) AS stevilo,
         COALESCE(SUM(kolicina), 0) AS skupna_kolicina
  FROM narocnik
  LEFT OUTER JOIN
       narocilo USING (narocnik_id)
 WHERE priimek = 'Kmetec'
 GROUP BY narocnik_id, ime, priimek
HAVING COALESCE(SUM(kolicina), 0) <= 3
 ORDER BY stevilo;

Stikanje

...
FROM narocnik LEFT OUTER JOIN narocilo USING (narocnik_id)
...

`narocnik_id`	`ime`	`priimek`	`narocilo_id`	`kolicina`
1	Damjan	Kmetec	1	2
1	Damjan	Kmetec	2	1
2	Aljaž	Kmetec	`NULL`	`NULL`
3	Špela	Prezelj	3	1
4	Metka	Kmetec	4	4

Filtriranje

...
WHERE priimek = 'Kmetec'
...

`narocnik_id`	`ime`	`priimek`	`narocilo_id`	`kolicina`
1	Damjan	Kmetec	1	2
1	Damjan	Kmetec	2	1
2	Aljaž	Kmetec	`NULL`	`NULL`
4	Metka	Kmetec	4	4

Združevanje

...
GROUP BY narocnik_id, ime, priimek
...

`narocnik_id`	`ime`	`priimek`	`narocilo_id`	`kolicina`
1	Damjan	Kmetec	1	2
			2	1
2	Aljaž	Kmetec	`NULL`	`NULL`
4	Metka	Kmetec	4	4

Filtriranje skupin

...
HAVING COALESCE(SUM(kolicina), 0) <= 3
...

`narocnik_id`	`ime`	`priimek`	`narocilo_id`	`kolicina`	`skupna_kolicina`
1	Damjan	Kmetec	1	2	3
			2	1
2	Aljaž	Kmetec	`NULL`	`NULL`	0
4	~~Metka~~	~~Kmetec~~	4	4	4

Izračun stolpcev

SELECT narocnik_id, ime, priimek,
         COUNT(narocilo_id) AS stevilo,
         COALESCE(SUM(kolicina), 0) AS skupna_kolicina
...

`narocnik_id`	`ime`	`priimek`	`stevilo`	`skupna_kolicina`
1	Damjan	Kmetec	2	3
2	Aljaž	Kmetec	0	0

Urejanje

...
 ORDER BY stevilo;

`narocnik_id`	`ime`	`priimek`	`stevilo`	`skupna_kolicina`
2	Aljaž	Kmetec	0	0
1	Damjan	Kmetec	2	3

Še nekaj primerov

Baza filmov (SQLite)
V katerih žanrih je posnetih največ filmov?
Podatki v tabeli pripada - film ima lahko več žanrov!
Imena žanrov so v tabeli zanr.

SELECT naziv, COUNT(*) AS stevilo
  FROM pripada
  JOIN zanr ON zanr = id
 GROUP BY id, naziv
 ORDER BY stevilo DESC;

Povprečna ocena in dolžina po žanrih

Ocene in dolžine so v tabeli film.

SELECT naziv,
       AVG(ocena) AS povprecna_ocena,
       AVG(dolzina) AS povprecna_dolzina
  FROM film
  JOIN pripada ON film.id = film
  JOIN zanr ON zanr = zanr.id
 GROUP BY zanr.id, naziv
 ORDER BY povprecna_ocena DESC;

Filmi z več kot enim režiserjem

SELECT naslov, COUNT(*) AS st_reziserjev
  FROM vloga
  JOIN film ON film = id
 WHERE tip = 'R'
 GROUP BY naslov
HAVING st_reziserjev > 1
 ORDER BY st_reziserjev DESC;

Zakaj je to narobe?

Pravilno:

SELECT naslov, COUNT(*) AS st_reziserjev
  FROM vloga
  JOIN film ON film = id
WHERE tip = 'R'
GROUP BY id, naslov
HAVING st_reziserjev > 1
ORDER BY st_reziserjev DESC;

Romantične komedije

Zanimajo nas filmi, ki imajo med žanri Comedy in Romance.

Poskusimo:

SELECT film FROM pripada
 WHERE zanr = 4 AND zanr = 8;

Zakaj to ni OK?

Želimo tiste filme, za katere obstaja tako vrstica z zanr = 4 kot tudi z zanr = 8!

Romantične komedije (2)

Dobimo ID-je vseh filmov, za katere se pojavita oba žanra.

SELECT film FROM pripada
  JOIN zanr ON zanr = id
 WHERE naziv IN ('Comedy', 'Romance')
 GROUP BY film
HAVING COUNT(*) = 2;

Lahko bi uporabili kot podpoizvedbo, ali pa staknemo zraven še tabelo filmi.

SELECT naslov FROM film
  JOIN pripada ON film.id = film
  JOIN zanr ON zanr = zanr.id
 WHERE naziv IN ('Comedy', 'Romance')
 GROUP BY film.id, naslov
HAVING COUNT(*) = 2;

Igralec in režiser v istem filmu

SELECT naslov, ime FROM film
  JOIN vloga ON film.id = film
  JOIN oseba ON oseba = oseba.id
 GROUP BY film.id, naslov, oseba.id, ime
HAVING COUNT(*) = 2;

Deluje, ker sta edini vlogi igralec in režiser.
Kaj pa, če bi lahko imeli več vlog, pa nas vseeno zanimata samo ti dve?
Ali pa če se nekdo večkrat pojavi kot igralec (npr. več likov v istem filmu)?

Igralec in režiser v istem filmu (2)

WITH reziser AS (
       SELECT * FROM vloga
        WHERE tip = 'R'
     ),
     igralec AS (
       SELECT * FROM vloga
        WHERE tip = 'I'
     )
SELECT naslov, ime FROM reziser
  JOIN igralec USING (oseba, film)
  JOIN film ON film.id = film
  JOIN oseba ON oseba.id = oseba;

Kaj počne ta poizvedba?

SELECT film.*
  FROM pripada AS prvi
  JOIN pripada AS drugi USING (film)
  JOIN film ON id = film
 WHERE prvi.zanr = 4 AND
       drugi.zanr = 8 AND
       oznaka <> 'R'
 ORDER BY film.glasovi DESC;

Primer ER diagrama - šola

w:1000px

Primer ER diagrama - knjižnica