In [2]:
%load_ext sql

In [3]:
%sql mysql://steinam:steinam@localhost/sommer_2014


Out[3]:
'Connected: steinam@sommer_2014'

Frage 1

Erstellen Sie eine SQL-Abfrage, die alle Artikel auflistet, deren Artikelbezeichnungen die Zeichenketten "Schmerzmittel" oder "schmerzmittel" enthalten. Zu jedem Artikel sollen jeweils alle Attribute ausgeben werden.

Lösung


In [5]:
%%sql 

select * from artikel
    where Art_Bezeichnung like '%Schmerzmittel%' or Art_Bezeichnung like '%schmerzmittel%';


1 rows affected.
Out[5]:
Art_ID Art_Nummer Art_Bezeichnung Art_Preis
1 IB767126 Bosko_Schmerzmittel 10.5

Frage 2

Erstellenn Sie eine Abfrage, die alle Kunden und deren Umsätze auflistet. Zu jedem Kunden aollen alle Attribute ausgegeben werden. Die Liste soll nach Umsatz absteigend sortiert werden.

Lösung


In [4]:
%%sql 
  select k.Kd_firma, sum(rp.RgPos_Menge * rp.RgPos_Preis) as Umsatz 
    from Kunde k left join Rechnung r
    on k.Kd_Id = r.Rg_Kd_ID
    inner join Rechnungsposition rp
        on r.Rg_ID = rp.RgPos_RgID
    group by k.`Kd_Firma`
    order by Umsatz desc;


4 rows affected.
Out[4]:
Kd_firma Umsatz
STE AG 5067.200000000001
ZOB AG 720.0
SYC AG 380.0
SMA AG 180.0

In [ ]:
%%sql 
-- Originallösung bringt das gleiche Ergebnis

select k.`Kd_Firma`,
(select sum(RgPos_menge * RgPos_Preis)
    from `rechnungsposition` rp, rechnung r 
        where r.`Rg_ID` = `rp`.`RgPos_RgID` and r.`Rg_Kd_ID` = k.`Kd_ID`) as Umsatz
from kunde k order by Umsatz desc

Frage 3

Erstellen Sie eine SQL-Abfrage, die für jeden Artikel Folgendes ermittelt:

  • Die Menge, die insgesamt verkauft wurde
  • Die Anzahl der Rechnungspositionen

Lösung


In [7]:
%%sql
-- meine Lösung
select artikel.*, sum(RgPos_Menge) as Menge, count(RgPos_ID) as Anzahl
from artikel inner join `rechnungsposition`
where `rechnungsposition`.`RgPos_ArtID` = `artikel`.`Art_ID`
group by artikel.`Art_ID`


3 rows affected.
Out[7]:
Art_ID Art_Nummer Art_Bezeichnung Art_Preis Menge Anzahl
1 IB767126 Bosko_Schmerzmittel 10.5 334 2
2 HM67C7 Lidor Kamillentee 3.2 300 2
3 87876234-IK Schnarr Rachendrops 4.0 250 2

In [ ]:
%%sql
-- Leitungslösung

select artikel.* , 
    (select sum(RgPOS_Menge)  from Rechnungsposition rp 
        where rp.RgPos_ArtID = artikel.Art_ID) as Menge,
    (select count(RgPOS_menge)  from Rechnungsposition rp 
        where rp.RgPos_ArtID = artikel.Art_ID) as Anzahl
from Artikel

Frage 4

Deutschland ist in 10 Postleitzahlregionen (0-9, 1. Stelle der PLZ) eingeteilt. Erstellen Sie eine SQl-Abfrage für eine Liste, die für jede PLZ-Region (0-9) den Gesamtumsatz aufweist. Die Liste soll nach Gesamtumsatz absteigend sortiert werden.

Lösung


In [9]:
%%sql
-- Original
select left(kunde.`Kd_PLZ`,1) as Region,
    sum(`rechnungsposition`.`RgPos_Menge` * `rechnungsposition`.`RgPos_Preis`) as Summe
    from kunde left join rechnung
        on kunde.`Kd_ID` = rechnung.`Rg_Kd_ID`
    left join rechnungsposition 
        on `rechnung`.`Rg_ID` = `rechnungsposition`.`RgPos_RgID`
group by Region
order by Summe;


4 rows affected.
Out[9]:
Region Summe
7 180.0
8 380.0
6 720.0
9 5067.200000000001

