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(population)
from country c 
join encompasses e on c.Code = e.Country
where e.Continent ='Europe';

-- Welche Flüsse fließen durch Österreich?

select gr.River from geo_river gr
where Country = "A";

select * from geo_river
where country = "A";

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

select river
from geo_river gr 
join country c on c.Code = gr.Country
join encompasses e on c.Code = e.Country
where e.Continent ='Europe'
group by river;

-- Wie viel Prozent der Menschen leben im Kontinent Europa?

select 100*sum(population) / (select sum(Population) from country) as populationInProzent
from country c 
join encompasses e on c.Code = e.Country
where e.Continent ='Europe' and e.Percentage > 99;

-- Alle Länder die mit einem A anfangen

select name from country
where name like "a%";

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

select name, province, population from country
where name like "a%"
order by population desc;

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

select r.name, r.length, c.Name  
from geo_river gr 
join river r on gr.River = r.name 
join country c on gr.Country = c.Code 
join encompasses e on c.Code = e.Country
where e.Continent = 'Europe' -- and e.Percentage > 50
group by name
order by length desc;

-- Alle Inseln im Pazifik mit >50% islamischem Bekenntnis

select i.Island,r.Name,r.Percentage 
from islandin i
join geo_island gi on i.island = gi.island
join religion r on gi.Country = r.Country 
where i.sea = 'Pacific Ocean' and r.Name ='Muslim' and Percentage > 50;

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

select m.Name, count(*) from mountain m 
join geo_mountain gm  on m.name = gm.Mountain
join country c on gm.Country = c.Code 
join religion r on r.Country = c.Code
where m.Height >= 3000 and r.Name  = 'Roman Catholic' and r.Percentage >= 60
group by m.name
order by m.name;

select * from geo_mountain gm ;
select * from country c ;

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

select c.Name, count(*)  from country c
join geo_lake gl on c.Code = gl.Country 
join geo_mountain gm on c.Code = gm.Country 
join lake l on gl.Lake = l.Name 
join mountain m on gm.Mountain = m.Name 
where l.`Depth` >= 100
and m.Height >= 1500
group by name
order by count(*) desc;

-- Einwohnerzahl pro Religion

select sum(population)
from country c 
join religion r on c.Code = r.Country
where r.Name = 'muslim'; 

select r.Name, round( sum(c.Population/100 * r.Percentage),2) as pop from religion r 
join country c on r.Country = c.Code
where c.Code = r.Country
group by r.Name
order by pop desc;

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

select c.Name, gs.Sea  from country c 
join geo_sea gs on c.Code = gs.Country
order by name;

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

select distinct c.Name, gs.Sea  from country c 
left join geo_sea gs on c.Code = gs.Country
order by name;

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

select distinct p.Independence, c.Name ,d.Name  from politics p 
join country c on p.Country = c.Code 
join ethnicgroup e on e.Country = c.Code 
join geo_desert gd on gd.Country = c.Code 
join desert d on gd.Desert = d.Name
where e.Name = 'African';

-- Welche Länder haben mehr als 150 Städte?

with citycount as (select co.Name, count(*) as citycount
	from country co
	join city ci on co.code = ci.Country
	group by co.Name 
	order by co.Name
)
select * from citycount
where citycount > 150;

-- Welche Länder haben genau 3 Städte? Wie heißen diese Städte?

-- Teil 1
select co.Name, count(*) as citycount from country co
join city ci on co.Code = ci.Country 
group by co.Name; 
-- Teil2
select Name from(
select co.Name, count(*) as citycount from country co
join city ci on co.Code = ci.Country 
group by co.Name 
)as subquerry
where citycount = 3;
-- Teil3
select Name, citycount from 
	(select co.Name, count(*) citycount
	from country co
	join city ci on co.code = ci.Country
	group by co.Name 	
) as tempTable
where citycount = 3
order by Name;

with threecity (coName, citycount) as (
select co.Name, count(*) citycount
from country co
join city ci on co.code = ci.Country
group by co.Name 
)
select threecity.coName, threecity.citycount from threecity
where threecity.citycount = 3;

-- population biggest 3 in america

select * from city ci 
join country co on ci.Country =co.Code
join encompasses e on e.Country = co.Code
join continent cont on cont.Name = e.Continent
where cont.Name = 'America'
order by ci.Population desc
limit 3;

-- biggest mountain in austria

select m.Name ,m.Height from mountain m 
join geo_mountain gm on gm.Mountain = m.Name 
join country c on c.Code = gm.Country
where c.Name = 'austria'
order by m.Height desc 
limit 1;

