In [2]:
%load_ext sql
In [3]:
%sql mysql://steinam:steinam@localhost/sommer_2014
Out[3]:
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.
In [5]:
%%sql
select * from artikel
where Art_Bezeichnung like '%Schmerzmittel%' or Art_Bezeichnung like '%schmerzmittel%';
Out[5]:
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.
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;
Out[4]:
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
Erstellen Sie eine SQL-Abfrage, die für jeden Artikel Folgendes ermittelt:
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`
Out[7]:
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
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.
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;
Out[9]:
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;
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;
Out[5]:
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
Out[6]:
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;
Out[10]: