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?

Welche Flüsse fließen durch Österreich?

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?

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';