6.4 SQL – avanserte spørringer og JOIN

SQL – avanserte spørringer og sammenkoblinger

Hittil har vi jobbet med data fra én tabell om gangen. Men den virkelige kraften i relasjonsdatabaser ligger i muligheten til å koble sammen data fra flere tabeller. Tenk deg at du vil se elevnavn sammen med fagnanvn og karakterer – den informasjonen er fordelt over tre tabeller (elever, fag og karakterer). For å sette dette sammen trenger vi JOIN.

I tillegg skal vi lære å beregne statistikk med aggregeringsfunksjoner (telle, summere, finne gjennomsnitt), gruppere data med GROUP BY, filtrere grupper med HAVING, og bruke underspørringer for å løse komplekse dataspørsmål.

Disse teknikkene er det som gjør SQL til et kraftig analyseverktøy. Med dem kan du svare på spørsmål som «Hva er gjennomsnittskarakteren per fag?», «Hvilke elever har karakter over gjennomsnittet?» og «Hvilken klasse har flest elever med toppkarakter?».

En JOIN kobler rader fra to tabeller basert på en felles kolonne. Den vanligste formen er INNER JOIN, som returnerer bare rader der det finnes en match i begge tabellene.

Syntaks for INNER JOIN

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

Eksempel: Elever med klassenavn

I stedet for å se bare klasse_id (som er et tall), vil vi se klassenavnet:

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

Resultat:

Tabellaliaser

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

Aggregeringsfunksjoner beregner én verdi basert på mange rader. De vanligste er:

Eksempler

-- 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 bare H2024-karakterer, deretter grupperer GROUP BY etter fag, og til slutt filtrerer HAVING bort grupper med snitt under 3.5.

En subquery (underspørring) er en SELECT-setning som er nestet inne i en annen spørring. Underspørringen kjøres først, og resultatet brukes av den ytre spørringen.

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 beregner underspørringen først gjennomsnittskarakteren, og den ytre spørringen 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ørringen finner alle elev_id-er med karakter 6, og den ytre spørringen henter navnene til disse elevene.

Subquery i FROM (avledede tabeller)

-- 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 lager underspørringen en midlertidig tabell (elevsnitt) med gjennomsnitt per elev, og den ytre spørringen filtrerer denne.

Oppsummering

I dette kapittelet har du lært:

- JOIN: kobler sammen data fra flere tabeller.
- Typer JOIN: INNER, LEFT, RIGHT og FULL.
- Aggregeringsfunksjoner: COUNT, SUM, AVG, MIN og MAX.
- GROUP BY og HAVING: grupperer og filtrerer grupper.
- Subqueries: underspoerringer for komplekse spørringer.

Noekkelbegreper