-- bigest mountain in europe limit 1

select c.Name , m.Name ,m.Height from mountain m 
join geo_mountain gm on gm.Mountain = m.Name 
join country c on c.Code = gm.Country
join encompasses e on e.Country = c.Code 
where e.Continent  = 'Europe' and e.Percentage > 50
order by m.Height desc 
limit 1;

-- concat for group for one biggest mountain in 2 countrys

with mont (Name, Country, height) as (
select m.name, gm.Country, m.height
from mountain m 
join geo_mountain gm on gm.Mountain = m.Name 
join encompasses e on e.Country = gm.Country  
where e.Continent  = 'Europe' and e.Percentage > 50
)
select mont.name, mont.height, group_concat(Country.Name SEPARATOR ' / ') countries 
from mont 
join country on mont.country = country.code
where mont.height = (select max(Height) from mont)
group by mont.name, mont.height;

-- mountains in continents with ranks

WITH mount 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 mountain.Height desc)
select distinct continent, name, height from mount WHERE mount.areaRank = 1;

-- mountains, continents , subqueries

SELECT DISTINCT big.Continent, m.Name, m.Height
FROM
	(
	SELECT Continent, MAX(mo.Height) maxH
	FROM mountain mo
	JOIN geo_mountain gm on mo.Name = gm.Mountain
	join encompasses e on gm.Country = e.Country
	WHERE e.Percentage >= 50
	group by Continent
	) big
join encompasses e on big.Continent = e.Continent
JOIN geo_mountain gm2 on e.Country = gm2.Country
JOIN mountain m ON gm2.Mountain = m.Name
WHERE e.Percentage >= 50 AND m.Height = big.maxH
ORDER BY m.Height desc;

-- mountains in continents with subqueries

WITH mont (Continent, Name, Height) AS
(
	SELECT e.Continent, mo.Name, mo.Height
	FROM mountain mo
	JOIN geo_mountain gm on mo.Name = gm.Mountain
	join encompasses e on gm.Country = e.Country
	WHERE e.Percentage >= 50	
)
SELECT DISTINCT big.Continent, mont.Name, mont.Height
FROM
(
	SELECT Continent, MAX(mont.Height) maxH
	FROM mont
	GROUP BY mont.Continent
) AS big
JOIN mont ON mont.Continent = big.Continent AND mont.Height = big.maxH
ORDER BY Height desc;

-- mountain, continent, heighest

WITH mount 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 mountain.Height desc
)
select distinct continent, name, height,areaRank from mount 
WHERE mount.areaRank = 1;

-- count religion in countrys

-- Teil 1
select c.Name, count(*) as anzahl  from religion r
join country c on c.Code = r.Country 
group by c.Name 
order by anzahl desc;

-- Teil 2
select Name, anzahl from (
select c.Name, count(*) as anzahl  from religion r
join country c on c.Code = r.Country 
group by c.Name 
order by anzahl desc)
as test
where anzahl = 5;

-- Teil 3
WITH religion_count AS (
  SELECT c.Name, COUNT(*) AS anzahl
  FROM religion r
  JOIN country c ON c.Code = r.Country
  GROUP BY c.Name
)
SELECT Name, anzahl
FROM religion_count
WHERE anzahl = (SELECT MAX(anzahl) FROM religion_count);

-- organization count

with corps as (select o.Abbreviation, o.Country as Founder  
from organization o
where country = 'A'
)
select Abbreviation, count(*) from corps
join ismember i on corps.Abbreviation = i.Organization
group by Abbreviation
;

-- organizations
select o.Abbreviation, count(*) anzahlMG 
from organization o 
join ismember i on o.Abbreviation = i.Organization 
where o.Country = 'A'
group by o.Abbreviation

-- top 10 languages

select l.name, round(sum(c.population/100 * l.Percentage), -6) as lang 
from `language` l 
join country c on l.country = c.code
where c.code = l.country
group by l.name
order by lang desc
limit 10
;

select * from `language` l ;
-- languages with subqueries
with lang2 as 
(
	with languages as (
		select 
			l.name, 
			round(sum(c.population/100 * l.Percentage)) as lang		
		from `language` l 
		join country c on l.country = c.code
		group by l.name)
	select *, rank () over (order by lang desc) as languageRank 
	from languages
)
select Name, lang
from lang2 
 where languageRank in (2,3,5);   -- between 1 and 15; -- languageRank in (2,4,6,8);