dbsys.info

Løsningsforslag til kapittel 5

Oppgave 1a

Medlemsliste med navn og klasse.

Løsning i MS Access:


SELECT MedlemsNr, Fornavn, Etternavn,
  IIF(YEAR(DATE())-Fødselsår < 18, Kjønn & YEAR(DATE())-Fødselsår, Kjønn) AS Klasse
FROM Medlem

Løsning i MySQL:


SELECT MedlemsNr, Fornavn, Etternavn,
  IF(YEAR(CURDATE())-Fødselsår < 18, CONCAT(Kjønn, YEAR(CURDATE())-Fødselsår), Kjønn) AS Klasse
FROM Medlem

IIF er en funksjon i MS Access. I MySQL bruker man IF eller CASE.

CONCAT er en funksjon i MySQL som slår sammen to (eller flere) tekster (konkatenerer).

Oppgave 1b

De under 18 år som løp raskere enn gjennomsnittet i sin klasse i et bestemt løp.

Det er nyttig å innføre et utsnitt som har med klasse:


CREATE VIEW MedlemMedKlasse(MedlemsNr, Fornavn, Etternavn, Kjønn, Fødselsår, Klasse) AS
SELECT MedlemsNr, Fornavn, Etternavn, Kjønn, Fødselsår,
  IF(YEAR(CURDATE())-Fødselsår < 18, CONCAT(Kjønn, YEAR(CURDATE())-Fødselsår), Kjønn)
FROM Medlem

MS Access må settes opp med SQL Server syntaks for å bruke utsnitt. Med standardoppsett kan man i stedet definere MedlemMedKlasse som en vanlig spørring.


SELECT *
FROM MedlemMedKlasse AS M1, Resultat AS R1
WHERE LøpsNr = 10
AND M1.MedlemsNr = R1.MedlemsNr
AND R1.Tid <
  (
    SELECT AVG( Tid )
    FROM MedlemMedKlasse AS M2, Resultat AS R2
    WHERE LøpsNr = 10
    AND M2.MedlemsNr = R2.MedlemsNr
    AND M1.Klasse = M2.Klasse
  )

Spørringen ser på løp 10 (tilfeldig valgt). I MS Access vil det være naturlig å bruke en parameterspørring. Bruk betingelse LøpsNr = [Skriv løpsnr:] (må settes inn to steder).

Oppgave 1c

Navn på vinnere.


SELECT MedlemsNr, Fornavn, Etternavn
FROM MedlemMedKlasse AS M1, Resultat AS R1
WHERE M1.MedlemsNr = R1.MedlemsNr
AND Tid <= (SELECT MIN(Tid)
               FROM MedlemMedKlasse AS M2, Resultat AS R2
               WHERE M2.MedlemsNr = R2.MedlemsNr AND
               R1.LøpsNr = R2.LøpsNr AND M1.Klasse = M2.Klasse)

Oppgave 1d

Utøvere som ikke har deltatt i noe løp.


SELECT *
FROM Medlem AS M
WHERE NOT EXISTS
  (SELECT *
   FROM Resultat AS R
   WHERE M.MedlemsNr = R.MedlemsNr)

Oppgave 1e

Utøvere som har deltatt i alle 400 metersløp siste år.

For slike utøvere skal det ikke finnes noe løp som er slik at det ikke finnes en rad i Resultat for dette løpet og denne utøveren:


SELECT *
FROM Medlem AS M
WHERE NOT EXISTS
  (
    SELECT *
    FROM Løp AS L
    WHERE L.Distanse = 400 AND NOT EXISTS
      (
        SELECT *
        FROM Resultat AS R
        WHERE L.LøpsNr = R.LøpsNr AND M.MedlemsNr = R.MedlemsNr
      )
  )

Oppgave 1f

Navn og alder for de 10 beste i et bestemt løp.


