Løsningsforslag til kapittel 4

Oppgave 1a

Anta at vi har 161 varer plassert i 21 kategorier. Hvor mange rader gir spørringen SELECT * FROM Vare, Kategori?

Spørringen gir alle rader fra Vare krysskoblet med alle rader fra Kategori. Antall rader i spørreresultatet vil inneholde produktet av antall rader i Vare og antall rader i Kategori. Med 21 kategorier og 161 varer får vi 21 × 161 = 3381 rader i spørreresultatet.

Oppgave 1b

Hvor mange rader vil en likekobling av tabellene Vare og Kategori med hensyn på KatNr inneholde? Skriv SQL-koden. Hva skjer hvis noen av varene ikke er plassert i en kategori?

Spørringen knytter hver vare til sin kategori. Hvis alle varer er knyttet til en kategori så blir antall rader her 161, noe som altså tilsvarer antall rader i Vare. SQL-kode:

SELECT *
FROM Vare INNER JOIN Kategori ON Vare.KatNr = Kategori.KatNr

Med WHERE-betingelse:

SELECT *
FROM Vare, Kategori
WHERE Vare.KatNr = Kategori.KatNr

En vare som ikke er plassert i en kategori vil ha et nullmerke i kolonnen KatNr. Slike varer blir ikke med i likekoblingen. Hvis for eksempel 10 varer har nullmerke i KatNr, så vil spørringen over gi 151 rader.

Oppgave 1c

Vis alle ordrelinjer påført varenavn og ordredato.

SELECT Ordrelinje.*, Vare.Betegnelse, Ordre.Ordredato
FROM Vare INNER JOIN
       (Ordrelinje INNER JOIN Ordre
	    ON Ordrelinje.OrdreNr = Ordre.OrdreNr)
     ON Ordrelinje.VNr = Vare.VNr

Med WHERE-betingelse:

SELECT Ordrelinje.*, Vare.Betegnelse, Ordre.Ordredato
FROM Ordrelinje, Ordre, Vare
WHERE Ordrelinje.OrdreNr = Ordre.OrdreNr
AND Ordrelinje.VNr = Vare.VNr

Oppgave 1d

Utvid SQL-koden fra oppgave 1c med en ny kolonne som viser totalbeløp for hver ordrelinje.

SELECT OL.*, V.Betegnelse, O.Ordredato,
  OL.Antall * OL.PrisPrEnhet AS Beløp
FROM Vare AS V INNER JOIN
       (Ordrelinje AS OL INNER JOIN Ordre AS O
	    ON OL.OrdreNr = O.OrdreNr)
     ON OL.VNr = V.VNr

Med WHERE-betingelse:

SELECT OL.*, V.Betegnelse, O.Ordredato,
  OL.Antall * OL.PrisPrEnhet AS Beløp
FROM Ordrelinje AS OL, Ordre AS O, Vare AS V
WHERE OL.OrdreNr = O.OrdreNr
AND OL.VNr = V.VNr

Løsningene viser også bruk av kortnavn (AS).

Oppgave 1e

Vis samlet beløp hver kunde har handlet for.

SELECT K.KNr, K.Fornavn, K.Etternavn,
SUM(OL.Antall * OL.PrisPrEnhet) AS Beløp
FROM Kunde AS K INNER JOIN
       (Ordrelinje AS OL INNER JOIN Ordre AS O
		ON OL.OrdreNr = O.OrdreNr)
     ON K.KNr = O.KNr
GROUP BY K.KNr, K.Fornavn, K.Etternavn

Her kunne vi også startet med å koble Kunde og Ordre i en innerste parentes.

Med WHERE-betingelse:

SELECT K.KNr, K.Fornavn, K.Etternavn,
SUM(OL.Antall * OL.PrisPrEnhet) AS Beløp
FROM Ordrelinje AS OL, Ordre AS O, Kunde AS K
WHERE OL.OrdreNr = O.OrdreNr
AND K.KNr = O.KNr
GROUP BY K.KNr, K.Fornavn, K.Etternavn

Strengt tatt kunne vi nøyd oss med å vise kundenummeret, og det ville da ikke vært nødvendig å bruke tabellen Kunde (ettersom KNr også forekommer i tabellen Ordre). Når vi først tar med fornavn og etternavn (i SELECT) bør disse kolonnene også være med i grupperingen (i GROUP BY).

Oppgave 1f

Prøv å utvide SQL-koden fra oppgave 1e med en ny kolonne som inneholder antall ordrer for hver kunde. Hva er problemet?

Her er det fristende kun å legge til COUNT(*) i SELECT-delen i svar til oppgave 1g. Dette vil imidlertid telle opp antall ordrelinjer som hører til hver kunde. Følgende spørring teller opp antall ordre pr. kunde:

SELECT KNr, COUNT(*) AS AntallOrdrer
FROM Ordre
GROUP BY KNr

Det vi ønsker er å koble dette resultatet med resultatet av spørringen i oppgave 1g. En mulighet er å bruke teknikkene beskrevet i avsnitt 5.2.3 Spørringer mot visninger. Anta at det er laget to såkalte utsnitt (eventuelt navngitte spørringer i MS Access) BeløpPrKunde og OrdrerPrKunde som svarer til de to spørringene. En mulig løsning er da:

SELECT OrdrerPrKunde.KNr, Beløp, AntallOrdrer
FROM BeløpPrKunde INNER JOIN OrdrerPrKunde
     ON BeløpPrKunde.KNr = OrdrerPrKunde.KNr

Med WHERE-betingelse:

SELECT OrdrerPrKunde.KNr, Beløp, AntallOrdrer
FROM BeløpPrKunde, OrdrerPrKunde
WHERE BeløpPrKunde.KNr = OrdrerPrKunde.KNr

Alternativt kan man bruke en delspørring i SELECT-delen, se avsnitt 5.4.4. Problemet var altså at vi måtte bruke SQL-konstruksjoner som ennå ikke er forklart!

Oppgave 1g

Vis samlet beløp pr. ordre.

SELECT OrdreNr, SUM(Antall * PrisPrEnhet) AS TotalPrOrdre
FROM Ordrelinje
GROUP BY OrdreNr

Oppgave 1h

Lag en vareliste som for hver vare viser antall enheter på lager og samlet lagerverdi for denne varen.

SELECT VNr, Antall, Antall*Pris AS LagerVerdi
FROM Vare

Oppgave 1i

Finn samlet verdi av varelageret.

SELECT SUM(Antall*Pris) AS PrisVarelager
FROM Vare

Oppgave 1j

Finn ut hvor mye hver varekategori har solgt for. Lag en sortert liste med bestselgerne først, og få med navn på kategori i utskriften.

SELECT Kategori.KatNr, Kategori.Navn, SUM(Ordrelinje.Antall*PrisPrEnhet) AS SamletPris
FROM
  Ordrelinje INNER JOIN
    (Vare INNER JOIN Kategori
	 ON Vare.KatNr = Kategori.KatNr)
  ON Vare.VNr = Ordrelinje.VNr 
GROUP BY Kategori.KatNr, Kategori.Navn
ORDER BY SUM(Ordrelinje.Antall*PrisPrEnhet) DESC

Med WHERE-betingelse:

SELECT Kategori.KatNr, Kategori.Navn, SUM(Ordrelinje.Antall*PrisPrEnhet) AS SamletPris
FROM Vare, Ordrelinje, Kategori
WHERE Vare.VNr = Ordrelinje.VNr AND Vare.KatNr = Kategori.KatNr
GROUP BY Kategori.KatNr, Kategori.Navn
ORDER BY SUM(Ordrelinje.Antall*PrisPrEnhet) DESC

Oppgave 1k

Vis alle postnumre der det enten bor en ansatt eller en kunde. Hva med steder der det bor både en ansatt og en kunde? Hva kan du gjøre for å få med navn på poststedet?

Følgende spørring gir postnumre der det enten bor en ansatt eller en kunde, eller både en ansatt og en kunde:

SELECT DISTINCT Poststed.PostNr
FROM Ansatt, Kunde, Poststed
WHERE Ansatt.PostNr = Poststed.PostNr OR Kunde.PostNr = Poststed.PostNr

Merk bruken av DISTINCT for å fjerne duplikater. Ved å bruke AND i stedet for OR får vi bare postnumre der det både bor en ansatt og en kunde.

Oppgave 2a

Vis alle bøker som ble utlånt på en bestemt dag. Sorter listen med hensyn på boktittel.

Bruker standard datoformat i MySQL: åååå-mm-dd.

SELECT Bok.*
FROM Utlån, Bok
WHERE Utlån.ISBN = Bok.ISBN AND Utlånsdato = '2019-08-20'
ORDER BY Tittel

Oppgave 2b

Finn aldersfordelingen til bøkene i samlingen, det vil si antall bøker som er 1 år, antall bøker som er 2 år, og så videre.

SELECT Year(Date())-Year(UtgittÅr) AS Alder, COUNT(*)
FROM Bok
GROUP BY Year(Date())-Year(UtgittÅr)

Oppgave 2c

Vis de hyppigst utlånte bøkene i 2019. Sorter utskriften synkende på antall utlån. Kun bøker som er lånt ut flere enn to ganger skal med på listen.

SELECT ISBN, COUNT(*)
FROM Utlån
WHERE Year(Utlånsdato) = 2019
GROUP BY ISBN
HAVING COUNT(*) > 2
ORDER BY COUNT(*) DESC

Oppgave 2d

Lag en liste over registrerte lånere som viser antall bøker den enkelte har lånt i 2019. Også personer som ikke har lånt bøker skal med på listen.

Hvis vi kun hadde data for 2019, så ville dette vært en enkel løsning:

SELECT Låner.LNr, Fornavn, Etternavn, COUNT(UtlånsNr) AS AntallLån
FROM Låner LEFT OUTER JOIN Utlån ON Låner.LNr = Utlån.LNr
GROUP BY Låner.LNr, Fornavn, Etternavn;

