SQL — fra SELECT til triggere

DDL: CREATE INDEX — endrer skjemastrukturen (selv om indeksen er en separat datastruktur, hører den hjemme her).
DCL: GRANT SELECT — gir et privilegium til en bruker eller rolle.
DML: UPDATE — endrer rader, ikke skjemaet.
TCL: COMMIT — avslutter en transaksjon ved å gjøre alle DML-endringene permanente.

Den klassiske huskeregelen: DDL endrer struktur, DML endrer data, DCL endrer rettigheter, TCL grupperer DML i atomiske enheter.

Pensum: Kap. 3 — SQL er flere språk i ett

Tre ting samtidig: (1) UNIQUE — ingen to rader kan ha samme id; (2) NOT NULL — kolonnen kan aldri være ukjent; (3) en B+-tre-indeks (i de fleste DBMS-er) for rask oppslag og unikhetssjekk.

Composite primary key skrives på egen linje: PRIMARY KEY (a, b). Hvis du fjerner PRIMARY KEY-kvalifikatoren, mister du alle tre garantiene — du må eksplisitt skrive UNIQUE NOT NULL for å beholde to av dem, og legge til indeksen separat.

Pensum: 3A — DDL og spørringer

Riktig: A. Logisk evalueringsrekkefølge: FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT.

Konsekvens: i WHERE kan du ikke referere til en kolonnealias laget i SELECT, men i ORDER BY kan du. Derfor er SELECT salary*1.1 AS hike … WHERE hike > 80000 en feil, mens … ORDER BY hike er lovlig.

B er feil fordi SELECT evalueres etter HAVING men før ORDER BY. C er feil — HAVING evalueres etter GROUP BY men før SELECT. D er feil fordi WHERE evalueres allerede etter FROM, før både gruppering og SELECT.

Pensum: Kap. 3 — Anatomi av en spørring

SQL er multiset-basert som default — duplikater beholdes. DISTINCT tvinger DBMS-en til å fjerne dubletter, og det krever enten sortering (O(N log N)) eller en hash-tabell (O(N) men med minne-overhead) over hele resultatsettet.

Du trenger ikke DISTINCT når:

• Du selecter en supernøkkel — utvalget er allerede unikt per definisjon.
• Spørringen din inneholder GROUP BY g og du selecter bare gruppekolonnene + aggregater — én rad per gruppe.
• Du bruker UNION (uten ALL) — den dedupliserer allerede.

Vanlig misbruk: legge til DISTINCT for å "skjule" en feilskreven JOIN som lager kartesiske dubletter. Da bør du heller fikse JOIN-en.

Pensum: 3A — SELECT-grunnformer

Aggregatfunksjoner (alle untatt COUNT(*)) ignorerer NULL:

• SUM(x) = 6 (1+2+3).
• AVG(x) = 2 — gjennomsnitt over 3 rader, ikke 5! Dette er en klassisk eksamensfelle: AVG er SUM/COUNT, og COUNT teller ikke NULL.
• MAX(x) = 3.
• COUNT(x) = 3 — antall ikke-NULL verdier i x.
• COUNT(*) = 5 — antall rader uavhengig av innhold.

Spesialtilfelle: hvis alle rader er NULL, returnerer SUM/AVG/MAX/MIN NULL, mens COUNT(*) fortsatt teller radene og COUNT(x) blir 0.

Pensum: 3A — Aggregat og GROUP BY

Hver kolonne i SELECT må enten være: (1) en av gruppekolonnene g1 eller g2, (2) et aggregat (SUM, AVG, COUNT, …), eller (3) en deterministisk funksjon av kolonner i (1) og (2).

Grunnen: en gruppe består av flere rader. SELECT name, COUNT(*) FROM emp GROUP BY dept_id er udefinert — hvilken name skulle vi velge? SQL-standarden krever at hver verdi i resultatet er entydig.

MySQL har historisk vært slapp her og returnerer en vilkårlig verdi — det er en kjent fallgruve som er rettet med ONLY_FULL_GROUP_BY-modusen i MySQL 5.7+.

Pensum: 3A — Aggregat og GROUP BY

WHERE filtrerer rader før gruppering. HAVING filtrerer grupper etter gruppering.

Konkret: hvis predikatet er på en aggregert verdi (COUNT(*) > 5, AVG(salary) >= 50000) må det stå i HAVING — aggregatene finnes ikke før GROUP BY har kjørt. Hvis predikatet er på en rå kolonneverdi som ikke trenger aggregering (dept_name = 'CS') hører det hjemme i WHERE — det filtrerer tidligere og reduserer arbeidet.

Hovedregel: "Bruk WHERE hvis du kan, HAVING bare når du må." WHERE er nesten alltid raskere fordi det reduserer antallet rader før gruppering.

Pensum: 3A — Aggregat og GROUP BY

Riktig: C — {1, 1, 1, 2, 2, 2, 3, 4} (8 rader).

UNION ALL er en ren konkatenasjon av multimengdene — tellinger summeres. r har 1 to ganger og s har 1 én gang, så resultatet har 1 tre ganger.

A er resultatet av UNION (uten ALL), som dedupliserer.
B er ingen ekte SQL-operasjon — verken UNION eller UNION ALL gjør dette.
D er r INTERSECT s (med dedup) eller r INTERSECT ALL s som ville gitt {1, 2} (min-multiplisitet).

Hvorfor velge UNION ALL fremfor UNION? Ytelse. UNION må sortere/hashe for å fjerne dubletter; UNION ALL slipper det. Hvis du vet at delspørringene ikke overlapper, er ALL gratis.

Pensum: 3A — UNION/INTERSECT/EXCEPT

Begge er lovlige fordi ORDER BY evalueres etter SELECT. På det stadiet er aliaset bump tilgjengelig.

Forskjellen er stilistisk og praktisk:

• ORDER BY bump DESC — selvdokumenterende. Hvis kolonnerekkefølgen i SELECT endres senere, virker det fortsatt.
• ORDER BY 1 DESC — refererer til kolonnen i posisjon 1 i SELECT-listen. Kompakt, men skjør: hvis du legger til en kolonne foran salary*1.1, sorterer du nå plutselig på en annen kolonne. SQL:2003 anbefaler å unngå posisjons-ORDER BY for produksjonsspørringer.

