5.2 Avansert SQL

Avansert SQL

Du kan allereie SELECT, WHERE, ORDER BY og grunnleggjande SQL. No skal vi ta steget vidare og lære teknikkar som gjer at du kan hente ut kompleks informasjon frå fleire tabellar samtidig, gruppere data, lage virtuelle tabellar og optimalisere ytinga.

I dette kapittelet lærer du:
- JOIN for å kombinere data frå fleire tabellar
- GROUP BY og HAVING for å aggregere og filtrere grupper
- Subqueries (underspørjingar) for komplekse spørjingar
- Views for å lage gjenbrukbare "virtuelle tabellar"
- Indeksar for å gjere spørjingar raskare

JOIN – å kombinere tabellar

I ein normalisert database er data spreidde over fleire tabellar. For å hente ut meiningsfull informasjon må vi ofte kombinere data frå fleire tabellar. Det gjer vi med JOIN.

INNER JOIN

-- Hent alle bøker med forfatternavnet
SELECT Bok.tittel, Forfatter.navn
FROM Bok
INNER JOIN Forfatter ON Bok.forfatterID = Forfatter.forfatterID;

LEFT JOIN (LEFT OUTER JOIN)

-- Hent alle forfattere, også de uten bøker
SELECT Forfatter.navn, Bok.tittel
FROM Forfatter
LEFT JOIN Bok ON Forfatter.forfatterID = Bok.forfatterID;

Forfattarar utan bøker vil få NULL i bok-kolonnane.

RIGHT JOIN

FULL OUTER JOIN

Flerveis JOIN

SELECT Kunde.navn, Ordre.ordreID, Produkt.produktnavn
FROM Kunde
INNER JOIN Ordre ON Kunde.kundeID = Ordre.kundeID
INNER JOIN OrdreLinjer ON Ordre.ordreID = OrdreLinjer.ordreID
INNER JOIN Produkt ON OrdreLinjer.produktID = Produkt.produktID;

Eksempel: JOIN i praksis

Gitt desse tabellane:

-- Elev (elevID, navn, klasseID)
-- Klasse (klasseID, klassenavn)
-- Karakter (karakterID, elevID, fagnavn, karakter)

Oppgåve: Hent alle elevar med klassenamn og karakterane deira.

SELECT
    Elev.navn AS elevnavn,
    Klasse.klassenavn,
    Karakter.fagnavn,
    Karakter.karakter
FROM Elev
INNER JOIN Klasse ON Elev.klasseID = Klasse.klasseID
LEFT JOIN Karakter ON Elev.elevID = Karakter.elevID
ORDER BY Elev.navn, Karakter.fagnavn;

Vi brukar:
- INNER JOIN mellom Elev og Klasse (alle elevar må ha ein klasse)
- LEFT JOIN mellom Elev og Karakter (nokre elevar kan mangle karakterar)

GROUP BY og aggregatfunksjonar

GROUP BY lèt oss gruppere rader og bruke aggregatfunksjonar på kvar gruppe.

Vanlege aggregatfunksjonar:

Grunnleggjande GROUP BY:

-- Hvor mange elever i hver klasse?
SELECT klasseID, COUNT(*) AS antall_elever
FROM Elev
GROUP BY klasseID;

-- Gjennomsnittskarakter per fag
SELECT fagnavn, AVG(karakter) AS snittkarakter
FROM Karakter
GROUP BY fagnavn;

HAVING – filtrering av grupper

-- Fag med snittkarakter over 4.0
SELECT fagnavn, AVG(karakter) AS snitt
FROM Karakter
GROUP BY fagnavn
HAVING AVG(karakter) > 4.0;

Rekkjefølgje i SQL-spørjingar:

Eksempel: GROUP BY med JOIN

La oss kombinere JOIN og GROUP BY:

-- Hvor mange bøker har hver forfatter skrevet?
SELECT
    Forfatter.navn,
    COUNT(Bok.ISBN) AS antall_bøker
FROM Forfatter
LEFT JOIN Bok ON Forfatter.forfatterID = Bok.forfatterID
GROUP BY Forfatter.forfatterID, Forfatter.navn
ORDER BY antall_bøker DESC;

Tips:
- Bruk LEFT JOIN dersom du vil inkludere forfattarar utan bøker (dei får COUNT = 0)
- GROUP BY må inkludere alle kolonnar frå SELECT som ikkje er aggregatfunksjonar
- ORDER BY kjem alltid til slutt

