6.4 SQL – avanserte spørringer og JOIN

SQL – avanserte spørjingar og samankoplingar

Hittil har vi jobba med data frå éin tabell om gongen. Men den verkelege krafta i relasjonsdatabasar ligg i moglegheita til å kople saman data frå fleire tabellar. Tenk deg at du vil sjå elevnamn saman med fagnamn og karakterar – den informasjonen er fordelt over tre tabellar (elever, fag og karakterer). For å setje dette saman treng vi JOIN.

I tillegg skal vi lære å rekne ut statistikk med aggregeringsfunksjonar (telje, summere, finne gjennomsnitt), gruppere data med GROUP BY, filtrere grupper med HAVING, og bruke underspørjingar for å løyse komplekse dataspørsmål.

Desse teknikkane er det som gjer SQL til eit kraftig analyseverktøy. Med dei kan du svare på spørsmål som «Kva er gjennomsnittskarakteren per fag?», «Kva for elevar har karakter over gjennomsnittet?» og «Kva for klasse har flest elevar med toppkarakter?».

Ein JOIN koplar rader frå to tabellar basert på ein felles kolonne. Den vanlegaste forma er INNER JOIN, som returnerer berre rader der det finst ein match i begge tabellane.

Syntaks for INNER JOIN

SELECT kolonne1, kolonne2, ...
FROM tabell1
INNER JOIN tabell2 ON tabell1.kolonne = tabell2.kolonne;

Eksempel: Elever med klassenavn

I staden for å sjå berre klasse_id (som er eit tal), vil vi sjå klassenamnet:

SELECT elever.fornavn, elever.etternavn, klasser.klassenavn
FROM elever
INNER JOIN klasser ON elever.klasse_id = klasser.klasse_id;

Resultat:

Tabellaliasar

SELECT e.fornavn, e.etternavn, k.klassenavn
FROM elever e
INNER JOIN klasser k ON e.klasse_id = k.klasse_id;

Aggregeringsfunksjonar reknar ut éin verdi basert på mange rader. Dei vanlegaste er:

Eksempel

-- Antall elever i databasen
SELECT COUNT(*) AS antall_elever FROM elever;

-- Gjennomsnittskarakter for alle
SELECT AVG(karakter) AS gjennomsnitt FROM karakterer;

-- Høyeste og laveste karakter
SELECT MAX(karakter) AS hoyeste, MIN(karakter) AS laveste FROM karakterer;

Resultat: hoyeste = 6, laveste = 2

-- Antall karakterer registrert
SELECT COUNT(*) AS antall_karakterer FROM karakterer;

Eksempel: Antall elever per klasse

SELECT klasse_id, COUNT(*) AS antall_elever
FROM elever
GROUP BY klasse_id;

Resultat:

Eksempel: Gjennomsnittskarakter per fag

SELECT f.fagnavn, AVG(k.karakter) AS snittkarakter
FROM karakterer k
INNER JOIN fag f ON k.fag_id = f.fag_id
GROUP BY f.fagnavn;

Eksempel: Antall fag per elev

SELECT e.fornavn, e.etternavn, COUNT(*) AS antall_fag
FROM karakterer k
INNER JOIN elever e ON k.elev_id = e.elev_id
GROUP BY e.elev_id, e.fornavn, e.etternavn;

Resultat:

Viktig forskjell:
- WHERE filtrerer rader før gruppering
- HAVING filtrerer grupper etter gruppering

Eksempel: Fag med gjennomsnittskarakter over 4

SELECT f.fagnavn, AVG(k.karakter) AS snittkarakter
FROM karakterer k
INNER JOIN fag f ON k.fag_id = f.fag_id
GROUP BY f.fagnavn
HAVING AVG(k.karakter) > 4;

Resultat:

Eksempel: Elever med mer enn 2 fag

SELECT e.fornavn, e.etternavn, COUNT(*) AS antall_fag
FROM karakterer k
INNER JOIN elever e ON k.elev_id = e.elev_id
GROUP BY e.elev_id, e.fornavn, e.etternavn
HAVING COUNT(*) > 2;

Kombinere WHERE og HAVING

-- Gjennomsnittskarakter per fag for termin H2024,
-- men bare fag der snittet er over 3.5
SELECT f.fagnavn, AVG(k.karakter) AS snittkarakter, COUNT(*) AS antall
FROM karakterer k
INNER JOIN fag f ON k.fag_id = f.fag_id
WHERE k.termin = 'H2024'
GROUP BY f.fagnavn
HAVING AVG(k.karakter) > 3.5;

Her filtrerer WHERE først ut berre H2024-karakterar, deretter grupperer GROUP BY etter fag, og til slutt filtrerer HAVING bort grupper med snitt under 3.5.

Ein subquery (underspørjing) er ei SELECT-setning som er nesta inne i ei anna spørjing. Underspørjinga blir køyrd først, og resultatet blir brukt av den ytre spørjinga.

Subquery i WHERE

-- Finn alle elever med karakter høyere enn gjennomsnittet
SELECT e.fornavn, e.etternavn, k.karakter
FROM karakterer k
INNER JOIN elever e ON k.elev_id = e.elev_id
WHERE k.karakter > (SELECT AVG(karakter) FROM karakterer);

Her reknar underspørjinga først ut gjennomsnittskarakteren, og den ytre spørjinga bruker dette resultatet for å filtrere.

Subquery med IN

-- Finn alle elever som har karakter 6 i et eller annet fag
SELECT fornavn, etternavn
FROM elever
WHERE elev_id IN (SELECT elev_id FROM karakterer WHERE karakter = 6);

Underspørjinga finn alle elev_id-ar med karakter 6, og den ytre spørjinga hentar namna til desse elevane.

Subquery i FROM (avleidde tabellar)

-- Finn elevenes gjennomsnittskarakter og vis bare de med snitt over 4
SELECT fornavn, etternavn, snitt
FROM (
    SELECT e.fornavn, e.etternavn, AVG(k.karakter) AS snitt
    FROM karakterer k
    INNER JOIN elever e ON k.elev_id = e.elev_id
    GROUP BY e.elev_id, e.fornavn, e.etternavn
) AS elevsnitt
WHERE snitt > 4;

Her lagar underspørjinga ein mellombels tabell (elevsnitt) med gjennomsnitt per elev, og den ytre spørjinga filtrerer denne.

Oppsummering

I dette kapittelet har du lært:

- JOIN: koplar saman data frå fleire tabellar.
- Typar JOIN: INNER, LEFT, RIGHT og FULL.
- Aggregeringsfunksjonar: COUNT, SUM, AVG, MIN og MAX.
- GROUP BY og HAVING: grupperer og filtrerer grupper.
- Subqueries: underspørjingar for komplekse spørjingar.

Nøkkelomgrep