In [1]:
%load_ext sql
In [49]:
%sql mysql://steinam:steinam@localhost/sommer_2015
Out[49]:
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.
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;
In [50]:
%%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;
Out[50]:
In [26]:
%sql select count(*) as AnzahlFahrten from fahrten
Out[26]:
In [ ]:
Warum geht kein Join ??
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`
In [24]:
%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`
Out[24]:
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
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`
In [28]:
%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`
Out[28]:
Hinweis: In Rechnung gibt es zusätzlich ein Feld Rechnung.Kd_ID
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.
In [42]:
%sql mysql://steinam:steinam@localhost/winter_2015
Out[42]:
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`
In [46]:
%%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`
Out[46]:
Es geht auch mit einem Subselect
select kd.`Kd_Name`,
(select COUNT(*) from Rechnung as R
where R.`Rg_KD_ID` = KD.`Kd_ID` and 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
In [47]:
%%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
Out[47]:
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.
In [65]:
%sql -- your code goes here
Out[65]:
In [54]:
%sql mysql://steinam:steinam@localhost/versicherung_complete
Out[54]:
In [63]:
%%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`
Out[63]:
In [59]:
result = _
In [60]:
result
Out[60]: