In [1]:
%load_ext sql
In [2]:
%sql mysql://steinam:steinam@localhost/versicherung_complete
Out[2]:
In [ ]:
%%sql
-- meine Lösung
select distinct(Land) from Fahrzeughersteller;
In [3]:
%%sql
-- deine Lösung
SELECT DISTINCT Land
FROM fahrzeughersteller
Out[3]:
In [18]:
%%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[18]:
In [95]:
%%sql
-- deine Lösung
select
f.Bezeichnung,
( select count(f.ID)) as Anzahl
from fahrzeugtyp ft
where
ft.ID = f.Fahrzeugtyp_ID )# Tabellenverknüpfung
from fahrzeugtyp f
order by ft.Bezeichnung;
In [97]:
%%sql
-- meine Lösung
select Name, vorname, Bezeichnung from Mitarbeiter inner join Abteilung
on Mitarbeiter.Abteilung_ID = Abteilung.ID
where Abteilung.Ort in('Dortmund', 'Bochum')
Out[97]:
In [96]:
%%sql
select
concat(m.Name, ' ',m.Vorname) as Mitarbeiter, # Zusammenführung von Vor- & Nachname
ab.Bezeichnung as Abteilung
from mitarbeiter m, abteilung ab
where
m.Abteilung_ID = ab.ID and
upper(ab.Ort) in ('DORTMUND', 'BOCHUM'); # Ort in Upper-case Buchstaben selektieren um Matchquote zu erhöhen
Out[96]:
In [28]:
%%sql
-- meine Lösung
select fahrzeughersteller.id, year(Datum), 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 zuordnung_sf_fz.Schadensfall_ID = schadensfall.ID
group by fahrzeughersteller.id, year(Datum)
Out[28]:
In [99]:
%%sql
-- deine Lösung
select
f.id,
year(s.Datum) as Jahr, # Verwendung von der Systemfunktin year() um das Datum zu konvertieren
(select min(Schadenshoehe) from schadensfall where year(Datum) = Jahr ) as Min, # Subselect für min
(select max(Schadenshoehe) from schadensfall where year(Datum) = Jahr ) as MAX # Subselect für max
# Berechnung der Differenz (Max - Min)
from fahrzeug f, zuordnung_sf_fz z, schadensfall s
where
z.Fahrzeug_ID = f.ID and
s.ID = z.Schadensfall_ID
group by f.ID, year(s.Datum);
Out[99]:
In [48]:
%%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[48]:
In [100]:
%%sql
-- deine Lösung
select
concat(m.Name, ' ',m.Vorname) as Mitarbeiter,
d.Kennzeichen,
fh.Name as Hersteller
from mitarbeiter m, dienstwagen d, fahrzeugtyp ft, fahrzeughersteller fh
where
d.Mitarbeiter_ID = m.ID and
ft.ID = d.Fahrzeugtyp_ID and
fh.ID = ft.Hersteller_ID and
upper(fh.Name) = 'OPEL';
Out[100]:
In [50]:
%%sql
-- meine Lösung
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[50]:
In [101]:
%%sql
-- deine Lösung
select
f.ID ,
f.Kennzeichen,
s.Schadenshoehe
from fahrzeug f, zuordnung_sf_fz z, schadensfall s
where
z.Fahrzeug_ID = f.ID and
s.ID = z.Schadensfall_ID and
s.Schadenshoehe > ( select avg(Schadenshoehe) from schadensfall ) # Durchschnitt durch Subselect ermitteln
group by f.ID;
Out[101]:
In [90]:
%%sql
select Mitarbeiter.Name, Mitarbeiter.Geburtsdatum
from Mitarbeiter
where Geburtsdatum > (select avg(Geburtsdatum) from Mitarbeiter)
order by Mitarbeiter.Name
-- oder anders
-- where (now() - Geburtsdatum) > (select now() - (select avg(geburtsdatum) from mitarbeiter);
Out[90]:
In [103]:
%%sql
select
concat(m.Name, ' ',m.Vorname) as Mitarbeiter,
m.Geburtsdatum
from mitarbeiter m
where
m.Geburtsdatum > ( select avg(Geburtsdatum) from mitarbeiter);
Out[103]: