Versicherung on Paper


In [1]:
%load_ext sql

In [2]:
%sql mysql://steinam:steinam@localhost/versicherung_complete


Out[2]:
'Connected: steinam@versicherung_complete'
  • Gesucht wird eine wiederholungsfreie Liste der Herstellerländer 3 P

In [ ]:
%%sql 
-- meine Lösung

select distinct(Land) from Fahrzeughersteller;

In [3]:
%%sql
-- deine Lösung
SELECT DISTINCT Land
FROM fahrzeughersteller


5 rows affected.
Out[3]:
land
None
Deutschland
Frankreich
Schweden
  • Listen Sie alle Fahrzeugtypen und die Anzahl Fahrzeuge dieses Typs, aber nur, wenn mehr als 2 Fahrzeuge des Typs vorhanden sind. Sortieren Sie die Ausgabe nach Fahrzeugtypen. 4 P

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


1 rows affected.
Out[18]:
Bezeichnung Anzahl
Corsa 3

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;


(_mysql_exceptions.ProgrammingError) (1064, <NULL>) [SQL: '-- deine Lösung\nselect \n\tf.Bezeichnung, \n\t( select count(f.ID)) as Anzahl\n        from fahrzeugtyp ft\n        where \n            ft.ID = f.Fahrzeugtyp_ID )# Tabellenverknüpfung\nfrom fahrzeugtyp f\t\norder by ft.Bezeichnung;']
  • Ermittle die Namen und Vornamen der Mitarbeiter incl. Abteilungsname, deren Abteilung ihren Sitz in Dortmund oder Bochum hat.

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')


14 rows affected.
Out[97]:
Name vorname Bezeichnung
Müller Kurt Finanzbuchhaltung
Schneider Daniela Finanzbuchhaltung
Meyer Walter Anlagenbuchhaltung
Schmitz Michael Anlagenbuchhaltung
Wagner Gaby Kostenrechnung
Feyerabend Werner Kostenrechnung
Langmann Matthias Kostenplanung
Peters Michael Kostenplanung
Aagenau Karolin Lagerhaltung
Pinkart Petra Lagerhaltung
Olschewski Pjotr Produktion
Nordmann Jörg Produktion
Carlsen Zacharias Forschung und Entwicklung
Baber Yvonne Forschung und Entwicklung

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


14 rows affected.
Out[96]:
Mitarbeiter Abteilung
Müller Kurt Finanzbuchhaltung
Schneider Daniela Finanzbuchhaltung
Meyer Walter Anlagenbuchhaltung
Schmitz Michael Anlagenbuchhaltung
Wagner Gaby Kostenrechnung
Feyerabend Werner Kostenrechnung
Langmann Matthias Kostenplanung
Peters Michael Kostenplanung
Aagenau Karolin Lagerhaltung
Pinkart Petra Lagerhaltung
Olschewski Pjotr Produktion
Nordmann Jörg Produktion
Carlsen Zacharias Forschung und Entwicklung
Baber Yvonne Forschung und Entwicklung
  • Gesucht wird für jeden Fahrzeughersteller (Angabe der ID reicht) und jedes Jahr die kleinste und größte Schadenshöhe. Geben Sie falls möglich auch die Differenz zwischen den beiden Werten mit in der jeweiligen Ergebnismenge aus. Ansonsten erzeugen Sie für diese Aufgabe ein eigenes sql-Statement. 5 P

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)


6 rows affected.
Out[28]:
id year(Datum) min(zuordnung_sf_fz.schadenshoehe) max(zuordnung_sf_fz.Schadenshoehe) Differenz
2 2007 1234.50 2653.40 1418.90
3 2007 2276.85 2276.85 0.00
7 2008 731.50 731.50 0.00
9 2007 852.00 1438.75 586.75
10 2007 865.00 1983.00 1118.00
11 2007 1438.75 1438.75 0.00

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);


12 rows affected.
Out[99]:
id Jahr Min MAX
1 2008 1438.75 1983.00
2 2007 1234.50 3715.60
2 2008 1438.75 1983.00
3 2009 865.00 4092.15
4 2007 1234.50 3715.60
5 2007 1234.50 3715.60
5 2008 1438.75 1983.00
6 2009 865.00 4092.15
7 2007 1234.50 3715.60
7 2009 865.00 4092.15
24 2007 1234.50 3715.60
25 2008 1438.75 1983.00
  • Zeige alle Mitarbeiter und deren Autokennzeichen, die als Dienstwagen einen Opel fahren. 4 P

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'


2 rows affected.
Out[48]:
Name Kennzeichen
Kalman DO-WB 354
Kolic DO-WB 384

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';


2 rows affected.
Out[100]:
Mitarbeiter Kennzeichen Hersteller
Kalman Aydin DO-WB 354 Opel
Kolic Ivana DO-WB 384 Opel
  • Welche Fahrzeuge haben Schäden verursacht, deren Schadenssumme höher als die durchschnittliche Schadenshöhe sind. 5 P

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)


6 rows affected.
Out[50]:
kennzeichen sum(schadenshoehe)
BO-GH 102 2290.75
GE-AB 123 1438.75
K-XR 147 2276.85
RE-CD 456 3197.00
RE-LM 902 2486.00
RE-LM 903 2653.40

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;


4 rows affected.
Out[101]:
ID Kennzeichen Schadenshoehe
3 RE-LM 903 4092.15
4 GE-AB 123 3715.60
7 BO-GH 102 4092.15
24 K-XR 147 3715.60
  • Welche Mitarbeiter sind älter als das Durchschnittsalter der Mitarbeiter. 4 P

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);


15 rows affected.
Out[90]:
Name Geburtsdatum
Deiters 1989-08-26
Eggert 1986-07-23
Feyerabend 1982-04-01
Friedrichsen 1983-06-20
Grosser 1980-05-17
Hinkel 1977-04-16
Janssen 1974-03-15
Kalman 1976-12-17
Kolic 1971-02-14
Langmann 1976-03-28
Müller 1977-01-05
Peters 1973-11-15
Pohl 1980-10-27
Schneider 1980-02-16
Wagner 1970-01-18

In [103]:
%%sql
select 
	concat(m.Name, ' ',m.Vorname) as Mitarbeiter,
	m.Geburtsdatum
from mitarbeiter m 
where 
	m.Geburtsdatum > ( select avg(Geburtsdatum) from mitarbeiter);


15 rows affected.
Out[103]:
Mitarbeiter Geburtsdatum
Müller Kurt 1977-01-05
Schneider Daniela 1980-02-16
Wagner Gaby 1970-01-18
Feyerabend Werner 1982-04-01
Langmann Matthias 1976-03-28
Peters Michael 1973-11-15
Pohl Helmut 1980-10-27
Kalman Aydin 1976-12-17
Kolic Ivana 1971-02-14
Janssen Bernhard 1974-03-15
Hinkel Martina 1977-04-16
Grosser Horst 1980-05-17
Friedrichsen Angelina 1983-06-20
Eggert Louis 1986-07-23
Deiters Gisela 1989-08-26