Lösung Fragezeichenfolien

P
7. Einfache Auswertungen aus der Datenbank (select)
[…] 7.3 Projektion
Erzeuge eine Übersicht aller existierender Warengruppen
Select distinct warengruppe
from artikel
Datenbanken 1
- WS 2007/2008 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 1
P
7.4 Selektion (where-Klausel)
[…] 7.4.2 Bereichsprüfung mittels between
Nenne Rechnungsnummer, Kundennummer und
Rechnungsbetrag aller Rechnungen, die nicht länger als 3
Monate fällig sind und noch nicht bezahlt wurden !
Select Rechnung_nr, kunde_nr, rechnung_betrag
From rechnungen
Where bezahlt=0 and datum_faellig between current_date and
current_date-90
Datenbanken 1
- WS 2007/2008 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 2
P
7.4 Selektion (where-Klausel)
7.4.3 Operation IN / NOT IN
Mächtig, wenn die Prüfung auf Enthalten-Sein nicht auf einer
konstanten sondern einer berechneten Menge basiert:
Welche Kunden (Kundennummer, Name) haben in 2005 noch
keinen Auftrag erteilt ?
Select kunde_nr, name
From kunden
Where kunde_nr not in
(select kunde_nr from auftraege where extract(year from auftragsdatum) = 2005)
Datenbanken 1
- WS 2007/2008 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 3
P
7.4 Selektion (where-Klausel)
7.4.3 Operation IN / NOT IN
Erzeuge eine Liste mit Artikelnummer und Bezeichnung aller
Artikel, die im Jahre 2004 verkauft wurden
Select artikel_nr, bezeichnung
From artikel
Where artikel_nr in
(select artikel_nr from auftragspositionen where extract(year from termin)=2004)
Datenbanken 1
- WS 2007/2008 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 4
P
7.4 Selektion (where-Klausel)
[…] 7.4.4 Operator Like <Muster>:“Matchen“ von Zeichenketten
Beispiel:
Nenne die Artikelnummer aller Artikel, in deren Bezeichnung die
Kennworte ‘Digital‘ und ‘Kamera‘ vorkommen.
Select artikel_nr
from artikel
where bezeichnung like ‚%Digital%‘ and bezeichnung like ‚%Kamera%‘
Datenbanken 1
- WS 2007/2008 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 5
P
7.4 Selektion (where-Klausel)
[…] 7.4.4 Operator Like <Muster>:“Matchen“ von Zeichenketten
Beispiel:
Nenne die Artikelnummer aller Artikel, in deren Bezeichnung die
Kennworte ‘Digital‘ und ‘Kamera‘ in dieser Reihenfolge
vorkommen.
Select artikel_nr
from artikel
where bezeichnung like ‚%Digital%Kamera%‘
Datenbanken 1
- WS 2007/2008 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 6
P
[..] 7.5 Sortierung (order by – Klausel)
Geben Sie die Artikel geordnet nach ihrer Bezeichnung aus.
Artikel mit leerer Bezeichnung sollen am Ende stehen.
select *
from artikel
order by case when bezeichnung is null then ‘ZZZZ‘ else
bezeichnung end
Datenbanken 1
- WS 2007/2008 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 7
P
7.9 Gruppierungen von Daten
[…] 7.9.1 Gruppenfunktionen
Was ist die maximale Gewinnspanne im Artikelbereich ?
Select max(verkaufspreis - einkaufspreis)
From artikel;
Datenbanken 1
- WS 2007/2008 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 8
P
7.9 Gruppierungen von Daten
[…] 7.9.1 Gruppenfunktionen
Was ist die maximale Gewinnspanne im Artikelbereich ? Wenn
sie größer ist als das zweifache des größten Einkaufpreises,
schreiben Sie ‘!!!‘ dahinter.
Select
case when max(verkAUFSPREIS-einkaufspreis) > 2*max(einkaufspreis)
then cast( max(verkAUFSPREIS-einkaufspreis) as char(10) ) || ‚!!‘
else cast( max(verkAUFSPREIS-einkaufspreis) as char(10))
end
From artikel;
Datenbanken 1
- WS 2007/2008 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 9
P
7.9 Gruppierungen von Daten
[…] 7.9.2 Gruppieren von Daten (group by)
Ermittle je Kunde (Nummer) die Anzahl seiner Aufträge im Jahr
2005 !
Select kunde_Nr, count(*)
From auftraege
Where extract (year from auftrag_datum) = 2005
Group by 1
Datenbanken 1
- WS 2007/2008 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 10
P
7.9 Gruppierungen von Daten
[…] 7.9.2 Gruppieren von Daten (group by)
Ermittle die Summe der Rechnungsbeträge der in den Jahren
2002-2006 gestellten Rechnungen gestaffelt nach Jahr.
Select extract(year from rechnung_datum), sum(rechnung_betrag)
From rechnungen
Where extract(year from rechnung_datum) between 2002 and 2006
Group by extract(year from rechnung_datum)
Datenbanken 1
- WS 2007/2008 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 11
P
7.9 Gruppierungen von Daten
[…] 7.9.2 Gruppieren von Daten (group by)
Ermittle den größten erzielten Verkaufspreis je Artikel je
Verkaufsjahr (auftragspositionen, termin)
select artikel_nr, extract( year from termin), max(preis)
From auftragspositionen
group by artikel_nr, extract( year from termin)
order by 1;
Datenbanken 1
- WS 2007/2008 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 12
P
7.9 Gruppierungen von Daten
[…] 7.9.3 Einschränkungen der Aggregierung (having)
Listen Sie je Kunde die Kundennummer sowie das Fälligkeitsdatum
(datum_faellig) seiner unbezahlten Rechnung mit der längsten
Überfälligkeit auf (Überfälligkeit = fällig und nicht bezahlt). Führen Sie nur
Kunden auf, bei denen dieses Fälligkeitsdatum um mehr als 2 Wochen
überschritten ist.
select kunde_nr, min(datum_faellig) "Längste Überfälligkeit"
from rechnungen
where bezahlt=0 and datum_faellig <= current_date
group by kunde_nr
having min(datum_faellig) < current_date -14;
Datenbanken 1
- WS 2007/2008 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 13
P
7.9 Gruppierungen von Daten
[…] 7.9.3 Einschränkungen der Aggregierung (having)
Listen Sie die Auftragsnummer und den größten Positionstermin je
Auftrag auf. Geben Sie nur dann die Daten aus, wenn der größte
Positionstermin älter als ein Jahr ist.
Select auftrag_nr, max(termin)
From auftragspositionen
Group by auftrag_nr
Having current_date – max(termin) > 365;
Datenbanken 1
- WS 2007/2008 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 14
P
[…] 8.3 Unterabfragen (mit Variablen)
Listen Sie alle Aufträge auf (Auftragsnummer, Kunde_nr), bei denen der
größte Liefertermin (Feld termin in den Auftragspositionen) sich vom
Auftragsdatum um mehr als 1 Jahr unterscheidet.
select a.auftrag_nr , kunde_nr
from auftraege a
where abs(auftrag_datum – (select max(termin) from
auftragspositionen b where a.auftrag_nr=b.auftrag_nr) ) > 365
Datenbanken 1
- WS 2007/2008 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 15
P
[…] 8.3 Unterabfragen (mit Variablen)
Nenne je Branche den Kunden aus der Branche, der den höchsten
Jahresumsatz hat.
select branche, kunde_nr, jahresumsatz
from kunden k2
where jahresumsatz=
(select max(jahresumsatz) from kunden k1 where k1.branche=k2.branche);
Datenbanken 1
- WS 2007/2008 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 16
P
[…] 8.4 Weitere Vergleiche mit Subqueries (all, any, exist)
Listen Sie alle Artikel (artikel_nr) auf, die im Jahr 2005 noch nicht verkauft
wurden (Datum Termin in Tabelle Auftragspositionen).Lösung über exists.
Select artikel_nr
From artikel a
Where not exists
(Select artikel_nr
from auftragspositionen p
Where extract(year from auftrags_datum)= 2005
And a.artikel_nr = p.artikel_nr)
Datenbanken 1
- WS 2007/2008 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 17
PJoin
[…] 9.3/4 Join
Listen Sie für alle Artikel die Artikelnummer auf, den Inhalt des Attributs
„bestand_lager“ aus der Tabelle „artikel“ sowie die Summe der
Lagerbestände jedes Artikels auf allen Lagerplätzen. Wenn ein Artikel
keinen Bestand auf Lagerplätzen hat, schreiben Sie stattdessen „kein
Lagerplatzbestand“ in die entsprechende Spalte.
select a.artikel_nr, a.bestand_lager, case when
isnull(sum(ar.bestand_lager),0)=0 then ‚kein lagerplatzbestand’ else cast
(sum(ar.bestand_lager) as char(25) ) end
from artikel a left outer join artikel_lager ar on a.artikel_nr=ar.artikel_nr
group by 1,2
Datenbanken 1
- WS 2007/2008 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 18
P
[…] 9.3/4 Join
Artikel können zu einem Preis verkauft werden (Preis in Tabelle
auftragspositionen), der vom Normalpreis abweicht (Verkaufspreis in
Tabelle artikel).
Listen Sie für alle Artikel, die in Auftragspositionen vorkommen, über alle
Positionen hinweg die durchschnittliche Abweichung des Verkaufspreises
vom Normalpreis auf.
Select a.artikel_nr, avg(abs(preis-verkaufspreis))
From auftragspositionen a join artikel b on a.artikel_nr=b.artikel_nr
Group by a.artikel_nr
Datenbanken 1
- WS 2007/2008 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 19
P
[…] 9.3/4 Join
Erzeugen Sie eine Liste, die je Warengruppe die Anzahl aktiver
Positionen angibt, in denen Artikel mit dieser Warengruppe vorkommen.
Berücksichtigen Sie nur Warengruppen mit mehr als 10 Positionen.
Ordnen Sie die Ausgabe nach der Positionszahl absteigend.
Select warengruppe, count(*)
From artikel join auftragspositionen on
artikel.artikel_nr=auftragspositionen.artikel_nr
Where auftragspositionen.aktiv = 1
Group by 1
Having count(*) > 10
Order by count(*) desc
Datenbanken 1
- WS 2007/2008 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 20
P Outer
[…] 9.5 Der Outer – Join
Ermitteln Sie aus den Daten die Summe der Auftragswerte aller Aufträge
je Kunde, die diese im Jahr 2005 (auftragdatum)erteilt haben
(Auftragswert= Summe verkaufsmenge * verkaufspreis über alle
Positionen des Auftrages). Geben Sie die Kundennummer, den Namen
sowie die Auftragswertsumme aus. Berücksichtigen Sie auch Kunden, die
im Jahr 2005 keine Aufträge erteilt haben.
select k.kunde_nr, k.name, sum(p.preis*p.menge)
from kunden k left outer join
(auftraege a join auftragspositionen p on a.auftrag_nr=p.auftrag_nr)
on k.kunde_nr = a.kunde_nr
and extract(year from a.auftrag_datum) = 2005
group by k.kunde_nr, k.name
Datenbanken 1
- WS 2007/2008 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 21
P Outer
[…] 9.5 Der Outer – Join
Ermitteln Sie je Kunde die Anzahl der für den Kunden
geschriebenen Rechnungen, sowie die Summe der in Mahnung
befindlichen Rechnungswerte . Gemahnt wird immer die
Differenz aus Rechnungsbetrag und Zahlbetrag. Eine Rechnung
ist in Mahnung, wenn sie ein Mahndatum hat und nicht bezahlt
ist. Führen auch Kunden auf, zu denen es keine Rechnung gibt.
Select kunde_nr, name, count(r.kunde_nr),
sum(case when datum_mahnung is not null and nvl(bezahlt,0)=0
then rechnungbetrag-zahlbetrag_gesamt else 0 end )
From kunden k left outer join rechnungen r on k.kunde_nr=r.kunde_nr
Group by 1,2
Datenbanken 1
- WS 2007/2008 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 22
P View
[…] 11.2 Anwendungsbereiche von Views
Ermitteln Sie je Vertreter die Anzahl der von ihm abgeschlossenen
Aufträge im Jahr 2004 sowie den Wert dieser Aufträge (Produkt
Menge*Preis der betreffenden Auftragspositionen). Tipp: Benutzen Sie
einen Hilfs-View!
Datenbanken 1
- WS 2007/2008 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 23
P View
[…] 11.2 Anwendungsbereiche von Views
create view awert ( vert_nr, wert) as
select vertreter_nr, sum(menge*preis) from auftraege join
auftragspositionen on auftraege.auftrag_nr =
auftragspositionen.auftrag_nr where year(auftrag_datum)=2004
group by 1
select vertreter_nr, wert, count(*)
from auftraege join awert on vertreter_nr=vert_nr) where
year(auftrag_datum)=2004
group by 1,2
Datenbanken 1
- WS 2007/2008 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 24
P View
[…] 11.2 Anwendungsbereiche von Views
Ermitteln Sie je in den Kunden vorkommenden Vertreter die Anzahl der
von ihm abgeschlossenen Aufträge im Jahr 2005 (auftrag_datum), die
Summe der Rechnungsbeträge aller Rechnungen, die aus Aufträgen des
Vertreters hervorgegangen sind (nur Rechnungen des Jahres 2005
berücksichtigen) sowie die Summe der offenen Beträge
(rechnungsbetrag-zahlbetrag_gesamt) aus den unbezahlten Rechnungen
(auch nur 2005), die dem Vertreter zuzuordnen sind. Führen Sie auch
Vertreter auf, zu denen es keine Aufträge bzw. Rechnungen in 2005 gibt
(zu jeder Rechnung muss es einen Auftrag geben !). (Hinweis: Die
Aufgabe ist nur mit views lösbar, beginnen Sie mit einem view, der alle
Vertreter ermittelt)
Datenbanken 1
- WS 2007/2008 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 25
P View
[…] 11.2 Anwendungsbereiche von Views
Create view summrech (vertreter_nr, summrech) as
Select k.vertreter_nr, sum(r.rechnung_betrag)
From kunden k join rechnungen r on k.kunde_nr=r.kunde_nr
year(rechnung_datum)=2005
Where
Group by 1
Create view summoffrech (vertreter_nr, summoffrech) as
Select k.vertreter_nr, sum(r.rechnungsbetrag-r.zahlbetrag_gesamt)
From kunden k join rechnungen r on k.kunde_nr=r.kunde_nr
Where year(rechnung_datum)=2005 and bezahlt=0
Group by 1
Select a.vertreter_nr, anzahl, summrech, summoffrech
From alle_vertreter a. left outer join
((anzauft t1 left outer join summrech t2 on t1.vertreter_nr=t2.vertreter_nr)
join summoffrech t3 on t2.vertreter_nr=t3.vertreter_nr)
on a.vertreter_nr = t1.vertreter_nr
Datenbanken 1
- WS 2007/2008 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 26