Likekoblinger kan kodes på to måter i SQL: Ved bruk av WHERE-betingelser og ved bruk av INNER JOIN notasjon. Dette løsningsforslaget bruker stort sett WHERE-betingelser.
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.
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, Kategori
WHERE Vare.KatNr = Kategori.KatNr
Alternativ notasjon:
SELECT *
FROM Vare INNER JOIN Kategori ON 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.
Resterende løsningsforslag bruker koblingsbetingelser i WHERE-delen.
Vis alle ordrelinjer påført varenavn og ordredato.
SELECT Ordrelinje.*, Vare.Betegnelse, Ordre.Ordredato
FROM Ordrelinje, Ordre, Vare
WHERE Ordrelinje.OrdreNr = Ordre.OrdreNr
AND Ordrelinje.VNr = Vare.VNr
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 Ordrelinje AS OL, Ordre AS O, Vare AS V
WHERE OL.OrdreNr = O.OrdreNr
AND OL.VNr = V.VNr
Løsningen viser også bruk av kortnavn (AS).
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 Ordrelinje AS OL, Ordre AS O,
Vare AS V, Kunde AS K
WHERE OL.OrdreNr = O.OrdreNr
AND OL.VNr = V.VNr
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).
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, 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!
Vis samlet beløp pr. ordre.
SELECT OrdreNr, SUM(Antall * PrisPrEnhet) AS TotalPrOrdre
FROM Ordrelinje
GROUP BY OrdreNr
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
Finn samlet verdi av varelageret.
SELECT SUM(Antall*Pris) AS PrisVarelager
FROM Vare
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 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
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.
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 = '2015-08-20'
ORDER BY Tittel
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)
Vis de hyppigst utlånte bøkene i 2015. 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) = 2015
GROUP BY ISBN
HAVING COUNT(*) > 2
ORDER BY COUNT(*) DESC
Lag en liste over registrerte lånere som viser antall bøker den enkelte har lånt i 2015. Også personer som ikke har lånt bøker skal med på listen.
SELECT Låner.LNr, Fornavn, Etternavn, COUNT(*) AS AntallLån
FROM Låner LEFT OUTER JOIN Utlån ON Låner.LNr = Utlån.LNr
WHERE Year(Utlånsdato) = 2015
GROUP BY Låner.LNr, Fornavn, Etternavn
Lag en spørring som viser navn og flateinnhold for alle land med flateinnhold større enn 300 000 km2 og som hadde færre enn 10 millioner innbyggere i 2015. 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 = 2015
ORDER BY Flateinnhold DESC
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
Lag en spørring som viser navnet på alle naboland til Tyskland.
SELECT Land2
FROM Grense
WHERE Land1 = 'Tyskland'
Lag en spørring som viser total befolkning på kloden for hvert av årene fra og med 1980 til og med 1989. Utskriften skal være sortert stigende på årstall.
SELECT Årstall, SUM(Antall) AS Befolkning
FROM Innbyggertall
WHERE Årstall BETWEEN 1980 AND 1989
GROUP BY Årstall
ORDER BY Årstall
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
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
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 deGaulle' AND AvgKl > 12
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'
Registrer Norwegian som nytt flyselskap med kode DY.
INSERT INTO Flyselskap(Kode, Navn)
VALUES ('DY','Norwegian')
Endre ankomstklokkeslettet for BA 565 til 13:30.
UPDATE Flyavgang
SET AnkKl = '13:30'
WHERE Selskapskode='BA' AND LøpeNr=565
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';