In [1]:
%load_ext sql
In [2]:
%sql mysql://steinam:steinam@localhost/sommer_2015
Out[2]:
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 [3]:
%%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[3]:
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 [6]:
%%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`
Out[6]:
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 [8]:
%%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;
Out[8]:
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 [10]:
%sql mysql://steinam:steinam@localhost/winter_2015
Out[10]:
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 [11]:
%%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[11]:
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 [12]:
%%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[12]:
In [13]:
%%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;
Out[13]:
In [14]:
%%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
Out[14]:
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 [2]:
%sql mysql://steinam:steinam@localhost/versicherung_complete
Out[2]:
In [12]:
%%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[12]:
In [10]:
%%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
Out[10]:
In [8]:
%%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' );
Out[8]:
In [6]:
%%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
Out[6]:
In [15]:
%%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
Out[15]:
In [59]:
result = _
In [60]:
result
Out[60]: