Podatkovne baze 1

« nazaj

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  
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  
I2 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  
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 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 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
 );

    1. 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, predmeti, vloge in izvajalci 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;
Ta stran je odprtokodna. Izboljšaj to stran.
Ta zapisek je dostopen tudi na HackMD.