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 [7]:
%%sql
select *, (select count(*) from fahrzeug
where fahrzeug.fahrzeugtyp_id = fahrzeugtyp.id) as Fahrzeuge
from fahrzeugtyp
having Fahrzeuge > 2
order by fahrzeugtyp.bezeichnung;
Out[7]:
In [8]:
%%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[8]:
In [11]:
%%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[11]:
In [12]:
%%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[12]:
In [13]:
%%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[13]:
4 P
In [14]:
%%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[14]:
In [15]:
%%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[15]:
In [16]:
%%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[16]:
In [17]:
%%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))
Out[17]:
In [19]:
%%sql
select Mitarbeiter.Name, Mitarbeiter.Geburtsdatum
from Mitarbeiter
where Geburtsdatum < (select avg(Geburtsdatum) from Mitarbeiter ma)
order by Mitarbeiter.Name
Out[19]:
In [21]:
%%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[21]:
In [22]:
%%sql
-- deine Lösung Wortmann
select * from mitarbeiter
having mitarbeiter.geburtsdatum < (select sum(mitarbeiter.geburtsdatum) from mitarbeiter) / (select count(*) from mitarbeiter)
Out[22]: