%load_ext sql

"""
Explanation: Unterricht zur Kammerprüfung
End of explanation
"""


%sql mysql://steinam:steinam@localhost/sommer_2014

"""
Explanation: Sommer_2014
End of explanation
"""


%%sql 

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

"""
Explanation: 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
End of explanation
"""


%%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;

%%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

"""
Explanation: Frage 2
Erstellen 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
End of explanation
"""


%%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`

%%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

"""
Explanation: 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
End of explanation
"""


%%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;

%%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;

"""
Explanation: 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
End of explanation
"""


%%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; 

"""
Explanation: Heiko Mader
O-Ton: ich glaube es ist richtig :-)
Aufgabe 2
Syntax geht, aber Ergebnis stimmt nicht
End of explanation
"""


%%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


"""
Explanation: Aufgabe 3
End of explanation
"""


%%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;

"""
Explanation: Aufgabe 4
Original von H.M ergibt fehler
End of explanation
"""


%%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 sum(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;


%%sql
select a.*, sum(rp.RgPos_Menge) as "MengeGesamt", 
count(rp.RgPos_ArtId) as "AnzahlRechPos"
from artikel a
inner join RechnungsPosition rp
on rp.RgPos_ArtId = a.Art_Id
group by Art_ID


"""
Explanation: 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 ???
End of explanation
"""