I begge tilfeller kan du legge til ASC (default) eller DESC, og NULL-håndtering med NULLS FIRST / NULLS LAST (DBMS-avhengig).

Pensum: 3A — ORDER BY/DISTINCT

Mønsteret krever: (1) nøyaktig ett tegn (hva som helst), så (2) bokstaven a, så (3) null eller flere vilkårlige tegn, og til slutt (4) bokstaven n.

Eksempler som matcher: 'Dan', 'Sahn', 'Jan', 'Tarjan', 'San Joaquin'.
Eksempler som ikke matcher: 'Anna' (starter med A uten foran-tegn), 'an' (mangler første ukjente tegn), 'Dann' (slutter på nn men siste tegn er n — vent, dette matcher faktisk), 'Adam' (slutter ikke på n).

Spesial-tegn: _ = nøyaktig ett tegn; % = null eller flere tegn. For å matche selve tegnene _ eller % i strengen brukes ESCAPE: name LIKE '50\%' ESCAPE '\'.

For regulære uttrykk er SIMILAR TO (SQL-standard) eller dialekt-spesifikke som ~ (PostgreSQL) eller REGEXP (MySQL) sterkere alternativer.

Pensum: 3A — Strenger

Riktig: B — ('A', 150), ('C', 300).

Steg for steg:

• Gruppe A: SUM = 100+200 = 300, AVG = 300/2 = 150. SUM > 100 ✓ — beholdes.
• Gruppe B: NULL ignoreres, så SUM = 50, AVG = 50/1 = 50. SUM (50) ikke > 100 ✗ — fjernes av HAVING.
• Gruppe C: SUM = 300, AVG = 300. SUM > 100 ✓ — beholdes.

A er feil fordi den har gruppe B med — men den feiler HAVING. C gjør samme feil og bruker også feil AVG for B (25 = (50+0)/2 — som ville behandlet NULL som 0). D er feil fordi AVG av A er 150, ikke 100.

Vanligste fellen her er å tro at NULL behandles som 0 i AVG/SUM. Det gjør det ikke — den ignoreres helt, og divisoren i AVG er antall ikke-NULL verdier.

Pensum: 3A — Aggregat og GROUP BY

VALUES-formen setter inn én eller flere konkrete rader spesifisert direkte i SQL-en: INSERT INTO t (a, b) VALUES (1, 'foo'), (2, 'bar'). Brukes for konstantverdier — typisk testdata, oppslagstabeller, manuell input.

SELECT-formen setter inn rader hentet fra en spørring: INSERT INTO archive SELECT * FROM orders WHERE created < '2020-01-01'. Antall rader er ikke kjent på forhånd; det avhenger av datagrunnlaget. Brukes typisk for ETL, arkivering, materialisering av delsett.

Begge støtter eksplisitt kolonneliste etter tabellnavnet — alt som ikke er listet, får DEFAULT eller NULL. Begge må respektere alle constraints (PK, FK, NOT NULL, CHECK) — ellers feiler hele setningen som én transaksjon.

Pensum: 3A — DML-endringer

Alle tre returnerer matchende par pluss umatchede rader fra én eller begge sider, med NULL der det mangler match:

• LEFT OUTER JOIN — alle rader fra venstre tabell beholdes; umatchede rader fra høyre side gir NULL i kolonnene fra høyre.
• RIGHT OUTER JOIN — speilbildet: alle fra høyre, NULL i venstres kolonner ved umatch.
• FULL OUTER JOIN — alle umatchede rader fra begge sider, NULL der den andre siden mangler.

Praktisk regel: A LEFT JOIN B ≡ B RIGHT JOIN A — bare svap rekkefølgen. Derfor brukes LEFT JOIN nesten alltid i praksis; RIGHT JOIN er sjelden naturlig å skrive.

MySQL støtter ikke FULL OUTER JOIN; man emulerer det med LEFT JOIN UNION RIGHT JOIN.

Pensum: 3B — Inner vs outer

NATURAL JOIN matcher automatisk på alle kolonner med samme navn i de to tabellene, og fjerner duplikatkolonnene fra resultatet (én kolonne per match).

Faren: resultatet endrer seg når skjemaet endres. Hvis noen senere legger til en kolonne created_at til begge tabellene, blir den med i match-betingelsen — og spørringen begynner plutselig å returnere færre rader, eller ingen, uten at SQL-en endres.

Eksempel som er sårbart:

SELECT student.name, course.title
FROM enrollment NATURAL JOIN student NATURAL JOIN course

Bedre — eksplisitt:

… enrollment e JOIN student s ON e.sid = s.sid JOIN course c ON e.cid = c.cid

NATURAL JOIN er praktisk for tavle-eksempler og hurtig prototyping, men aldri i kode som lever lenger enn én commit.

Pensum: 3B — ON vs USING

Riktig: B.

JOIN ... ON: hvis du selecter SELECT *, får du begge id-kolonnene — r.id og s.id — selv om de inneholder identiske verdier (når match-betingelsen er likhet).

JOIN ... USING (id): én sammenslått id-kolonne i resultatet. Etterpå refererer du til den som bare id, ikke r.id. USING krever at kolonnenavnene er identiske i begge tabellene.

A er feil — kolonnesettet er forskjellig (en ekstra kolonne i ON-versjonen).
C er feil — USING fungerer på alle datatyper så lenge navnene matcher.
D er feil — USING er en navne-match, ikke en nøkkel-match. Den krever ingen PK/FK-relasjon.

Praktisk: USING er kompakt for like-navnede join-kolonner; ON er mer fleksibelt (ulike navn, ikke-likhet, sammensatte uttrykk).

Pensum: 3B — ON vs USING

Bruk en self-join med to alias for samme tabell:

SELECT e.name AS employee, m.name AS manager
FROM employee e
LEFT JOIN employee m ON e.manager_id = m.id;

Hvorfor alias? Tabellen forekommer to ganger — én gang som "den ansatte" (e) og én gang som "sjefen" (m). Uten alias kan ikke parseren skille mellom de to forekomstene; employee.id ville vært tvetydig.

