In [1]:
%load_ext sql
In [2]:
%sql mysql://steinam:steinam@localhost/versicherung_complete
Out[2]:
In [5]:
%%sql
-- meine Lösung
select distinct(Land) from Fahrzeughersteller;
Out[5]:
In [125]:
%%sql
-- deine Lösung
select fahrzeughersteller.Land
from fahrzeughersteller
group by fahrzeughersteller.Land
;
Out[125]:
In [3]:
%%sql
-- meine Lösung
select fahrzeugtyp.Bezeichnung, count(fahrzeug.iD) as Anzahl
from fahrzeugtyp left join fahrzeug
on fahrzeugtyp.id = fahrzeug.fahrzeugtyp_id
group by fahrzeugtyp.bezeichnung
having count(Anzahl) > 2
Out[3]:
In [70]:
%%sql
select *, (select count(*) from fahrzeug where fahrzeug.fahrzeugtyp_id = fahrzeugtyp.id) as Fahrzeuge
from fahrzeugtyp
having Fahrzeuge > 2
order by fahrzeugtyp.bezeichnung;
Out[70]:
In [72]:
%%sql
-- meine Lösung
-- select ID from Abteilung where Abteilung.Ort = 'Dortmund' or abteilung.Ort = 'Bochum'
select Name, vorname, Bezeichnung, Abteilung.ID, Mitarbeiter.Abteilung_ID, Abteilung.Ort from Mitarbeiter inner join Abteilung
on Mitarbeiter.Abteilung_ID = Abteilung.ID
where Abteilung.Ort in('Dortmund', 'Bochum')
order by Name
Out[72]:
In [74]:
%%sql
-- deine Lösung
select mitarbeiter.Name, mitarbeiter.Vorname,
(select abteilung.bezeichnung from abteilung where abteilung.id = mitarbeiter.abteilung_id) as Abteilung,
(select abteilung.ort from abteilung where abteilung.id = mitarbeiter.abteilung_id) as Standort
from mitarbeiter having Standort = "Dortmund" or Standort = "Bochum";
Out[74]:
In [84]:
%%sql
-- meine Lösung
select fahrzeughersteller.id, year(datum) as Jahr, min(zuordnung_sf_fz.schadenshoehe), max(zuordnung_sf_fz.Schadenshoehe),
(max(zuordnung_sf_fz.schadenshoehe) - min(zuordnung_sf_fz.schadenshoehe)) as Differenz
from fahrzeughersteller left join fahrzeugtyp
on fahrzeughersteller.id = fahrzeugtyp.hersteller_ID
inner join fahrzeug on fahrzeugtyp.id = fahrzeug.fahrzeugtyp_id
inner join zuordnung_sf_fz
on fahrzeug.id = zuordnung_sf_fz.fahrzeug_id
inner join schadensfall on schadensfall.id = zuordnung_sf_fz.schadensfall_id
group by fahrzeughersteller.id, year(datum)
Out[84]:
In [84]:
%%sql
-- redigierte Version von Wortmann geht
select
fahrzeughersteller.Name,
(select min(zuordnung_sf_fz.schadenshoehe) from zuordnung_sf_fz
where zuordnung_sf_fz.fahrzeug_id in(
select fahrzeug.id from fahrzeug
where fahrzeug.fahrzeugtyp_id in(
select fahrzeugtyp.id from fahrzeugtyp
where fahrzeugtyp.hersteller_id = fahrzeughersteller.id
)
)
) as Kleinste,
(select max(zuordnung_sf_fz.schadenshoehe) from zuordnung_sf_fz
where zuordnung_sf_fz.fahrzeug_id in(
select fahrzeug.id from fahrzeug
where fahrzeug.fahrzeugtyp_id in(
select fahrzeugtyp.id from fahrzeugtyp
where fahrzeugtyp.hersteller_id = fahrzeughersteller.id
)
)
) as `Groesste`
from fahrzeughersteller;
Out[84]:
4 P
In [153]:
%%sql
select Mitarbeiter.Name, dienstwagen.Kennzeichen
from Mitarbeiter inner join dienstwagen
on mitarbeiter.id = dienstwagen.Mitarbeiter_id
inner join fahrzeugtyp
on dienstwagen.fahrzeugtyp_Id = fahrzeugtyp.id
inner join fahrzeughersteller
on fahrzeugtyp.hersteller_id = fahrzeughersteller.id
where Fahrzeughersteller.NAme = 'Opel'
Out[153]:
In [87]:
%%sql
select * from mitarbeiter
where mitarbeiter.id in(
select dienstwagen.mitarbeiter_id from dienstwagen
where
dienstwagen.mitarbeiter_id = mitarbeiter.id
and dienstwagen.fahrzeugtyp_id in(
select fahrzeugtyp.id from fahrzeugtyp
where fahrzeugtyp.hersteller_id in(
select fahrzeughersteller.id from fahrzeughersteller
where fahrzeughersteller.name = "Opel"
)
)
)
Out[87]:
In [13]:
%%sql
select fahrzeug.kennzeichen, sum(schadenshoehe)
from fahrzeug inner join zuordnung_sf_fz
on fahrzeug.id = zuordnung_sf_fz.fahrzeug_id
group by fahrzeug.kennzeichen
having sum(schadenshoehe) > (select avg(schadenshoehe) from zuordnung_sf_fz)
Out[13]:
In [98]:
%%sql
-- deine Lösung Wortmann
/*
select * from fahrzeug having fahrzeug.id in(
select zuordnung_sf_zf.fahrzeugtyp_id from zuordnung_sf_zf
where zuordnung_sf_zf.schadenhoehe > ((select sum(zuordnung_sf_zf.schadenhoehe) from zuordnung_sf_zf)) / (select count(*) from zuordnung_sf_zf))
*/
select * from fahrzeug having fahrzeug.id in(
select zuordnung_sf_fz.fahrzeug_id from zuordnung_sf_fz
where zuordnung_sf_fz.schadenshoehe > ((select sum(zuordnung_sf_fz.schadenshoehe) from zuordnung_sf_fz)) / (select count(*) from zuordnung_sf_fz))
In [37]:
%%sql
select Mitarbeiter.Name, Mitarbeiter.Geburtsdatum
from Mitarbeiter
where Geburtsdatum < (select avg(Geburtsdatum) from Mitarbeiter ma)
order by Mitarbeiter.Name
Out[37]:
In [49]:
%%sql
-- geht auch
select ma.Name, ma.Geburtsdatum
from Mitarbeiter ma
where (now() - ma.Geburtsdatum) < (now() - (select avg(geburtsdatum) from mitarbeiter))
order by ma.Name;
Out[49]:
In [101]:
%%sql
-- deine Lösung Wortmann
select * from mitarbeiter
having mitarbeiter.geburtsdatum < (select sum(mitarbeiter.geburtsdatum) from mitarbeiter) / (select count(*) from mitarbeiter)
Out[101]: