09 - Arbeitsblatt Gruppieren

use bibliothek;

-- 1
select Autor, count(*)
from buecher
group by Autor
order by count(*);

-- 2
select Sachgebiet, count(*)
from buecher
group by Sachgebiet
order by count(*);

-- 3
select Geschlecht, count(*)
from leser
group by Geschlecht;

-- 4
select Sachgebiet, round(avg(Jahr))
from buecher
group by Sachgebiet;

-- 5
select min(timestampdiff(YEAR, geboren, CURDATE())),
       max(timestampdiff(YEAR, geboren, CURDATE())),
       Ort
from leser
group by Ort;

-- 6
select count(*), Jahr
from buecher
group by Jahr
order by Jahr;

-- 7
select Vorname, count(*)
from leser
group by Vorname
order by count(*) desc;

-- 8
select ausgestellt, count(*)
from leser
group by ausgestellt
order by count(*) desc
limit 3;

-- 9
select Verlag, count(*)
from buecher
group by Verlag
order by count(*) desc;

-- 10
select Ort
from leser
group by Ort
having count(*) < 5;

-- 11
select Ort, count(distinct Verlag)
from buecher
group by Ort
having count(*) > 1
order by count(*) desc;

-- 12
select Titel, Jahr
from buecher
where Jahr is not null
order by Jahr
limit 1;

-- 13
select avg(year(now()) - Jahr), Sachgebiet
from buecher
group by Sachgebiet;

-- 14
select Verlag, count(*)
from buecher
group by Verlag
having count(*) > 10
order by count(*) desc;

-- 15
select count(*), Ort
from buecher
group by Ort
order by count(*) desc;

-- 16
select Postleitzahl,
       group_concat(distinct Ort order by Ort),
       count(distinct Ort)
from leser
group by Postleitzahl
having count(distinct Ort) > 1
order by Postleitzahl;