Hvorfor LEFT JOIN? Fordi toppsjefen har manager_id = NULL — hen har ingen sjef. Med INNER JOIN ville toppsjefen forsvunnet fra resultatet; LEFT JOIN beholder hen og gir manager = NULL.

Self-join er også standardteknikken for å sammenligne rader i samme tabell (f.eks. "alle par av ansatte i samme avdeling med ulik lønn").

Pensum: 3B — Self-join

Riktig: D.

En ukorrelert subquery er en selvstendig spørring — den kan kjøres alene og returnerer det samme uavhengig av den ytre. Optimizeren evaluerer den én gang og bruker resultatet videre.

Eksempel ukorrelert: SELECT * FROM emp WHERE salary > (SELECT AVG(salary) FROM emp). Den indre spørringen er konstant.

En korrelert subquery refererer til en kolonne fra den ytre spørringen, og må derfor logisk evalueres for hver rad i den ytre.

Eksempel korrelert: SELECT * FROM emp e WHERE salary > (SELECT AVG(salary) FROM emp WHERE dept = e.dept). Her endres subquery-resultatet for hver rad — gjennomsnittet er per avdeling.

A er feil — begge typer kan returnere skalar eller tabell. B er feil — korrelerte er ofte tregere (men optimizere kan transformere dem til joins). C er feil — korrelerte subqueries kan stå i SELECT, FROM, WHERE og HAVING.

Naivt evalueres korrelerte O(n²); en god optimizer omskriver ofte til en semi-join eller window function.

Pensum: 3B — Korrelerte subqueries

Fordi én eneste NULL i subquery-resultatet ødelegger hele NOT IN-uttrykket.

SQL bruker tre-verdi-logikk. Når subqueryen returnerer {1, 2, NULL}, blir x NOT IN (1, 2, NULL) til x ≠ 1 AND x ≠ 2 AND x ≠ NULL. Den siste sammenligningen gir UNKNOWN, og TRUE AND TRUE AND UNKNOWN = UNKNOWN. WHERE filtrerer bort alt som ikke er TRUE → ingen rader.

Tre løsninger:

• Bruk EXISTS: … WHERE NOT EXISTS (SELECT 1 FROM dept d WHERE d.id = e.dept_id). Robust mot NULL.
• Filtrer NULL i subqueryen: … NOT IN (SELECT id FROM dept WHERE id IS NOT NULL).
• LEFT JOIN + IS NULL: … FROM emp e LEFT JOIN dept d ON e.dept_id = d.id WHERE d.id IS NULL.

Dette er én av SQLs mest beryktede stille feil — den kompilerer, kjører, og returnerer feil resultat uten advarsel.

Pensum: 3B — EXISTS/IN/ANY/ALL

x > ALL (subquery) er sant hvis x er større enn hver eneste verdi subqueryen returnerer. Tom subquery → TRUE (vakuumtilfellet).

x > ANY (subquery) er sant hvis x er større enn minst én verdi. SOME er synonymt med ANY. Tom subquery → FALSE.

Idiomatiske uttrykk:

• x > ALL (SELECT salary FROM emp) ≡ x > (SELECT MAX(salary) FROM emp) — "større enn alle" = "større enn maks".
• x > ANY (SELECT salary FROM emp) ≡ x > (SELECT MIN(salary) FROM emp) — "større enn minst én" = "større enn min".
• x = ANY (subquery) ≡ x IN (subquery).
• x <> ALL (subquery) ≡ x NOT IN (subquery) — og arver dessverre samme NULL-felle.

I praksis brukes IN/NOT IN/EXISTS oftere enn ANY/ALL fordi de leses tydeligere.

Pensum: 3B — EXISTS/IN/ANY/ALL

Riktig: C — 4 rader.

Resultat:

(1, 'x')
(1, 'y')
(2, NULL)
(3, NULL)

Logisk gjennomgang: for hver rad i venstre (r), finn alle matcher i høyre (s). Hvis det er n matcher, gir det n rader; hvis 0 matcher, gir det 1 rad med NULL i høyre kolonner.

• a=1 → 2 matcher i s → 2 rader.
• a=2 → 0 matcher → 1 rad (a=2, b=NULL).
• a=3 → 0 matcher → 1 rad (a=3, b=NULL).
• Raden (4, 'z') i s er ikke med — LEFT JOIN beholder bare ekstra rader fra venstre side.

A er INNER JOIN. B glemmer at a=1 matcher to ganger. D er FULL OUTER JOIN.

Generell formel: |r LEFT JOIN s| = sum over r-rader av max(1, antall match).

Pensum: 3B — Inner vs outer

En Common Table Expression (CTE) er en navngitt midlertidig spørring i begynnelsen av en SELECT:

WITH high_earners AS (
  SELECT * FROM emp WHERE salary > 100000
)
SELECT name FROM high_earners WHERE dept = 'CS';

Grunn 1 — lesbarhet. Komplekse spørringer kan brytes ned i navngitte steg som leses ovenfra og ned, akkurat som programkode. Sammenlign med å pakke samme logikk inn i nestede subqueries med fire nivåer parenteser.

Grunn 2 — gjenbruk. En CTE kan refereres flere ganger i samme spørring (selv om DBMS ofte re-evaluerer den). Ekstra: WITH RECURSIVE tillater rekursive spørringer (transitive lukninger, hierarkier) — en kapabilitet som inline subqueries ikke har.

Bonus: CTE-er fungerer som en kompromiss mellom views (persistente, deles) og subqueries (kun innenfor en spørring).

Pensum: 3B — WITH/CTE

Det kartesiske produktet: hver rad i r kombinert med hver rad i s. Hvis r har n rader og s har m rader, har resultatet n × m rader.

Som SQL-syntaks er r CROSS JOIN s ekvivalent med r, s (komma) i FROM-klausulen.

Når er det ønsket?

