Datenbanken Loesungen

Die meisten Übungsbeispiele gelöst, hauptsächlich mit JOIN nicht mit WHERE. Fügt die WHERE Queries, wenn möglich hinzu. Gibts bessere Lösung, dann rein damit!!!

Wie viele Einwohner hat Österreich?

SELECT Population FROM Country WHERE NAME = 'Austria';

Wie viele Einwohner hat der Kontinent Europa?

SELECT SUM(Country.Population) AS EuropePopulation FROM Continent, Encompasses, Country
WHERE Continent.Name = Encompasses.Continent
AND Encompasses.Country = Country.Code
AND Continent.Name = 'Europe';

Welche Flüsse fließen durch Österreich?

SELECT Country.Name, River.Name FROM Country, Geo_river, River
WHERE Country.Code = Geo_river.Country
AND Geo_river.River = River.Name
AND Country.Name = 'Austria';

Welche Flüsse fließen durch Europa (nur den Flussnamen)?

SELECT river.Name from continent, encompasses, country, province, geo_river, river
WHERE 
continent.Name = encompasses.Continent
AND encompasses.Country = country.Code
AND country.Code = province.Country
AND province.Name = geo_river.Province
AND river.Name = geo_river.River
AND continent.Name like 'Europe'
GROUP BY river.Name
ORDER by river.Name

SELECT river.Name FROM continent
INNER JOIN encompasses ON continent.Name = encompasses.Continent
INNER JOIN country ON country.Code = encompasses.Country
INNER JOIN province ON province.Country = country.Code
INNER JOIN geo_river ON geo_river.Province = province.Name
INNER JOIN river ON river.Name = geo_river.River
WHERE continent.Name like 'Europe'
GROUP BY river.Name
ORDER BY river.Name

Wie viel Prozent der Menschen leben im Kontinent Europa?

WITH Worldpercentage AS(
SELECT SUM(Population) AS Worldpopulation FROM Country)
SELECT SUM(Country.Population), Worldpercentage.Worldpopulation,
100/ Worldpercentage.Worldpopulation * SUM(Country.Population) AS Percentage
FROM Worldpercentage, Continent
INNER JOIN Encompasses ON Continent.Name = Encompasses.Continent
INNER JOIN Country ON Encompasses.Country = Country.Code
WHERE Continent.Name = 'Europe'

Alle Länder die mit einem A anfangen

SELECT * FROM country WHERE name like 'A%';

Alle Länder die mit einem A anfangen und deren Bundesländer mit Einwohnerzahl, sortiert nach Einwohnerzahl

SELECT country.name, province.name as provincename, province.population FROM country
INNER JOIN province ON country.code = province.country
ORDER BY country.name, province.Population desc;

Alle Flüsse die durch Europa fließen (nur Flüsse und deren Länge), sortiert nach Länge

SELECT DISTINCT river.name, river.length FROM river
INNER JOIN geo_river ON geo_river.River = river.name
INNER JOIN encompasses ON encompasses.Country = geo_river.Country
WHERE encompasses.Continent like 'Europe'
ORDER BY river.Length DESC;

Alle Inseln im Pazifik mit >50% islamischem Bekenntnis

SELECT country.Name, island.name, religion.Percentage FROM island, islandin, geo_island, religion, country
WHERE
island.Name = islandin.Island AND
islandin.Sea like 'Pacific%' AND
geo_island.Island = island.name AND
geo_island.Country = religion.Country AND
country.code = geo_island.country AND
religion.Percentage > 0 AND
religion.Name = 'Roman catholic';

Alle 3000er, welche in einem Land sind, welches zu mindestens 60% römisch Katholisch ist

SELECT DISTINCT m.name, m.Height, c.Name FROM mountain as m
INNER JOIN geo_mountain as ge ON ge.Mountain = m.Name
INNER JOIN country as c ON c.Code = ge.Country
INNER JOIN religion as rrr ON rrr.Country = c.Code
WHERE m.Height >= 3000 AND rrr.Percentage >= 60 AND rrr.Name = 'Roman Catholic'
ORDER by m.height DESC;

Alle Länder mit mindestens einem See mit mindestens 100 Meter Tiefe und mindestens einem Berg mit mindestens 1500 Höhenmeter

