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.3.
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.
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)
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.
SELECT DISTINCT Forfatter
FROM Bok
WHERE ISBN NOT IN (SELECT ISBN FROM Utlån)
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.
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.
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 visningen Varekategorier oppdaterbar?
Visningen Varekategorier inneholder ikke primærnøkkelen og er ikke oppdaterbar. Fra en rad i Varekategorier er det ikke mulig entydig å identifisere en rad i den underliggende tabellen Varer.
Visningen DyreVarer er ikke oppdaterbar i henhold til definisjonen beskrevet på side 117 i læreboka fordi den inneholder mengdefunksjoner. Man kan likevel argumentere for at den i teorien er oppdaterbar, fordi det til enhver redigering via visningen er gitt hva som skal endres i den underliggende tabellen. Test gjerne ut hva som skjer i forskjellige systemer!