%load_ext sql


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

"""
Explanation: Subselects
End of explanation
"""


%%sql 
select k.kd_id, k.kd_plz, 
    (select count(a.Au_ID) from auftrag a where a.au_kd_id = k.kd_id ) as AnzahlAuftr,
    (select count(f.`f_id`) from fahrten f, auftrag a 
         where f.f_au_id = a.au_id and a.`au_kd_id` = k.`kd_id`) as AnzahlFahrt, 
    (select sum(ts.ts_strecke) from teilstrecke ts, fahrten f, auftrag a 
         where ts.ts_f_id = f.f_id and a.au_id = f.`f_au_id` and a.`au_kd_id` = k.`kd_id`) as SumStrecke 
from kunde k order by k.kd_plz;

%sql select count(*) as AnzahlFahrten from fahrten

"""
Explanation: Sommer 2015
Datenmodell

Aufgabe
Erstellen Sie eine Abfrage, mit der Sie die Daten aller Kunden, die Anzahl deren Aufträge, die Anzahl der Fahrten und die Summe der Streckenkilometer erhalten. Die Ausgabe soll nach Kunden-PLZ absteigend sortiert sein.

Lösung
mysql
select k.kd_id, 
    (select count(a.Au_ID) from auftrag  a 
        where a.au_kd_id = k.kd_id ) as AnzahlAuftr,
    (select count(f.`f_id`) from fahrten  f, auftrag a
        where f.f_au_id = a.au_id and a.`au_kd_id` = k.`kd_id`) as AnzahlFahrt,
    (select sum(ts.ts_strecke) from teilstrecke ts, fahrten f, auftrag a
        where ts.ts_f_id = f.f_id and a.au_id = f.`f_au_id` and a.`au_kd_id` = k.`kd_id`) as SumStrecke
from kunde k
order by k.kd_plz;
End of explanation
"""


%sql select k.kd_id, k.`kd_firma`, k.`kd_plz`,  count(a.Au_ID) as AnzAuftrag,  count(f.f_id) as AnzFahrt,  sum(ts.ts_strecke) as SumStrecke from kunde k left join auftrag a on k.`kd_id` = a.`au_kd_id` left join fahrten f on a.`au_id` = f.`f_au_id` left join teilstrecke ts on ts.`ts_f_id` = f.`f_id` group by k.kd_id order by k.`kd_plz`


"""
Explanation: Warum geht kein Join ??
mysql
select k.kd_id, k.`kd_firma`, k.`kd_plz`, 
        count(a.Au_ID) as AnzAuftrag, 
        count(f.f_id) as AnzFahrt, 
        sum(ts.ts_strecke) as SumStrecke
from kunde k left join auftrag a
    on k.`kd_id` = a.`au_kd_id`
left join fahrten f
    on a.`au_id` = f.`f_au_id`
left join teilstrecke ts
    on ts.`ts_f_id` = f.`f_id`
group by k.kd_id    
order by k.`kd_plz`
End of explanation
"""


%sql select k.kd_id, k.`kd_firma`, k.`kd_plz`, a.`au_id` from kunde k left join auftrag a on k.`kd_id` = a.`au_kd_id` left join fahrten f on a.`au_id` = f.`f_au_id` left join teilstrecke ts on ts.`ts_f_id` = f.`f_id` order by k.`kd_plz`

"""
Explanation: Der Ansatz mit Join funktioniert in dieser Form nicht, da spätestens beim 2. Join die Firma Trappo mit 2 Datensätzen aus dem 1. Join verknüpft wird. Deshalb wird auch die Anzahl der Fahren verdoppelt. Dies wiederholt sich beim 3. Join.
Die folgende Abfrage zeigt ohne die Aggregatfunktionen das jeweilige Ausgangsergebnis
mysql
select k.kd_id, k.`kd_firma`, k.`kd_plz`, a.`au_id`
from kunde k left join auftrag a
    on k.`kd_id` = a.`au_kd_id`
left join fahrten f
    on a.`au_id` = f.`f_au_id`
left join teilstrecke ts
    on ts.`ts_f_id` = f.`f_id`
order by k.`kd_plz`
End of explanation
"""


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

"""
Explanation: Winter 2015
Datenmodell

Hinweis: In Rechnung gibt es zusätzlich ein Feld Rechnung.Kd_ID
Aufgabe
Erstellen Sie eine SQL-Abfrage, mit der alle Kunden wie folgt aufgelistet werden, bei denen eine Zahlungsbedingung mit einem Skontosatz größer 3 % ist, mit Ausgabe der Anzahl der hinterlegten Rechnungen aus dem Jahr 2015.

Lösung
End of explanation
"""


%%sql 
select count(rechnung.`Rg_ID`), kunde.`Kd_Name`  from rechnung 
    inner join kunde on `rechnung`.`Rg_KD_ID` = kunde.`Kd_ID` 
        inner join `zahlungsbedingung`  on kunde.`Kd_Zb_ID` = `zahlungsbedingung`.`Zb_ID` 
        where `zahlungsbedingung`.`Zb_SkontoProzent` > 3.0 
        and year(`rechnung`.`Rg_Datum`) = 2015 group by Kunde.`Kd_Name`

"""
Explanation: ``mysql
select count(rechnung.Rg_ID), kunde.Kd_Namefrom rechnung inner join kunde
    onrechnung.Rg_KD_ID= kunde.Kd_IDinner joinzahlungsbedingungon kunde.Kd_Zb_ID=zahlungsbedingung.Zb_IDwherezahlungsbedingung.Zb_SkontoProzent&gt; 3.0
        and year(rechnung.Rg_Datum) = 2015
group by Kunde.Kd_Name`
```
End of explanation
"""


%%sql 
select kd.`Kd_Name`, 
(select COUNT(*) from Rechnung as R 
     where R.`Rg_KD_ID` = KD.`Kd_ID` and year(R.`Rg_Datum`) = 2015) as Anzahl
from Kunde kd inner join `zahlungsbedingung`  
    on kd.`Kd_Zb_ID` = `zahlungsbedingung`.`Zb_ID` 
        and `zahlungsbedingung`.`Zb_SkontoProzent` > 3.0

"""
Explanation: Es geht auch mit einem Subselect
``mysql
    select kd.Kd_Name, 
        (select COUNT(*) from Rechnung as R
            where R.Rg_KD_ID= KD.Kd_IDand year(R.Rg_Datum`) = 2015)
from Kunde kd inner join `zahlungsbedingung` 
on kd.`Kd_Zb_ID` = `zahlungsbedingung`.`Zb_ID`

and zahlungsbedingung.Zb_SkontoProzent > 3.0
```
End of explanation
"""


%sql -- your code goes here

"""
Explanation: Versicherung
Zeigen Sie zu jedem Mitarbeiter der Abteilung „Vertrieb“ den ersten Vertrag (mit einigen Angaben) an, den er abgeschlossen hat. Der Mitarbeiter soll mit ID und Name/Vorname angezeigt werden.
Datenmodell Versicherung
End of explanation
"""


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

%%sql 
select min(`vv`.`Abschlussdatum`) as 'Erster Abschluss', `vv`.`Mitarbeiter_ID`
from `versicherungsvertrag` vv inner join mitarbeiter m 
    on vv.`Mitarbeiter_ID` = m.`ID`
where vv.`Mitarbeiter_ID` in ( select m.`ID` from mitarbeiter m 
    inner join Abteilung a
        on m.`Abteilung_ID` = a.`ID`) 
group by vv.`Mitarbeiter_ID`

result = _

result

"""
Explanation: Lösung
End of explanation
"""