• Generere alle kombinasjoner — f.eks. en pivot-tabell med alle (måned, region)-par, der noen kombinasjoner kanskje ikke har faktiske rader, og du vil LEFT JOIN-e fakta etterpå.
• Lage testdata eller serier — kombinere tall-tabeller for å produsere kalendere, tidsserier osv.
• Tom høyre side brukes med en konstant: SELECT * FROM emp CROSS JOIN (VALUES (1.10)) AS f(rate) for å feste en konstant til hver rad.

Vanlig feil: glemme JOIN-betingelsen i en gammeldags FROM r, s-skriving — da får du et utilsiktet kartesisk produkt med eksplosivt resultatsett.

Pensum: 3B — Hvorfor join

En skalar subquery er en SELECT som returnerer nøyaktig én rad og én kolonne — altså én verdi. SQL-runtime sjekker dette: returneres flere rader (uten LIMIT 1), gir det runtime-feil; returneres null rader, blir verdien NULL.

Skalar subqueries kan stå nesten hvor som helst en konstant kan stå:

• I SELECT-listen: SELECT name, (SELECT AVG(s) FROM x) FROM emp.
• I WHERE-betingelsen: WHERE salary > (SELECT AVG(salary) FROM emp).
• I HAVING: HAVING SUM(amount) > (SELECT threshold FROM cfg).
• I VALUES eller assignments i UPDATE: SET rank = (SELECT MAX(s) FROM x).

Idiomet er kraftig fordi det lar deg interpolere én verdi (et aggregat, en oppslagsverdi) inn i en spørring uten en eksplisitt JOIN. Men hvis subqueryen er korrelert eller tung, og brukes i SELECT-listen over en stor tabell, blir den evaluert per rad — pass på.

Pensum: 3B — Subqueries

En anti-join returnerer rader fra venstre tabell som ikke har noen matchende rad i høyre tabell. Det er negasjonen av en semi-join (som returnerer rader som har en match).

Tre måter å uttrykke "alle ansatte uten et prosjekt" (emp, assigns(emp_id)):

• NOT EXISTS — den kanoniske og NULL-trygge formen:
SELECT * FROM emp e WHERE NOT EXISTS (SELECT 1 FROM assigns a WHERE a.emp_id = e.id)

• NOT IN — kompakt, men sårbar for NULL i subqueryen (se egen flashcard):
SELECT * FROM emp WHERE id NOT IN (SELECT emp_id FROM assigns)

• LEFT JOIN ... WHERE høyre IS NULL — rederlig form, ofte raskest:
SELECT e.* FROM emp e LEFT JOIN assigns a ON e.id = a.emp_id WHERE a.emp_id IS NULL

Optimizere transformerer ofte mellom disse tre formene; for mennesker er NOT EXISTS robust og lesbar — den foretrukne formen i pensum.

Pensum: 3B — EXISTS/IN/ANY/ALL

Et view er en navngitt, lagret SELECT-spørring som kan brukes som om den var en tabell. CREATE VIEW v AS SELECT … registrerer den; deretter spør du SELECT * FROM v som mot en vanlig tabell.

Tre motivasjoner:

• Abstraksjon. Skjermer applikasjonskoden fra tabell-skjemaet. Når underliggende tabeller endres, kan view-et omskrives og applikasjonen kjører uendret.
• Sikkerhet. Gi brukere SELECT-tilgang til et view i stedet for tabellen — view-et kan ekskludere sensitive kolonner (salary, ssn) eller filtrere rader (WHERE owner = current_user).
• Forenkling. Komplekse spørringer (multi-join, aggregater) kan navngis og gjenbrukes — særlig for dashboards og rapporter.

Default er virtuelt: viewets innhold beregnes ved hver bruk. Et materialized view derimot lagrer resultatet på disk og må refreshes — ekvivalent med en cache.

Pensum: 3C — Views

En materialized view er en view der resultatet er fysisk lagret (materialisert) på disk. Når brukeren spør view-et, leses det forhåndsberegnede resultatet — ingen re-eksekvering av den underliggende SELECT-en.

Fordeler:

• Rask lesning, særlig for tunge aggregater (millioner av rader → noen tusen).
• Stabilt utgangspunkt for rapporter (selv om underliggende tabeller endres).
• Kan indekseres som en vanlig tabell.

Ulemper:

• Stale data. Når underliggende tabeller endres, blir view-et utdatert til neste refresh.
• Diskplass — view-et tar fysisk plass.
• Refresh-strategi må velges — ON COMMIT (umiddelbart, gir overhead på alle DML), periodisk (planlagt cron), eller manuell (REFRESH MATERIALIZED VIEW).

Praktisk regel: bruk virtuelt view som default; bytt til materialized når lesning blir flaskehalsen og stale data i noen minutter er akseptabelt.

Pensum: 3C — Materialized vs virtual

Riktig: A.

SQL-standarden krever at et view er entydig invertibelt for at det skal være oppdaterbart: gitt en endring på view-radene, skal DBMS-en kunne mappe det tilbake til en entydig endring på basistabellen.

A bryter dette på flere måter:

• Bruker GROUP BY — én rad i view-et representerer mange rader i emp.
• Bruker AVG — det finnes ingen invers ("hva betyr det å sette AVG til 50000?").

B, C og D er alle direkte projeksjoner/seleksjoner over én tabell, uten aggregat eller DISTINCT — alle er oppdaterbare. (Caveat: en INSERT i B eller D må sette de uoppgitte kolonnene til DEFAULT eller NULL, og hvis de er NOT NULL uten default, feiler INSERT.)

Generelle regler for oppdaterbarhet (SQL-92): én tabell i FROM, ingen DISTINCT, ingen GROUP BY/HAVING, ingen aggregater, ingen UNION, og hver utvalgt kolonne må være en direkte basistabellkolonne (ikke et uttrykk).

Pensum: 3C — Oppdaterbare views

WITH CHECK OPTION sørger for at en INSERT eller UPDATE gjennom view-et fortsatt produserer en rad som er synlig gjennom view-et. Hvis ikke, avvises operasjonen.

Konkret eksempel:

CREATE VIEW cs_emp AS
SELECT * FROM emp WHERE dept = 'CS'
WITH CHECK OPTION;