In [ ]:
%%sql
-- Inner join ändert nichts
select left(kunde.`Kd_PLZ`,1) as Region,
    sum(`rechnungsposition`.`RgPos_Menge` * `rechnungsposition`.`RgPos_Preis`) as Summe
    from kunde inner join rechnung
        on kunde.`Kd_ID` = rechnung.`Rg_Kd_ID`
    inner join rechnungsposition 
        on `rechnung`.`Rg_ID` = `rechnungsposition`.`RgPos_RgID`
group by Region
order by Summe;

Heiko Mader

O-Ton: ich glaube es ist richtig :-)

Aufgabe 2 Syntax geht, aber Ergebnis stimmt nicht


In [5]:
%%sql
select kunde.*, umsatz from kunde
	inner join (
    	select (RgPos_menge * RgPos_Preis) as Umsatz, kd_id
        	from `rechnungsposition`
            	inner join rechnung on `rechnungsposition`.`RgPos_ID` = `rechnung`.`Rg_ID`
                inner join kunde on `rechnung`.`Rg_Kd_ID` = Kunde.`Kd_ID`
            
    group by `Kd_ID`
    ) a
    on Kunde.`Kd_ID` = a.Kd_ID
    order by umsatz desc;


4 rows affected.
Out[5]:
Kd_ID Kd_Firma Kd_Strasse Kd_PLZ Kd_Ort umsatz
1 STE AG None 98765 None 2527.2000000000003
4 ZOB AG None 65432 None 380.0
2 SYC AG None 87654 None 180.0
3 SMA AG None 76543 None 180.0

Aufgabe 3


In [6]:
%%sql

select a.*, mengeGesamt,anzahlRechPos
from artikel a
Inner join (
	select SUM(RgPos_menge) as mengeGesamt, art_id
    from `rechnungsposition` inner join artikel 
    	on `rechnungsposition`.`RgPos_ArtID` = artikel.`Art_ID`
        group by art_id
        ) b on a.`Art_ID` = b.art_id
        Inner join 
        (select count(*) as anzahlRechPos, art_id
        	from `rechnungsposition` inner join artikel
            	    	on `rechnungsposition`.`RgPos_ArtID` = artikel.`Art_ID`
        group by art_id
        ) c on a.`Art_ID` = c.art_id


3 rows affected.
Out[6]:
Art_ID Art_Nummer Art_Bezeichnung Art_Preis mengeGesamt anzahlRechPos
1 IB767126 Bosko_Schmerzmittel 10.5 334 2
2 HM67C7 Lidor Kamillentee 3.2 300 2
3 87876234-IK Schnarr Rachendrops 4.0 250 2

Aufgabe 4

Original von H.M ergibt fehler


In [ ]:
%%sql

select gebiet, umsatz from `kunde`
	inner join (
    	select kd_plz as gebiet, kd_id from `kunde`	
        where kd_plz in 
        (0%,1%,2%,3%,4%,5%,6%,7%,8%,9%)
        group by kd_id
        ) a on kunde.`Kd_ID` = b.kd_id
    inner join (
    	select rgPos_Menge * rgPos_Preis as Umsatz2, kd_id
        	from `rechnungsposition` inner join
            	rechnung on `rechnungsposition`.`RgPos_RgID` = rechnung.`Rg_ID`
            inner join kunde on `rechnung`.`Rg_Kd_ID` = kunde.`Kd_ID`
            group by kd_id
        ) b on `kunde`.`Kd_ID` = b.kd_id
    order by umsatz desc;

Leichte Änderungen führen zu einem "fast richtigen" Ergebnis

er multipliziert dabei aber nur den jeweils ersten Datensatz aus der Rechnungsposition-Tabelle (siehe 2527,2) für PLZ 9 das wird auch bei der Aufgabe 3 ein möglicher fehler sein, der fällt aber da nicht evtl. auf ???


In [10]:
%%sql
select gebiet, umsatz from `kunde`
	inner join (
    	select kd_plz as gebiet, kd_id from `kunde`	
        where left(kd_plz,1) in 
        (0,1,2,3,4,5,6,7,8,9)
        group by kd_id
        ) a on kunde.`Kd_ID` = a.kd_id
    inner join (
    	select rgPos_Menge * rgPos_Preis as Umsatz, kd_id
        	from `rechnungsposition` inner join
            	rechnung on `rechnungsposition`.`RgPos_RgID` = rechnung.`Rg_ID`
            inner join kunde on `rechnung`.`Rg_Kd_ID` = kunde.`Kd_ID`
            group by kd_id
        ) b on `kunde`.`Kd_ID` = b.kd_id
    order by umsatz desc;


4 rows affected.
Out[10]:
gebiet umsatz
98765 2527.2000000000003
65432 720.0
87654 380.0
76543 180.0