Databasteknik: Övning 5 - Sverige

Det finns lösningsförslag. Se länkarna nedan.

SQL-kommandona är provkörda i Mimer 10.0.6J och (inte alla lösningsförslagen än) i SQL Server 2012.

Mål

• Att öva på SQL-frågor, både enkla och mer avancerade.

Förberedelser

• Introduktion till frågespråket SQL (kapitel 7 i boken)
• Mer om SQL (kapitel 8 i boken)

Scenario

Utförligare beskrivning

Varje län delas sedan in i flera kommuner, till exempel Örebro kommun och Kumla kommun, som båda finns i Örebro län. Totalt finns det 290 kommuner. Varje kommun har ett unikt namn och en unik kommunkod, och en folkmängd. (Läs mer om Sveriges kommuner i Wikipedia. De har hela listan.)

Varje kommun innehåller en eller flera tätorter. Som exempel finns i Örebro kommun 14 olika tätorter: Örebro, Hovsta, Garphyttan, Odensbacken, Vintrosa, Ekeby-Almby, Stora Mellösa, Glanshammar, Norra Bro, Latorpsbruk, Ölmbrotorp, Hampetorp, Kilsmo och Askersby. Totalt finns det 1956 tätorter. Varje tätort har ett namn och en folkmängd. Det kan inte finnas två tätorter med samma namn i en och samma kommun, men i olika kommuner kan det finnas tätorter som har samma namn. För att tätorterna ska få en enkel nyckel har de en unik tätortskod. De flesta av tätorterna (men inte riktigt alla) har kartkoordinater i form av latitud och longitud, och en höjd över havet ("elevation"). (Läs mer om Sveriges tätorter i Wikipedia. De har hela listan.)

Kod är primärnyckel i samtliga tabeller.

I tabellen Län är Bokstav och Namn två olika alternativnycklar.
I tabellen Kommuner är Namn en alternativnyckel.
I tabellen Tätorter bildar Namn och Kommun en sammansatt alternativnyckel.

Län.Residensstad refererar till Tätorter.Kod
Kommuner.Län refererar till Län.Kod
Tätorter.Kommun refererar till Kommun.Kod

Arbetsgång

read all input from 'C:\Documents and Settings\tpy\Skrivbord\skapa-sverige-databasen.txt';

Det finns också en annan fil där namn på tabeller och kolumner har rensats från svenska tecken: skapa-sverige-databasen-utan-aao.txt. Där heter tabellerna Lan, Kommuner och Tatorter. Välj själv om du vill använda den i stället. Vilket är enklast?

Deluppgift 1: Rita diagram

Det vanliga när man jobbar med ER-diagram är att man utgår från den del av världen som man ska beskriva, ritar ett konceptuellt schema i form av ett diagram, och sen översätter diagrammet till tabeller. Här ska vi alltså gå åt andra hållet.

[Lösningsförslag]

Deluppgift 2: Enkel SQL med bara en tabell

2a) Vad är länsbokstaven för Skåne län?

2b) Hur många människor bor det i Åre kommun?

2c) Vad är länsbokstaven och folkmängden för Örebro län?

2d) Vilka kommuner har mindre än 3000 invånare?

2e) Vilka kommuner har namn som börjar på bokstaven Å?

2f) Vilka tätorter har namn som slutar på stad?

2g) Vilka tätorter med namn som slutar på stad har mindre än 1000 invånare? (Ledtråd: En annan formulering av samma fråga: Vilka tätorter har namn som slutar på stad och samtidigt mindre än 1000 invånare?)

2h) Vilka tätorter har antingen ett namn som slutar på stad, eller mindre än 1000 invånare (eller både och)? Sortera dem i bokstavsordning.

2i) Vilka tätorter har mellan 8000 och 9000 invånare (inklusive gränserna)?

2j) Vilka tätorter saknar vi koordinater (latitud och longitud) för? Ledtråd: is null

2k) Vilka tätorter har vi koordinaterna för? Ledtråd: is not null

