---
tags: vaje, pb1, sql, spreminjanje, ustvarjanje
hackmd: https://hackmd.io/ylrqJhqlRSmq9yWqjH5haQ
---
# Podatkovne baze 1 - vaje 15.11.2021

---

## Stikanje in `NULL`

SELECT stolpci
FROM tabela1 [LEFT | RIGHT | FULL] JOIN tabela2 ON pogoj
WHERE stolpec IS [NOT] NULL
GROUP BY stolpci
HAVING pogoj
ORDER BY stolpci
LIMIT število;

| Naloga | TA | ŽA | AB | PK | KK | AL | JL | HL | MM | AO | DR | BR | AS | MS | DŠ | LT | TT |
| ------ | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- |
| 1 | x | x | x | x | x | x | x | x |x | | x | x | x | x | x | x | |
| 2 | x | x | x | x | x | x | x | x |x | | x | x | x | x | x | x | |
| 3 | x | x | x | x | x | x | x | x |x | | x | x | x | x | x | x | |
| 4 | x | x | x | x | x | x | x | x |x | | x | x | x | x | x | x | |
| 5 | x | x | x | x | x | x | x | x |x | | x | x | x | x | x | x | |
| 6 | x | x | x | x | x | x | x | x |x | | x | x | x | x | x | x | |
| 7 | x | x | x | x | x | x | x | x |x | | x | x | x | x | x | x | |
| 8 | x | x | x | x | x | x | x | x |x | | x | x | x | x | x | x | |

---

## Spreminjanje podatkov

INSERT INTO tabela [(stolpec1, stolpec2, ...)]
VALUES (vrednost1, vrednost2, ...);

INSERT INTO tabela [(stolpec1, stolpec2, ...)]
SELECT ...;

UPDATE tabela SET stolpec1 = vrednost1, stolpec2 = vrednost2, ...
WHERE pogoj;

DELETE FROM tabela
WHERE pogoj;

| Naloga | TA | ŽA | AB | PK | KK | AL | JL | HL | MM | AO | DR | BR | AS | MS | DŠ | LT | TT |
| ------ | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- |
| I1 | x | x | x | x | x | x | x | x |x | | x | x | x | x | x | x | |
| I2 | x | x | x | x | x | x | x | x |x | | x | x | x | x | x | x | |
| U1 | x | x | x | x | x | x | x | x |x | | x | x | x | x | | x | |
| U2 | x | | x | x | x | x | x | x |x | | x | x | x | x | | x | |
| U3 | x | | x | x | x | x | x | x |x | | x | x | x | x | | x | |
| D1 | x | | x | x | x | x | x | x |x | | x | x | x | x | x | x | |
| D2 | x | | x | x | x | x | x | x |x | | x | x | x | x | | x | |

---

## Ustvarjanje tabel

CREATE TABLE tabela (
  stolpec tip [PRIMARY KEY] [UNIQUE] [NOT NULL] [DEFAULT vrednost] [CHECK (pogoj)]
    [REFERENCES tabela (stolpec)]
    [AUTOINCREMENT], -- v SQLite
  stolpec2 tip2 ...,
  PRIMARY KEY (stolpec1, stolpec2, ...),
  UNIQUE (stolpec2, stolpec2, ...),
  CHECK (pogoj),
  FOREIGN KEY (stolpec1, stolpec2, ...) REFERENCES tabela (stolpec1, stolpec2, ...)
);

1. Odprite program **SQLite Studio**, kjer boste ustvarili bazo z učitelji.

1. Dodajte novo bazo (napišite ime še neobstoječe datoteke) in odprite urejevalnik stavkov SQL.

2. Naredite tabelo `ucitelji`, ki naj ima stolpce `id`, `ime`, `priimek` in `email`. Stolpec `id` naj bo tipa `integer`, ostali stolpci pa tipa `text`. Stolpec `id` naj bo glavni ključ tabele.

       CREATE TABLE ucitelji (
         id integer PRIMARY KEY,
         ime text,
         priimek text,
         email text
       );

