---
tags: vaje, pb1, sql, spreminjanje, ustvarjanje
hackmd: https://hackmd.io/9k5h7IkuSI2K0KiKO-DoIg
---
# Podatkovne baze 1 - vaje 9.11.2020

---

## Stikanje in `NULL`

| Naloga | DA | DB | MČ | AG | HKK | LM | MO | SO | MP | TP | PR | TS | LŠ | AT |
| ------ | -- | -- | -- | -- | --- | -- | -- | -- | -- | -- | -- | -- | -- | -- |
| 1 | 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 |
| 3 | 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 |
| 5 | 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
| 7 | 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 |

---

## 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 | DA | DB | MČ | AG | HKK | LM | MO | SO | MP | TP | PR | TS | LŠ | AT |
| ------ | -- | -- | -- | -- | --- | -- | -- | -- | -- | -- | -- | -- | -- | -- |
| I1 | 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 |
| U1 | 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 |
| U3 | 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 |
| D2 | 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 tab2(st)],
  st2 tip ...,
  PRIMARY KEY (st1, st2, ...),
  UNIQUE (stolpci),
  CHECK (pogoj),
  FOREIGN KEY (stolpci) REFERENCES tab2(stci)
);

ALTER TABLE tabela ADD COLUMN stolpec tip ...;

DROP TABLE tabela;

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

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

ALTER TABLE ucitelji ADD COLUMN kabinet text;

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

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

SELECT kabinet, COUNT(*) FROM ucitelji
GROUP BY kabinet
HAVING COUNT(*) = (
  SELECT MAX(stevilo) FROM (
    SELECT COUNT(*) AS stevilo FROM ucitelji
    WHERE kabinet IS NOT NULL
    GROUP BY kabinet
  )
)