2l) Vilka tätorter har vi latitud och longitud för, men inte höjden över havet?

[Lösningsförslag]

Deluppgift 3: Enkel SQL med flera tabeller

3a) Vad heter tätorterna som finns i Pajala kommun?

3b) Det finns en tätort som heter Flurkmark. Vad heter kommunen som den ligger i?

3c) Vad heter länet som Flurkmark ligger i?

3d) Vilka tätorter ligger i kommuner som har mindre än 3000 invånare?

3e) Vilka län innehåller minst en tätort som har mer än 100000 invånare?

3f) Vilka tätorter med mer än 1000 invånare ligger i kommuner som har mindre än 3000 invånare?

3g) Vilka tätorter i Västerbottens län har mer än 1000 invånare och ligger i kommuner som har mindre än 4000 invånare?

3h) Vad heter residensstaden i Kalmar län, och hur många personer bor det i den?

3i) I vilket län är Karlstad residensstad?

3j) Det finns en tätort som heter Löwenströmska lasarettet. Vad heter kommunen och länet som den ligger i?

3k) Finns det några kommuner som inte innehåller några tätorter? Vad heter de? (Kan det verkligen finnas kommuner utan tätorter? Vad skulle det kunna bero på?)

[Lösningsförslag]

Deluppgift 4: Lite mer avancerad SQL

4a) Namn på tätorter är som nämnts bara unika inom en kommun, så två eller flera tätorter i Sverige kan ha samma namn. Vilka namn gäller det? Ledtråd: Skapa två alias för tabellen Tätorter med hjälp av nyckelordet as.

[Lösningsförslag]

Deluppgift 5: SQL, med aggregatfunktioner

5a) Hur många kommuner finns det?

5b) Vad är den sammanlagda folkmängden av alla län?

5c) Hur många tätorter har mer än 100000 invånare?

5d) Vad är den genomsnittliga folkmängden för alla tätorter?

5e) Hur många kommuner finns det i Örebro län?

5f) Vad är den genomsnittliga folkmängden för tätorterna i Örebro kommun?

5g) Hur många tätorter ligger i kommuner som har mindre än 3000 invånare?

5h) Vad heter Sveriges minsta kommun (i folkmängd räknat)?

5i) Vad heter den tätort som ligger på högst höjd över havet (elevation)?

5j) Vad heter den sydligaste tätorten?

5k) Vad heter den östligaste tätorten?

5l) Hur många tätorter ligger öster om Stockholm?

5m) Vad heter den största tätorten i Stockholms län?

Nu börjar frågorna bli lite krångliga...

5n) Vad heter det nordligaste länet? (Använd residensstadens koordinater!)

5o) Vilka kommuner innehåller bara en enda tätort? Vad heter kommunerna, och tätorterna? (Tips: Man kan börja med att skapa en vy som anger antalet tätorter i de olika kommunerna.)

5p) Vilken kommun innehåller flest tätorter?

5q) I vilken kommun bor det flest personer i tätorterna?

5r) I vilket län bor det flest personer i tätorterna?

5s) Finns det någon kommun där befolkningen i kommunen är större än summan av befolkningen i kommunens tätorter? (Vad skulle det kunna bero på om det gör det?)

5t) Finns det någon kommun där befolkningen i kommunen är mindre än summan av befolkningen i kommunens tätorter? (Vad skulle det kunna bero på om det gör det?)

5u) Finns det något län där befolkningen i länet inte är lika med summan av befolkningen i länets kommuner? (Vad skulle det kunna bero på om det gör det?)

5v) I vilken kommun bor det flest personer utanför tätorterna? (Vilken kommun är det? Vad skulle det kunna bero på att det blev just den kommunen?)

5w) I vilket län bor det flest personer utanför tätorterna?

[Lösningsförslag]

Deluppgift 6 (överkurs): Ännu roligare SQL (Är det verkligen möjligt?!)

[Lösningsförslag]