SELECT M.MedlemsNr, M.Fornavn, M.Etternavn, YEAR(CURDATE())-M.Fødselsår AS Alder
FROM Medlem AS M, Resultat AS R
WHERE M.MedlemsNr = R.MedlemsNr AND R.LøpsNr = 5
ORDER BY Tid DESC
LIMIT 10

I MS Access brukes TOP 10 i SELECT i stedet for LIMIT 10. I Oracle brukes delspørringer og pseudokolonnen ROWNUM, se avsnitt 5.5. Spørringen ser på løp 5 (tilfeldig valgt). I MS Access vil det være naturlig å bruke en parameterspørring.

Oppgave 1g

Flytt løpsresultater eldre enn 3 måneder til en historikktabell.

Antar at historikktabellen er gitt navn GamleResultater og har samme struktur som Resultat.


INSERT INTO GamleResultater
SELECT *
FROM Resultat
WHERE LøpsNr IN
  (SELECT LøpsNr
   FROM Løp
   WHERE DateDiff('m',Dato,Date()) < 3);

DELETE FROM Resultat
WHERE LøpsNr IN (SELECT LøpsNr FROM GamleResultater);

Løsningen forutsetter at det ikke finnes rader i GamleResultater med samme LøpsNr som i de kopierte radene.

DateDiff beregner avstand mellom to datoer i MS Access, og med 1. parameter satt til 'm' får vi svaret i antall måneder. Se vedlegg A for datofunksjoner i MySQL og Oracle.

Oppgave 2a

Samtlige utlån for en bestemt person. LNr skal være parameter.

Parameterspørringer er spesielt for Access, som forklart i avsnitt 5.3.


SELECT *
FROM Utlån
WHERE LNr = [Skriv lånernummer:]

Oppgave 2b

Samtlige låntakere med deres tilhørende lån. Også låntakere som ennå ikke har lånt bøker skal med i utskriften.


SELECT L.*, U.UtlånsNr, U.ISBN, U.Utlånsdato, U.Levert
FROM Låner AS L LEFT OUTER JOIN Utlån AS U ON L.LNr = U.LNr

Oppgave 2c

Låntakere som har lånt flest bøker siste måned.

Hvis vi vet at det er nøyaktig én låntaker som har lånt flest bøker kan vi bruke gruppering og LIMIT:


SELECT L.LNr, L.Fornavn, L.Etternavn, COUNT(*) AS Antall
FROM Låner AS L, Utlån AS U
WHERE L.LNr = U.LNr
AND YEAR(CURDATE()) = YEAR(ut_dato) AND MONTH(CURDATE()) = MONTH(ut_dato)
GROUP BY L.LNr, L.Fornavn, L.Etternavn
ORDER BY COUNT(*) DESC
LIMIT 1

Generelt kan nok flere låntakere ha lånt like mange (og flest). For å få med alle kan vi bruke en delspørring i gruppebetingelsen:


SELECT L.LNr, L.Fornavn, L.Etternavn, COUNT(*) AS Antall
FROM Låner AS L, Utlån AS U
WHERE L.LNr = U.LNr
AND YEAR(CURDATE()) = YEAR(ut_dato) AND MONTH(CURDATE()) = MONTH(ut_dato)
GROUP BY L.LNr, L.Fornavn, L.Etternavn
HAVING COUNT(*) >=
  (SELECT COUNT(*) AS Antall
   FROM Låner AS L1, Utlån AS U1
   WHERE L1.LNr = U1.LNr
   GROUP BY L1.LNr)

Oppgave 2d

Antall forlag biblioteket har kjøpt bøker fra.


SELECT COUNT(DISTINCT Forlag) AS AntallForlag
FROM Bok

For verktøy som ikke støtter COUNT(DISTINCT ... ), se avsnitt 5.2.2.

Oppgave 2e

Navn på forfattere som ikke er utlånt.


