Kommentar: Oppgaven tolkes slik at for de aldersbestemte klassene vil de som er født i samme år bli plassert i samme klasse. I løpet av et kalenderår vil noen i klasse J14, for å ta et eksempel, være kun 13 år. Formuleringer av typen "de under 18 år" tolkes som de medlemmene som er plassert i klassene for 17 år og yngre.
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).
De under 18 år som løp raskere enn gjennomsnittet i sin klasse i et bestemt løp.
Det er nyttig å innføre en visning 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 visninger. Med standardoppsett kan man i stedet definere MedlemMedKlasse som en vanlig spørring.
SELECT M1.*, R1.LøpsNr, R1.Tid
FROM MedlemMedKlasse AS M1, Resultat AS R1
WHERE LøpsNr = 10
AND M1.MedlemsNr = R1.MedlemsNr
AND YEAR(CURDATE())-M1.Fødselsår < 18
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).
Her er en alternativ løsning basert på en visning som både utvider med klasse og tar med løpsresultater:
CREATE VIEW ResultaterMedKlasse(MedlemsNr, Fornavn, Etternavn, Kjønn, Fødselsår, Klasse, LøpsNr, Tid) AS
SELECT M.MedlemsNr, M.Fornavn, M.Etternavn, M.Kjønn, M.Fødselsår,
IF(YEAR(CURDATE())-M.Fødselsår < 18, CONCAT(M.Kjønn, YEAR(CURDATE())-M.Fødselsår), M.Kjønn),
R.LøpsNr, R.Tid
FROM Medlem AS M, Resultat AS R
WHERE M.MedlemsNr = R.MedlemsNr
Med en litt mer omfattende visning, så blir selve spørringen litt enklere:
SELECT *
FROM ResultaterMedKlasse AS RK1
WHERE LøpsNr = 10
AND YEAR(CURDATE())-RK1.Fødselsår < 18
AND Tid <
(
SELECT AVG( Tid )
FROM ResultaterMedKlasse AS RK2
WHERE LøpsNr = 10
AND RK1.Klasse = RK2.Klasse
)
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)
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)
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
)
)
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
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.
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.
Samtlige utlån for en bestemt person. LNr skal være parameter.
Parameterspørringer er spesielt for Access, som forklart i avsnitt 5.2.5.
SELECT *
FROM Utlån
WHERE LNr = [Skriv lånernummer:]
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
Låntakere som har lånt flest bøker siste måned.
SELECT L.LNr, L.Fornavn, L.Etternavn, COUNT(*) AS Antall
FROM Låner AS L INNER JOIN Utlån AS U ON L.LNr = U.LNr
WHERE YEAR(CURDATE()) = YEAR(Utlånsdato) AND MONTH(CURDATE()) = MONTH(Utlånsdato)
GROUP BY L.LNr, L.Fornavn, L.Etternavn
HAVING COUNT(*) >= ALL
(SELECT COUNT(*) AS Antall
FROM Låner AS L1 INNER JOIN Utlån AS U1 ON L1.LNr = U1.LNr
GROUP BY L1.LNr);
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.
Navn på forfattere som ikke er utlånt.
Vi bruker en delspørring for å finne forfattere som er utlånt og lister forfattere som ikke er med på denne listen.
SELECT DISTINCT Forfatter
FROM Bok
WHERE Forfatter NOT IN
(SELECT Forfatter
FROM Utlån AS U INNER JOIN Bok AS B ON U.ISBN = B.ISBN);
Sammenhengen mellom forlag og antall utlån som en krysstabellspørring.
Lag først et view 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.4.2.
Det er ikke opplagt hvordan man skal tolke "siste år" i oppgaveteksten. I løsningsforslaget er det tolket som tidsrommet fra nyttår og fram til dagens dato, altså når kjøringen blir utført. Kanskje kunne det vært like naturlig å regne seg 12 måneder bakover fra dagens dato?
Følgende view teller opp antall utlån for hver forfatter "siste år" (en liten "variant" av viewet laget til oppgave 2f, altså):
CREATE VIEW AntallUtlånIÅr AS
SELECT Forfatter, COUNT(*) AS AntallGangerUtlånt
FROM Utlån INNER JOIN Bok ON Utlån.ISBN = Bok.ISBN
WHERE YEAR(UtlånsDato) = YEAR(CURDATE())
GROUP BY Forfatter;
Deretter finner vi hvem som er utlånt minst dobbelt så mange ganger som gjennomsnittet:
SELECT Forfatter, AntallGangerUtlånt
FROM AntallUtlånIÅr
WHERE AntallGangerUtlånt >= (SELECT 2*AVG(AntallGangerUtlånt) FROM AntallUtlånIÅr);
Hvis spørringen kjøres mot eksempeldatabasen på nettsiden, så må man redigere utlånsdatoene for å få et ikke-tomt resultat. Det er heller ingen forfattere som er utlånt nok ganger til å komme med, så igjen må man legge til eksempeldata.
SELECT ISBN, Tittel
FROM Bok
WHERE ISBN NOT IN (
SELECT ISBN
FROM Utlån
WHERE Utlånsdato >= DATE_ADD(CURDATE(), INTERVAL -5 YEAR)
);
Løsningsforslag er ikke laget ennå.
Dagplan og Ukeplan.
Viewet 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.
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
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.
Er viewet Varekategorier oppdaterbart?
Viewet 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.
Viewet DyreVarer er ikke oppdaterbart i henhold til definisjonen beskrevet på side 124 i læreboka fordi den inneholder mengdefunksjoner. Man kan likevel argumentere for at det i teorien er oppdaterbart, fordi det til enhver redigering via viewet er gitt hva som skal endres i den underliggende tabellen. Test gjerne ut hva som skjer i forskjellige systemer!
Sannhetsverditabell for NOT.
Løsningsforslag er ikke laget ennå.
Samlet salg for hver uke i 2019 og kumulativt gjennomsnitt.
Løsningsforslag er ikke laget ennå.