Men med data for flere år inne, så må vi først filtrere ut og telle opp 2019-tall, før vi gjør ytre kobling:

SELECT Låner.LNr, Fornavn, Etternavn, AntallLån
FROM Låner LEFT OUTER JOIN
  (
    SELECT LNr, COUNT(UtlånsNr) AS AntallLån
    FROM Utlån
    WHERE Year(Utlånsdato) = 2019
    GROUP BY LNr
  ) AS Lån2019 ON Låner.LNr = Lån2019.LNr;

Oppgave 3a

Lag en spørring som viser navn og flateinnhold for alle land med flateinnhold større enn 300 000 km² og som hadde færre enn 10 millioner innbyggere i 2019. Utskriften skal være sortert synkende på flateinnhold.

SELECT Land.Navn, Flateinnhold
FROM Land, Innbyggertall
WHERE Land.Navn = Innbyggertall.Navn
AND Flateinnhold > 300000
AND Antall > 10000000
AND Årstall = 2019
ORDER BY Flateinnhold DESC

Oppgave 3b

Lag en spørring som for alle land viser navn på landet, samt navn og flateinnhold for hovedstaden.

SELECT Land.Navn, Byer.Navn, Byer.Flateinnhold
FROM Land, Byer
WHERE Land.Hovedstad = Byer.Navn

Oppgave 3c

Lag en spørring som viser navnet på alle naboland til Tyskland.

SELECT Land2
FROM Grense
WHERE Land1 = 'Tyskland'

Oppgave 3d

Lag en spørring som viser total befolkning på kloden for hvert av årene fra og med 2010 til og med 2019. Utskriften skal være sortert stigende på årstall.

SELECT Årstall, SUM(Antall) AS Befolkning
FROM Innbyggertall
WHERE Årstall BETWEEN 2010 AND 2019
GROUP BY Årstall
ORDER BY Årstall

Oppgave 3e

Lag en spørring som viser hvilke land som har flere enn to naboland.

SELECT Land1, COUNT(*) AS AntallNaboer
FROM Grense
GROUP BY Land1
HAVING COUNT(*) > 2

Oppgave 4a

Lag en liste over alle avganger fra avgangsflyplass nummer 57. Ta med navn på flyselskap, og sorter med hensyn på ankomstflyplass.

SELECT A.*, S.Navn AS Selskap, P.Navn AS Ankomst
FROM Flyavgang AS A, Flyselskap AS S, Flyplass AS P
WHERE A.SelskapsKode = S.Kode AND A.AnkPlass = P.Nr
AND AvgPlass = 57
ORDER BY P.Navn

Oppgave 4b

Vis alle avganger mellom Gardermoen og Charles deGaulle med avgang etter kl. 12. Flyplassnummeret til flyplassene er ikke kjent. Tips: Bruk en egenkobling.

SELECT A.*
FROM Flyavgang AS A, Flyplass AS P1, Flyplass AS P2
WHERE A.AvgPlass = P1.Nr AND A.AnkPlass = P2.Nr
AND P1.Navn = 'Gardermoen' AND P2.Navn = 'Charles de Gaulle' AND AvgKl > '12:00'

Oppgave 4c

Vis alle reiseruter mellom Oslo og Paris med nøyaktig en mellomlanding.

Vi må finne to avganger a1 og a2, som er slik at a1 har avgang fra Oslo, a2 har ankomst i Paris, og a1 har ankomst på samme flyplass (og før) som a2 har avgang.

SELECT A1.*, A2.*
FROM Flyavgang AS A1, Flyplass AS P1, Flyavgang AS A2,
    Flyplass AS P2
WHERE A1.AvgPlass = P1.Nr AND A2.AnkPlass = P2.Nr
AND A1.AnkPlass = A2.AvgPlass AND A1.AnkKl < A2.AvgKl
AND P1.Bynavn = 'Oslo' AND P2.Bynavn = 'Paris'

Oppgave 4d

Registrer Norwegian som nytt flyselskap med kode DY.

INSERT INTO Flyselskap(Kode, Navn)
VALUES ('DY','Norwegian')

Oppgave 4e

Endre ankomstklokkeslettet for BA 565 til 13:30.

UPDATE Flyavgang

SET AnkKl = '13:30'

WHERE Selskapskode='BA' AND LøpeNr=565

Oppgave 4f

Slett flyselskapet UpAnGo, men kopier først informasjon om selskapet til en hjelpetabell AvgangsHistorikk.

Vi må bruke flere spørringer, og avslutter da hver spørring med semikolon.

INSERT INTO AvgangsHistorikk
SELECT Date() AS Dato, FA.Selskapskode, LøpeNr,
    FS.Navn, AvgPlass, AvgKl, AnkPlass, AnkKl
FROM Flyavgang AS FA, Flyselskap AS FS
WHERE FA.Selskapskode = FS.Kode
AND FS.Navn = 'UpAndGo';

DELETE FROM Flyavgang
WHERE Navn = 'UpAndGo';

DELETE FROM Flyselskap
WHERE Navn = 'UpAndGo';