Uten CHECK OPTION kunne en bruker skrive INSERT INTO cs_emp VALUES (…, dept='HR', …) — raden ville havne i tabellen, men ikke være synlig gjennom view-et. Det er forvirrende ("hvorfor er ikke raden jeg nettopp insertet der?") og potensielt et sikkerhetsproblem.

Med CHECK OPTION blir samme INSERT avvist med en feilmelding: "row violates check option for view".

To varianter: LOCAL (sjekker bare predikatet i denne view-en) og CASCADED (sjekker også alle underliggende views' predikater). Default er CASCADED i SQL-standarden.

Pensum: 3C — Oppdaterbare views

Tre forskjeller:

1. Antall. En tabell kan ha én PRIMARY KEY, men flere UNIQUE constraints. PK er den semantiske "hovednøkkelen"; UNIQUE er bare et unikhetskrav.

2. NULL. PRIMARY KEY impliserer NOT NULL automatisk. UNIQUE tillater NULL — og i SQL-standarden tillater den flere NULL-verdier (siden NULL ≠ NULL i 3VL). MySQL og PostgreSQL følger standarden; SQL Server tillater bare én NULL per UNIQUE.

3. FK-mål. En foreign key kan referere til både PK og UNIQUE constraints. PK er det vanlige målet, men REFERENCES tab(unique_col) er gyldig.

Praktisk: bruk PK for "den naturlige radidentifikatoren" (ofte en surrogat-id), og UNIQUE for andre felter som må være unike (e-post, brukernavn, naturnøkler).

Pensum: 3C — Integritet

Syntaks — som tabell-constraint, ikke kolonne-constraint:

CREATE TABLE enrollment (
  student_id INT NOT NULL,
  course_id INT NOT NULL,
  grade CHAR(1),
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES student(id),
  FOREIGN KEY (course_id) REFERENCES course(id)
);

Composite PK passer for koblings­tabeller i mange-til-mange-relasjoner: kombinasjonen er raden, og det finnes ingen naturlig grunn til å ha en egen id (alternativt er det greit å ha en surrogat-id i tillegg for ergonomi, men da bør (student_id, course_id) ha UNIQUE).

Argumentet for surrogat-id i stedet: hvis composite PK må refereres fra en tredje tabell, blir foreign key-deklarasjonen klumpete (to kolonner). Surrogat gir én pen FK.

Argumentet mot surrogat: ekstra kolonne, ekstra indeks, ekstra navngivning. For en pur kobling-tabell er composite ofte renest.

Pensum: 3C — Integritet

Riktig: D.

De fire vanligste alternativene for ON DELETE:

• RESTRICT (default i mange DBMS) — sletting blokkeres umiddelbart hvis det finnes refererende rader. Det er det A beskriver.
• NO ACTION — som RESTRICT, men sjekken utsettes til slutten av setningen (eller transaksjonen i deferred mode). I praksis nesten alltid samme observable oppførsel.
• CASCADE — slett også de refererende radene. Det er det B beskriver.
• SET NULL — sett FK-kolonnen til NULL i de refererende radene. Krever at kolonnen tillater NULL. Det er det D beskriver — riktig svar.
• SET DEFAULT — sett FK-kolonnen til kolonnens DEFAULT-verdi.

C er ikke et faktisk alternativ — referensiell integritet håndheves alltid; "foreldreløse" pekere er per definisjon ikke tillatt i et relasjonelt skjema.

Tilsvarende finnes ON UPDATE som styrer hva som skjer hvis PK-en oppdateres (sjelden — typisk ikke aktuelt for surrogat-PK).

Pensum: 3C — FK-actions

Flere kolonner i samme tabell: ja. En CHECK kan referere til en hvilken som helst kombinasjon av kolonner i raden den evalueres på:

CREATE TABLE rectangle (
  w NUMERIC, h NUMERIC,
  CHECK (w > 0 AND h > 0 AND w >= h)
);

Kolonner i andre tabeller: nei (i SQL-standarden). CHECK evalueres lokalt på én rad ved hver insert/update på den tabellen. Subqueries i CHECK er forbudt i SQL-standard og avvises av de fleste DBMS-er, fordi det gjør semantikken hårete: "hva skjer hvis den andre tabellen endres?".

Hvis du trenger en cross-tabell-regel, har du tre alternativer:

• ASSERTION — SQL-standardens svar, men nesten ingen DBMS implementerer det.
• Trigger — kjør en sjekk i en BEFORE INSERT/UPDATE-trigger, kast feil med RAISE hvis brutt.
• Materialized view + CHECK — DB2-trikset: oppretthold et materialized view som aggregerer det tverrgående tilstandet, legg en CHECK på det.

For den klassiske eksamensfellen "total budsjett ≤ X" — det er ikke en CHECK; det krever en trigger eller assertion.

Pensum: 3C — Integritet

En ASSERTION er en SQL-standard-konstruksjon for å håndheve en vilkårlig boolean-betingelse over hele databasen:

CREATE ASSERTION budget_ok
CHECK ((SELECT SUM(amount) FROM grants) <= 1000000);

Den minner om en CHECK, men er frittstående — ikke knyttet til en bestemt tabell — og kan inneholde subqueries over flere tabeller.

Hvorfor sjelden brukt: Implementeringen er en pinligheten av SQL-standarden. Hver gang en hvilken som helst involvert tabell endres, må DBMS-en revurdere assertionen — eller bruke avansert inkrementell vedlikehold for ikke å sliten ned ytelsen. Effektiv inkrementell evaluering for vilkårlige assertions er et åpent forskningsspørsmål.

Resultat: ingen av de store DBMS-ene (PostgreSQL, MySQL, Oracle, SQL Server, DB2) implementerer ASSERTION i praksis. Pensumet nevner det for fullstendighetens skyld; i virkeligheten skriver man triggere i stedet.

Pensum: 3C — Assertion

Atomisitet = "alt eller ingenting". En transaksjon er en logisk enhet av arbeid; enten gjennomføres alle dens DML-endringer permanent (commit), eller ingen (rollback). Det skal aldri være mulig å observere et partielt resultat.

SQL-kommandoene:

• BEGIN (eller START TRANSACTION) — markerer starten på en transaksjon. Mange DBMS-er starter en implisitt transaksjon ved første DML hvis ingen er aktiv.
• COMMIT — gjør alle endringer permanent. Etter dette punkt er endringene synlige for andre transaksjoner og overlever krasj.
• ROLLBACK — kast alle endringene siden BEGIN. Tabellen ser ut som før transaksjonen startet.

Klassisk eksempel: penger overføres fra konto A til B i to UPDATE-er. Krasj mellom dem uten transaksjon ville etterlatt penger som forsvinner. Med BEGIN ... COMMIT er enten begge eller ingen utført.

De andre ACID-egenskapene — Consistency, Isolation, Durability — handler om korrekthet, samtidighet og holdbarhet etter krasj. De læres dypere i kap. 8.

Pensum: 3C — Transaksjoner

Et SAVEPOINT er et navngitt punkt inni en transaksjon. ROLLBACK TO sp_name ruller tilbake bare endringer gjort etter savepointet — alt før savepointet beholdes, og transaksjonen fortsetter åpen.

Syntaks:

BEGIN;
UPDATE … ;
SAVEPOINT before_risky;
UPDATE … ; -- noe risikabelt
-- hvis feiler:
ROLLBACK TO before_risky;
-- fortsett med andre operasjoner ...
COMMIT;

Bruksområder:

• Lange batch-jobber der noen mindre feilbare delsteg kan ignoreres uten å miste hele transaksjonens arbeid.
• ORM-er bruker savepoints internt for å implementere "nestede transaksjoner" (SQL-standarden støtter ikke ekte nestede transaksjoner).
• Sikkerhetsnett ved interaktiv testing.

Trade-off: hvert savepoint koster minne i transaction log og kan låses lengre. Bruk dem når flyten faktisk krever delvis tilbakerulling, ikke som en generell forsiktighet.

Pensum: 3C — Transaksjoner

Riktig: B.

Default for CREATE INDEX i de fleste DBMS-er er en B+-tre-indeks (ikke clustered, ikke unik). Det fungerer for alle datatyper som har en ordensrelasjon — INT, VARCHAR, DATE, osv. — som er bredeste mulige støtte.

A er feil — hash-indeks er ofte tilgjengelig (USING HASH) men ikke default; den krever ikke numerisk type, men gir ikke range-støtte.
C er feil — clustered må eksplisitt bes om, ofte gjennom PK eller spesiell syntaks (Oracle ORGANIZATION INDEX, SQL Server CLUSTERED). Default er secondary.
D er feil — vanlig CREATE INDEX tillater dubletter. Du må skrive CREATE UNIQUE INDEX for å håndheve unikhet (eller bruke en UNIQUE constraint).

Hvorfor B+-tre? Den kombinerer rask point-lookup (O(log n)) med rask range-scan (sortert blader). Indekser læres dypere i kap. 6.

Pensum: 3C — CREATE INDEX

Både Bob og Carol mister SELECT-privilegiet. Dette kalles cascading revoke.

Logikken: når et privilegium revokes med CASCADE, fjernes alle nedstrøms-privilegier som kun kan spores tilbake til denne kjeden. Carol fikk SELECT fra Bob, og Bob hadde det fra Alice; når Alices grant til Bob fjernes, blir Bobs grant til Carol også ugyldig.

Caveat: hvis Carol også fikk SELECT fra en annen kilde (f.eks. en annen rolle eller direkte fra Alice), beholder hun privilegiet. SQL sporer grant chains som en graf, og REVOKE CASCADE fjerner bare grenene som blir foreldreløse.

Alternativet er REVOKE … FROM bob RESTRICT — det feiler hvis Bob har videre-grantet privilegiet. Det tvinger Alice til å gjøre seg klart om hun vil rydde opp i hele kjeden eller ikke.

Standarden krever CASCADE eller RESTRICT; mange DBMS-er antar CASCADE som default, men dialekt varierer.

Pensum: 3C — Autorisasjon

Funksjon: returnerer en verdi (eller en tabell), kalles inni en SELECT/uttrykk, har vanligvis ikke side-effekter:
SELECT name, calc_bonus(salary) FROM emp;

Prosedyre: returnerer ingenting (eller via OUT-parametre), kalles med CALL-syntaks, kan ha side-effekter (INSERT, UPDATE, COMMIT, …):
CALL transfer_funds(123, 456, 100.00);

Konsekvenser:

• Funksjoner brukes typisk som beregninger som er deterministiske og lesbare i en SQL-kontekst.
• Prosedyrer brukes for forretningsprosesser med flere DML-steg ("registrer en bestilling": insert order, update lager, send notifikasjon).
• DBMS-er som tillater funksjoner å gjøre DML (PostgreSQL, MySQL) blir "uren" — pass på ytelse og sideeffekter.

Stored procedures gir også servervidd kapsling og kan ha eksplisitte transaksjonsgrenser (BEGIN/COMMIT) som funksjoner ikke har.

Pensum: 3D — Funksjoner

• IN (default) — innparameter: prosedyren leser verdien, men endringer er ikke synlige for kalleren. Som value-parameter i C.
• OUT — utparameter: prosedyren tilordner en verdi som returneres til kalleren. Innverdien ignoreres.
• INOUT — begge deler: prosedyren leser inn og kan skrive en oppdatert verdi tilbake.

Eksempel:

CREATE PROCEDURE get_balance(
  IN account_id INT,
  OUT balance DECIMAL(10,2)
) AS $$
BEGIN
  SELECT amount INTO balance FROM account WHERE id = account_id;
END;
$$;

Kall (PostgreSQL):

CALL get_balance(123, NULL); — NULL som plassholder; resultatet returneres som "result row" og kan konsumeres av klienten.

Kall (MySQL/standard):

SET @b = 0;
CALL get_balance(123, @b);
SELECT @b;

Bruk OUT-parametre når en prosedyre må returnere mer enn ett resultat — eller bruk en TABLE-returnerende funksjon i stedet.

Pensum: 3D — Prosedyrer

BEFORE: kjører før raden faktisk skrives til tabellen. Brukes når triggeren skal:

• Validere data og avbryte operasjonen (RAISE EXCEPTION) hvis ugyldig.
• Endre verdier i den nye raden — sett en updated_at-tidsstempel, normaliser e-post til lowercase, fyll inn beregnede kolonner.

I BEFORE er NEW-recorden mutbar: NEW.email := lower(NEW.email) påvirker det som faktisk lagres.

AFTER: kjører etter raden er skrevet og constraints sjekket. Brukes når triggeren skal:

• Logge endringen i en audit-tabell (INSERT INTO audit VALUES (OLD, NEW, now())).
• Oppdatere et derivert aggregat i en annen tabell (UPDATE summary SET total = total + NEW.amount).
• Sende en notifikasjon (LISTEN/NOTIFY i PostgreSQL).

I AFTER er NEW read-only — endringen er allerede lagret. Hvis du prøver å modifisere NEW i AFTER, får du feil.

Tommelfinger: BEFORE for "endre eller avbryt", AFTER for "reager på fakta".

Pensum: 3D — Triggere

Riktig: C — FOR EACH ROW.

Den fyrer én gang for hver rad som rammes av setningen, og inni triggerkroppen er NEW og OLD tilgjengelige som "denne radens nye/gamle verdier". Idiomet:

CREATE TRIGGER log_emp_changes
AFTER UPDATE ON emp
FOR EACH ROW
EXECUTE FUNCTION log_change();

Inne i log_change(): INSERT INTO audit VALUES (OLD.id, OLD.salary, NEW.salary, now()).

A er feil — STATEMENT-trigger fyrer én gang per setning, og NEW/OLD som enkeltrader er ikke tilgjengelig der. Du må bruke transition tables (REFERENCING NEW TABLE AS new_rows), som er en mer avansert konstruksjon. Det er også overdesignet hvis du faktisk bare vil ha "én log per rad".
B er feil — CHECK constraints kan ikke skrive til andre tabeller; de er pure rad-validering.
D er feil — du kan ikke stole på at applikasjonskoden alltid kaller en prosedyre. Triggere er DBMSet sin garanti at logikken kjøres.

Når STATEMENT er riktig: hvis du vil oppdatere én aggregert verdi (f.eks. UPDATE summary SET row_count = row_count + N), er det dumt å gjøre det per rad.

Pensum: 3D — Triggere

Logikken: NEW representerer "raden etter operasjonen", OLD representerer "raden før operasjonen". Det som ikke gir mening, finnes ikke.

• INSERT: kun NEW — det finnes ingen "før"-rad. OLD er NULL/utilgjengelig.
• UPDATE: begge. OLD = raden slik den var, NEW = raden slik den blir/ble.
• DELETE: kun OLD — etter sletting finnes ingen rad. NEW er utilgjengelig.

I en BEFORE UPDATE kan du sammenligne: IF NEW.salary > OLD.salary * 2 THEN RAISE EXCEPTION 'Ulovlig lønnshopp'.

I en BEFORE INSERT kan du fylle defaults: NEW.created_at := now().

I AFTER DELETE skriver du audit fra OLD: INSERT INTO trash VALUES (OLD.id, OLD.name, now()).

Spesialtilfelle: i FOR EACH STATEMENT-triggere finnes verken NEW eller OLD som enkeltrader; bruk transition tables.

Pensum: 3D — Triggere

En kaskade oppstår når en trigger på tabell A gjør en endring i tabell B, som har sin egen trigger som endrer C, og så videre.

Tre konkrete problemer:

1. Uoverskuelig kontrollflyt. En enkel UPDATE emp SET salary = … kan utløse 5–10 trigger-eksekveringer som rører ved tabeller du ikke engang visste eksisterte. Debugging blir et mareritt.

2. Sykler og uendelige løkker. Trigger på A oppdaterer B; trigger på B oppdaterer A; → uendelig rekursjon. Mange DBMS-er har en max-rekursjonsgrense (typisk 16–64) som stopper det med en feil — men det stopper også din transaksjon.

3. Ytelseskollaps. En BATCH-UPDATE av 1 million rader, hvor hver trigger kjører en subquery, blir en N²-katastrofe.

4. Vanskelige race conditions / låsing. Triggere kan låse rader i andre tabeller, og hvis to samtidige operasjoner triggerer kryssende lås-rekkefølger, oppstår deadlock.

Beste praksis: hold triggere idempotente, kortvarige og uten ko-modifikasjoner som kunne vært i en eksplisitt prosedyre. Bruk DEFERRABLE constraints og statement-level triggere fremfor row-level når mulig.

Pensum: 3D — Trigger-fellene

Riktig: A.

En rekursiv CTE har to ledd kombinert med UNION ALL:

• Anker (basistilfelle): SELECT parent FROM par WHERE child='Anna' — Annas direkte foreldre.
• Rekursivt ledd: SELECT p.parent FROM par p, anc a WHERE p.child = a.p — for hver forfader vi har funnet hittil, finn deres foreldre. Refererer til CTE-navnet (anc) inne i seg selv.

Iterasjon stopper automatisk når et ny iterasjon ikke produserer flere rader (fixpoint).

B er feil — den definerer ingen anker, og UNION uten ALL kan i teorien fungere men er ikke garantert avslutning og er semantisk feil her (CROSS JOIN er ikke det vi vil).
C er feil — uten RECURSIVE-nøkkelordet er CTE-en ikke-rekursiv og kan ikke referere til seg selv. Dette gir bare direkte foreldre, ikke besteforeldre.
D er feil — det finnes ingen RECURSIVE-modifikator på et enkelt SELECT. Rekursjon krever WITH-strukturen.

Pensum: 3D — WITH RECURSIVE

Riktig: D — 5 iterasjoner.

Steg-for-steg:

• Iterasjon 1 (anker): startsett = {1}. Returnerer 1 ny rad.
• Iterasjon 2: from {1}, follow edge → {2}. 1 ny rad.
• Iterasjon 3: from {2}, follow edge → {3}. 1 ny rad.
• Iterasjon 4: from {3}, follow edge → {4}. 1 ny rad.
• Iterasjon 5: from {4}, follow edge → {5}. 1 ny rad.
• Iterasjon 6: from {5}, ingen kanter → 0 nye rader. Fixpoint nådd; stopper.

De fleste DBMS-er teller "iterasjon 6" som "rekursjonen avsluttes" og inkluderer ikke den i antall produktive iterasjoner. Hvis spørsmålet teller alle iterasjoner inkl. den siste tomme, er svaret 6; hvis det teller produktive iterasjoner (de som faktisk genererte minst én rad), er svaret 5.

Generell regel: for en kjede av lengde n krever rekursiv lukning n produktive iterasjoner (anker + n−1 ekspansjoner) pluss 1 stopp-iterasjon.

A og B er for få — de gir oss bare {1} og {1,2}, ikke hele transitive lukningen. C glemmer den finale tomme iterasjonen som stopper rekursjonen.

Pensum: 3D — Transitive lukninger

To grunner — én for ytelse, én for korrekthet.

Ytelse. UNION ALL har ingen deduplisering. UNION ville måttet sortere/hashe hele det akkumulerte resultatet etter hver iterasjon for å fjerne duplikater — det skalerer dårlig på store grafer.

Korrekthet. SQL-standarden definerer WITH RECURSIVE-semantikken med UNION ALL. Med UNION ville fixpoint-iterasjonen vært semi-naïv på en annen måte og kunne ikke garantere terminering i alle dialekt — det er rett og slett ikke standardformen.

Men hva med sykler? Hvis grafen har en syklus 1 → 2 → 3 → 1, vil UNION ALL faktisk produsere infinitt mange duplikater og aldri nå fixpoint. Løsningen er ikke å bytte til UNION — det er å holde besøkt-mengde manuelt:

WITH RECURSIVE walk(node, path) AS (
  SELECT 1, ARRAY[1]
  UNION ALL
  SELECT e.b, path || e.b
  FROM edge e, walk w
  WHERE e.a = w.node AND e.b <> ALL(path)
) SELECT * FROM walk;

Path-arrayet hindrer å besøke samme node to ganger. Det er studentens ansvar — DBMSet beskytter deg ikke mot syklisk infinitt rekursjon utover en max-iterasjonsgrense (PostgreSQL: 100, kan endres).

Pensum: 3D — WITH RECURSIVE

En deterministisk funksjon returnerer alltid samme verdi for samme input — ingen sideeffekter, ingen referanser til klokka, tilfeldighet eller tabellinnhold.

Eksempler:

• Deterministisk: square(x) = x*x, upper('foo').
• Ikke-deterministisk: now(), random(), en funksjon som leser fra en tabell som kan endres.

Hvorfor bryr DBMS-en seg?

• Funksjons-baserte indekser. En indeks på UPPER(name) krever at UPPER er deterministisk; ellers kan indeksen bli usynkronisert med tabellen.
• Materialized views stoler på at deterministiske funksjoner kan rekalkuleres ved refresh.
• Optimizer-friheter. DBMS-en kan cache resultater, fjerne kall, eller flytte funksjonskall ut av løkker — men bare hvis den er deterministisk.
• Replikering. Statement-based replikering krever determinisme; en NOW() ville gitt forskjellige verdier på master og slave.

SQL-standarden bruker også STRICT (returnerer NULL hvis et argument er NULL) som en separat egenskap; PostgreSQL har VOLATILE (default), STABLE og IMMUTABLE som tre nivåer.

Pensum: 3D — Funksjoner

Standard SQL/PSM-syntaks:

IF condition THEN
  -- ...
ELSEIF other_condition THEN
  -- ...
ELSE
  -- ...
END IF;

WHILE condition DO
  -- ...
END WHILE;

Også LOOP … END LOOP (uendelig løkke, exit med LEAVE label) og FOR var IN cursor DO … END FOR for cursor-iterasjon.

RETURN value; avslutter en funksjon og returnerer en verdi. I prosedyrer brukes RETURN; uten verdi for å avslutte tidlig.

Hver dialekt har sin egen variant: PL/pgSQL bruker $$-blokker og RAISE EXCEPTION; T-SQL bruker BEGIN…END-blokker uten $$; Oracle PL/SQL bruker BEGIN…END; og EXCEPTION WHEN…. Pensumet fokuserer på SQL/PSM-standardformen, men eksamen kan godta små variasjoner.

Hovedpoenget: stored procedures er turing-komplette programmeringsspråk — du kan skrive løkker, betingelser, lokale variabler og feilhåndtering, alt utført på serveren uten å sende data frem og tilbake til klienten.

Pensum: 3D — Kontrollflyt

Hovedregel: Hvis logikken returnerer én verdi (eller en relasjon) som skal komponeres inn i en SQL-spørring, skriv den som funksjon. Hvis den utfører flere tabellendringer, skriv den som prosedyre.

Tabellen sammenligner:

• Returnerer skalar/tabell? Funksjon ✓ — du kan skrive SELECT name, calc_bonus(salary) FROM emp. Prosedyre ✗.
• Endrer flere tabeller? Prosedyre ✓ — det er hva CALL og OUT-parametre er til for. Funksjon kan ofte tekniskt sett (PostgreSQL), men det bryter konvensjonen og gir uventede ytelseskonsekvenser.
• Skal kalles fra applikasjonskode? Begge fungerer; prosedyrer er litt enklere å transaksjonsstyre.
• Kalles inne i indekser eller views? Bare funksjoner — og bare hvis de er DETERMINISTIC.
• Atomicitet? Funksjoner kjører innenfor den ytre transaksjonen. Prosedyrer kan eksplisitt COMMIT/ROLLBACK i mange dialekter.

Konkret eksempel: calc_tax(amount, region) → funksjon. register_order(customer, items[]) → prosedyre.

Fellen: programmerere fra prosedurale språk lager altfor lett alt som prosedyrer fordi det føles imperativt. Resultatet blir et N+1-mønster der applikasjonskoden iterativt kaller en prosedyre per rad. Riktig svar er ofte å skrive logikken som en funksjon og bruke den i ett enkelt SET-basert SQL-uttrykk.

Pensum: 3D — Funksjon vs prosedyre i bruk