5a) Hur många kommuner finns det?
SELECT COUNT(*) FROM Kommuner;
5b) Vad är den sammanlagda folkmängden av alla län?
SELECT SUM("folkmängd") FROM "Län";
5c) Hur många tätorter har mer än 100000 invånare?
SELECT COUNT(*) FROM "Tätorter" WHERE "folkmängd" > 100000;
5d) Vad är den genomsnittliga folkmängden för alla tätorter?
SELECT AVG("folkmängd") FROM "Tätorter";
5e) Hur många kommuner finns det i Örebro län?
Ett alternativ:SELECT COUNT(*) FROM Kommuner, "Län" WHERE Kommuner."län" = "Län".kod AND "Län".namn = 'Örebro län';
SELECT COUNT(*) FROM Kommuner WHERE "län" IN (SELECT kod FROM "Län" WHERE namn = 'Örebro län');
5f) Vad är den genomsnittliga folkmängden för tätorterna i Örebro kommun?
SELECT AVG("Tätorter"."folkmängd")
FROM "Tätorter", Kommuner, "Län"
WHERE "Tätorter".kommun = Kommuner.kod
AND Kommuner."län" = "Län".kod
AND "Län".namn = 'Örebro län';
Ett alternativ:
SELECT AVG("folkmängd")
FROM "Tätorter"
WHERE kommun IN (SELECT kod
FROM Kommuner
WHERE "län" IN (SELECT kod
FROM "Län"
WHERE namn = 'Örebro län'));
5g) Hur många tätorter ligger i kommuner som har mindre än 3000 invånare?
SELECT COUNT(*) FROM "Tätorter", Kommuner WHERE "Tätorter".kommun = Kommuner.kod AND Kommuner."folkmängd" < 3000;
5h) Vad heter Sveriges minsta kommun (i folkmängd räknat)?
SELECT namn
FROM Kommuner
WHERE "folkmängd" = (SELECT MIN("folkmängd") FROM Kommuner);
5i) Vad heter den tätort som ligger på högst höjd över havet (elevation)?
SELECT namn FROM "Tätorter" WHERE elevation = (SELECT MAX(elevation) FROM "Tätorter");
5j) Vad heter den sydligaste tätorten?
SELECT namn FROM "Tätorter" WHERE latitud = (SELECT MIN(latitud) FROM "Tätorter");
5k) Vad heter den östligaste tätorten?
SELECT namn FROM "Tätorter" WHERE longitud = (SELECT MAX(longitud) FROM "Tätorter");
5l) Hur många tätorter ligger öster om Stockholm?
SELECT COUNT(*) FROM "Tätorter" WHERE longitud > (SELECT longitud FROM "Tätorter" WHERE namn = 'Stockholm');
5m) Vad heter den största tätorten i Stockholms län?
SELECT namn
FROM "Tätorter"
WHERE "folkmängd" = (SELECT MAX("Tätorter"."folkmängd")
FROM "Tätorter", Kommuner, "Län"
WHERE "Tätorter".kommun = Kommuner.kod
AND Kommuner."län" = "Län".kod
AND "Län".namn = 'Stockholms län');
Nu börjar frågorna bli lite krångliga...
Hör gärna av er om ni kommer på enklare, vackrare, rättare eller mer lättbegripliga SQL-frågor än de här lösningsförslagen! thomas.padron-mccarthy@oru.se
5n) Vad heter det nordligaste länet? (Använd residensstadens koordinater!)
SELECT namn
FROM "Län"
WHERE residensstad IN (SELECT kod
FROM "Tätorter"
WHERE latitud = (SELECT MAX(latitud)
FROM "Tätorter", "Län"
WHERE "Tätorter".kod = "Län".residensstad));
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.)
CREATE VIEW "AntalTätorterPerKommun" AS SELECT Kommuner.kod AS kommun, COUNT(*) AS "tätorter" FROM "Tätorter", Kommuner WHERE "Tätorter".kommun = Kommuner.kod GROUP BY Kommuner.kod; SELECT "Tätorter".namn, Kommuner.namn FROM "Tätorter", Kommuner WHERE "Tätorter".kommun = Kommuner.kod AND Kommuner.kod IN (SELECT kommun FROM "AntalTätorterPerKommun" WHERE "tätorter" = 1);
I en del databashanterare, till exempel Microsoft SQL Server, kan man använda sig av Common Table Expressions (CTE), som är vyer som bara definieras och existerar inom en viss SQL-fråga. Då slipper man skräpa ner databasens schema med en global vy, som AntalTätorterPerKommun ovan. Exempel:
WITH "AntalTätorterPerKommun" AS ( SELECT Kommuner.kod AS kommun, COUNT(*) AS "tätorter" FROM "Tätorter", Kommuner WHERE "Tätorter".kommun = Kommuner.kod GROUP BY Kommuner.kod ) SELECT "Tätorter".namn, Kommuner.namn FROM "Tätorter", Kommuner WHERE "Tätorter".kommun = Kommuner.kod AND Kommuner.kod IN (SELECT kommun FROM "AntalTätorterPerKommun" WHERE "tätorter" = 1);
5p) Vilken kommun innehåller flest tätorter?
SELECT namn FROM Kommuner WHERE kod IN
(SELECT kommun FROM "AntalTätorterPerKommun" WHERE "tätorter" =
(SELECT MAX("tätorter") FROM "AntalTätorterPerKommun"));
Samma fråga, men kanske tydligare uppställd:
SELECT namn
FROM Kommuner
WHERE kod IN (SELECT kommun
FROM "AntalTätorterPerKommun"
WHERE "tätorter" = (SELECT MAX("tätorter")
FROM "AntalTätorterPerKommun"));
5q) I vilken kommun bor det flest personer i tätorterna?
CREATE VIEW "AntalInvånareITätorterPerKommun" AS
SELECT Kommuner.kod AS kommun, SUM("Tätorter"."folkmängd") AS "invånare"
FROM "Tätorter", Kommuner
WHERE "Tätorter".kommun = Kommuner.kod
GROUP BY Kommuner.kod;
SELECT namn
FROM Kommuner
WHERE kod IN (SELECT kommun
FROM "AntalInvånareITätorterPerKommun"
WHERE "invånare" = (SELECT MAX("invånare")
FROM "AntalInvånareITätorterPerKommun"));
Alternativt, med en CTE:
WITH "AntalInvånareITätorterPerKommun" AS
(
SELECT Kommuner.kod AS kommun, SUM("Tätorter"."folkmängd") AS "invånare"
FROM "Tätorter", Kommuner
WHERE "Tätorter".kommun = Kommuner.kod
GROUP BY Kommuner.kod
)
SELECT namn
FROM Kommuner
WHERE kod IN (SELECT kommun
FROM "AntalInvånareITätorterPerKommun"
WHERE "invånare" = (SELECT MAX("invånare")
FROM "AntalInvånareITätorterPerKommun"));
5r) I vilket län bor det flest personer i tätorterna?
CREATE VIEW "AntalInvånareITätorterPerLän" AS
SELECT "Län".kod AS "län", SUM("Tätorter"."folkmängd") AS "invånare"
FROM "Tätorter", Kommuner, "Län"
WHERE "Tätorter".kommun = Kommuner.kod
AND Kommuner."län" = "Län".kod
GROUP BY "Län".kod;
SELECT namn
FROM "Län"
WHERE kod IN (SELECT "län"
FROM "AntalInvånareITätorterPerLän"
WHERE "invånare" = (SELECT MAX("invånare")
FROM "AntalInvånareITätorterPerLän"));
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?)
SELECT Kommuner.kod, Kommuner.namn, Kommuner."folkmängd" AS "kommunfolkmängden", SUM("Tätorter"."folkmängd") AS "tätortssumman"
FROM "Tätorter", Kommuner
WHERE "Tätorter".kommun = Kommuner.kod
GROUP BY Kommuner.kod, Kommuner.namn, Kommuner."folkmängd"
HAVING SUM("Tätorter"."folkmängd") < Kommuner."folkmängd";
Det gäller nästan alla Sveriges kommuner! Det är inget konstigt. En del människor bor helt enkelt på landsbygden, utanför tätorterna.
SQL-frågan ovan tar inte med kommuner som saknar tätorter. För att få med dem, behövs en yttre join. Så här kan man behöva skriva:
CREATE VIEW "AntalInvånareITätorterPerKommun" AS
SELECT Kommuner.kod, Kommuner.namn, Kommuner."folkmängd" AS "kommunfolkmängden", COALESCE(SUM("Tätorter"."folkmängd"), 0) AS "tätortssumman"
FROM Kommuner LEFT OUTER JOIN "Tätorter" ON Kommuner.kod = "Tätorter".kommun
GROUP BY Kommuner.kod, Kommuner.namn, Kommuner."folkmängd";
SELECT * FROM "AntalInvånareITätorterPerKommun"
WHERE "kommunfolkmängden" > "tätortssumman";
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?)
SELECT Kommuner.kod, Kommuner.namn, Kommuner."folkmängd" AS "kommunfolkmängden", SUM("Tätorter"."folkmängd") AS "tätortssumman"
FROM "Tätorter", Kommuner
WHERE "Tätorter".kommun = Kommuner.kod
GROUP BY Kommuner.kod, Kommuner.namn, Kommuner."folkmängd"
HAVING SUM("Tätorter"."folkmängd") > Kommuner."folkmängd";
Vissa tätorter sträcker sig över flera kommuner. Till exempel ligger tätorten Vallentuna huvudsakligen i Vallentuna kommun, men sträcker sig även in i Täby kommun. Ett annat exempel är tätorten Stockholm, som täcker hela Stockholms, Solna och Sundbybergs kommuner, samt delar av ytterligare åtta kommuner. I databasen står varje tätort bara med en gång, med hela sin befolkning, och då i den kommun där den huvudsakligen ligger.
En viktig lärdom: Världen är ibland mer komplicerad än vad den ser ut att vara i ett databasschema!
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?)
SELECT "Län".kod, "Län".namn, "Län"."folkmängd" AS "länsfolkmängden", SUM(Kommuner."folkmängd") AS kommunsumman FROM Kommuner, "Län" WHERE Kommuner."län" = "Län".kod GROUP BY "Län".kod, "Län".namn, "Län"."folkmängd" HAVING SUM(Kommuner."folkmängd") <> "Län"."folkmängd";
En orsak till att man kan skulle kunna få rader i det här svaret är helt enkelt att det finns felaktiga data i databasen! Man skulle också kunna tänka sig kommuner som sträcker sig över en länsgräns, eller områden som tillhör ett län men inte räknas in i någon kommun, men några sådana finns inte i Sverige.
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?)
CREATE VIEW Kommunfolk AS
SELECT Kommuner.kod, Kommuner.namn, Kommuner."folkmängd" AS "kommunfolkmängden", SUM("Tätorter"."folkmängd") AS "tätortssumman", Kommuner."folkmängd" - SUM("Tätorter"."folkmängd") AS skillnaden
FROM "Tätorter", Kommuner
WHERE "Tätorter".kommun = Kommuner.kod
GROUP BY Kommuner.kod, Kommuner.namn, Kommuner."folkmängd";
SELECT namn
FROM Kommunfolk
WHERE skillnaden = (SELECT MAX(skillnaden) FROM Kommunfolk);
Man skulle kanske gissa att svaret är någon kommun i Norrland, med små tätorter och vidsträckt landsbygd, men det visar sig att det är Huddinge kommun, en förort till Stockholm. Den som varit i Huddinge vet att där finns skog men kanske inte så mycket landsbygd, så svaret på SQL-frågan ovan kan verka förvånande. Men om man tittar på vilka tätorter som finns i Huddinge kommun (SELECT "Tätorter".namn FROM "Tätorter" WHERE kommun = (SELECT kod FROM Kommuner WHERE namn = 'Huddinge kommun')), märker man att de tätorter som finns där är Gladö kvarn, Vidja och Ådran, och att centralorten i kommunen, Huddinge, märkligt nog saknas! Det beror på att tätorten Huddinge växt ihop med tätorten Stockholm, så nu räknas alltihop till Stockholm, och hela den befolkningen finns listad under tätorten Stockholm, som hör till Stockholms kommun, trots att delar av tätorten alltså ligger i Huddinge kommun. För att få reda på hur många i kommunen som faktiskt bor utanför tätorter skulle vi behöva mer information än vad som finns i den här databasen.
En viktig lärdom: Det är inte alltid självklart vad datat i en databas egentligen betyder!
(SQL-frågan ovan tar inte med kommuner som saknar tätorter. För att få med dem, behövs en yttre join.)
5w) I vilket län bor det flest personer utanför tätorterna?
CREATE VIEW "Länsfolk" AS
SELECT "Län".namn, "Län"."folkmängd" AS "länsfolkmängden", SUM("Tätorter"."folkmängd") AS "tätortssumman", "Län"."folkmängd" - SUM("Tätorter"."folkmängd") AS skillnaden
FROM "Tätorter", Kommuner, "Län"
WHERE "Tätorter".kommun = Kommuner.kod
AND Kommuner."län" = "Län".kod
GROUP BY "Län".namn, "Län"."folkmängd";
SELECT namn
FROM "Länsfolk"
WHERE skillnaden = (SELECT MAX(skillnaden) FROM "Länsfolk");
SQL-frågan ovan tar inte med kommuner som saknar tätorter. För att få med dem, behövs en yttre join.