Subselects


In [1]:
%load_ext sql

In [49]:
%sql mysql://steinam:steinam@localhost/sommer_2015


Out[49]:
'Connected: steinam@sommer_2015'

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


In [ ]:
%%sql

In [ ]:
%sql select count(*) as AnzahlFahrten from fahrten

In [ ]:

Warum geht kein Join ??



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`


3 rows affected.
Out[24]:
kd_id kd_firma kd_plz AnzAuftrag AnzFahrt SumStrecke
2 Öhlandi None 0 0 None
1 Trapo None 16 16 3199
3 Müller None 1 0 None

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`


18 rows affected.
Out[28]:
kd_id kd_firma kd_plz au_id
1 Trapo None 1
1 Trapo None 1
1 Trapo None 1
1 Trapo None 1
1 Trapo None 1
1 Trapo None 1
1 Trapo None 1
1 Trapo None 1
1 Trapo None 1
1 Trapo None 1
1 Trapo None 1
1 Trapo None 1
1 Trapo None 1
1 Trapo None 1
1 Trapo None 2
1 Trapo None 2
2 Öhlandi None None
3 Müller None 3

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


In [42]:
%sql mysql://steinam:steinam@localhost/winter_2015


Out[42]:
'Connected: steinam@winter_2015'
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`


2 rows affected.
Out[46]:
count(rechnung.`Rg_ID`) Kd_Name
4 Mustermann
2 Peters

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


2 rows affected.
Out[47]:
Kd_Name Anzahl
Peters 2
Mustermann 4

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


In [65]:
%sql -- your code goes here


0 rows affected.
Out[65]:
[]

Lösung


In [54]:
%sql mysql://steinam:steinam@localhost/versicherung_complete


Out[54]:
'Connected: steinam@versicherung_complete'

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`


2 rows affected.
Out[63]:
Erster Abschluss Mitarbeiter_ID
1974-05-03 9
1974-08-07 10

In [59]:
result = _

In [60]:
result


Out[60]:
Erster Abschluss Mitarbeiter_ID
1974-05-03 9
1974-08-07 10