3. Naredite tabelo `predmeti`, ki naj vsebuje stolpce `id`, `ime` in `ects`. Stolpca `id` in `ects` naj bosta tipa `integer`, `ime` predmeta pa `text`. Spet naj bo stolpec `id` glavni ključ tabele.

       CREATE TABLE predmeti (
         id integer PRIMARY KEY,
         ime text,
         ects integer
       );

4. V tabeli `ucitelji` smo pozabili na stolpec `kabinet`. Tabelam lahko dodajamo stolpce na naslednji način: `ALTER TABLE ime_tabele ADD COLUMN ime_stolpca tip_stolpca;`. Tip stolpca naj bo kar `text`, saj oznaka kabineta lahko vsebuje tudi piko in črke.

       ALTER TABLE ucitelji ADD COLUMN kabinet text;

5. Naredite še šifrant vlog, in sicer kot tabelo `vloge`, ki ima stolpca `id` (tipa `integer`) in `opis` (tipa `text`). Poskrbi tudi za glavni ključ. Vloga z `id` 0 ustreza predavateljem, vloga 1 pa, da gre za asistenta.

    CREATE TABLE vloge (
      id integer PRIMARY KEY,
      opis text
    );

6. Naredite tabelo `izvajalci`, ki naj ima tri stolpce (vsi so tipa `integer`): `idpredmeta`, `iducitelja` in `vloga`. Poskrbi za ustrezne reference na ostale tabele.

       CREATE TABLE izvajalci (
         idpredmeta integer REFERENCES predmeti (id),
         iducitelja integer REFERENCES ucitelji (id),
         vloga integer REFERENCES vloge (id),
         PRIMARY KEY (idpredmeta, iducitelja, vloga)
       );

2. Napolnite tabele [`ucitelji`](https://ucilnica.fmf.uni-lj.si/mod/resource/view.php?id=20472), [`predmeti`](https://ucilnica.fmf.uni-lj.si/mod/resource/view.php?id=20473), [`vloge`](https://ucilnica.fmf.uni-lj.si/mod/resource/view.php?id=20475) in [`izvajalci`](https://ucilnica.fmf.uni-lj.si/mod/resource/view.php?id=20474) s pripetimi stavki `INSERT`.

1. Da ne bo potrebno izvajati vsakega stavka posebej, v SQLite Studiu pritisnite **F2** in odstranite kljukico pri *Execute only the query under the cursor*.

2. Naredite poizvedbo, ki poišče najbolj zasedene kabinete.

       SELECT kabinet, COUNT(*) AS stevilo
       FROM ucitelji
       WHERE kabinet IS NOT NULL
       GROUP BY kabinet
       ORDER BY stevilo DESC;

3. Naredite poizvedbo, ki bo prikazala vse pare cimrov. Izpisati je treba tabelo, ki ima 4 stolpce `(ime1, priimek1, ime2, priimek2)`. Za vsaka dva učitelja, ki si delita pisarno, se mora v rezultatu pojaviti po ena vrstica.

       SELECT u1.ime AS ime1, u1.priimek AS priimek1,
              u2.ime AS ime2, u2.priimek AS priimek2
       FROM ucitelji AS u1 JOIN ucitelji AS u2
       ON u1.kabinet = u2.kabinet
       WHERE u1.id < u2.id;

4. Naredite poizvedbo, ki bo vrnila tabelo vseh trojic predmet-učitelj-asistent. Iz te tabele se bo dalo razbrati, pri kolikih predmetih sodelujeta nek učitelj in asistent.

       SELECT predmeti.ime AS predmet,
              u1.priimek AS ucitelj, u2.priimek AS asistent
       FROM izvajalci AS i1 JOIN izvajalci AS i2 USING (idpredmeta)
       JOIN predmeti ON idpredmeta = predmeti.id
       JOIN ucitelji AS u1 ON i1.iducitelja = u1.id
       JOIN ucitelji AS u2 ON i2.iducitelja = u2.id
       WHERE i1.vloga = 0 AND i2.vloga = 1;