<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Subselects
Step1:  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;
Step2:  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`
Step3:  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`
Step4:  Winter 2015
Datenmodell
Hinweis
Step5:  ``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`
```
Step6:  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
```
Step7:  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
Step8:  Lösung
<ASSISTANT_TASK:>
Python Code:
%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(distinct a.Au_ID) as AnzAuftrag, 
        count(distinct 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 kunde.Kd_ID, kunde.Kd_Firma, kunde.Kd_Strasse, kunde.Kd_PLZ, 
kunde.Kd_Ort, COUNT(distinct auftrag.Au_ID) AS AnzahlAuftr, COUNT(distinct fahrten.F_ID) AS AnzahlFahrt, SUM(teilstrecke.Ts_Strecke) AS SumStrecke
FROM kunde
LEFT JOIN auftrag ON auftrag.Au_Kd_ID = kunde.Kd_ID
LEFT JOIN fahrten ON fahrten.F_Au_ID = auftrag.Au_ID 
LEFT JOIN Teilstrecke ON teilstrecke.Ts_F_ID = fahrten.F_ID 
GROUP BY kunde.Kd_ID
ORDER BY kunde.Kd_PLZ desc;
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
%%sql
-- wortmann und prinz
select 
	(select count(rechnung.rg_id) from rechnung 
		where
			rechnung.rg_kd_id = kunde.kd_id
			and (select zb_skontoprozent from zahlungsbedingung where zahlungsbedingung.zb_id = kunde.kd_zb_id) > 3
			and YEAR(rechnung.rg_datum) = 2015
	) as AnzRechnungen,
	kunde.*
from kunde;
%%sql
SELECT COUNT(r.rg_id) AS AnzRechnung, k.*
FROM kunde AS k
LEFT JOIN rechnung AS r ON k.kd_id = r.Rg_KD_ID
WHERE k.kd_zb_id IN 
    (SELECT zb_id FROM zahlungsbedingung WHERE zb_skontoprozent > 3) AND YEAR(r.Rg_Datum) = 2015
GROUP BY k.Kd_ID
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`
%%sql
-- rm
SELECT m.ID, m.Name, m.Vorname, v.*
FROM versicherungsvertrag AS v
JOIN mitarbeiter AS m ON m.ID = v.Mitarbeiter_ID
WHERE v.Abschlussdatum = (SELECT min(v.Abschlussdatum) 
               FROM versicherungsvertrag AS v WHERE v.Mitarbeiter_ID = m.ID 
               )
GROUP BY v.Mitarbeiter_ID
%%sql
-- original
SELECT vv.ID as VV, vv.Vertragsnummer, vv.Abschlussdatum, vv.Art,
mi.ID as MI, mi.Name, mi.Vorname
from Versicherungsvertrag vv
right join ( select MIN(vv2.ID) as ID, vv2.Mitarbeiter_ID
from Versicherungsvertrag vv2
group by vv2.Mitarbeiter_id ) Temp
on Temp.ID = vv.ID
right join Mitarbeiter mi on mi.ID = vv.Mitarbeiter_ID
where mi.Abteilung_ID = ( select ID from Abteilung
where Bezeichnung = 'Vertrieb' );
%%sql
-- rm
SELECT m.ID, m.Name, m.Vorname, v.*
FROM versicherungsvertrag AS v
JOIN mitarbeiter AS m ON m.ID = v.Mitarbeiter_ID
GROUP BY v.Mitarbeiter_ID
ORDER BY v.Abschlussdatum ASC
%%sql
-- ruppert_hartmann
Select mitarbeiter.ID, mitarbeiter.Name, mitarbeiter.Vorname, 
			mitarbeiter.Personalnummer,
			abteilung.Bezeichnung, 
			min(versicherungsvertrag.abschlussdatum), 
            versicherungsvertrag.vertragsnummer
FROM mitarbeiter
LEFT JOIN abteilung ON Abteilung_ID = Abteilung.ID
LEFT JOIN versicherungsvertrag ON versicherungsvertrag.Mitarbeiter_ID = mitarbeiter.ID
WHERE abteilung.Bezeichnung = 'Vertrieb'
GROUP BY mitarbeiter.ID  
result = _
result
Explanation: Lösung
End of explanation
<END_TASK>