%load_ext sql

%sql mysql://steinam:steinam@localhost/versicherung_complete

"""
Explanation: Versicherung on Paper
End of explanation
"""


%%sql 
-- meine Lösung

select distinct(Land) from Fahrzeughersteller;

%%sql
-- deine Lösung
select fahrzeughersteller.Land 
from fahrzeughersteller 
group by fahrzeughersteller.Land 
; 

"""
Explanation: Gesucht wird eine wiederholungsfreie Liste der Herstellerländer         3 P
End of explanation
"""


%%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

%%sql
select *, (select count(*) from fahrzeug where fahrzeug.fahrzeugtyp_id = fahrzeugtyp.id) as Fahrzeuge 
from fahrzeugtyp 
having Fahrzeuge > 2
order by fahrzeugtyp.bezeichnung;





"""
Explanation: 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
End of explanation
"""


%%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


%%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";




"""
Explanation: Ermittle die Namen und Vornamen der Mitarbeiter incl. Abteilungsname, deren Abteilung ihren Sitz in Dortmund oder Bochum hat.
End of explanation
"""


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

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

"""
Explanation: 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
End of explanation
"""


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



%%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"
			)
		)
)

"""
Explanation: Zeige alle Mitarbeiter und deren Autokennzeichen, die als Dienstwagen einen Opel fahren. 
                                            4 P
End of explanation
"""


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

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



"""
Explanation: Welche Fahrzeuge haben Schäden verursacht, deren Schadenssumme höher als die durchschnittliche Schadenshöhe sind.                         5 P
End of explanation
"""


%%sql

select Mitarbeiter.Name, Mitarbeiter.Geburtsdatum
from Mitarbeiter
where Geburtsdatum < (select avg(Geburtsdatum) from Mitarbeiter ma)   
order by Mitarbeiter.Name
                                   


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

%%sql
-- deine Lösung Wortmann

select * from mitarbeiter 
    having mitarbeiter.geburtsdatum < (select sum(mitarbeiter.geburtsdatum) from mitarbeiter) / (select count(*) from mitarbeiter)
                                             

"""
Explanation: Welche Mitarbeiter sind älter als das Durchschnittsalter der Mitarbeiter.     4 P
End of explanation
"""