SELECT country.name, lake.name as lake, lake.depth, mountain.name, mountain.height, geo_lake.Province, geo_mountain.Province FROM lake
INNER JOIN geo_lake ON geo_lake.Lake = lake.Name
INNER JOIN geo_mountain ON geo_mountain.Country = geo_lake.Country
INNER JOIN country ON country.Code = geo_lake.Country
INNER JOIN mountain ON mountain.Name = geo_mountain.Mountain
WHERE mountain.Height >= 1500 and lake.Depth >= 100;

Einwohnerzahl pro Religion

SELECT religion.name, sum(country.population * religion.Percentage/100)/1000000 as people FROM religion
INNER JOIN country ON country.code = religion.country
GROUP BY religion.Name order by people desc;

Alle Länder, nur die Namen und die Meere dazu (keine Null Werte)

SELECT distinct country.Name, sea.Name FROM country
INNER JOIN province ON country.Code = province.Country
INNER JOIN geo_sea ON province.Name = geo_sea.Province
INNER JOIN sea ON geo_sea.Sea = sea.Name
ORDER BY country.Name;

Alle Länder, nur die Namen, und wenn sie haben, das Meer dazu

SELECT country.name, geo_sea.Sea FROM country
LEFT JOIN geo_sea ON country.code = geo_sea.country;

SELECT distinct country.Name, sea.Name "Sea Name" FROM country
LEFT OUTER JOIN geo_sea ON geo_sea.Country = country.Code
LEFT OUTER JOIN sea ON sea.Name = geo_sea.Sea
ORDER BY country.Name

Das Unabhängigkeitsdatum von Ländern die eine Wüste haben und die ethnische Gruppe African

SELECT DISTINCT country.name, politics.Independence, ethnicgroup.name, religion.Name, religion.Percentage FROM country
INNER JOIN geo_desert ON geo_desert.Country = country.code
INNER JOIN politics ON politics.Country = country.code
INNER JOIN ethnicgroup ON ethnicgroup.country = country.code
INNER JOIN religion ON religion.Country = country.Code
WHERE politics.Independence IS NOT NULL AND ethnicgroup.name like 'African';

Welche Länder haben genau 3 Städte? Welche Länder sind dies?

WITH sub as (SELECT count(*) AS anzahl, country.Name from country, city WHERE
country.Code = city.Country
GROUP BY country.Code)
SELECT Name FROM sub WHERE anzahl = 3

Welches sind die 3 größten Städte von Amerika (Kontinent)?

SELECT city.Name, city.Population FROM continent, encompasses, country, city
WHERE continent.Name = encompasses.Continent
AND encompasses.Country = country.Code
AND city.Country = country.Code
AND continent.Name = "America"
ORDER BY city.population DESC
LIMIT 3

Was ist der größte Berg von Österreich? Wie hoch ist er?

SELECT mountain.Name, mountain.Height FROM country, geo_mountain, mountain
WHERE country.Code = geo_mountain.Country
AND geo_mountain.Mountain = mountain.Name
AND country.Name = "Austria"
ORDER BY mountain.Height DESC
LIMIT 1

Was ist der größte Berg von Europa? Wie hoch ist er?

Was ist der größte Berg pro Kontinent? Wie hoch sind diese?

-- https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column
WITH maxMountains as (SELECT continent.Name as continent, mountain.Name, mountain.Height,
RANK() OVER (PARTITION BY continent.Name ORDER BY mountain.Height DESC) AS areaRank from continent, encompasses, country, geo_mountain, mountain
WHERE continent.Name = encompasses.Continent
AND encompasses.Country = country.Code
AND geo_mountain.country = country.Code
AND mountain.Name = geo_mountain.mountain
ORDER BY continent.Name, mountain.Height)
SELECT * from maxMountains WHERE areaRank = 1;

Welches Land hat am meisten anerkannte Religionen? Wie viel sind es?

Welche Organisationen haben deren Hauptsitz in Österreich? Wie viele Mitglieder haben diese Organisationen?

Was sind die Top 10 Sprachen? Wie viele native Speaker sprechen diese Sprache?