SELECT DISTINCT Forfatter
FROM Bok
WHERE ISBN NOT IN (SELECT ISBN FROM Utlån)

Oppgave 2f

Sammenhengen mellom forlag og antall utlån som en krysstabellspørring.

Lag først en visning som finner antall ganger hver forfatter er utlånt:


CREATE VIEW AntallUtlån AS
SELECT Forfatter, COUNT(*) AS AntallGangerUtlånt
FROM Bok, Utlån
WHERE Bok.ISBN = Utlån.ISBN
GROUP BY Forfatter

Presentasjonen kan gjøres i et regnearkprogram. For MS Access kan man bruke TRANSFORM/PIVOT, se avsnitt 5.7.

Oppgave 3

Dagplan og Ukeplan.

Visningen Ukeplan:


CREATE VIEW Ukeplan(AnsattNr, DatoMandag, Man, Tir, Ons, Tor, Fre) AS
SELECT DP1.AnsattNr, DP1.Dato, DP1.ProsjektNr, DP2.ProsjektNr,
  DP3.ProsjektNr, DP4.ProsjektNr, DP5.ProsjektNr
FROM Dagplan AS DP1, Dagplan AS DP2, Dagplan AS DP3,
  Dagplan AS DP4, Dagplan AS DP5
WHERE Weekday(DP1.Dato,2) = 1
AND DP1.AnsattNr = DP2.AnsattNr AND DateAdd("d", 1, DP1.Dato) = DP2.Dato
AND DP2.AnsattNr = DP3.AnsattNr AND DateAdd("d", 1, DP2.Dato) = DP3.Dato
AND DP3.AnsattNr = DP4.AnsattNr AND DateAdd("d", 1, DP3.Dato) = DP4.Dato
AND DP4.AnsattNr = DP5.AnsattNr AND DateAdd("d", 1, DP4.Dato) = DP5.Dato

Fra Ukeplan til Dagplan:


SELECT AnsattNr, DatoMandag AS Dato, Man AS ProsjektNr
FROM Ukeplan
UNION
SELECT AnsattNr, DateAdd("d", 1, DatoMandag) AS Dato, Tir AS ProsjektNr
FROM Ukeplan
UNION
SELECT AnsattNr, DateAdd("d", 2, DatoMandag) AS Dato, Ons AS ProsjektNr
FROM Ukeplan
UNION
SELECT AnsattNr, DateAdd("d", 3, DatoMandag) AS Dato, Tor AS ProsjektNr
FROM Ukeplan
UNION
SELECT AnsattNr, DateAdd("d", 4, DatoMandag) AS Dato, Fre AS ProsjektNr
FROM Ukeplan

Denne oppgaven var nok i overkant vanskelig.

Oppgave 4

Lag spørring med samme effekt direkte mot tabellen Vare.


SELECT VNr, Betegnelse AS Navn
FROM Vare
WHERE Antall < 5
AND Pris > 100
ORDER BY Betegnelse

Oppgave 5

Lag spørring med samme effekt direkte mot tabellen Vare.


SELECT KatNr, COUNT(*) AS Antall
FROM Vare
GROUP BY KatNr
HAVING COUNT(*) >

Merk hvordan WHERE-betingelsen blir omgjort til en gruppebetingelse.

Oppgave 6

Er visningen Varekategorier oppdaterbar?

Utsnittet Varekategorier inneholder ikke primærnøkkelen og er ikke oppdaterbart. Fra en rad i Varekategorier er det ikke mulig entydig å identifisere en rad i den underliggende tabellen Varer.

Utsnittet DyreVarer er ikke oppdaterbart i henhold til definisjonen beskrevet på side 117 i læreboka fordi den inneholder mengdefunksjoner. Man kan likevel argumentere for at det i teorien er oppdaterbart, fordi det til enhver redigering via utsnittet er gitt hva som skal endres i den underliggende tabellen. Test gjerne ut hva som skjer i forskjellige systemer!