Subqueries (underspørjingar)

Ein subquery er ei SQL-spørjing inne i ei anna SQL-spørjing. Dei blir ofte brukte når du treng resultatet av éi spørjing som input til ei anna.

Subquery i WHERE:

-- Finn elever som har bedre snittkarakter enn gjennomsnittet
SELECT navn, AVG(karakter) AS snitt
FROM Elev
INNER JOIN Karakter ON Elev.elevID = Karakter.elevID
GROUP BY Elev.elevID, Elev.navn
HAVING AVG(karakter) > (
    SELECT AVG(karakter) FROM Karakter
);

Subquery i FROM:

-- Bruk resultatet av en spørring som en "tabell"
SELECT klassenavn, gjennomsnitt
FROM (
    SELECT
        Klasse.klassenavn,
        AVG(Karakter.karakter) AS gjennomsnitt
    FROM Elev
    JOIN Klasse ON Elev.klasseID = Klasse.klasseID
    JOIN Karakter ON Elev.elevID = Karakter.elevID
    GROUP BY Klasse.klasseID, Klasse.klassenavn
) AS klasseresultater
WHERE gjennomsnitt > 4.0;

IN og NOT IN med subquery:

-- Finn elever som har fått 6 i minst ett fag
SELECT DISTINCT navn
FROM Elev
WHERE elevID IN (
    SELECT elevID FROM Karakter WHERE karakter = 6
);

Views – virtuelle tabellar

Ei view er ei lagra SQL-spørjing som oppfører seg som ein tabell. Ho lagrar ikkje data, men gir deg ei "ferdig" spørjing du kan gjenbruke.

Opprette ei view:

CREATE VIEW ElevOversikt AS
SELECT
    Elev.navn AS elevnavn,
    Klasse.klassenavn,
    AVG(Karakter.karakter) AS snittkarakter
FROM Elev
INNER JOIN Klasse ON Elev.klasseID = Klasse.klasseID
LEFT JOIN Karakter ON Elev.elevID = Karakter.elevID
GROUP BY Elev.elevID, Elev.navn, Klasse.klassenavn;

Bruke ei view:

-- Nå kan du bruke ElevOversikt som en vanlig tabell:
SELECT * FROM ElevOversikt WHERE snittkarakter > 4.5;

Fordelar med views:

Slette ei view:

DROP VIEW ElevOversikt;

Indeksar – optimalisering

Ein indeks er ein datastruktur som gjer det raskare å søkje etter data i ein tabell. Han fungerer som stikkordregisteret bak i ei bok.

Når bruke indeksar?

Opprette ein indeks:

-- Indeks på kolonnen "navn" i Elev-tabellen
CREATE INDEX idx_elev_navn ON Elev(navn);

Indeks på fleire kolonnar:

CREATE INDEX idx_karakter_elev_fag ON Karakter(elevID, fagnavn);

Fordelar og ulemper:

Fordelar:
- Dramatisk raskare SELECT-spørjingar
- Raskare JOIN-operasjonar

Ulemper:
- Tek opp ekstra plass
- Gjer INSERT, UPDATE og DELETE litt tregare (indeksen må oppdaterast)

Tips: Ikkje lag indeks på alt. Berre på kolonnar som faktisk blir mykje brukte i spørjingar.

Slette ein indeks:

DROP INDEX idx_elev_navn;

INNER JOIN: Returnerer berre rader med matchande verdiar i begge tabellane.

LEFT JOIN: Returnerer alle rader frå venstre tabell, også dei utan match.

GROUP BY: Grupperer rader med like verdiar i éin eller fleire kolonnar.

HAVING: Filtrerer grupper etter aggregering (blir brukt med GROUP BY).

Subquery: Ei SQL-spørjing inne i ei anna spørjing.

View: Ei lagra SQL-spørjing som kan brukast som ein virtuell tabell.

Indeks: Ein datastruktur som akselererer søk i ein tabell.

Oppsummering

I dette kapittelet har du lært:

- JOIN: kombinerer tabellar (INNER, LEFT, RIGHT, FULL).
- GROUP BY og aggregatfunksjonar: COUNT, SUM, AVG, MIN, MAX.
- HAVING: filtrerer grupper.
- Subqueries: underspørjingar i WHERE og FROM.
- Views og indeksar: virtuelle tabellar og ytingsoptimalisering.

Nøkkelbegrep